Inserting MySQL results from PHP into JavaScript Array - Stack Overflow

admin管理员组

文章数量:1202775

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.

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.

• javascript
• php
• mysql
• arrays

Share Improve this question Follow edited Aug 30, 2021 at 11:14 Dharman♦ 33.2k2727 gold badges101101 silver badges146146 bronze badges asked Jan 21, 2014 at 3:43 Adam FirstAdam First 44511 gold badge55 silver badges2020 bronze badges 2

• Consider using Ajax calls and passing data in JSON format instead of this cross-language madness that you have now – 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 Commented Jan 21, 2014 at 4:20

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 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>

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

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.

本文标签： Inserting MySQL results from PHP into JavaScript ArrayStack Overflow