Wildcard in Where clause?

Anon

Hi All,
Is it possible to assign a "Wildcard" to a variable that will select all the data stored in a column?

Here's the query, and I would like to specify a specific item to search for on any of these columns, or some of them. If only a few are being searched on, I need the ones not specified to search all the columns.

$sql = "SELECT * FROM tbl_Files ";
$sql .= "WHERE year = '$selyear'";
$sql .= "AND make = '$selmake'";
$sql .= "AND model = '$selmodel'";
$sql .= "AND engine = '$selengine'";
$sql .= "ORDER BY filename ASC";
$result = mysql_query($sql, $db);
$rows = mysql_num_rows($result);

Any ideas or suggestions would be appreciated.

Thanks...

[deleted]

OK, here is a possible solution for you. You'll need to test it and make sure you don't get any wierd results with your data.

$sql = "SELECT * FROM tbl_Files ";
$sql .= "WHERE year LIKE '%$selyear'";
$sql .= "AND make LIKE '%$selmake'";
$sql .= "AND model LIKE '%$selmodel'";
$sql .= "AND engine LIKE '%$selengine'";
$sql .= "ORDER BY filename ASC";

What that is going to do is if anyone of those variables is empty, it will search for just '%' and that will match everything...the only thing to be careful for is a situation like this:

$selengin = "v6"

and you have data in your table where engine = "turbo-v6", the query I supplied will match that, because it has v6 at the end.

Hope that helps,
Matt Wade
codewalkers.com

iifs044

Another solution might be to enclose each of your conditions inside an if{} statement and only include the "AND model="... section if $selmodel actually contains a value. For example,

$sql = "SELECT * FROM tbl_Files ";
$sql .= "WHERE 1=1"
if($selyear) $sql .= " AND year='" . $selyear . "'";
if($selmake) $sql .= " AND make='" . selmake . "'";

This makes a sorta funny lookin' SQL statement because of the "WHERE 1=", but that's done to eliminate logic that determines whether WHERE or AND should be used. If you want better looking SQL, you could use something more like this, at the cost of more PHP code:

$sql = "SELECT * FROM tbl_Files ";
$nextclause = "WHERE "
if($selyear) {
$sql .= $nextclause;
$sql .= "year='" . $selyear . "' ";
$nextclause = "AND ";
}
if($selmake) {
$sql .= $nextclause;
$sql .= "make='" . selmake . "'";
}

-- Mitch

gmbot

Great Job! Thank you for the quick response. That worked exactly as I needed.

Although my test database is relatively small, I'm polling the database for all the unique items in each column and then populating a pull down menu for each column so that the issue you discussed (engine = "turbo-v6" vs. v6) won't be a problem.

Again, thanks for your help. I hope I'll be able to return the deed.

GMbot