# MySQL and PHP Search Function



## stu673

Hi guys, I am (trying to) write a search function for my website but having a bit of dificulty!

the major problem i think is how to you find the number of results of the MySQL query?

im using 

While ($row=mysql_fetch_array($result,MYSQL_ASSOC)){
....
}

to loop through and print out all of the results but am keen to introduce a function that recognises when there is only one result and immediately loads up that page instead of the search results. 

Any ideas? I think its fairly simple, i just cant seem to get it working!


----------



## stu673

Sorry, forgot to mention the second problem. I am also keen to display the product name in the search listings (which i can do no problem) but also display about 2 lines worth of the product description. Is there a php function to truncate the result?


----------



## dogmatic69

easy one there... $total = count($result); echo $total; then you can say if ($total == 1 ) //go right there else if ($total > 1) // do whatever else //do nothing... sorry for all on one line but i cant user enter for some reason


----------



## Redcore

stu673 said:


> the major problem i think is how to you find the number of results of the MySQL query?


Easy.



PHP:


<?php
$myquery = mysql_query("SELECT fname, lname, gpa FROM students WHERE class = 'senior'"
$recordcount = mysql_num_rows($myquery);
?>

So if you have 400 students and only 50 seniors, it'll only pull up 50 records - therefore $recordcount should equal 50.

To loop through and print those records...



PHP:


<?php
$i = 0; // this is just the incremental number
while($i < $recordcount){
// Define the database result for this record into a variable
$fname=mysql_result($myquery,$i,"fname");
$lname=mysql_result($myquery,$i,"lname");
$gpa=mysql_result($myquery,$i,"gpa");

// Now print the results. Example:
// "Brady, Tom - 4.0 GPA"
echo $lname . ", " . $fname . " - " . $gpa . " GPA";

// Now add one more to the incremental number and loop around
++$i;

}

// If there are no records returned, tell the user
if($recordcount == 0){
echo "There are no records returned for your search query.";
}
?>




stu673 said:


> ...but am keen to introduce a function that recognises when there is only one result and immediately loads up that page instead of the search results.


It can only recognize that there is only one result AFTER it has run through the same type of search script. Anyways, it doesn't take long to run this type of query. To make it more efficient, don't use "SELECT * FROM..." - instead specify what you want to pick out, as I did in the example query. If you have 10 fields for each student (in keeping with my example) and only want to extract 3 fields - if you're pulling 50 records, it's a waste of resources to 350 extra fields you won't be using. That isn't much of a deal if it's just your own personal script that only you and maybe one other person will be using once in awhile - but in a production environment (like any public website) it's just good practice to be efficient and watch how you script. :tongue:


----------

