search through different fields

[kikilahooch]

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>";
[/code]

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!)

[fenway]

I'm not sure what you mean... you're only "doing" anything with the last query... sounds like you want to use a series of OR condition in the where clause, or UNION the results (which can often be faster, depending on the indexing).

[HeyRay2]

Perhaps...

[code=php:0]
$query = "SELECT * FROM product WHERE dept LIKE '%".$trimmed."%' OR type LIKE '%".$trimmed."%' OR prodName LIKE '%".$trimmed."%' OR shopName LIKE '%".$trimmed."%' ORDER BY dept, type, prodName, shopName, brand"
[/code]

Or you could look into setting up your database for full-text searching with [url=http://www.phpfreaks.com/tutorials/129/0.php]this PHP Freaks Tutorial[/url].

;D