Query MySQL via PHP inside JavaScript with time interval

Question

Basically I want to query MySQL database using an external PHP script. I want this script to be called every 2 seconds. This 2 seconds interval are initiated with a javascript (jquery, flot), which is as follows:

<script type='text/javascript'>
    var data = [];
    var dataset;
    var totalPoints = 50;
    var updateInterval = 1000;
    var now = new Date().getTime();

    function GetData() {
        data.shift();
        while (data.length < totalPoints) {
            var y;

            $.ajax({
                url: 'current_api.php', 
                success: function(currentValue){
                    y = currentValue;
                    console.log(y);
                },
            });

            var temp = [now += updateInterval, y];
            data.push(temp);
        }
    }

    var options = {
        ...
    }

    $(document).ready(function () {
        GetData();

        dataset = [
            { label: "CURRENT READING", data: data }
        ];

        $.plot($("#flot-line-chart"), dataset, options);

        function update() {
            GetData();

            $.plot($("#flot-line-chart"), dataset, options)
            setTimeout(update, updateInterval);
        }

        update();
    });
</script>

Currently, Im getting a NULL value at console.log(y);. My PHP script (current_api.php) that handles MySQL queries is as simple as follows:

<?php
    require "dbCon.php";    // database credentials
    $databaseName = "MAIN";
    $tableName = "day"

    // OPEN MYSQL DATABASE CONNECTION IN PHP
    $dbs = mysql_select_db($databaseName, $con);

    // FETCH DATA FROM MYSQL DATABASE
    $sql = "SELECT value FROM $tableName ORDER BY id DESC LIMIT 1";
    $result = mysql_query($sql);

    header('Content-Type: application/json');
    while ($row = mysql_fetch_assoc($result)) {
        $currentValue = (int) round($row['value']);
    }
    echo json_encode($currentValue);

    // CLOSE THE DB CONNECTION
    mysql_close($con);
?>

I'm new with AJAX and does not know if what I'm trying to do is possible. Can someone help me to debug why i'm getting a NULL value? Thanks in advance.

Why are you using a `while` loop to fetch the query results, when it can only return one row? — Barmar, Feb 04 '16 at 09:12
Why not using the HTTP cache? A HEAD query with the eTag header. And you can optimize your SQL query: `SELECT value FROM $tableName ORDER BY id DESC LIMIT 1` — C Würtz, Feb 04 '16 at 09:13
You shouldn't escape the database name given to `mysql_select_db`. You only need to escape strings that are substituted into SQL. — Barmar, Feb 04 '16 at 09:14
Open Developer Tools, go to the Network tab, and check the response to the AJAX request. Make sure it only contains the JSON that you're sending, not anything else. — Barmar, Feb 04 '16 at 09:15
You need to put the `var temp` and `data.push` lines inside the `success` function. — Barmar, Feb 04 '16 at 09:16
This javascript is behind a [Flotchart](http://www.flotcharts.org/); I believe I need to use a `while` loop for it to present the graph in real time. — Spark, Feb 04 '16 at 09:19
`$_GET['dbSelect']` is running well... Ill edit the PHP code to lessen the confusion... :) — Spark, Feb 04 '16 at 09:22
@C Würtz: I've edited the PHP code with your MySQL query suggestion. Thanks! :) — Spark, Feb 04 '16 at 09:50
@Barmar: I don't think it needed to be inside the `success` function, I just wanted to have a value for `y`. — Spark, Feb 04 '16 at 09:55
If you use `y` outside the `success` function, it will be undefined, because AJAX is asynchronous. — Barmar, Feb 04 '16 at 10:00
I see.. even though I declare the `y` variable outside the `$.ajax` function? then... how can I get/extract a variable outside the `success` function? Sorry if I sound stupid, I'm really new to this. I really appreciate your help. :) — Spark, Feb 04 '16 at 10:15

jeroen · Answer 1 · 2016-02-04T09:26:32.480

1

You are calling current_api.php in your ajax script without any data. So there is no query string, no $_GET['dbSelect'] and no database. So your json contains only an undefined variable, NULL.

Apart from that this is not correct, you cannot use escaping functions to clean up a user-provided table name, you need to check it against a whitelist.

edited Feb 04 '16 at 09:26

answered Feb 04 '16 at 09:20

jeroen

91,079
21
114
132

query string for `$_GET['dbSelect']` is running well... Sorry if I did not include other PHP files that handles different part of this project. I've edited the PHP code to lessen the confusion... :) Thanks for the reminder, – Spark Feb 04 '16 at 09:39

score 0 · Answer 2 · edited May 23 '17 at 12:31

Got it!

the problem is the declaration of variable y and the url in $.ajax. All thanks to barmar and jeroen for the hints!

The javascript should be:

<script type='text/javascript'>
    var data = [];
    var dataset;
    var totalPoints = 50;
    var updateInterval = 1000;
    var now = new Date().getTime();
    var y;

    function GetData() {
        data.shift();
        while (data.length < totalPoints) {

            $.ajax({
                url: 'current_api.php?dbSelect=R9640E5F1E2', 
                success: function(currentValue) {
                    y = currentValue;
                    console.log(currentValue);
                },
            });

            var temp = [now += updateInterval, y];
            data.push(temp);
        }
    }

    var options = {
        ...
    }

    $(document).ready(function () {
        GetData();

        dataset = [
            { label: "CURRENT READING", data: data }
        ];

        $.plot($("#flot-line-chart"), dataset, options);

        function update() {
            GetData();

            $.plot($("#flot-line-chart"), dataset, options)
            setTimeout(update, updateInterval);
        }

        update();
    });
</script>

where R9640E5F1E2 is the database; and the PHP stays as is. :) Now.. move on to another problem... Query strings inside javascripts.

score -1 · Answer 3 · answered Feb 04 '16 at 09:20

-1

$.ajax({
      dataType: "json",//insert line: receive data from server to json
      url: 'current_api.php?dbSelect=123', //a method 
      /* a method other
     type:"GET",
     data:{dbSelect:dbSelect},
     url: 'current_api.php',
     */

      success: function(currentValue){
        y = currentValue[0];///edit code   y = currentValue
       console.log(y);
        },
      });

answered Feb 04 '16 at 09:20

Hamidreza Bavafa

18
4

His PHP sends the `Content-type: application/json` header. `$.ajax` uses that to determine the default `dataType`. – Barmar Feb 04 '16 at 09:57
will the `[0]` on `y = currentValue[0];` makes a difference? I'm just querying a single row of data in every 2 seconds. As I understand `[]` signifies a position in an array. Is this also true in AJAX? – Spark Feb 04 '16 at 10:10

Query MySQL via PHP inside JavaScript with time interval

3 Answers3