CODEWITHSUNDEEP
phpmysql
Steven
Steven

Reputation: 13975

Mysql query generation?

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?

Upvotes: 2

Views: 189

Answers (4)

Silver Light
Silver Light

Reputation: 45922

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']);
?>

Upvotes: 0

Sondre
Sondre

Reputation: 1898

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

$args = array(
  'email' => '[email protected]',
  '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
}

Upvotes: 0

TomaszSobczak
TomaszSobczak

Reputation: 2926

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);

Upvotes: 0

karim79
karim79

Reputation: 342635

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);

Upvotes: 1

Related Questions