Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

MySQL Query HELP NEEDED!


  • Please log in to reply

#1
Dallasjmoore

Dallasjmoore

    New Member

  • Member
  • Pip
  • 4 posts
I'm looking for the best way to provide the best search results.

The database is much more complex, but lets keep it simple for this example.
Lets say I only have one table named "products" that has the following fields:
1. category
2. vehicle_make
3. vehicle_model
4. vehicle_year
3. product_name
4. product_description
5. price


The search form has 3 fields:
1. Category (list menu)
2. Vehicle Make (list menu)
3. Keyword (text field)

Here is quick example at: http://monsterbumpers.com/search7.php

Question 1: (Search Form)
Within the dropdown lists "category" and "vehicle make" I've created a select option of "Any" for any category or any make. What should I set the value for these two selections to allow a search through all categories and/or all vehicle makes?

Question 2: (Results Page)
How can I write a query to search by:
- a single variable (ex: search just by 'category' only),
- a combination of variables (ex: 'vehicle make' & 'keyword' only),
- or all of the variables ('category', 'vehicle make' , & 'keyword')

Question 3:
How can I order the results by relevance grouped by category and vehicle_make?
  • 0

Advertisements


#2
BirdieUK

BirdieUK

    Member

  • Member
  • PipPip
  • 33 posts
Why not just do it the easy way?

Make mutliple if statements because i guess you're using php. So..

if($_POST[category] != "" AND $_POST[keyword] == "")
{
$sql = "SELECT * FROM products WHERE vehicle_category='$_POST[category]'";
}
elseif($.......
Seems like a logical method?
  • 0

#3
Dallasjmoore

Dallasjmoore

    New Member

  • Topic Starter
  • Member
  • Pip
  • 4 posts
Thanks! I'm still having a little trouble with the correct PHP code. Could you elaborate on the syntax if I'm using the "get" method instead of "post"?
  • 0

#4
BirdieUK

BirdieUK

    Member

  • Member
  • PipPip
  • 33 posts

Thanks! I'm still having a little trouble with the correct PHP code. Could you elaborate on the syntax if I'm using the "get" method instead of "post"?

Er, you simply just change $_GET to $_POST. Sorry i just typed it off my head.

Edited by BirdieUK, 13 July 2007 - 07:17 PM.

  • 0

#5
Stryker250

Stryker250

    Member

  • Member
  • PipPip
  • 16 posts
Tru this,or atleast your form and code should look something like this...

This is just the search box


<form method="get" action="search.php">
<p><input type="text" name="keywords" /></p>
<p><select name="searchFor">
<option value="any">Any</option>
<option value="cat">Category</option>
<option value="make">Vehicle Make</option>
</select></p>
<p><input type="submit" value=" Search " /></p>


Here is the php script (search.php)


$keyword = $_GET['keywords'];

if(isset($_GET['searchFor']) && $_GET['searchFor'] == 'cat'){
//The person is searching for a category

$where = "WHERE category LIKE '%$keyword%' ORDER BY price";

}else if(isset($_GET['searchFor'] && $_GET['searchFor'] == 'make'){
//The user is searching for a make

$where = "WHERE vehicle_make LIKE '%$keyword%' ORDER BY price";

}else if(isset($_GET['searchFor'] && $_GET['searchFor'] == 'any'){
//The user has chosen to search for any and all occurnces of his chosen keywords

$where = "WHERE category LIKE '%$keyword%' OR vehicle_make LIKE '%$keyword%' OR vehicle_model like '%$keyword%' OR 

vehicle_year LIKE '%$keyword%' OR product_name LIKE '%$keyword%' OR product_description LIKE '%$keyword%' ORDER BY price";
}

$query = "SELECT * FROM theTableName ".$where."";
$result = mysql_query($query);
while($info = mysql_fetch_assoc($result)){
$productName = $info['product_name']'
$productDescr = $info['produce_description'];
$price = $info['price'];

echo "<p>".$productName."<br />".$productDescr."<br />".$price."</p>";

}

/***
*  I used the LIKE option in the queries rather than the = sign because
*  the LIKE option searches for any occurance of the text / keywords in the
*  given table field, instead of looking for exact matches
***/


Hope that helps
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP