[RESOLVED] search and display results example

lowlyhack

Where might I get a generic script that has a search box which searches a mysql database and displays all pertinent results? I am assuming there would be a while loop in there somewhere but I can't seem to figure out how to get it to display all results and not just one (or the first one)

p2grace

A simple search engine would look as follows:

$query = "SELECT `field1`,`field2` FROM `db` WHERE `field1` LIKE '%$search%' OR `field2` LIKE '%$search%'";
$run = mysql_query($query);
while($arr = mysql_fetch_assoc($run)){
   extract($arr);
   echo "$field1 $field2 <br />";
}

lowlyhack

@: I guess my real question is regarding your code, the echo in line #6 will only echo the first result, how would I have it echo all results? I'm also speaking in terms of having my search results as hyperlinks to a record detail page and the possible need for a google-like pagination if the search results are more than the specified results page is designed to accommodate.

bradgrafelman

lowlyhack wrote:
the echo in line #6 will only echo the first result

What makes you say that? If that echo() statement only produces one line, then only one row was matched in the table; otherwise, it will output all rows matched.

lowlyhack wrote:
I'm also speaking in terms of having my search results as hyperlinks to a record detail page

Well then you'd simply add a link such as record.php?id=5 where '5' is some unique data that you use to identify each row (commonly an AUTO_INCREMENT column).

lowlyhack wrote:
and the possible need for a google-like pagination

A simple search for 'pagination' (or perhaps even 'MySQL pagination') of this board should produce several examples for you.

lowlyhack

bradgrafelman wrote:
What makes you say that? If that echo() statement only produces one line, then only one row was matched in the table; otherwise, it will output all rows matched.

Not exactly sure why I made that assumption, I think it must have come from my past experimentations with php... my newbieness probably caused me to draw the wrong assumption of that.

Well then you'd simply add a link such as record.php?id=5 where '5' is some unique data that you use to identify each row (commonly an AUTO_INCREMENT column).

So then are you saying the following code should do the job?

$query = "SELECT `recnum`,`field1`,`field2` FROM `db` WHERE `field1` LIKE '%$search%' OR `field2` LIKE '%$search%'";
$run = mysql_query($query);
while($arr = mysql_fetch_assoc($run)){
   extract($arr);
   echo "<a href="record.php?id=$recnum">$field1 $field2</a> <br />";
}

not sure my syntax is correct regarding the quotes though....

bradgrafelman

lowlyhack wrote:
not sure my syntax is correct regarding the quotes though....

Indeed there's a problem. Notice where the syntax highlighting goes awry? If you're going to use double quotes as your string delimiter, you must escape any double quotes that you want to actually appear in the string with a backslash. Example:

echo "This "string" is broken and will cause errors!";
// however...
echo "This \"string\" isn't broken and will parse just fine!";

And yes, if recnum holds some unique value (e.g. it is an AUTO_INCREMENT column, or else has a PRIMARY/UNIQUE index on it), that would allow you to retrieve the row that the user clicked on and display more/whatever information you want pertaining to that row.

lowlyhack

Thank you very much.