CODEWITHSUNDEEP
phpjquerymysql
Jakemmarsh
Jakemmarsh

Reputation: 4671

Feeding JQuery autocomplete from mysql database

I am trying to create an autocomplete field using JQuery, which receives its autocompletions from a mysql database.

Index.php: 

        <!-- Place favicon.ico and apple-touch-icon.png in the root directory -->

        <link rel="stylesheet" href="css/normalize.css">
        <link rel="stylesheet" href="css/main.css">
        <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.0/themes/base/jquery-ui.css">
        <link rel="stylesheet" href="css/bootstrap.min.css">
        <script src="js/vendor/modernizr-2.6.2.min.js"></script>
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
        <script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/jquery-ui.min.js"></script>
        <script>window.jQuery || document.write('<script src="js/vendor/jquery-1.8.2.min.js"><\/script>')</script>
        <script src="js/plugins.js"></script>
        <script src="js/main.js"></script>
        <script type="text/javascript">
        $(function() {

            $("#college").autocomplete({
                source: "search.php",
                minLength: 2
            });
        });
        </script>
    </head>
    <body>

        <form action="<?php echo $_SERVER['PHP_SELF'];?>"  method="post">
            <fieldset>
            <legend>jQuery UI Autocomplete Example - PHP Backend</legend>

            <label for="state">State (abbreviation in separate field): </label>

            <input type="text" id="college"  name="college" />

            <input type="submit" name="submitBtn" value="Submit" />

            </fieldset>
        </form>

        <?php
if (isset($_POST['submit'])) {
echo "<p>";
    while (list($key,$value) = each($_POST)){
    echo "<strong>" . $key . "</strong> = ".$value."<br />";
    }
echo "</p>";
}
?>




        <!--[if lt IE 7]>
            <p class="chromeframe">You are using an <strong>outdated</strong> browser. Please <a href="http://browsehappy.com/">upgrade your browser</a> or <a href="http://www.google.com/chromeframe/?redirect=true">activate Google Chrome Frame</a> to improve your experience.</p>
        <![endif]-->

        <!-- Add your site or application content here -->


        <!-- Google Analytics: change UA-XXXXX-X to be your site's ID. -->
        <script>
            var _gaq=[['_setAccount','UA-XXXXX-X'],['_trackPageview']];
            (function(d,t){var g=d.createElement(t),s=d.getElementsByTagName(t)[0];
            g.src=('https:'==location.protocol?'//ssl':'//www')+'.google-analytics.com/ga.js';
            s.parentNode.insertBefore(g,s)}(document,'script'));
        </script>
    </body>
</html>

search.php: 

/* Connection vars here for example only. Consider a more secure method. */
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'college';

try {
  $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}
catch(PDOException $e) {
    echo $e->getMessage();
}

$return_arr = array();

if ($conn)
{
    $ac_term = "%".$_GET['term']."%";
    $query = "SELECT * FROM college_list where name like :term";
    $result = $conn->prepare($query);
    $result->bindValue(":term",$ac_term);
    $result->execute();

    /* Retrieve and store in array the results of the query.*/
    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
        $row_array['name'] = $row['name'];

        array_push($return_arr,$row_array);
    }


}
/* Free connection resources. */
//$conn = null; 
/* Toss back results as json encoded array. */
echo json_encode($return_arr);

?>

It's currently retrieving correctly from the database, because it's getting the correct number of autocompletions. However, they're all blank. It appears to not actually be feeding "name" back into the field and I can't seem to figure out why. Any ideas?

Upvotes: 1

Views: 3535

Answers (1)

jeroen
jeroen

Reputation: 91792

Without seeing the autocomplete code you are using, I would say that you need a 1-dimensional array instead of a 2-dimensional one as you are generating now.

You could try changing:

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $row_array['name'] = $row['name'];

    array_push($return_arr,$row_array);
}

To:

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    array_push($return_arr, $row['name']);
}

edit: check the api documentation, you need to build your array differently, something like:

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    array_push($return_arr, array('label' => $row['name'], 'value' => $row['name']));
}

Upvotes: 1

Related Questions