Mysql query generation?

Question

I have several different filters and search functions that I need to generate mysql queries for. I am wondering if there is a class or library I can use.

For example if they input there email with a date I need to add WHERE email='email' AND date`='date' in the middle of the sql query. But if they also enter a city then I need to add that.

I was thinking put everything I need to search by in an array and then imploding it by AND ? Does anyone have any better suggestion?

That could work. You can use key=>value to establish field=>searchcriteria. Not sure how much benefit you'll reap from this though in the long run...might be annoying to maintain as more complex needs arise (like doing LIKE or in comparisons). There may be a library someone can point to that has some of this built in. — clifgriffin, Jan 05 '11 at 15:02
possible duplicate of [SQL Builder for PHP, with JOIN support?](http://stackoverflow.com/questions/216138/sql-builder-for-php-with-join-support) — Gordon, Jan 05 '11 at 15:05

score 1 · Accepted Answer · answered Jan 05 '11 at 15:08

1

I use Zend_Db for that sort of thing. Quick example, using Zend_Db_Select:

$select = $db->select()->from("users");
if($email) {
    $select->where('email = ?', $email);
}
if($date) {
    $select->where('date = ?', $date);
}
// etc

// show query
// will output something like SELECT * from `users` WHERE email = 'email' AND date = 'date'
print_r($select->__toString()); 

// execute
$results = $db->fetchAll($select);
print_r($results);

answered Jan 05 '11 at 15:08

karim79

339,989
67
413
406

1

+1 for Zend_Db, Zend_Db_Select can be used without the complete Zend Framework and is an excellent query builder. – regilero Jan 05 '11 at 15:38

score 0 · Answer 2 · answered Jan 05 '11 at 15:08

I use array solution very often as it is most flexible solution for query conditionals

$a[] = "email = 'email'";
$a[] = "firstname LIKE '%firstname%'";
$a[] = "date BETWEEN 'date_a' AND 'date_d'";
$a[] = "id > 123";

$query = ... " WHERE " implode(' AND ', $a);

score 0 · Answer 3 · answered Jan 05 '11 at 15:09

I'd go for adding all data that is given into an array like this:

$args = array(
  'email' => 'test@test.com',
  'date' => '2011-01-05',
  'city' => 'MyTown'
);

Then just foreach through it adding the key and value to the search

$SQL = "WHERE "; 
foreach($args as $key => $val) {
  $SQL .= $key."='".$val."'"; 
  //And add the AND or OR where needed
}

score 0 · Answer 4 · answered Jan 05 '11 at 15:14

Common approach is to create an array that will contain different query parts and just add elements to them, depending on what you need to filter. For example:

<?php

$sql_parts = array(
    'select' => array(),
    'from' => array(),
    'where' => array()
);

if ($filter_by_name != ''){
    $sql_parts['select'][] = 'u.*';
    $sql_parts['from'][] = 'users AS u';
    $sql_parts['where'][] = "u.name = '".mysql_real_escape_string($filter_by_name)."'";
}

if ($filter_by_surname != ''){
    $sql_parts['select'][] = 'u.*';
    $sql_parts['from'][] = 'users AS u';
    $sql_parts['where'][] = "u.surname = '".mysql_real_escape_string($filter_by_surname)."'";
}

//filter by data from another table
if ($filter_by_city_name != ''){
    $sql_parts['select'][] = 'u.*, c.*';
    $sql_parts['from'][] = 'cities AS c';
    $sql_parts['from'][] = 'users AS u';
    $sql_parts['where'][] = "c.cityname = '".mysql_real_escape_string($filter_by_city_name)."'";
    $sql_parts['where'][] = "c.id = u.cityid";
}

$sql_parts['select'] = array_unique($sql_parts['select']);
$sql_parts['from'] = array_unique($sql_parts['from']);
$sql_parts['where'] = array_unique($sql_parts['where']);

$sql_query = "SELECT ".implode(", ", $sql_parts['select']).
                "FROM ".implode(", ", $sql_parts['from']).
                "WHERE ".implode(" AND ", $sql_parts['where']);
?>

Mysql query generation?

4 Answers4