Jason0391 Posted November 23, 2010 Share Posted November 23, 2010 Can someone please double check my search query to make sure it is the minimum and most efficient coding. My goal is an exact match search of field1 from my database table and list field1, field2, field3, field4 and field5 if the part number is in the database or no results if it is not. The query works as is but I have a feeling it could be done more efficiently or professionally and minimum work for my poor little server. Thanks in advance. <?php //connect to the database include("./databaseconnect.php"); //get query $q=$_GET['q']; //convert query to uppercase & remove all spaces and special charactars $q=strtoupper(preg_replace("/[^A-Za-z0-9]/","",$q)); //if blank query if ($q == "") { echo "You did not enter a search term"; } else { //exact match only query $query = "SELECT * FROM ".$dbtable." WHERE field1 like \"$q\""; $result = mysql_query($query); //if query returns no results if(mysql_num_rows($result)==0) { echo "<span class=\"noresults\">There were no results for your search</span>"; //display database results } else { while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo "<span class=\'results\'>SEARCH RESULTS</span><br /> <span class=\'list\'>Part Number: ".$row['field1']."<br /> Manufacturer: ".$row['field2']."<br /> Cost per unit: ".$row['field3']."<br /> Warehouse Location: ".$row['field4']."<br /> Quantity Available: ".$row['field5']."<br /> Notes: ".$row['field6']."<br /> </span>"; } } } ?> Quote Link to comment Share on other sites More sharing options...
MatthewJ Posted November 23, 2010 Share Posted November 23, 2010 Your query is pretty straight forward, the only thing I would recommend would be to use an exact match search ( = instead of LIKE). Obviously, the entered keyword has to be an exact match. for the most part, databases are built specifically to handle requests like this. Quote Link to comment Share on other sites More sharing options...
Jason0391 Posted November 23, 2010 Author Share Posted November 23, 2010 Thanks for the input, I appreciate it. One more thing... the query is searching the database twice, once to count the results and again to display the results. Is this the only way to do this? Quote Link to comment Share on other sites More sharing options...
MatthewJ Posted November 23, 2010 Share Posted November 23, 2010 In the code you posted, it is only being queried once Quote Link to comment Share on other sites More sharing options...
Jason0391 Posted November 23, 2010 Author Share Posted November 23, 2010 ok, I thought everytime it ran $results like in if(mysql_num_rows($result)==0) and while($row = mysql_fetch_array($result, MYSQL_ASSOC)) it was running the query $query = "SELECT * FROM ".$dbtable." WHERE field1 like \"$q\""; $result = mysql_query($query); . sorry for the confusion... I will get it oneday Quote Link to comment Share on other sites More sharing options...
MatthewJ Posted November 23, 2010 Share Posted November 23, 2010 Nope, the $result variable holds a mysql resource. The functions get the data you need from the same resource. 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.