search a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

me666
Forum Commoner
Posts: 87
Joined: Wed Oct 08, 2008 5:04 pm

search a table

Post by me666 »

hey i have a scropt to search a table, so if the username in the table is 'test' and you search for 'test' it displays that users info, but i want it so that if you search for 'te' it will display all the usernames that has 'te' in them including 'test'.. i have the same script to search for fornames and surenames, and if you search for a surname that is used for multipule users then it displays them all, but same again just want it so you can search for part of a name and will display all the names that have that part in it...
thanks in advance

leo
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: search a table

Post by jaoudestudios »

Post the code you have so far! Then we can help you modify it accordingly!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: search a table

Post by califdon »

What you're looking for is the LIKE operator in SQL. Check out these:
http://www.sql-tutorial.net/SQL-LIKE.asp
http://www.w3schools.com/SQL/sql_like.asp
me666
Forum Commoner
Posts: 87
Joined: Wed Oct 08, 2008 5:04 pm

Re: search a table

Post by me666 »

the serve i use has gone down at min, but i have a backup copy somewhere, ill get it up as soon as found it, thanks
me666
Forum Commoner
Posts: 87
Joined: Wed Oct 08, 2008 5:04 pm

Re: search a table

Post by me666 »

ahh i have got it, wernt sure where it was though... here is the bit i use... i have made it to run with other scripts so that if ur not logged in it sends you to a different page, all this works fine... just the fact of wantin it to find 'test' if i search 'te'...

Code: Select all

Search for a Username here</br>
  <form>Search For Username: <input type="text" name="uname" maxlength="30"><input type="submit" name="search" value="Search"></form>
     <?php 
     if($logged_in){
$result = mysql_query("SELECT * FROM users WHERE username = '$uname'")or die(mysql_error());
  while($row = mysql_fetch_object($result))
  {
  echo "<ul>\n";
  echo "<li><b>Username: $row->username</li>\n<li>Gender: $row->sex</li>\n<li>Birthday: $row->day\n$row->month\n</li>\n<li>Date Registered: $row->signup</li>\n<li>About Them: $row->ame</li>\n<li>Hobbies: $row->hobbies</li>\n<li>Children: $row->kids</li>\n<li>Boys: $row->kidsboy</li>\n<li>Girls: $row->kidsgirl</li>\n<li>Pets: $row->pets</li>\n<li>Dogs: $row->petdog</li>\n<li>Cats: $row->petcat</li>\n<li>Birds: $row->petbird</li>\n<li>Other Pets: $row->petother $row->petother1</li>\n";
  echo "</ul>\n";
  }
   }
   else{
   { echo("<meta http-equiv='refresh' content='0; url=http://leos-webby.awardspace.co.uk/Error/401'>"); 
} 
   } ?>
me666
Forum Commoner
Posts: 87
Joined: Wed Oct 08, 2008 5:04 pm

Re: search a table

Post by me666 »

ahh ha thanks you califdon... i used to do mIRC scripting so i thought something along the lines of '*searchstring*' but it turns out its supposed to be '%searchstring%' or with just the % at 1 end and a LIKE rather than = ... when my server is back up and running ill giv it a test, i'll let u know if it works for me... thank you both for your help :)

leo
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: search a table

Post by novice4eva »

This must be what you are looking for :

Code: Select all

$result = mysql_query("SELECT * FROM users WHERE lower(username) like '%".strtolower($uname)."%'")or die(mysql_error());
me666
Forum Commoner
Posts: 87
Joined: Wed Oct 08, 2008 5:04 pm

Re: search a table

Post by me666 »

great, thanks but can i just ask you.... what does the.... lower(username) like '%".strtolower bit mean?

thanks
me666
Forum Commoner
Posts: 87
Joined: Wed Oct 08, 2008 5:04 pm

Re: search a table

Post by me666 »

u see i thought it would work if you put..... mysql_query("SELECT * FROM users WHERE username LIKE '%$uname%'")or die(mysql_error());
do say if you think this wont work though, im just not sure what the ower(username) like '%".strtolower means

thanks
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: search a table

Post by jaoudestudios »

You dont really need both of them, the lower and strtolower as the MySql LIKE is not case sensitive.

lower is a MySql function to set the string to lower case and the strtolower is a php function to set the string to lower case!
me666
Forum Commoner
Posts: 87
Joined: Wed Oct 08, 2008 5:04 pm

Re: search a table

Post by me666 »

ahh right i get it.. no prblem, i lknow its not case sensative as i have many usernames with the first caracter caps and it dosent effect it... so as i sed before using a code like this...
mysql_query("SELECT * FROM users WHERE username LIKE '%$uname%'")or die(mysql_error());
Would do the job no problem then.
for example i have 4 usernames called, test test2008 best and 45test
and i search for te using that code it will return the usernames, test test2008 and 45 test
and if i search for st it will return all 4 usernames.
am i right in saying that?

thanks for the help with this
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: search a table

Post by jaoudestudios »

Yes. I think you should try it!!! You will learn a lot more!
me666
Forum Commoner
Posts: 87
Joined: Wed Oct 08, 2008 5:04 pm

Re: search a table

Post by me666 »

yep, thats how i know what i do, try it, test it, if it works leave it, if it dont work, look again and chnge bits... cheers mate thanks for the help :)
me666
Forum Commoner
Posts: 87
Joined: Wed Oct 08, 2008 5:04 pm

Re: search a table

Post by me666 »

i tried that as my server i use is back up and running, but when i load the page it displays all the users from the table... if i then search, using the form, for 'te' it will only display test and anyother names with the letters te in them, thats fine, but how would i get it to display no results when the page loads, or the search box is empty?

thanks for the help
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: search a table

Post by jaoudestudios »

Put a conditional statement before your code that goes to get the results from the database, include the code that displays the results too.
Post Reply