Jump to content

search through different fields


kikilahooch

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.