Jump to content

Archived

This topic is now archived and is closed to further replies.

kikilahooch

search through different fields

Recommended Posts

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

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.