PHP And MySQL Queries

[berridgeab]

Hello

 

I have a design issue that I have never really known how to approach solving. Its a generic question not related to any specific scenairo. When I need to query a database table i.e. say a generic product table, sometimes I need to restrict what rows I return. I.e. I may only want products that have not been sold.

 

So my SQL string would look somthing like

$query = "SELECT * FROM products WHERE status != 'Sold';

Sometimes I perform more complicated queries on other tables. Say for instance I may need to join my product table to a theoretical orders table. This would result in a SQL query like

$query = "SELECT * FROM orders LEFT JOIN products ON products_id = orders_product_id WHERE order_id = 1 AND status != 'Sold';

My problem is that I now have 2 different pieces of SQL code in 2 different places. Both snippets of code have something in common

status != 'Sold' /*This is the only bit im really interested in*/

What is the best way to maintain bits of SQL code that needs to be the same across the entire site?

[mac_gyver]

  On 6/28/2013 at 10:16 AM, berridgeab said:

What is the best way to maintain bits of SQL code that needs to be the same across the entire site?

 

you could create a 'view' in your database for the products table that only 'matches' status != 'Sold' and use the view name in the queries instead of the products table.