PHP script for AJAX

[Ryflex]

Hi all,

 

I have a PHP script ment for an AJAX livesearch.

It is supposed to check a hint (a few letters from a textfield) against 1 column in the database.

Somehow whatever I try it just won't give a suggestion from the database.

I used mysql_fetch_row which kept saying no suggestion but when I entered the exact name as in the database it gave the following error:

Warning: mysql_fetch_row() expects parameter 1 to be resource, string given in /customers/ryflex.nl/ryflex.nl/httpd.www/ajax_test.php on line 31 no suggestion

After asking on the AJAX part of the forum someone told me to use:

$a[] = mysql_fetch_object($result)->predicted;

So I made the following of it:

 $query = "SELECT * 
                  FROM users
                  WHERE voornaam ='".$q."'";
        $result = mysql_query($query) or die('Query1 failed: ' . mysql_error());
        $totalRows = mysql_num_rows($result);
      
    $a[] = mysql_fetch_object($result)->predicted;

Since that didn't do anything I set the code back to:

<?php
// Fill up array with names
//Include database connection details
require_once('config.php');

//get the q parameter from URL
$q=$_GET["q"];

//Connectie naar mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) 
{
    die('Failed to connect to server: ' . mysql_error());
}

//Selecteer database
$db = mysql_select_db(DB_DATABASE);
if(!$db) 
{
    die("Unable to select database");
}


        $query = "SELECT * 
                  FROM users
                  WHERE voornaam ='".$q."'";
        $result = mysql_query($query) or die('Query1 failed: ' . mysql_error());
        $totalRows = mysql_num_rows($result);
        $a = array();
        for ($j=0; $j<$totalRows; $j++) {
        $a[$j] = mysql_fetch_row($result);
        }


//lookup all hints from array if length of q>0
if (strlen($q) > 0)
  {
  $hint="";
  for($i=0; $i<count($a); $i++)
    {
    if (strtolower($q)==strtolower(substr($a[$i],0,strlen($q))))
      {
      if ($hint=="")
        {
        $hint=$a[$i];
        }
      else
        {
        $hint=$hint." , ".$a[$i];
        }
      }
    }
  }

// Set output to "no suggestion" if no hint were found
// or to the correct values
if ($hint == "")
  {
  $response="no suggestion";
  }
else
  {
  $response=$hint;
  }

//output the response
echo $response;
?>

 

The AJAX part of the code is:

<?php
// Fill up array with names
//Include database connection details
require_once('config.php');

//get the q parameter from URL
$q=$_GET["q"];

//Connectie naar mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) 
{
    die('Failed to connect to server: ' . mysql_error());
}

//Selecteer database
$db = mysql_select_db(DB_DATABASE);
if(!$db) 
{
    die("Unable to select database");
}


        $query = "SELECT * 
                  FROM users
                  WHERE voornaam ='".$q."'";
        $result = mysql_query($query) or die('Query1 failed: ' . mysql_error());
        $totalRows = mysql_num_rows($result);
        $a = array();
        for ($j=0; $j<$totalRows; $j++) {
        $a[$j] = mysql_fetch_row($result);
        }


//lookup all hints from array if length of q>0
if (strlen($q) > 0)
  {
  $hint="";
  for($i=0; $i<count($a); $i++)
    {
    if (strtolower($q)==strtolower(substr($a[$i],0,strlen($q))))
      {
      if ($hint=="")
        {
        $hint=$a[$i];
        }
      else
        {
        $hint=$hint." , ".$a[$i];
        }
      }
    }
  }

// Set output to "no suggestion" if no hint were found
// or to the correct values
if ($hint == "")
  {
  $response="no suggestion";
  }
else
  {
  $response=$hint;
  }

//output the response
echo $response;
?>

 

I hope someone will be able to help me out here since I'm getting nuts from this problem.

 

Greetz Ryflex

[fugix]

you posted the same code in you "AJAX part"...can you please post your AJAX code as well

 

Edit: also as a quick tip...this line

WHERE voornaam ='".$q."'";

does not need to be concatenated

[fugix]

Also, what is your logic behind these lines

$a = array();
        for ($j=0; $j<$totalRows; $j++) {
        $a[$j] = mysql_fetch_row($result);
        }

[Zane]

It is supposed to check a hint (a few letters from a textfield) against 1 column in the database.

Somehow whatever I try it just won't give a suggestion from the database.

 

I used mysql_fetch_row which kept saying no suggestion but when I entered the exact name as in the database it gave the following error:

Warning: mysql_fetch_row() expects parameter 1 to be resource, string given in /customers/ryflex.nl/ryflex.nl/httpd.www/ajax_test.php on line 31 no suggestion

 

In your query, you are selecting the rows where voornaam = "the three letters or so".  If you're only using the first three letters to query this, it's illogical to say you're looking for what equals those letters.  For instance, if I wanted space to show up as a suggestion and I entered...  spa, as my search criteria,  I wouldn't be searching for "space" in my query.  This is also why your query fails, which is why mysql_fetch_array fails. EDIT: just re-read the second quote.

 

What you need to be using is the SQL comparison function, LIKE

SELECT * FROM table WHERE voornaam LIKE "%spa%"

 

Notice how I used the percentage sign.  That is a wildcard.  If you wanted to search the first letters you would take that first wildcard off.

This is a 4 word search... using "spac"

SELECT * FROM table WHERE voornaam LIKE "spac%"

 

This would pull up things like,

space

spacious

spackle

..etc

[mikosiko]

and even implementing Zanus suggestion the issues pointed-out by requinix in your other thread still unsolved... what he was suggesting was to replace this part of your code:

 

        for ($j=0; $j<$totalRows; $j++) {
        $a[$j] = mysql_fetch_row($result);
        }

 

for  this

        for ($j=0; $j<$totalRows; $j++) {
        $a[$j] = mysql_fetch_object($result)->predicted;  // assuming that "predicted" is a field name in your table users.. otherwise replace it for a valid fieldname.. I would say that you should want to replace it for "voornaam".
        }

[Zane]

Also, what is your logic behind these lines

$a = array();
        for ($j=0; $j        $a[$j] = mysql_fetch_row($result);
        }

Fugix, if you knew anything at all, you would know it's just populating an array.  the way he does it has nothing to do with the error he provided.

[fugix]

its actually populating an array with a specified key. was wondering on the logic thats all.

[Ryflex]

Wow!!!

Thanks for al the quick replies!!!

Here is my AJAX code:

<html>
<head>
<script type="text/javascript">
function showHint(str)
{
var xmlhttp;
if (str.length==0)
  { 
  document.getElementById("txtHint").innerHTML="";
  return;
  }
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","ajax_test.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>

<h3>Start typing a name in the input field below:</h3>
<form action=""> 
First name: <input type="text" id="txt1" onkeyup="showHint(this.value)" />
</form>
<p>Suggestions: <span id="txtHint"></span></p> 

</body>
</html>

 

Btw to make it easier to understand for you all Voornaam means firstname in dutch.

I'm going to use this query so people can fill in the first part of a name of a user and it gives some options.

 

EDIT:

 

I used Zanus part with the wildcard to do the following in combination with mikosiko's:

 $query = "SELECT * 
                  FROM users
                  WHERE voornaam ='$q%'";
        $result = mysql_query($query) or die('Query1 failed: ' . mysql_error());
        $totalRows = mysql_num_rows($result);
        for ($j=0; $j<$totalRows; $j++) {
        $a[$j] = mysql_fetch_object($result)->predicted;  // assuming that "predicted" is a field name in your table users.. otherwise replace it for a valid fieldname.. I would say that you should want to replace it for "voornaam".
        }

[KevinM1]

its actually populating an array with a specified key. was wondering on the logic thats all.

 

The key is just a number in the range 0 to $totalRows - 1.  They'd get the same results with:

 

$count = 0;

while($count < $totalRows)
{
   $a[] = mysql_fetch_row($result);

   ++$count;
}

[fugix]

Wow!!!

Thanks for al the quick replies!!!

Here is my AJAX code:

<html>
<head>
<script type="text/javascript">
function showHint(str)
{
var xmlhttp;
if (str.length==0)
  { 
  document.getElementById("txtHint").innerHTML="";
  return;
  }
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","ajax_test.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>

<h3>Start typing a name in the input field below:</h3>
<form action=""> 
First name: <input type="text" id="txt1" onkeyup="showHint(this.value)" />
</form>
<p>Suggestions: <span id="txtHint"></span></p> 

</body>
</html>

 

Btw to make it easier to understand for you all Voornaam means firstname in dutch.

I'm going to use this query so people can fill in the first part of a name of a user and it gives some options.

did Zanus modification not work for you?

[Ryflex]

Like Homer: DOH

 

I forgot to mention it didn't work.

It keeps telling no suggestion also when filled in the complete name.

 

[mikosiko]

just checking.... did you use the code like this:

$a[$j] = mysql_fetch_object($result)->predicted; 
// assuming that "predicted" is a field name in your table users.. 
// otherwise replace it for a valid fieldname.. I would say that you should want to replace it for "voornaam".

 

or did you changed "predicted" for the field that you want? (assuming "predicted" is not one of your table fields of course)..

 

beyond that... you should debug your code to validate if your query is effectively returning data...

-  Modify this lines adding the commented lines

$query = "SELECT * 
                  FROM users
                  WHERE voornaam ='$q%'";
        $result = mysql_query($query) or die('Query1 failed: ' . mysql_error());
        $totalRows = mysql_num_rows($result);

       // add this line and check what do you get.
      echo "Rows Returned : " . $totalRows;

        for ($j=0; $j<$totalRows; $j++) {
        $a[$j] = mysql_fetch_object($result)->predicted;  
        }

        // dump your array to see what do you got
       var_dump($a);

[Zane]

I used Zanus part with the wildcard to do the following in combination with mikosiko's:

 $query = "SELECT * 
                  FROM users
                  WHERE voornaam ='$q%'";

No you didn't, you didn't use LIKE.  The query should be

 $query = "SELECT * 
                  FROM users
                  WHERE voornaam LIKE '$q%'";

[Ryflex]

Thanks for the help everyone,

 

The last comment of Zanus did the trick *mumbles about not using help in the full extend*

 

mikosiko I did change to voornaam but after I added the reply but the change didn't change it because my query was wrong.

THANKS ALL