Listing Search Results

[supanoob]

i have the following code, but i want to change it so it searches and lists anything that is LIKE the search for example:

if i searched for bat it would bring up all names with bat in it.

[code]<?php

if ($searched == 'yes')
      {
      $hostname="localhost";
      $username="twottk_Stefan";
      $password="364988979a";
      $dbname="twottk_twottk";
      $usertable="accounts";
     
      $link = mysql_connect($hostname, $username, $password);
     
           
      $query2 = "SELECT char_name, char_id FROM accounts WHERE char_id LIKE '$search' OR char_name LIKE '$search'";
      $result2= mysql_query ($query2);
      $num_rows=mysql_num_rows($result2);

      $row=mysql_fetch_array($result2);
      $search_char = $row['char_name'];
      $search_char_id = $row['char_id'];
     
      echo "$search_char ($search_char_id)";
     
     
      }

?>[/code]

[HuggieBear]

Change your query to this:

[code]$query2 = "SELECT char_name, char_id FROM accounts WHERE char_id LIKE '%$search%' OR char_name LIKE '%$search%'";[/code]

Notice the percent (%) signs in there.  They're wild cards for multiple characters.

Regards
Huggie

[supanoob]

what about the listing part what sorta loop would i use a while loop?

[HuggieBear]

Yes,

[code]<?php
// Loop through the results
while($row = mysql_fetch_array($results2, MYSQL_ASSOC)){
  echo $row['char_name'] . "(" . $row['char_id'] . ")";
}
?>[/code]

Regards
Huggie

[supanoob]

thanks, but for some reason it always misses out the first result in the db

for example, i have 3 people in my db with s in there name but it only shows the last 2 or the 2 with the highest id ><

[HuggieBear]

OK, well then either the query, or the data is incorrect.

The query I've given you will work even if you only put a single character in.  What happens when you run the query in phpMyAdmin?  Does it return three rows?  If it does, then maybe you can post all of your query code here?

Regards
Huggie

[supanoob]

[code]<?php

if ($searched == 'yes')
      {
      $hostname="localhost";
      $username="<username>";
      $password="<password>";
      $dbname="<db_name>";
      $usertable="accounts";
     
      $link = mysql_connect($hostname, $username, $password);
     
           
      $query2 = "SELECT char_name, char_id FROM accounts WHERE char_id LIKE '%$search%' OR char_name LIKE '%$search%'";
      $result2= mysql_query ($query2);
      $num_rows=mysql_num_rows($result2);     
      $row=mysql_fetch_array($result2);
     
      while($row = mysql_fetch_array($result2, MYSQL_ASSOC))
      {
     
      $search_char = $row['char_name'];
      $search_char_id = $row['char_id'];
     
     
      echo "$search_char ($search_char_id)<br>";
      }
     
     
     
      }

?>[/code]

that is all the code for the search and echo.

[HuggieBear]

In that case you're data must be incorrect, the code should pull all members that have an 's' in their name so long as you put 's' into $search.

Also, as a side note, I'm assuming that $search is coming from a form or URL parameter of some kind, if it is then you should really be using the global name for it, something like $_GET['search'] or $_POST['search'] as opposed to relying on 'Register Globals' being switched on.

Check that your query returns the right result in phpMyAdmin before continuing.

Regards
Huggie

[supanoob]

ok thanks :D and it is coming from a form