Hi ;D
I just want to know how to do a search box with an “if” (condition). to better explain: :
I want to look for a product in my site I choose the city “x” from the dropdown list and I type “pc” in the dialog box so I want all the pc in the city x selected appears
Assume you have something like this to select all the products:
[php]$sth = $dbh->prepare(‘SELECT * FROM product’);
$sth->execute();
$products = $sth->fetchAll();[/php]
You then want to be able to select only the products belonging to a spesific city. I will assume you populate a select box with values that are the city ids and texts that are the city names, and that each product has a foreign key relation to the city table.
[php]$sql = ‘SELECT * FROM product’;
$values = [];
if (isset($_GET[‘city’])
{
$sql .= ’ LEFT JOIN city ON product.city_id = city.id
WHERE city.name = ?’
$values[] = $_GET[‘city’];
}
$sth = $dbh->prepare($sql);
$sth->execute([$values]);
$products = $sth->fetchAll();[/php]
As you can see we now conditionally update the query if the user have selected a city. You can do this for as many conditions as you want, just keep in mind that the finished query string needs to be valid. This may lead to a lot of different implementations on methods on how to build these queries, which is also why ORM’s exist (Doctrine2, Eloquent/Active Record, etc)
[hr]
You then want to be able to search through the products for a string value as well, I’ll assume you want to search through the title and description fields. We’ll keep it simple for this example, you could as mentioned do this in a number of ways, ie by creating lists for selects, joins, wheres, etc and building a query string out of them. At that level you might be better off just using an ORM though (they do it better).
[php]$sql = ‘SELECT * FROM product’;
$values = [];
$where = [];
if (isset($_GET[‘city’])
{
$sql .= ’ LEFT JOIN city ON product.city_id = city.id’;
$where[] = ‘city.name = ?’;
$values[] = $_GET[‘city’];
}
if (isset($_GET[‘search’])
{
$where[] = ‘(product.title LIKE ? OR product.description LIKE ?)’;
$values[] = ‘%’ . $_GET[‘search’] . ‘%’;
$values[] = ‘%’ . $_GET[‘search’] . ‘%’; // need to add it twice as we use it twice in the query
}
if (!empty($where))
{
$sql = $sql . ’ WHERE ’ . implode(’ AND ', $where);
}
echo $sql;[/php]
This will produce different queries depending on which get params are set.
No params:
SELECT * FROM product
city is set
SELECT * FROM product LEFT JOIN city ON product.city_id = city.id WHERE city.name = ?
search is set
SELECT * FROM product WHERE (product.description LIKE ? OR product.description LIKE ?)
city and search is set
SELECT * FROM product LEFT JOIN city ON product.city_id = city.id WHERE city.name = ? AND (product.title LIKE ? OR product.description LIKE ?)