Inserting MySQL results from PHP into JavaScript Array

Question

I'm trying to make a very simple autocomplete function on a private website using a trie in JavaScript. Problem is the examples I have seen and trying are just using a predefined list in a JavaScript array.

e.g. var arrayObjects = ["Dog","Cat","House","Mouse"];

What I want to do is retrieve MySQL results using PHP and put them into a JavaScript array.

This is what I have so far for the PHP (the JavaScript is fine just need to populate the array):

<?php 
    $mysqli = new mysqli('SERVER', 'U/NAME', 'P/WORD', 'DB');
    if (!$mysqli)
    {
        die('Could not connect: ' . mysqli_error($mysqli));
    }
    if ($stmt = $mysqli->prepare("SELECT category.name FROM category")) {
        $stmt->bind_result($name);
        $OK = $stmt->execute();
    }   
while($stmt->fetch()) 
    {
     printf("%s, ", $name); 
    }
?>

Then I want to insert essentially each value using something like mysql_fetch_array ($name); (I know this is incorrect but just to show you guys what's going on in my head)

<script> -- this is the javascript part
(function() {
    <?php while $stmt=mysql_fetch_array($name))
     {
       ?>
        var arrayObjects = [<?php stmt($name) ?>];
    <?php } 
       ?>

I can retrieve the results echoing out fine, I can manipulate the trie fine without MYSQL results, I just can't put them together.

Consider using Ajax calls and passing data in JSON format instead of this cross-language madness that you have now — Juribiyan
– Juribiyan, Commented Jan 21, 2014 at 3:51
Thanks, not that I'm afraid of something new just a fair bit to learn with Ajax calls and passing JSON, I could have easily used an autocomplete function straight from the net but I thought I would have a go at it myself seeing as I did Red Black and Radix Tress in uni years ago but they were all java, self taught PHP so was just sticking with what I know. The cross language madness is only in my head ;) — Adam First
– Adam First, Commented Jan 21, 2014 at 4:20

Dharman · Accepted Answer · 2021-08-30 11:15:30Z

13

In this case, what you're doing is looping through your result array, and each time you're printing out the line var arrayObjects = [<?php stmt($name) ?>];. However this doesn't convert between the PHP array you're getting as a result, and a javascript array.

Since you started doing it this way, you can do:

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

<script>
    //now put it into the javascript
    var arrayObjects = <?php echo $json_array; ?>
</script>

edited Aug 30, 2021 at 11:15

Dharman♦

33.9k27 gold badges104 silver badges157 bronze badges

answered Jan 21, 2014 at 3:55

Max

2,11219 silver badges25 bronze badges

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

2 Comments

Adam First Over a year ago

Worked! Brilliant! Thanks heaps mate. I had already dabbled a few attempts putting results into a PHP array, just didn't know there was a conversion function to put into JSON and pass to javascript! Great example.

duhaime Over a year ago

Absolutely fantastic--this makes everything possible!

anthonygore · Accepted Answer · 2014-01-21 03:51:00Z

4

Use json_encode to turn your PHP array into a valid javascript object. For example, if you've got the results from your database in a php array called $array:

var obj = "<?php echo json_encode($array); ?>";

You can now use obj in your javascript code

answered Jan 21, 2014 at 3:51

anthonygore

4,9875 gold badges34 silver badges30 bronze badges

1 Comment

mplungjan Over a year ago

Why in quotes? Then you will need to JSON.parse the obj

Juribiyan · Accepted Answer · 2014-01-21 04:04:45Z

0

For the auto-completion you can use the <datalist> tag. This is a relatively new feature in HTML5 (see support table) but the polyfill exists. Fill the <option> tags in php when building the page and you a are done.

answered Jan 21, 2014 at 4:04

Juribiyan

75010 silver badges26 bronze badges

1 Comment

Adam First Over a year ago

Thanks, but not what I was after for this, good to know for future reference.

Collectives™ on Stack Overflow

Inserting MySQL results from PHP into JavaScript Array

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

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

Linked

Related