Live search with MySQL, PHP, and AJAX is not working

Question

Ok so i'm trying to do a live search using PHP, MySQL, and AJAX. I'm not to sure were i'm going wrong. My database is hosted on phpMyAdmin. The database name is Info and the table i'm trying to access is names.

My three pages are index.php connect.php and fetch.php Index.php

<!DOCTYPE html>
<html>
    <head>
        <title></title>
    </head>
        <style>

            #here
                {
                    width:400px;
                    height:300px;
                    border: 1px solid grey;
                    display:none;
                }

            #here a{
                display:block;
                width:98%;
                padding:1%;
                font-size:20px;
                border-bottom:1px solid grey;
            }

                </style>
        <body>

            <script src=jq.js></script>

            <script src="jq.js">
                $(document).ready(function(e)
                {
                    $("search").keyup(function()
                    {
                        $("#here").show();
                        var x = $(this).val();
                        $.ajax(
                            {
                                type:'GET',
                                url:'fetch.php',
                                data: 'q='+x,
                                success:function(data)
                                {
                                    $("#here").html(data);
                                }
                                ,
                            });
                    });
                });




            </script>


            <h1>Live Search</h1>
            <input type="search" name="search" id="search">
            <div id="here">

            </div>
        </body>
</html>

Fetch.php

<?php
if(!empty($_GET['q']))
{

    include 'connect.php';
    $q=$_GET['q'];
    $query = "select * from names where names like '%$q%';";
    while($output=mysqli_fetch_assoc($result))
    {
        echo '<a>'.$output['names'].'</a>';
    }
     $query = "select * from names";
}

fetch.php

    ?>

<?php
$host="localhost";
$user="andremac96";
$password="";
$db="Info";
$conn = mysqli_connect($host,$user,$password,$db);
?>

[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Apr 15 '16 at 20:41
Are you getting any errors in the browser's console or your error logs? — Jay Blanchard, Apr 15 '16 at 20:42
Can you confirm that the fetch.php is outputing the correct data if you use it directly? — Jared Drake, Apr 15 '16 at 20:45
@JayBlanchard No errors at all and its just for personal learning, the security doesn't bother me right now. — , Apr 15 '16 at 20:53
I hate when people say *"I'm not that far along..."* or *"This site will not be public..."* or *"It's only for school, so security doesn't matter..."*. If teachers and professors are not talking about security from day one, they're doing it wrong. Challenge them. They're teaching sloppy and dangerous coding practices which students will have to unlearn later. I also hate it when folks say, *"I'll add security later..."* or *"Security isn't important now"*. If you don't have time to do it right the first time, when will you find the time to add it later? — Jay Blanchard, Apr 15 '16 at 20:58
@Jay Blanchard I am annoyed that I was not taught more about SQL injection while taking classes. Keep up the good fight. These sorts of practices should be the standard. — Kurt Leadley, Apr 15 '16 at 20:59
Have you watched the AJAX request / response in the browser's developer tools? Are you running this on a web-server? — Jay Blanchard, Apr 15 '16 at 20:59
`while($output=mysqli_fetch_assoc($result))` <<< wrong variable. That should be `$query` here and error reporting would have thrown you something about an undefined variable. http://php.net/manual/en/function.error-reporting.php - I'm surprised nobody caught that. Edit: How about you @JayBlanchard ? — Funk Forty Niner, Apr 15 '16 at 21:01
$result = mysqli_query($link, $sql); or something like that is missing — MadeInDreams, Apr 15 '16 at 21:11
@Andre I spotted a few more errors in your code and have made an edit to that effect, so if you've seen it, you will have to reload my answer to see those additionals. I've tested this and it works. — Funk Forty Niner, Apr 15 '16 at 22:18
@Fred-ii- after making all your suggested changes i get this error "mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, null given in /home/ubuntu/workspace/fetch.php on line 8" I changed $result to $query on this line — , Apr 16 '16 at 14:37
@Andre Hi Andre. I've tested my answer which worked. Give me a minute, I'll post what I used as an edit (rewrite). — Funk Forty Niner, Apr 16 '16 at 14:38
@Andre Reload my answer and look under: "Edit: (rewrite as to what I used which tested successfully)." — Funk Forty Niner, Apr 16 '16 at 14:49

score 0 · Accepted Answer · edited May 23 '17 at 12:07

There are a few things wrong here.

Firstly, you never executed the query for:

$query = "select * from names where names like '%$q%';";

So, you need to include mysqli_query() and pass the db connection to it.

$query = mysqli_query($conn, "select * from names where names like '%$q%';");

Then, you would have been using the wrong variable $result for

while($output=mysqli_fetch_assoc($result))

which should have been $query, but again; querying it also.

Same thing for $query = "select * from names";

$query = mysqli_query($conn, "select * from names");

^ Unsure what you want to do with that one though.

Then you forgot to put the # for the search id in $("search").keyup(function() which should have read as:

$("#search").keyup(function()

Check for errors on PHP and MySQL:

Plus, check your JS console.

Then there's echo '<a>'.$output['names'].'</a>';

^ Unsure what you want to do here also.

Then the 2nd <script src="jq.js"> that should just read as <script>.

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements.

HTML stickler.

Place <style>...</style> inside <head></head>. Certain browsers will throw warnings in HTML source.

Edit: (rewrite as to what I used which tested successfully).

Sidenote: I added <form></form> tags, but did work without them.

File #1:

<!DOCTYPE html>
<html>
    <head>
        <title></title>

<script src="https://code.jquery.com/jquery-1.12.0.min.js"></script>

        <style>

            #here
                {
                    width:400px;
                    height:300px;
                    border: 1px solid grey;
                    display:none;
                }

            #here a{
                display:block;
                width:98%;
                padding:1%;
                font-size:20px;
                border-bottom:1px solid grey;
            }

                </style>

    </head>

        <body>


            <script>
                $(document).ready(function(e)
                {
                    $("#search").keyup(function()
                    {
                        $("#here").show();
                        var x = $(this).val();
                        $.ajax(
                            {
                                type:'GET',
                                url:'fetch.php',
                                data: 'q='+x,
                                success:function(data)
                                {
                                    $("#here").html(data);
                                }
                                ,
                            });
                    });
                });


            </script>


<h1>Live Search</h1>
<form>
    <input type="search" name="search" id="search">
</form>

    <div id="here">

    </div>


        </body>
</html>

File #2: (fetch.php)

<?php 

 include 'connect.php'; // being the MySQLi_ API

if(!empty($_GET['q']))
{


$q= mysqli_real_escape_string($conn, $_GET['q']);


    $query = mysqli_query($conn, "select * from names where names like '%$q%';") 
             or die(mysqli_error($conn));


    while($output=mysqli_fetch_assoc($query))
    {

        echo '<a>'.$output['names'].'</a>';


    }
//     $query = "select * from names";
}

Make sure your .php file paths are correct and that they are accessible for your script.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Then the rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

Thanks you so so much i owe you big time. My database was also structure wrong haha — , Apr 16 '16 at 15:09

Live search with MySQL, PHP, and AJAX is not working

1 Answers1