Hi I’ve just used WHERE 1=1 in my sql statement. I used it as I was told it is pratical and I can follow the WHERE with AND easier. I was just wondering if this is good practise or is there a better way I should be writing my code.
My code below is for a search form I created. I have 4 textboxes that when submitted will add to variable using AND and LIKE which will fill a select query to show the select item.
My code:
/* Name of Button */
if(isset($_POST['multi_search'])) {
/* Name of textboxes */
$mul_name = $_POST['mul_name'];
$mul_clothing = $_POST['mul_clothing'];
$mul_quantity = $_POST['mul_quantity'];
$mul_ordtot = $_POST['mul_ordtot'];
if ($mul_name) {
$multi_query = " AND Name LIKE ('%{$mul_name}%') ";
echo $multi_query;
}
if ($mul_clothing){
$multi_query .= " AND Clothing LIKE ('%{$mul_clothing}%') ";
}
if ($mul_quantity){
$multi_query = " AND quantity LIKE ('%{$mul_quantity}%') ";
}
if ($mul_ordtot){
$multi_query = " AND Order_Total LIKE ('%{$mul_ordtot}%') ";
}
//below are the exact columns in table
$query = "SELECT data.Name, product.Clothing, product.Price, orders.quantity, orders.Order_Total
FROM orders INNER JOIN data ON orders.cust_id = data.cust_id
INNER JOIN product ON orders.Product_id = product.Product_id
WHERE 1=1 {$multi_query}";
/* for select statement */
$select_products = mysqli_query($connection, $query);
while($row = mysqli_fetch_array($select_products)){
$clothing = $row['Clothing'];
$name = $row['Name'];
$quantity = $row['quantity'];
$price = $row['Price'];
$ord_tot = $row['Order_Total'];
?>
Assuming 1 will always = 1, that’s a way you can append ANDs to build up the query string. You don’t need to use it when building queries, but I’ve seen countless code that does.
One concern might be if the query fails after that. That would be like a query with no WHERE and might do what you want to not do.
The beginning of the query is like SELECT all, the, stuffs FROM the_table WHERE 1 = 1
the “AND” portion of the query is not defined prior to the conditional structure. If one of the conditionals equate to true, then a variable will be assigned a value like AND some_id = 123.
Because the AND portion variable was not defined prior to the conditionals, if all of the conditionals are false, it will be undefined.
So for code like
$query = $select_portion . $and_portion
as long as at least one conditional is true the built up query will be like SELECT all, the, stuffs FROM the_table WHERE 1 = 1 AND some_id = 123 But if for some reason all conditionals fail, the built up query will consist of two variables, one a string, the other undefined.
TBH, I don’t know if an error would be thrown preventing the query to run, or if the undefined would be seen as an empty string and the $select_portion would run as SELECT all, the, stuffs FROM the_table WHERE 1 = 1 which would be the same as SELECT all, the, stuffs FROM the_table and might not be desirable.
* a PHP issue, not a database problem. The database will do as its told, right or wrong.
As far as using WHERE 1 = 1 in and of itself in a query, it won’t cause any problems for the database.
actually, that is usually ~exactly~ what is desired
the intuitive behaviour of a search where you have the option of entering some filters, and you don’t enter any filters, is that you expect it to return all rows, unfiltered