search a table
Moderator: General Moderators
search a table
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
thanks in advance
leo
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: search a table
Post the code you have so far! Then we can help you modify it accordingly!
Re: search a table
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
http://www.sql-tutorial.net/SQL-LIKE.asp
http://www.w3schools.com/SQL/sql_like.asp
Re: search a table
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
Re: search a table
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'>");
}
} ?>Re: search a table
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
leo
- novice4eva
- Forum Contributor
- Posts: 327
- Joined: Thu Mar 29, 2007 3:48 am
- Location: Nepal
Re: search a table
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());Re: search a table
great, thanks but can i just ask you.... what does the.... lower(username) like '%".strtolower bit mean?
thanks
thanks
Re: search a table
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
do say if you think this wont work though, im just not sure what the ower(username) like '%".strtolower means
thanks
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: search a table
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!
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!
Re: search a table
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
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
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: search a table
Yes. I think you should try it!!! You will learn a lot more!
Re: search a table
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 
Re: search a table
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
thanks for the help
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: search a table
Put a conditional statement before your code that goes to get the results from the database, include the code that displays the results too.