joshgarrod Posted June 26, 2007 Share Posted June 26, 2007 Hi there everyone, I am a newb to PHP and MySQL but have managed to put together a search script that works... to an extent. I want to be able to search by a stock description however, I have to search the exact stock description for it to show up. I want it to return results containing the words they search. I would be extremely greatful for some help. Thanks guys. Below is the script: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Search for spares</title> </head> <body> <h2>Search</h2> <form name="search" method="post" action="<?=$PHP_SELF?>"> Seach for: <input type="text" name="find" /> in <Select NAME="field"> <Option VALUE="ID">Stock ID</option> <Option VALUE="StockNumber">Stock number</option> <Option VALUE="StockDescription">Stock description</option> </Select> <input type="hidden" name="searching" value="yes" /> <input type="submit" name="search" value="Search" /> </form> <? //This is only displayed if they have submitted the form if ($searching =="yes") { echo "<h2>Results</h2><p>"; //If they did not enter a search term we give them an error if ($find == "") { echo "<p>You forgot to enter a search term"; exit; } // Otherwise we connect to our Database mysql_connect("localhost", "sto21", "ixhp388") or die(mysql_error()); mysql_select_db("sto21") or die(mysql_error()); // We preform a bit of filtering $find = strtoupper($find); $find = strip_tags($find); $find = trim ($find); //Now we search for our search term, in the field the user specified $data = mysql_query("SELECT * FROM stock WHERE upper($field) LIKE'%$find%'"); //And we display the results while($result = mysql_fetch_array( $data )) { echo $result['ID']; echo " "; echo $result['StockNumber']; echo "<br>"; echo $result['StockDescription']; echo "<br>"; echo "<br>"; } //This counts the number or results - and if there wasn't any it gives them a little message explaining that $anymatches=mysql_num_rows($data); if ($anymatches == 0) { echo "Sorry, but we can not find an entry to match your query<br><br>"; } //And we remind them what they searched for echo "<b>Searched For:</b> " .$find; } ?> </body> </html> Quote Link to comment Share on other sites More sharing options...
TreeNode Posted June 26, 2007 Share Posted June 26, 2007 You can do a full-text search (which doesn't sound like what you want) or you'll need to build the query more than what you have already. Try exploding the input and build the query like so: $find_array = explode(" ", $find); $find_sql = ""; // init foreach ($find_array as $temp) $find_sql .= "LIKE '%$temp%' OR "; $find_sql = substr($find_sql, 0, -3); // throw away last OR statement $data = mysql_query("SELECT * FROM stock WHERE upper($field) $find_sql"); Quote Link to comment Share on other sites More sharing options...
joshgarrod Posted June 26, 2007 Author Share Posted June 26, 2007 Ok, so where do I place this piece of code, could you copy my above and place it where it need be. Thank you so much for your help Quote Link to comment Share on other sites More sharing options...
TreeNode Posted June 26, 2007 Share Posted June 26, 2007 replace $data = mysql_query("SELECT * FROM stock WHERE upper($field) LIKE'%$find%'"); with $find_array = explode(" ", $find); $find_sql = ""; // init foreach ($find_array as $temp) $find_sql .= "LIKE '%$temp%' OR "; $find_sql = substr($find_sql, 0, -3); // throw away last OR statement $data = mysql_query("SELECT * FROM stock WHERE upper($field) $find_sql"); Quote Link to comment Share on other sites More sharing options...
Illusion Posted June 26, 2007 Share Posted June 26, 2007 this might be helpful to you http://www.phpfreaks.com/forums/index.php/topic,145068.0.html Quote Link to comment Share on other sites More sharing options...
joshgarrod Posted June 26, 2007 Author Share Posted June 26, 2007 Cheers TreeNode works a treat thanks a lot Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.