Execute PHP Script while website is offline or no one using my website

Question

I am trying to develop a small webpage which just has to generate random number and post that generated number to MySQL database. it's a simple script which I got from various user's answer and help over the internet. thanks to them . everything is working right but I have a small issue while executing script, when I open that webpage, it generate number and post to Database, but when I close webpage , it stop to generate number, I tried searching everywhere but didn't get actual solution that I want. following are code I am using . Thanks for your help

<?php

include_once 'database.php';

if ($conn) {
    echo "success<br>";
}

$digits = 3;

$result = rand(pow(10, $digits-1), pow(10, $digits)-1);
//this is sum
$num = $result;  
$sum=0;
$rem=0;  
  for ($i =0; $i<=strlen($num);$i++)  
 {  
    $rem=$num%10;  
    $sum = $sum + $rem;  
    $num=$num/10;  
    $total = substr($sum, -1);
     
 }  
 
 //echo "Sum of digits $result is $sum"; 

$finres = $result.'-'.$total;
// $result = mysql_real_escape_string($result);
// $total = mysql_real_escape_string($total);


// while( $digits = 3 )
// {
//     echo $result;
//    sleep(10);
// }

?>
<!DOCTYPE html>
<html lang="en">
<head>

    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="refresh" content="5" > 
    <title>Ghanta Bazar</title>
</head>
<body>

<p><?php echo $finres; ?></p>
<?php 

$sql = "INSERT INTO ghanta_result (result)
VALUES ($finres)";

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}


?>
    
</body>
</html>

You are open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized prepared statements instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) and [MySQLi](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even by trusted users, [you are still at risk of corrupting your data](https://bobby-tables.com/). [Escaping is not enough](https://stackoverflow.com/q/5741187). — Jason K, Nov 28 '22 at 16:42
can you explain how you are running the script? and what you mean by "when I close webpage"? — ysth, Nov 28 '22 at 16:42
@JasonK thanks for your reply, actually i am an Android Developer and newbie to php but I will do what you suggested me. — Nilesh Singh Dahiya, Nov 28 '22 at 16:44
@ysth thanks for your reply, i hosted my webpage in a shared hosting and i can access using it url. number generated till webpage opened in web browser, after that it stops generating and adding data to Database — Nilesh Singh Dahiya, Nov 28 '22 at 16:47
@MarkusZeller thanks for your reply , actually i am newbie to it, can you please guide me further, if possible? — Nilesh Singh Dahiya, Nov 28 '22 at 16:48
You can use PHP from command line as well. Also known as PHP-cli. https://www.php.net/manual/en/features.commandline.php — Markus Zeller, Nov 28 '22 at 16:50
Generally when a php page is called. It runs once and is done. So when you are viewing it on the browse. The php program is no longer running. — Jason K, Nov 28 '22 at 16:51
You could likely create the number generator as a function in mysql and then schedule that to be called within an `event` - thus negating the need for offline state or no active web clients etc — Professor Abronsius, Nov 28 '22 at 16:54
@JasonK i've added auto refresh function so each time page reloads (eg :- for each 5 seconds) ,it generate a number and adds into database. and i want that it generate continue even nobody is loading or reloadng that page. — Nilesh Singh Dahiya, Nov 28 '22 at 17:03
@MarkusZeller thanks, i will read the documentation . thanks for it. — Nilesh Singh Dahiya, Nov 28 '22 at 17:04
@ProfessorAbronsius i think this could be solution for what i actually needed. let me give it a try. — Nilesh Singh Dahiya, Nov 28 '22 at 17:05
Or use cron (or any other task scheduling tool supported on your platform) for regularly running a php script. — Shadow, Nov 28 '22 at 17:48
@JasonK The script is not open to SQL injections. Dont just scream SQL injection because someone uses string interpolation in SQL query. He is also using `mysql_real_escape_string` so even if `$finres` is malicious it wouldnt execute the SQL. — Code Spirit, Nov 29 '22 at 02:41
How often does your script need to run? Once a day? Once an hour? Every minute? — Professor Abronsius, Nov 29 '22 at 09:01
@ProfessorAbronsius for each hour in a day between 09 AM to 09 PM. — Nilesh Singh Dahiya, Nov 29 '22 at 20:32

score 1 · Accepted Answer · answered Nov 29 '22 at 09:27

Per the comment regarding regarding using a scheduled Event entirely within the database this is how you might do that and remove the need for a separate PHP script.

To turn the event scheduler ON you can run this query:

SET GLOBAL event_scheduler = ON;

Or, better - within the mysqli.ini or .conf file:

event_scheduler = ON;

Given a very basic table structure for ghanta_result:

mysql> describe ghanta_result;
+-----------+-------------+------+-----+-------------------+-------+
| Field     | Type        | Null | Key | Default           | Extra |
+-----------+-------------+------+-----+-------------------+-------+
| result    | varchar(50) | YES  |     | NULL              |       |
| timestamp | timestamp   | YES  |     | CURRENT_TIMESTAMP |       |
+-----------+-------------+------+-----+-------------------+-------+
2 rows in set (0.01 sec)

And a simple random number generator function:

/*********************************************************
    utility function to generate a pseudo random number
    between low & high numbers where l=low & h=high
*/
CREATE FUNCTION `fn_mt_rand`(
    `l` INT,
    `h` INT
)
    RETURNS int(11)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin
    return round( ( rand() * ( h - l ) ) + l );
end

And the PHP string generator as a mysql function:

/**********************************************************
    function to replicate the PHP string/number generator
    as per the question.
*/
CREATE FUNCTION `fnRandomNumberGenerator`(
    `p_digits` INT
)
    RETURNS varchar(6) CHARSET latin1
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin
    declare _digits integer default 0;
    declare _result integer default 0;
    declare _i integer default 0;
    declare _len integer default 0;
    declare _low integer default 0;
    declare _high integer default 0;
    
    
    declare _sum integer default 0;
    declare _rem integer default 0;
    declare _num integer default 0;
    declare _total integer default 0;
    
    set _i=0;
    set _digits=cast( p_digits as unsigned );
    
    set _low=pow( 10, ( _digits - 1 ) );
    set _high=pow( 10, _digits );
    
    set _result=fn_mt_rand( _low - 1, _high );
    set _num=_result;
    set _len=length( _result );
    
    
    set _sum=0;
    set _rem=0;
    set _total=0;
    
    
    while( _i < _len ) do
        set _rem=_num % 10;
        set _sum=_sum + _rem;
        set _num=_num / 10;
        set _total=substr( _sum, -1 );
        
        set _i=_i + 1;
    end while;
    

    return concat( _result, '-', _total );
end

Could be combined and used in a new event that would be run to whatever schedule you specify

/*************************************************
    The scheduled event to run every N minutes
    or whatever the schedule is specified to be
*/
CREATE EVENT `evtGhantaResult`
    ON SCHEDULE
        EVERY 1 MINUTE STARTS '2022-11-29 09:15:00'
    ON COMPLETION PRESERVE
    ENABLE
    COMMENT ''
    
DO begin
    insert into `ghanta_result` ( `result` ) values ( fnRandomNumberGenerator( 3 ) );
end

Having run for a few minutes:

mysql> select * from ghanta_result;
+--------+---------------------+
| result | timestamp           |
+--------+---------------------+
| 137-2  | 2022-11-29 09:15:00 |
| 848-2  | 2022-11-29 09:16:00 |
| 108-0  | 2022-11-29 09:17:00 |
| 716-5  | 2022-11-29 09:18:00 |
| 804-2  | 2022-11-29 09:19:00 |
+--------+---------------------+
5 rows in set (0.00 sec)

let me check it out – Nilesh Singh Dahiya Nov 29 '22 at 20:34 — Nilesh Singh Dahiya, Nov 29 '22 at 20:34

Execute PHP Script while website is offline or no one using my website

1 Answers1