MySQL data from PHP to Javascript with JSON

Question

I am trying to use a code that will take data from a mySQL database bind that data to a variable, put all the resulting $x into a PHP array, and finally convert it to JSON format. I then take the JSON into javascript to handle the data from the data base there.

Please see my code:

    <?php
            //bind to $x
            $mysqli = new mysqli('localhost', 'root', 'root', 'mytable');
            if ($stmt = $mysqli->prepare("SELECT x FROM data")) {
                $stmt->bind_result($x);
                $OK = $stmt->execute();
            }
            //put all of the resulting $x into a PHP array
            $result_array = Array();
            while($stmt->fetch()) {
                $result_array[] = $x;
            }
            //convert the PHP array into JSON format, so it works with javascript
            $json_array = json_encode($result_array);
            
            if ($stmt = $mysqli->prepare("SELECT data.y FROM data")) {
                $stmt->bind_result($y);
                $OK = $stmt->execute();
            }
            //put all of the resulting y into a PHP array
            $result_array = Array();
            while($stmt->fetch()) {
                $result_array[] = $y;
            }
            //convert the PHP array into JSON format, so it works with javascript
            $json_array2 = json_encode($result_array);  
            
            
        ?>  
        
        <script>
            var xv = <?php echo $json_array; ?>;
            var yv = <?php echo $json_array2; ?>;
            var storage = [];
            for(var i=0;i<100;i++)
            { 
                var x = xv[i];
                var y = yv[i];
                var json = {x: x, y: y};
                storage.push(json); 
            }

My question is why is the page displaying this as an output and not transferring the data to the arrays

prepare("SELECT x FROM data")) 
  { 
  $stmt->bind_result($x); 
  $OK = $stmt->execute(); 
  } 
//put all of the resulting names into a PHP array 
$result_array = Array(); 
while($stmt->fetch()) 
  { 
  $result_array[] = $x; 
  } 
//convert the PHP array into JSON format, so it works with javascript 
$json_array = json_encode($result_array); 
/* 
if ($stmt = $mysqli->prepare("SELECT data.y FROM data")) 
  { 
  $stmt->bind_result($y); 
  $OK = $stmt->execute(); 
  } 
//put all of the resulting names into a PHP array 
$result_array = Array(); 
while($stmt->fetch()) 
  { 
  $result_array[] = $y; 
  } 
//convert the PHP array into JSON format, so it works with javascript
$json_array2 = json_encode($result_array);  
*/ 
?>

AS both x and y come from the same table, do select x,y .... and do it all in one query — RiggsFolly
– RiggsFolly, Commented Sep 28, 2018 at 11:53
Remove the quote from this var xv = "<?php echo $json_array; ?>"; its a javascript object so do var xv = <?php echo $json_array; ?>; — RiggsFolly
– RiggsFolly, Commented Sep 28, 2018 at 11:55
Okay I made those changes and I edited into the question what my issue is the issue still persists. I cannot grasp why it is showing the code as an output — user3371137
– user3371137, Commented Sep 28, 2018 at 12:25
This seems wrong I think, what is $x and $y supposed to have values here $stmt->bind_result($x); and here $stmt->bind_result($y); — Sameer Kumar Jain
– Sameer Kumar Jain, Commented Sep 28, 2018 at 12:44

aprogrammer · Accepted Answer · 2018-09-28 11:57:59Z

3

In those lines you have wrong code:

var xv = "<?php echo $json_array; ?>";
var yv = "<?php echo $json_array2; ?>";

You are printing output from json_encode into a double quoted section. This means when PHP render that page, output will be like that:

var xv = "[...smth]";

After those lines you are trying to get a value from array inside for but xv and xz variables are not type of object they are strings. Instead of this do it like that:

var xv = <?=$json_array;?>;
var yv = <?=$json_array2;?>;

answered Sep 28, 2018 at 11:57

aprogrammer

1,7781 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3371137 Over a year ago

Thank you for your answer I made the changes however the issue still remains

user3371137 Over a year ago

So I looked into it and there is a syntax error on the lines. var xv = <?=$json_array;?>; var yv = <?=$json_array2;?>;

Teddy Patriarca · Accepted Answer · 2018-09-28 12:37:53Z

0

The xv and yv is a string since you used json_encode.

You can use JSON.parse to convert the string into json.

var xv = "<?php echo $json_array; ?>";
xv = JSON.parse(xv);
var yv = "<?php echo $json_array2; ?>";
yv = JSON.parse(yv);
var storage = [];
for(var i=0;i<100;i++)
{ 
     var x = xv[i];
     var y = yv[i];
     var json = {x: x, y: y};
     storage.push(json); 
}

answered Sep 28, 2018 at 12:37

Teddy Patriarca

1096 bronze badges

1 Comment

user3371137 Over a year ago

Okay I implemented this edit the output result still seems to be the same output as described in the question.

autobottodoggo · Accepted Answer · 2018-09-28 14:35:21Z

First: if you're seeing PHP code in your output, you need to check your server and PHP configuration. Your PHP script is being interpreted by the web server as plain text and just spitting out the raw code instead of executing it through the PHP interpreter.

Second: embedding PHP in Javascript is less than ideal. You should have a PHP script to handle the MySQL querying, then fetching the output in Javascript with an AJAX request. Additionally, the way you're mutating the data is redundant and suboptimal.

This will retrieve your results from the database and encode it as JSON:

<?php
    // connect, query, bind results
    $result = [];
    $mysqli = new mysqli('localhost', 'user', 'pass', 'db');
    if ($stmt = $mysqli->prepare("SELECT x,y FROM table")) {
        $stmt->execute();
        $stmt->bind_result($x,$y);

        while ( $stmt->fetch() ) {
            $result[] = [
                'x' => $x, 
                'y' => $y
            ];
        }

        echo json_encode($result);
    }
?>

This is a basic AJAX example to retrieve the output from the PHP script:

<script>
function fetchMyStuff() {
    // basic ajax example
    var xhr = new XMLHttpRequest();
    xhr.onreadystatechange = function() {
        if ( xhr.readyState == 4 ) {
            console.log(
                JSON.parse( xhr.responseText )
            );
        }
    }
    xhr.open('GET', 'myscript.php', true);
    xhr.send();
}

fetchMyStuff();
</script>

You'll see the resulting object in the console corresponding to your query results:

[{x:'x1':y:'y1'},{x:'x2',y:'y2'},...]

Personally, I don't like the limitations of prepared statements and would prefer a much more optimized approach for my PHP file:

<?php
    $mysqli = new mysqli('localhost', 'user', 'pass', 'db');
    echo json_encode(
       $mysqli->query("SELECT x,y FROM table")->fetch_all( MYSQLI_ASSOC )
    );
?>

This leverages the mysql-nd module to perform all the work of fetching the full result set as an associative array and encoding it in only a few lines.

Collectives™ on Stack Overflow

MySQL data from PHP to Javascript with JSON

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related