Dynamic SQL statement

geogal

Hello all. I can really use someone's help with this problem. I'm transferring a search page I had coded in ASP to PHP. Since there are multiple form elements a user can search on, I was wondering how to dynamically build my SQL statement -- in particular the WHERE clause.

I know in VBScript I can inject "and if...then...end if" clause inside the WHERE portion of the SQL statement to check for each variable in my form. Is this possible in PHP? I don't know if I have to use an array of some kind or what.

Here's where I'm stuck in my code:

$sqlquery = "SELECT brokers.broker_name, builders.builder_name, communities.community_name, inventory2.lot_number, inventory2.price, models.model_name FROM (((inventory2 INNER JOIN brokers ON inventory2.fk_broker = brokers.brID) " INNER JOIN builders ON inventory2.fk_builder = builders.buID) INNER JOIN communities ON inventory2.fk_community = communities.cID) INNER JOIN models ON inventory2.fk_model = models.mID WHERE ????";

Many thanks!

notionlogic

Probably the simplest approach is to end the fixed query with "WHERE 1 " which then allows you to simply append further SQL statements as required (each starting with "AND...") to build a complete, dynamic final query but still works if you don't add anything to it

e.g.
WHERE 1
AND field1 = field2
AND anotherfield like '%something%'
etc

Does that help?

Weedpacket

Originally posted by notionlogic
Probably the simplest approach is to end the fixed query with "WHERE 1 " which then allows you to simply append further SQL statements as required

Well, it's messy, and makes extra work for the database, and opens up possibilities for errors later on.

Originally posted by geogal:I know in VBScript I can inject "and if...then...end if" clause inside the WHERE portion of the SQL statement to check for each variable in my form. Is this possible in PHP? I don't know if I have to use an array of some kind or what.

Certainly it's possible, You can do it the same way as in VBScript if you want, or you can use an array instead; make an array of all the "field=value" conditions you want to combine, if the final array has more than zero elements, attach the string " WHERE ".join(" AND ",$conditions_array) to the query being assembled.

It may be PHP instead of VBScript, but it's still programming. PHP is more powerful, is all.

geogal

Thank you so much, folks, for your responses! I ended up doing what notionlogic suggested , using "Where 1..." simply because I'm so new to PHP (and programming in general) I didn't know how to make an array from my form elements.

What Weedpacket suggested sounds like a more refined way to iterate through the form variables, so when I gain more knowledge I'll give it a shot. Thanks again!