database search query optimization

[Jason0391]

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>";
}
}
}
?>

[MatthewJ]

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.

[Jason0391]

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?

[MatthewJ]

In the code you posted, it is only being queried once

[Jason0391]

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

[MatthewJ]

Nope, the $result variable holds a mysql resource. The functions get the data you need from the same resource.