Problem mysql syntax

Databases
1

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:


&#65279;<?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:


&#65279;<?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

2

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?

3

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


&#65279;<?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


<!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>
4

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

For example:

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

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

5

I tried this to run:

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

6

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

$queryString = $_POST['queryString'];

in

$queryString = $_GET['queryString'];

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