search query, searching all fields in table

[ciaracous]

Hi,

I'm trying to create a search bar on my webpage. My web page is a clothes shop and I want to be able to search by brand, shop name, dept etc... Each of these is a field in the table product.

prodId int(10) No auto_increment
shopName varchar(30) No
prodName varchar(40) No
dept varchar(20) No
brand varchar(20) No
type varchar(20) No
image varchar(60) No
price double(3,2) No 0.00
NoInStock


This is the code I am using:


Code:

<?php 

$host= 

// Get the search variable from URL 
$var = @$_GET['q'] ; 
$trimmed = trim($var); //trim whitespace from the stored variable 

// rows to return 
$limit=10; 

// check for an empty string and display a message. 
if ($trimmed == "") 
{ 
echo "<p><b>Please enter a search...<B></p>"; 
exit; 
} 

// check for a search parameter 
if (!isset($var)) 
{ 
echo "<p><b>We dont seem to have a search parameter!<b></p>"; 
exit; 
} 

include "db.php"; 


$query = "select * from product where dept like \"%$trimmed%\" order by dept"; 

$query = "select * from product where type like \"%$trimmed%\" order by type"; 

$query = "select * from product where prodName like \"%$trimmed%\" order by prodName"; 

$query = "select * from product where shopName like \"%$trimmed%\" order by shopName"; 

$query = "select * from product where brand like \"%$trimmed%\" order by brand"; 

//db 
$result = mysql_query($query,$conn) or die(mysql_error()); 

//get the number of rows in the result set; should be 1 if a match 
if (mysql_num_rows($result) >= 1) { 

$image = mysql_result($result, 0, 'image'); 
$prodName = mysql_result($result, 0, 'prodName'); 
$price = mysql_result($result, 0, 'price'); 



$numresults=mysql_query($query); 
$numrows=mysql_num_rows($numresults); 


if ($numrows == 0) 
{ 
echo "<h4>Results</h4>"; 
echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";

when I use this code it only returns results for the last query in the list which in this case is

$query = "select * from product where brand like \"%$trimmed%\" order by brand";

So the only results it returns is when you enter a brand name in, when I enter in a shop name or dept name it says there are no results.

How can I get it to return results for all of the fields. (Please try to make your answer as simple as possible because I'm really new to this!)

[paulq]

Well, your are replacing your query string each you set it equal to something so naturally the final value would be brand name. What you would want to do is set up a way of asking what field they would like to search using a drop down menu (like amazon.com has "Search...Books\Popular Music\DVDs") and then check what they selected and then something like this...

PHP Code:

switch($category)
{
    case 'dept':
    $query = "select * from product where dept like \"%$trimmed%\" order by dept";
    break; 
    case 'type':
    $query = "select * from product where type like \"%$trimmed%\" order by type"; 
    break;
    ...
    default:
        print "Error, no category selected\n";
}

$result = mysql_query($query,$conn) or die(mysql_error()); 


Or you could even use what they select as the variable in one statement like so...:

PHP Code:

$query = "select * from product where \"$category\" like \"%$trimmed%\" order by \"$category\"";

$result = mysql_query($query,$conn) or die(mysql_error()); 


It is crucial that you validate and sanitize all user input before you pass it to an SQL query! As your code stands now, you are leaving yourself wide open for an easy SQL injection attack. You should look into stored procedures or at least use a function similar to mysql_real_escape_string. For more information see here. Hope this information helps!

[arnyinc]

$query = "select * from product where dept like \"%$trimmed%\" OR type like \"%$trimmed%\" OR prodName like \"%$trimmed%\" OR shopName like \"%$trimmed%\" OR brand like \"%$trimmed%\" order by dept, type, prodName, shopName, brand";

You may want to sort differently.