MySQL select where any = something

[Schmoopy]

Hey, I have quite a few columns, and I'm in the process of making a search bar, but I want to have it to so that when the database is queried instead of having to say:

mysql_query("SELECT * FROM table WHERE column1 = '$search' OR column2 = '$search' OR column3 = '$search' OR etc etc...");

I want to just be able to say:

mysql_query("SELECT * FROM table WHERE any of the columns = '$search'");

Is that possible, or do I have to list every column?

[Twey]

As far as I'm aware, there's no method to do this in MySQL. I think that it's probably an indication of bad design, generally.

[Schmoopy]

How else could you search through all columns and it not be bad design :S

[magicyte]

To make it somewhat easier, you could make an array of the column names, then form a loop which ties the column names into a string. This string could then be put into mysql_query() for execution.

Example:


<?php

// search query
$search = "dogs";

// table name
$table = "tableName";

// column names
$c = array(
"column0",
"column1",
"column2",
"column3",
"column4"
);

// mysql query string
$str = "SELECT * FROM `{$table}` WHERE ";

// ties in columns with mysql query string
for($i = 0; $i < count($c); $i++) {
if($i == (count($c)-1)) { $str .= ("`{$c[$i]}` = '{$search}'"); }
else { $str .= ("`{$c[$i]}` = '{$search}' OR "); }
}

// executes the mysql query string (uncomment to execute)
// mysql_query($str);

// echoes query string
echo $str;

?>

But, as you guys said, it may be bad deisign... :confused:

[Twey]

How else could you search through all columns and it not be bad design :SNo, you're missing the point. If you have to search through all columns, you've some design issues. :)

[Schmoopy]

Okay... But then how could I redesign it in a way where it wouldn't need to query all columns but still get all that information, I just don't see how that's possible :confused:

[Twey]

Well that depends on your application.

[Schmoopy]

It's for my A2 coursework, my client (runs a business making covers for boats) keeps track of customers' invoices and there's information like:

Customer ID
Boat type
Name
Description of job
Date completed
Pattern location (L7 for example, where the physical pattern for the cover is)


I want him to be able to put in any sort of search term, so if a customer rings up and they have their invoice, they can tell him their customer ID and he can just put it in, and then the record with that ID will show up.

But then some customers may not have their invoice ID, so they can just tell him their name and then he can put that in and search for the records that way.

That's why I need it to span across multiple columns, so that he can search for different types of information, name, what their boat was etc etc...

[Twey]

Pattern location (L7 for example, where the physical pattern for the cover is)What if the customer ID is 7? And here we see the issue with this approach. Have the user choose what they want to search — don't do it for them. By all means allow them to select multiple columns, but make it explicit.

[Schmoopy]

What if the customer ID is 7?

I don't think you understand, it's fine if the customer ID is 7, I'm letting the user search for anything they want, they could put in anything, ID, name, pattern location... The L7 is just an example.

The user has a load of shelves with patterns in them, ranging from A1 - L9 and counting, this is just so he knows where to get the pattern from if he needs to remake a cover in the future for the same boat.

[Twey]

If the user wants customer 7, and enters that ID, then instead of customer 7 they get all the customers with patterns A7 through L7 — an awful lot of false positives.

[Schmoopy]

Oh I see what you mean!

So... should I make some radio buttons and then say "Search by..."

ID, name, etc...

That would rule out any false positives I guess :)

[Twey]

Right. Much better.

Checkboxes are a better option, though, in case they do want to search more columns at once.

[Schmoopy]

Ah yes, clever thinking :D