Auto Complete Ajax php strange bug :confused: :confused:

[dflow]

i have the following script from a demo that works:

if i change the query to my db it only works with city_id as a number (response is [] for city_name

//sql:
CREATE TABLE `sks_color` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=14 ;




<?php

$link = mysql_connect('localhost', 'dbUsername', 'dbPassword');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
if (!mysql_select_db("database")) {
   echo "Unable to select mydbname: " . mysql_error();
   exit;
}

$result = mysql_query("SELECT name FROM  sks_color");
while ($row = mysql_fetch_assoc($result)) {
   		$colors[]=$row['name'];
}
mysql_free_result($result);
mysql_close($link);

// check the parameter
if(isset($_GET['part']) and $_GET['part'] != '')
{
// initialize the results array
$results = array();

// search colors
foreach($colors as $color)
{
	// if it starts with 'part' add to results
	if( strpos($color, $_GET['part']) === 0 ){
		$results[] = $color;
	}
}

// return the array as json with PHP 5.2
echo json_encode($results);
}
?>

 

my chamges

//sql
CREATE TABLE `citylist` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) NOT NULL,
  `city_name` varchar(200) NOT NULL,
  PRIMARY KEY (`city_id`),
  KEY `city_id` (`city_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=616 ; 

<?php

$link = mysql_connect('localhost', 'root', 'root');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
if (!mysql_select_db("international")) {
   echo "Unable to select mydbname: " . mysql_error();
   exit;
}

$result = mysql_query("SELECT city_name FROM citylist");
while ($row = mysql_fetch_assoc($result)) {
   		$colors[]=$row['city_name'];
}
mysql_free_result($result);
mysql_close($link);

// check the parameter
if(isset($_GET['part']) and $_GET['part'] != '')
{
// initialize the results array
$results = array();

// search colors
foreach($colors as $color)
{
	// if it starts with 'part' add to results
	if( strpos($color, $_GET['part']) === 0 ){
		$results[] = $color;
	}
}

// return the array as json with PHP 5.2
echo json_encode($results);
}
?>

:confused:

[TeNDoLLA]

What?

[dflow]

What?

 

the demo code works as is;

if i change the query

$result = mysql_query("SELECT city_name FROM citylist");

 

i get no results in the autocomplete

 

if i change the

[dflow]

ok it works the problem was capital letters:

 

how would i force to search :Amsterdam and amsterdam with preg_match?

[TeNDoLLA]

if (preg_match('/[Aa]msterdam/'))
{
    // Found
}

[dflow]

if (preg_match('/[Aa]msterdam/'))
{
    // Found
}

 

thanks

and in general for a whole list of cities?

 

[TeNDoLLA]

Is it the $_GET['part'] you are searching for inside your loop for every iteration? Add the regexp inside the loop where you loop through the citites. And better option for what I suggested earlier is to use the $_GET['part'] in incasesensitive match.

 

if (preg_match('/'. $_GET['part'] .'/i', $stringToSearch))
{
    // found
}

[dflow]

Is it the $_GET['part'] you are searching for inside your loop for every iteration? Add the regexp inside the loop where you loop through the citites. And better option for what I suggested earlier is to use the $_GET['part'] in incasesensitive match.

 

if (preg_match('/'. $_GET['part'] .'/i', $stringToSearch))
{
    // found
}

 

adding it to the check parameter part doesn't work?

// check the parameter
if(isset($_GET['part']) and $_GET['part'] != '' and preg_match('/'. $_GET['part'] .'/i', $row['city_name']))

[TeNDoLLA]

This is code I just tested out, works just fine. I added also '^' in the start of the pattern and '$' sign in the end so it will match exactly to a string like 'Amsterdam' but not to string 'XAmsterdam' etc.

 

$test = array('city_name' => 'AMSterdam');

if(isset($_GET['part']) && $_GET['part'] != '' && preg_match('/^'. $_GET['part'] .'$/i', $test['city_name']))
{
echo 'TRUE';
}
else
{
echo 'FALSE';
}

echo '<a href="?part=amsterdam">TEST</a>';

[dflow]

This is code I just tested out, works just fine. I added also '^' in the start of the pattern and '$' sign in the end so it will match exactly to a string like 'Amsterdam' but not to string 'XAmsterdam' etc.

 

$test = array('city_name' => 'AMSterdam');

if(isset($_GET['part']) && $_GET['part'] != '' && preg_match('/^'. $_GET['part'] .'$/i', $test['city_name']))
{
echo 'TRUE';
}
else
{
echo 'FALSE';
}

echo '<a href="?part=amsterdam">TEST</a>';

 

yep your test works fine but i still get nothing with the part i added  to the check parameter , empty array

[PFMaBiSmAd]

You should not be retrieving all the rows from your database table and then loop through the data trying to match it. You should be doing this in your query using a WHERE clause with a LIKE comparison.

[dflow]

You should not be retrieving all the rows from your database table and then loop through the data trying to match it. You should be doing this in your query using a WHERE clause with a LIKE comparison.

 

could you guide me on how to implement it with the ajax?

[PFMaBiSmAd]

You are already using ajax to request and display the information. What I suggested was how to implement the server side query so that you only retrieve the data you want in the most efficient manor.

 

Untested (of course), but should work -

<?php
if(isset($_GET['part']) and $_GET['part'] != '')
{
mysql_connect('localhost', 'root', 'root') or die('Could not connect: ' . mysql_error());
mysql_select_db("international") or die('Unable to select mydbname: ' . mysql_error());
$results = array();
$part = mysql_real_escape_string($_GET['part']);
$result = mysql_query("SELECT city_name FROM citylist WHERE city_name LIKE '$part%'");
while ($row = mysql_fetch_assoc($result)) {
   		$results[]=$row['city_name'];
}
echo json_encode($results);
}
?>

[dflow]

You are already using ajax to request and display the information. What I suggested was how to implement the server side query so that you only retrieve the data you want in the most efficient manor.

 

Untested (of course), but should work -

great thanks
<?php
if(isset($_GET['part']) and $_GET['part'] != '')
{
mysql_connect('localhost', 'root', 'root') or die('Could not connect: ' . mysql_error());
mysql_select_db("international") or die('Unable to select mydbname: ' . mysql_error());
$results = array();
$part = mysql_real_escape_string($_GET['part']);
$result = mysql_query("SELECT city_name FROM citylist WHERE city_name LIKE '$part%'");
while ($row = mysql_fetch_assoc($result)) {
   		$results[]=$row['city_name'];
}
echo json_encode($results);
}
?>