Use of WHERE 1=1

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.

um… not with ANDs, my good sir

It wouldn’t be a query like ?

SELECT all, the, stuffs FROM ..... {undefined}

not sure what you mean

having trouble imagining {undefined}

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.

you keep using that word undefined, and i don’t think it means what you think it means

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

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.