I have a database like this :
ID | Name | Model | Type
1 | Car | 4 | C
2 | Bar | 2 | B
3 | Car | 4 | D
4 | Car | 3 | D
And a form like this :
Name :
Model :
Type :
Now, I would like to search only the name, for example "Car" and it returns lines 1, 3, 4. (I left Model and Type empty)
If I search "Car" in Name and 4 in Model, it returns lines 1, 3. (I left Type empty)
And if I search "Car" in Name and "D" in Type, it returns line 3, 4 (I left Model empty)
Is it possible to do this in one query ?
This is what I had :
SELECT *
FROM items
WHERE (:name IS NOT NULL AND name = :name)
AND (:model IS NOT NULL AND model = :model)
AND (:type IS NOT NULL AND type = :type)
But it doesn't work. I would like to fill only 2 on 3 fields and the the "WHERE" adapts and ignore the blank field.
EDIT 1 : It is a little hard to explain but I have a form. I want to have only one required field, the two others are optional but if I also fill the one other or two others fields, they act like a filter.
So the name field is required (in the form). If I fill only the name field, it will select only where name = :name. If I fill name + model, it will select where name = :name AND model = :model. and so on...
Thank you for your help.
Here's an alternative approach using PHP. You'll need to update the variables.
<?php
$query = 'SELECT *
FROM items
WHERE 1 = 1 ';
//below used for testing can be remove
//$_GET['name'] = 'test';
//$_GET['car'] = 'test2';
//$_GET['type'] = 'test3';
if(!empty($_GET['name'])) {
$query .= ' and name = ? ';
$params[] = $_GET['name'];
}
if(!empty($_GET['car'])) {
$query .= ' and car = ? ';
$params[] = $_GET['car'];
}
if(!empty($_GET['type'])) {
$query .= ' and type = ? ';
$params[] = $_GET['type'];
}
if(!empty($params)) {
$dbh->prepare($query);
$sth->execute($params);
//fetch
} else {
echo 'Missing Values';
}
The 1=1 is so you can append and search field for each field with a value otherwise you'd need to see if it'd already been set.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments