Wrox Programmer Forums
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 30th, 2004, 05:41 PM
Registered User
Join Date: Jul 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default search help !!

i am trying to figure out how i can search a database with multiple columns. you can look at the database results here (limited output): http://test.rowait.com/new/inventory.php

basically what i want is to search like: "93 accord" and should get the expected results.

there is a year_from and year_to column which comprises the years for the first column.

so far, i have managed to get results only by a particular category, ie. part name, year, make, model, etc, only one, but not combinations.

thanx in advance.


Old July 31st, 2004, 08:07 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts

On the whole, your search follows the correct general ideas, since it seems to search the database on the basis of WHERE year LIKE '%$q%' OR make LIKE '%$q%' OR model LIKE '%$q%' OR part_name LIKE '%$q%'...

The inclusion of a combined free text query, where your programme, on receiving input, must parse the string in accordance with some predefined rules for search string format, really depends on how many different formats you are willing to cater for. You could build up an array of plausible input types and try to catch each one out, but this is inevitably going to be difficult and offers the possibility for items slipping through the search. After all, your aim is really to get car parts sold, isn't it?

A simpler option might be to impose more structure over the method of input. Have things like the year as a drop down list of reasonable options. Perhaps have the Make and Model presented similarly (and, while you're at it, turn your "make" and "model" fields, in your database, into types such as enum('Acura', 'Honda', 'Subaru' ... etc.) while you're at it, to help enforce some uniformity over your data - no point having a 'Subaru Impreza left front fender' sitting on the shelf gathering dust, simply because someone spelt it 'Impretza' when putting it in the database, so it never turns up in searches, is there ;).

Then you simply pull out your search values as $year=$_GET['year'], $make=$_GET['make'] and $q=$_GET['q'] and run a query like
SELECT <field list> FROM inventory WHERE year_from>='$year' OR year_to<='$year' AND Make='$make' AND part_name LIKE '%$q%'

Furthermore, you could cater for the 'any' option of Make and Year, by having the first element of each dropdown as <option value="">-Any-</option>. Build your query string over several lines and test for the "Any" options for year and make thus:

$query = "SELECT <field list, whatever> FROM inventroy WHERE ";
$query .= "year_from>='{$_GET['year']} AND year_to<='{$_GET'year']}' AND ";
$query .= "AND make='{$_GET['make']}' AND ";
$query .= "part_name LIKE '%$q%' AND other_fields LIKE '%$q%' AND so_on LIKE '%$q%' ORDER BY make";

Anyway, that's probably how I'd do it. Your aim is to sell car parts, like I say, so make your search form as explicit as possible about how the customer is to get to see the parts they want. After all, if they're just stuffed their Subaru Impreza into a hedge, they won't mind foregoing a little ease of input for the chance to get their hands on that new fender, are they?

Let us know how you get one.

Take it easy,

Similar Threads
Thread Thread Starter Forum Replies Last Post
Search button doesn't search Access DB cbones Visual Studio 2008 1 October 27th, 2008 07:36 PM
New text search doesn't preselect search string planoie Visual Studio 2005 0 July 23rd, 2007 06:47 AM
File Search / Indexing Search with .net 2.0 maulik77 ASP.NET 1.0 and 1.1 Basics 2 March 15th, 2007 12:45 AM
Search Engine for Full-text Search Kala ASP.NET 1.0 and 1.1 Professional 2 August 29th, 2004 02:16 AM
Search engine that search through local drive! wenzation Classic ASP Basics 0 August 26th, 2003 09:15 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.