search database

[Stephen W]

Hi
I am fairley new at this, and trying to search my database with mutiple questions and to return the exact answer instead of listing everything in the query, ie if I search for blue vw golf I want it to return all the blue vw golf not all the vw', all the blue's and all the golf's. Can anyone help ?

Steve

[AutumnsDecay]

MySQL allows you to use 'AND' or 'OR within you 'WHERE' argument.

Your statement would look something like:

SELECT * FROM cars WHERE color = 'Blue' AND make = 'VW';
 

This would select all blue VWs in your 'cars' table.

Replace the information I've populated with your own, ie. if your table isn't named 'cars', switch that to what it is called. Same with 'color' and 'make'.

[Stephen W]

thank you, but what would i have to put if i wanted a red one, would i have to keep duplicating same string ?

[AutumnsDecay]

Are you searching from a user accessible form?

If it's on a back-end of a website, you could just make yourself a form that has two 'select' boxes. The options of the select box could be the available options of colors and makes you want to offer. Example:

-------FORM-------

Choose your Make: _______________
|_Option 1: VW
|_Option 2: Chrysler
|_Option 3: Etc...

Choose your Color: _______________
|_Option 1: Blue
|_Option 2: Black
|_Option 3: Etc...

-----END FORM-----

Then compile your MySQL statement based on the selections the user has put in.

$color = $_POST['color'];
$make = $_POST['make'];
 
$query = "SELECT * FROM cars WHERE color = '$color' AND make = '$make'";
 

[Stephen W]

Hi
no I do not want to go down the route of having different check boxes, it is just a search box, not sure if I have to put a weighted search in but have no idea of how to acomplish that, or if it needs to search different tables, and again not sure how to set that up

[AutumnsDecay]

Essentially this would just be two boxes side by side.

A standard search box IS a form element with (usually) one text input and a submit button.

All we're doing here is creating a textbox that has set values. Not only is this an excellent choice for the type of MySQL you want to run, most users prefer this as they're having to guess what models you have available. The list will be right in front of them in the form of a multiple choice select box.

[Stephen W]

Hi
thankyou for your reply but this is not the route I want to go down, I do not want a form

Thank You
Merry Christmas

[Rabbit]

You don't want a form? A "search box" as you call it is a form.

We have no idea how your table is structured. That affects how you will be able to search for records.

[Stephen W]

ok ... I do not want radio buttons, I do not want drop down lists, all I want is the one "form" box with a submit on it the will search whatever query that is entered into it and return the correct result... not a list of variables

[Rabbit]

Be that as it may, you still have not told us how your database is structured. As I said, how it is structured will limit how you can query it.

[Stephen W]

I have 6 coulombs, id type make model colour and image ...but this is one of the questions do I need to change the table in order to achieve the result ? does it need to be weighted ?

[Rabbit]

I'm not sure what you mean by weighted. The layout is fine. The question now is what is the structure of the input? Will it always be color make model in that order?

I don't know MySQL but I believe it has a match against keyword that you can use.

[AutumnsDecay]

Here's the issue.

MySQL queries are structured, hence the name 'Structured Query Language'.

As mentioned earlier, a typical MySQL statement would be

SELECT * FROM cars WHERE color = 'red' AND make = 'VW'

The PHP to make this be able to be dynamic is:

SELECT * FROM cars WHERE color = '$color' AND make = '$make'

Now you have to ask, how is $make and $color obtaining it's values? Via user input through a single text box? Okay, well what potential issues might we run into? Well what if the user puts in "vw red" as a search? Even if we made a PHP code to select the first word in the box as the color, and the second word as the make (ie, red vw), we're still trusting that the user will be smart enough to read instructions.

So let's say the user enters 'vw red' as a search, our PHP take the first word and make it '$color' and then it takes the second word and makes it '$make', Well in this case that's wrong, as our SQL statement will then end up quering the database like so:

SELECT * FROM cars WHERE color = 'vw' AND make = 'red'

Which will not work. What you could TRY doing (if you really are set on ONE textfield) is adding a 'description' to your SQL table for each item, and have the query search for all cars with 'red' and 'vw' in the description field. But again, that's a pretty bad way of doing it.

If you like at 95% of the autodealer sites, you'll see that they offer drop down options for this very reason. There's too many variables for a standard user to get right, so you should define them for the user wherever possible. This would be one of those times.

[Stephen W]

hi
got it sorted thanks .... using explode implode and using the function of using + as to add the words together in the scripting of the search

thank you for your help

kind regards and happy new year

Steve