...

View Full Version : dynamic MySQL select statement



cthorn112
05-27-2011, 07:09 PM
Hello,

I'm putting together an internal company website. Right now its taking two fields firstName and lastName but I will be adding more fields (address,city,state,zip..etc). I want to create a dynamic select statement that will look at what fields the user has entered and use those in the select statement. I did some research and found two query generators/builders. I tried applying them to my code with no success. Is this the approach I should be taking or something else ? I have attached my two files along with two query generators/builders into one zip. Thanks in advance

-Chris

Fumigator
05-27-2011, 07:49 PM
This is really a PHP question, because the easy way to do this is with a little bit of PHP magic.

It's not really magic.

The idea is to build your query up one piece at a time using PHP's string glue, the period (.).

First, create your basic query:


$query = "SELECT * FROM table1 WHERE 1";

Note the "WHERE 1" is merely a tricky way to get the "WHERE" clause into the query. The reason for this is our query is now ready for any number of modifiers to the "WHERE" clause. We can add, 3, 4 or 0. (BTW, "WHERE 1" will always evaluate to "true".)

To add a modifier:


if (!empty($_POST['city']))
{
$query .= " AND city_column = '".mysql_real_escape_string($_POST['city'])."'";
}


Do this with each item you want to add to the query as a possible filter, and then call mysql_query($query), check for errors, fetch the data, so on and so forth.

cthorn112
05-27-2011, 08:37 PM
Fumigator,

I get what you are saying and like this approach. I have a question. I noticed that $query starts with AND so there has to be one required field correct ? What if I didn't want a required field. For example say I just wanted to look at a date range rather then names. I know I am jumping ahead but I'm just anticipating management requests :)

Fumigator
05-27-2011, 09:35 PM
Fumigator,

I get what you are saying and like this approach. I have a question. I noticed that $query starts with AND so there has to be one required field correct ? What if I didn't want a required field. For example say I just wanted to look at a date range rather then names. I know I am jumping ahead but I'm just anticipating management requests :)

Not so. Each qualifier starts with AND, but $query starts with SELECT. All fields are optional. If no fields are entered, you end up with this query:



SELECT * FROM mytable WHERE 1


If 3 fields are entered, you end up with this query:



SELECT * FROM mytable WHERE 1
AND field1 = 'field1value'
AND field2 = 'field2value'
AND field3 = 'field3value'

Old Pedant
05-27-2011, 09:37 PM
Read again what Fumigator said.

Look at his initial sql string:


$query = "SELECT * FROM table1 WHERE 1";

If you never added any more conditions to the query, the WHERE 1 would cause *ALL* records to be selected.

If you just wanted a date range, you might then do

$query .= " AND dateField BETWEEN '2011-1-1' AND '2011-5-31' ";


The operator used there is "dot equals", *NOT* just "equals". So that *APPENDS* to the initial value of $query and your SQL becomes


SELECT * FROM table1 WHERE 1 AND dateField BETWEEN '2011-1-1' AND '2011-5-31'


See?

************************

*SIGH* Old age is catching up with me. Two minutes too slow. <grin/>

cthorn112
05-27-2011, 10:00 PM
Oh ok I see what you are saying. Thank you very much for the information. I will be back with more questions after I apply some changes to the code.

Thanks,

-Chris



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum