Building conditional query with PHP -is this acceptable SQL?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Building conditional query with PHP -is this acceptable SQL?

Post by batfastad »

Hi everyone

I have a question about the best way for writing a conditional query in PHP to produce correct MySQL syntax based on PHP variables.

Here's my PHP code showing the block where the 2 variables are evaluated...

Code: Select all

// SQL QUERY FOR PUBLICATIONS
$sql_query = "SELECT * FROM publications WHERE ";

// CHECK WEB VAR
if ( isset($_GET['nonweb'])) {
	$sql_query .= "1";
} else {
	// WEB FILTER NOT SET - SHOW ONLY WEB ONES
	$sql_query .= "web=1";
}

// CHECK PUBLICATION VAR
if ( isset($_GET['filter'])) {
	// PUBLICATION FILTER SET
	$filter = mysql_real_escape_string($_GET['filter']);
	$sql_query .= " AND publication LIKE \"%$filter%\"";
}

$sql_query .= " ORDER BY art_date ASC LIMIT 0, 10000";
$sql_result = mysql_query($sql_query);
If the variables meet certain conditions I will end up with a query reading like this...

Code: Select all

SELECT * FROM publications WHERE 1 AND publication LIKE "%AMD%" ORDER BY art_date ASC LIMIT 0, 10000
My question is whether it's acceptable to do the WHERE 1 AND bit?
The query works.

But because of the way I've structured my PHP code around line 6 checking that first var, I could have ended up with the following query...

Code: Select all

SELECT * FROM publications WHERE AND publication LIKE "%AMD%" ORDER BY art_date ASC LIMIT 0, 10000
Which would fail as there's nothing between the WHERE and the AND statements.

So is it appropriate to just drop a 1 in there like I have to fill the gap in the query if certain conditions in my PHP are not met?

Hope this makes sense
Thanks
Ben
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

A good way to do this is to add all of your filter statements to an array, then use implode to put them into a string as follows

Code: Select all

$sql = "SELECT * FROM tbl WHERE " ;

$filters = array() ;
$filters[] = "name='fred'" ;
$filters[] = "age > 25" ;
$filters[] = 'deleted = 0' ;
$filterSQL = implode(' AND ', $filters) ;

$sql .= $filterSQL ;
$sql will now have SELECT * FROM tbl WHERE name='fred' AND age > 25 AND deleted = 0.

Using implode will allow you to add a lot of different filters selectively and not mess up having an AND right after a where. If there is only one item in your filters array then no AND will be appended.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Yes, you can have WHERE 1, but you could also doing something like this:

Code: Select all

<?php
// SQL QUERY FOR PUBLICATIONS
$sql_query = "SELECT * FROM publications";
$sql_append = '';
// CHECK WEB VAR
if ( !isset($_GET['nonweb'])) {
    $sql_append .= "WHERE web=1";
}

// CHECK PUBLICATION VAR
if ( isset($_GET['filter'])) {
    // PUBLICATION FILTER SET
    $filter = mysql_real_escape_string($_GET['filter']);
    
    if (!empty($sql_append)) {
        $sql_query = $sql_query . $sql_append . " AND publication LIKE \"%$filter%\"";
    } else {
        $sql_query .= " WHERE publication LIKE \"%$filter%\"";
    }
}

$sql_query .= " ORDER BY art_date ASC LIMIT 0, 10000";
$sql_result = mysql_query($sql_query);
?>
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post by batfastad »

That's awesome.
What a great tip!!

I was pretty worried about having to write further queries in a couple of weeks that were going to have loads of different variables in them, but that's just made things so much easier!

Thanks again :lol: :lol:
Post Reply