How can I repeat a mysql query every 5 minutes?

Question

I have an sql query that looks like this:

<?php
    include("settings.php");

    $sql = conn->query("SELECT * FROM table LIMIT 1");

    $content = $sql->fetch_assoc();

    $id = $content['id'];

    /* PHP Operations here. */
    ?>

Now, I want to be able to repeat this sql query every lets say 5 minutes, because the tables content will change. How can I do this? Is there a way to do an sql request in a javascript or ajax script?

I need to get the first row of this table into a variable. When the table changes, the variable need to change to it aswell. I need a solution.. — Samil Karahisar, Feb 27 '16 at 18:19
This is a cron jobs. Or you can make a function that run in every 5 min. Ajax will be good. Settimeinterval there. — Murad Hasan, Feb 27 '16 at 18:21
I want to store the data inside a $variable in php. And isn't ajax a way to load content of a file, how can I use it for mysql queries? Can you give me an exemple please? — Samil Karahisar, Feb 27 '16 at 18:23
Now I am sleeping, I can do it next day at my end. After 9 hour. — Murad Hasan, Feb 27 '16 at 18:26
I will wait Frayne, noproblem. It would be kind of you to remember helping me. — Samil Karahisar, Feb 27 '16 at 18:27
@trincot, The table is very simple, there are only usernames, one simple column thats all. — Samil Karahisar, Feb 27 '16 at 18:28
The script you provided above has errors. `conn`? That will not work. — trincot, Feb 27 '16 at 18:28
conn is a variable inside db_settings.php that I included to this php file. The code works fine actually. But I need to repeat it every 5 minutes. What error do you see? — Samil Karahisar, Feb 27 '16 at 18:30

score 2 · Answer 1 · answered Feb 27 '16 at 18:24

2

If you just want to refresh content, then use an AJAX call in a settimeout().

answered Feb 27 '16 at 18:24

BDS

200
10

I only know how to use ajax to load a files content, for exemple the content of a txt file. But I don't know how to implement it to AJAX. Could you please show me? – Samil Karahisar Feb 27 '16 at 18:25
Here you have a good [example](http://stackoverflow.com/questions/8567114/how-to-make-an-ajax-call-without-jquery) – BDS Feb 27 '16 at 18:29
in the example you gave:$.ajax({ url: "test.html", context: document.body, success: function(){ $(this).addClass("done"); } }); this ajax code, loads a html file, I need to do a mysql request. This is why I don't understand how to use AJAX for my purpose. – Samil Karahisar Feb 27 '16 at 18:54
In an AJAX call you can make a request to a PHP page either. A page where you perform your MySQL queries. In the above example just replace test.html with your PHP page name. – BDS Feb 28 '16 at 21:13

score 0 · Answer 2 · answered Feb 27 '16 at 18:31

0

Solution

Use a cron job. If the file you are showing is "/var/www/some/file.php" then just execute the following as the user which runs the web server (for example, "www-data" or "apache"):

{
    # Will output to stdout the current user's crontab
  crontab -l;
    # Add a line to execute the php script every 5 minutes
  echo '*/5 * * * * '"$(which php5) /var/www/some/file.php";
} | crontab - # The current user's crontab will be replaced by stdin

Assumptions

You're using Linux.
You're using apache httpd.

Background

PHP web applications are PHP scripts which are run by the web server in response to an HTTP request. What you need in here is to run the PHP script not in response to an HTTP request but in response to a time event, say, 5 minutes have passed since the last run.

answered Feb 27 '16 at 18:31

Diego Augusto Molina

231
2
7

Yes, but than every 5 minutes the user will see the page refreshing right? But I don't want that. I want it to be a "dynamic" thing. I want to reload the data, without refreshing the page. – Samil Karahisar Feb 27 '16 at 18:44
This is an unattended solution, this won't necessarily have any effect on any particular user. You have just said you want a "dynamic" thing. For that it is more appropiate an asynchronous request as suggested by others like "setTimeout(function(){ make_request(); },300);" called in the "onload" event of the document (or anywhere else you need it). – Diego Augusto Molina Feb 27 '16 at 18:58
yeah, well the problem is, I can do that with javascript. But you can't have mysql queries inside javascript code right? Than how will I? Sorry if I am being annoying, but I am quite a beginner. – Samil Karahisar Feb 27 '16 at 19:03
I get you. You should rephrase your question. I will try to answer in a new answer. – Diego Augusto Molina Feb 27 '16 at 19:08

Diego Augusto Molina · Answer 3 · 2016-02-28T16:04:46.840

Well, according to the Q&A the real problem would have a solution like this:

Files

You have one PHP file which is directly requested by the user, which I will call "/index.php", located at "/var/www/index.php". This has the presentation of the page and some javascript.
You have a second PHP file which is requested by the user indirectly, which I will call "/bg.php", located at "/var/www/bg.php". This file's content only has the routines to update the database and returns javascript code or something else.

index.php

Here you will have something like this:

<script type="text/javascript">
/* Have jquery included before */

function poll_the_server(){
    /* Get the data from the server with ajax */
  var jqxhr = $.ajax( "/bg.php" )
    .always(function(){
         /* Prepare the next run */
        setTimeout(poll_the_server, 300);
      })
    .done(function(){
        /* Succeded: Do whatever you want with your data */
      })
    .fail(function(){
        /* Error: you may ask the user what to do */
      });
}

  /* Do the first call */
setTimeout(poll_the_server, 300);
</script>

Edit:

References

http://api.jquery.com/jquery.ajax/

So its inside bg.php that I do my mysql query right? index.php connects there and retrieves the information into jqxhr? Correct me if I am wrong. And thank you very much for your time. — Samil Karahisar, Feb 27 '16 at 19:59
That is correct, @SamilKarahisar . It's "bg.php" the one which connects to the database, possibly retrieving something from it, and then passes back to the main program, "index.php". The request and the processing of the results (the functions for "always", "done" and "fail") are done asynchronously, so the user sees no loading and has a smooth experience. See the reference in the edited text for more information about (a)synchronous requests in the jqxhr object. — Diego Augusto Molina, Feb 28 '16 at 16:03