Add WHERE clause to all sql strings or AND if already exists

[DssTrainer]

I am trying something creative with my online store (PHP).

I am adding a signifying field to each db table. This new field will need to be qualified for ALL db select queries. So instead of manually editing hundreds of queries, I want to try regex adding it to the end of each query as it passes through the common db class.

 

There is a common db class that all db queries go through, so I would like to intercept the arriving mysql string and append this last bit at the WHERE clause level. I need to do this regardless of existing WHERE clause or not.

 

Examples:

Incoming sql: "SELECT * from table"

changed sql: "SELECT * from table WHERE mid = 1"

 

Incoming sql: "SELECT * from table WHERE language = 1 ORDER BY name"

changed sql: "SELECT * from table WHERE language = 1 AND mid = 1 ORDER BY name"

 

Incoming sql: "SELECT * from table1 t1 left join table2 t2 on (t1.id = t2.id) WHERE xid = 3 LIMIT 3"

changed sql: "SELECT * from table1 t1 left join table2 t2 on (t1.id = t2.id) WHERE xid = 3 AND mid = 1 LIMIT 3"

 

I'm not sure how to create the regex to match the string, depending on whether there is a WHERE clause already or not. And to check that it gets placed before the ORDER BY and LIMIT sections

[ignace]

You could use Zend_Db_Select from the Zend framework providing you also would use their database adapter classes you could do:

 

$select = new Zend_Db_Select(new Zend_Db_Adapter_Mysqli(array('host' => 'localhost', 'username' => '', ..)));
$select->from('table')->where('..')->where('..')->where('mid = ?', 1);
print $select;// returns SELECT * FROM table WHERE .. AND .. AND mid = 1

 

If you are using PDO to connect to your database you can use one of the Zend_Db_Adapter_Pdo_* classes.

[DssTrainer]

That looks like I'd need to know what else I'm adding to the where clause. I need it to be completely dynamic. I want all queries to have this appended, but I don't know which query will be coming at which time, or if anyone has custom queries. So I need to "hack" it into any select statement that comes through this particular db class function.

 

Something like:

$sql = "SELECT * from table WHERE language = 1";
$pattern = "/WHERE\s*=\s*/";
$sql = preg_replace($pattern, 'WHERE\s*=\s AND mid = \'1\'', $sql);

[DssTrainer]

Perhaps another way would be to explode on space so I would end up with

array[0]="SELECT";

array[1]="*";

array[2]="FROM";

array[3]="table";

array[4]="WHERE";

array[5]="language";

array[6]="=";

array[7]="'1'";

 

Then I can loop through each condition

if it doesn't find WHERE, then add a Where clause with my extra code

if it finds WHERE then add an AND clause after the where clause

 

That might be the easiest way.. ill give it a shot

 

[salathe]

How many queries do you have that means it's more attractive to hack something together than just go and edit the queries directly?  That's quite apart from questioning why every single table and every single query would need a fixed column/value pair throughout your entire application.

 

I'm all for helping you to solve a puzzle (inserting a string into some other strings) but don't want you to be entirely wasting your time.

[DssTrainer]

Thank you for your reply. There are hundreds of queries and infinite possible. It is for a shopping cart system. I am doing a little outside the box thinking and basically adding a way to handle multiple vendors on one site, simply by adding a "vendor_id" column to each table.

 

So products currently is:

prod_id, name, description, price, weight, etc

I would change to

prod_id, name, description, price, weight, vendor_id, etc

 

the same is true for all the tables (categories, orders, addresses, etc)

So I figured instead of editing each query to add an additional where clause, I would intercept it at the common point that all queries must go, which is class function where the execution of all queries takes place. Theoretically letting me mod the entire site PLUS future modules dynamically, editing only a single file and running a few alter commands on the db. The goal here is to be packageable for others store owners to also easily implement. And as shown in my first post, queries can be any shape or form, so I need to be able to handle them.

 

I might even be making it too complex with regex. Maybe a simple if/elseif on different conditionals..

if (strpos('WHERE', $sql) === false) {

   

} elseif (strpos('WHERE', $sql) !== false) {

 

}.. etc

 

The end result then, is that I can have the vendor login and set his vendor_id to the session variable while he's logged in and all queries will return only his products and nobody elses dynamically

[DavidAM]

If you add this new column to EVERY table, then you are going to have a problem with JOINs.

 

From your example in the original post:

Incoming sql: "SELECT * from table1 t1 left join table2 t2 on (t1.id = t2.id) WHERE xid = 3 LIMIT 3"

changed sql: "SELECT * from table1 t1 left join table2 t2 on (t1.id = t2.id) WHERE xid = 3 AND mid = 1 LIMIT 3"

 

If mid was added to both tables, this new condition is ambiguous and will produce an error.  You would have to actually add "AND t1.mid = 1 AND t2.mid = 1".

 

I think you would be better off putting the vendor ID in a session variable and editing all of your queries.

 

[DssTrainer]

Ah.. good catch.. way to rain on my parade. Back to the drawing board

 

thanks

[DssTrainer]

At the risk of having some hacky "special case" style code, I think I will still attempt this. For statements with joins I will just have to add some special handling to append the first alias to the mid field to avoid ambiguity.... not something I'm proud of.... but in the end it achieves more for what I need. The main goal is being able to dynamically adapt to future queries or 3rd party mods that don't exist yet. I will just have to play it by ear for any queries that cause a problem.