problem mysql syntax

**newphpcoder** · Oct 27, 2011 19:48

I found a autosuggest code, but the connection to database is mysqli, so I edit the syntax to mysql_connect..

Here is the original syntax:

PHP Code:


<?php

   $db = new mysqli('DB_HOST', 'USERNAME' ,'PASSWORD', 'DATABASE_NAME');

    

    if(!$db) {

    

        echo 'Could not connect to the database.';

    } else {

    

        if(isset($_POST['queryString'])) {

            $queryString = $db->real_escape_string($_POST['queryString']);

            

            if(strlen($queryString) >0) {



                $query = $db->query("SELECT country FROM countries WHERE country LIKE '$queryString%' LIMIT 10");

                if($query) {

                echo '<ul>';

                    while ($result = $query ->fetch_object()) {

                         echo '<li onClick="fill(\''.addslashes($result->country).'\');">'.$result->country.'</li>';

                     }

                echo '</ul>';

                    

                } else {

                    echo 'OOPS we had a problem :(';

                }

            } else {

                // do nothing

            }

        } else {

            echo 'There should be no direct access to this script!';

        }

    }

?>

and I edit it:

PHP Code:


<?php

 $db_host         = 'localhost';

$db_user         = 'root';

$db_password     = '';

$db_name         = 'db_upload';

    

    

$db = mysql_connect($db_host , $db_user ,$db_password, $db_name);

    

        if(isset($_POST['queryString'])) {

            $queryString = mysql_real_escape_string($_POST['queryString']);

            

            if(strlen($queryString) >0) {



                $query = mysql_query("SELECT country FROM countries WHERE country LIKE '$queryString%' LIMIT 10");

                if($query) {

                echo '<ul>';

                    while ($result = mysql_fetch_object($query)) {

                         echo '<li onClick="fill(\''.addslashes($result->country).'\');">'.$result->country.'</li>';

                     }

                echo '</ul>';

                    

                } else {

                    echo 'OOPS we had a problem :(';

                }

            } else {

                // do nothing

            }

        } else {

            echo 'There should be no direct access to this script!';

        }

?>

when i run it, it was always fall in else statement

'OOPS we had a problem :('
even I input correct data.

here is the link where I found the code:
http://papermashup.com/jquery-php-ajax-autosuggest/

Thank you

**r937** · Oct 27, 2011 20:26

SELECT country FROM countries WHERE country LIKE '$queryString%' LIMIT 10

this looks fine, so i'm not sure it's actually a mysql problem

did you test this outside of php first?

**newphpcoder** · Oct 27, 2011 21:23

I revised my code and I think I have a problem in javascript because the data did not display.

PHP Code:


<?php



include 'config.php';



    



            $queryString = $_POST['queryString'];

            

            

    if(strlen($queryString) >0) {



    $query = "SELECT country FROM countries WHERE country LIKE '$queryString%' LIMIT 10";

    $result = mysql_query($query) or die(mysql_error());

    if(mysql_num_rows($result))

    {

        echo '<ul class="list">';

        while($row = mysql_fetch_array($result))

        {



            echo '<li onClick="fill(\''.addslashes($row->country).'\');">'.$row->country.'</li>';

        }

        echo "</ul>";

    }

   else {

                    echo 'OOPS we had a problem :(';



            }

    

?>

and here is the index.php

PHP Code:


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<title>jQuery PHP Ajax Autosuggest</title>





<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.js"></script>

<script>

function suggest(inputString){

        if(inputString.length == 0) {

            $('#suggestions').fadeOut();

        } else {

        $('#country').addClass('load');

            $.post("autosuggest.php", {queryString: ""+inputString+""}, function(data){

                if(data.length >0) {

                    $('#suggestions').fadeIn();

                    $('#suggestionsList').html(data);

                    $('#country').removeClass('load');

                }

            });

        }

    }



    function fill(thisValue) {

        $('#country').val(thisValue);

        setTimeout("$('#suggestions').fadeOut();", 600);

    }



</script>



<style>

#result {

    height:20px;

    font-size:16px;

    font-family:Arial, Helvetica, sans-serif;

    color:#333;

    padding:5px;

    margin-bottom:10px;

    background-color:#FFFF99;

}

#country{

    padding:3px;

    border:1px #CCC solid;

    font-size:17px;

}

.suggestionsBox {

    position: absolute;

    left: 0px;

    top:40px;

    margin: 26px 0px 0px 0px;

    width: 200px;

    padding:0px;

    background-color: #000;

    border-top: 3px solid #000;

    color: #fff;

}

.suggestionList {

    margin: 0px;

    padding: 0px;

}

.suggestionList ul li {

    list-style:none;

    margin: 0px;

    padding: 6px;

    border-bottom:1px dotted #666;

    cursor: pointer;

}

.suggestionList ul li:hover {

    background-color: #FC3;

    color:#000;

}

ul {

    font-family:Arial, Helvetica, sans-serif;

    font-size:11px;

    color:#FFF;

    padding:0;

    margin:0;

}



.load{

background-image:url(loader.gif);

background-position:right;

background-repeat:no-repeat;

}



#suggest {

    position:relative;

}



</style>

</head>



<body>









 <form id="form" action="#">

    <div id="suggest">Start to type a country: <br />

      <input type="text" size="25" value="" id="country" onkeyup="suggest(this.value);" onblur="fill();" class="" />

     

      <div class="suggestionsBox" id="suggestions" style="display: none;"> <img src="arrow.png" style="position: relative; top: -12px; left: 30px;" alt="upArrow" />

        <div class="suggestionList" id="suggestionsList"> &nbsp; </div>

      </div>

   </div>

</form>







</body>

</html>

**guido2004** · Oct 28, 2011 02:20

Before moving it to the PHP or javascript forum, could you call the autosuggest.php script directly to see what happens?

For example:

Code:

http://www.yoursite.com/autosuggest.php?queryString=A

should give the list of all countries starting with the letter 'A'.

**newphpcoder** · Oct 28, 2011 02:32

I tried this to run:

http://localhost:8080/file:/C:/xampp...?queryString=A
the display page is blank page

**guido2004** · Oct 28, 2011 02:35

Ah yes sorry, the variables passed in the url are found in $_GET. You'd have to change your php script for this test.
Change

PHP Code:


$queryString = $_POST['queryString'];

in

PHP Code:


$queryString = $_GET['queryString'];

Don't forget to change it back to $_POST when you've finished the testing.

User Tag List

Thread: problem mysql syntax

Thread Tools

Display

problem mysql syntax

Bookmarks

Bookmarks

Posting Permissions