Searching Databases

[SkaFreaks]

Hey
I would like to learn more about how to create a search on a page to search a database. I've looked around but can't seem to find anywhere that explains this. Can anyone help me learn or suggest a place to check out for help with this?
Thanks

[bassrek]

It all depends on what fields you want your users to search on. You'd provide an HTML form with the field(s) you want to have available for search. Then the form would submit to another page where you'd query the database.

For example, if you had band name as a search field for your site, you have a basic form:

<form id='search' action='search.php' method='post'>
<input type='text' name='BandName' id='BandName'><br>
<input type='submit' name='zSubmit' id='zSubmit'>
</form>


On your search.php page, you'd build a simple query like :

$result = mysql_query("SELECT * FROM albums WHERE band LIKE '%$_POST["BandName"]%'")

You'd then loop through the $result to display them to the user. If you have multiple search criteria, you can append AND statements to the end of your SELECT statement for more precise results.

Here's a link from scoutt's site that gets more detailed into PHP & MySQL:
http://www.sitepoint.com/article/530

[illogique]

$result = mysql_query("SELECT * FROM albums WHERE band LIKE '%$_POST["BandName"]%'")


will not work!


$result = mysql_query('SELECT * FROM albums WHERE band LIKE "%'.$_POST['BandName'].'%"')
or
$result = mysql_query("SELECT * FROM albums WHERE band LIKE '%".$_POST["BandName"]."%'")
or
$result = mysql_query("SELECT * FROM albums WHERE band LIKE '%{$_POST['BandName']}%'")

will

[bassrek]

But I though this
http://www.htmlforums.com/showthread.php?s=&threadid=33809
thread said that it would...particularly scoutt's post about 1/2 way down. I'm trying to clarify for myself, not to be confrontational...

[SkaFreaks]

Thanks for all the help.
I'll definitly try it all out when I get time.
Thanks!

[illogique]

Originally posted by bassrek
But I though this
http://www.htmlforums.com/showthread.php?s=&threadid=33809
thread said that it would...particularly scoutt's post about 1/2 way down. I'm trying to clarify for myself, not to be confrontational...

yes but you start the string with " and you have $_POST["bandname"]
so the string will stop at the first " it see! making it
"SELECT * FROM albums WHERE band LIKE '%$_POST["
which is invalid!

[bassrek]

Heh, so if I had just stuck with what I normally do, I would have been fine. OK, fair enough. It had been a while since I stuck my foot in my mouth I guess. :)

[SkaFreaks]

well, thanks for all the help, but that doesnt seem to work.

I have the database setup for shows. fields are
date, artist, city, statecountr, venue, cost, doorsshowtime

I have the form setup at http://www.musicfreaksonline.com/learningphp/try2.php
<form id='search' action='search.php' method='post'>
<input type='text' name='ArtistName' id='ArtistName'><br>
<input type='submit' name='zSubmit' id='zSubmit'>
</form>

Then the display code is on http://www.musicfreaksonline.com/learningphp/search.php
<?php
$db = mysql_connect("localhost", "NAME", "PASS");
mysql_select_db("DATABASE",$db);
$result = mysql_query("SELECT * FROM Shows WHERE artist LIKE '%".$_POST["ArtistName"]."%'")
if ($myrow = mysql_fetch_array($result)) {
do {
echo "<table border=1>\n";
echo "<tr><td>Date</td><td>Artist</td><td>City</td><td>State/Country</td><td>Venue</td><td>Cost</td><td>Doors/Show Time</td></tr>\n";
do {
printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</tr>\n", $myrow["date"], $myrow["artist"], $myrow["city"], $myrow["statecountry"], $myrow["venue"], $myrow["cost"], $myrow["doorsshowtime"]);
} while ($myrow = mysql_fetch_array($result));
echo "</table>\n";
} else {
echo "Sorry, no records were found!";
}
?>


then it gives me the error
arse error: parse error in /home2/big/s5299839/public_html/skafreaks/learningphp/search.php on line 5
everytime I try to search for something.
an artist that is in the database is 'Bad Manners' for testing purposes

Thanks for any and all help!

[illogique]

add ; at line 4

[SkaFreaks]

fixed that but now I'm getting another error

Parse error: parse error, expecting `T_WHILE' in /home2/big/s5299839/public_html/skafreaks/learningphp/search.php on line 13

[illogique]

if ($myrow = mysql_fetch_array($result)) {
do {
echo "<table border=1>\n";
echo "<tr><td>Date</td><td>Artist</td><td>City</td><td>State/Country</td><td>Venue</td><td>Cost</td><td>Doors/Show Time</td></tr>\n";
do {



you have 2 "do" but only 1 "while"
remove the first one...

[SkaFreaks]

DAMN! I shouldv'e caught that one. It was so easy. i need to start looking harder.

It now works perfectly! Thank you for all the help!

[scoutt]

Originally posted by bassrek
But I though this
http://www.htmlforums.com/showthread.php?s=&threadid=33809
thread said that it would...particularly scoutt's post about 1/2 way down. I'm trying to clarify for myself, not to be confrontational...
illogique is correct, that is why I said in my posts that is all depends on what you start with.

select("
select('

both are different. but it is always best to ask if you are not sure. :)

that is why I didn't want anybody to get confused. :)