Building a query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dx2
Forum Newbie
Posts: 2
Joined: Sun Jan 24, 2010 8:34 am

Building a query

Post by dx2 »

Ok its high time for me to deal with this.Its not necessary a php question - its more like general programming matter. What is the best strategy for creating complex database queries. Lets have for example a page and its content can be filtered by a few criteria. The building of the query string could turn into nightmare when more filters exist. The code begins to look something like this(pseudo(php)code):

Code: Select all

 
$q = "SELECT * FROM articles";
$where = "";
if($filter1!=="All"){
 $where.="WHERE author=".$filter1;
}
if($filter2!=="All"){
 if($where!==""){
  $where.="WHERE category=".$filter2;
 }else{
 $where.= " AND category=".$filter2;
}
}
 
.....and s.o.
So what is the right way to build complex queries depending on many parameters to avoid situations like the one above?
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Building a query

Post by JakeJ »

You didn't specify where the filter is coming from but I assume it's from a form. Are users choosing a value from a combo box?

If not, are they filling out a text box? Are they filling out more than one text box?

Whichever method is being used could make a big difference in how your query is constructed.

This is a guess, but I get the impression that users are filling in different text boxes and your query needs to be constructed from that.

Try this. First, when the form is processed, check to see if the different variables are set using isset($_POST['filter1'], etc.

If isset() returns true, set a variable to $var = 'AND filter1 = "ALL"';

Alternatively, $var = 'AND filter1 = "', $_POST['filter1'], '"'; <-- Please note that is a single quote, a double quote and another single quote. Always gotta get your quotes right!

In the end, have a variable that concatenates all of your filters in to a one long query string which you can then build a query on.
$finalquery = "SELECT * FROM table WHERE ', $filter1, $filter2, $filter3, $filter4, " ORDER BY ",

So if a particular variable is not set, it just goes on to the next one. And you get a correct sql statement.
Post Reply