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
– clifgriffin, Commented Jan 5, 2011 at 15:02
possible duplicate of SQL Builder for PHP, with JOIN support? — Gordon
– Gordon, Commented Jan 5, 2011 at 15:05

karim79 · Accepted Answer · 2011-01-05 15:08:06Z

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 5, 2011 at 15:08

karim79

343k67 gold badges420 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

regilero Over a year ago

+1 for Zend_Db, Zend_Db_Select can be used without the complete Zend Framework and is an excellent query builder.

TomaszSobczak · Accepted Answer · 2011-01-05 15:08:50Z

0

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

answered Jan 5, 2011 at 15:08

TomaszSobczak

2,9261 gold badge23 silver badges25 bronze badges

Comments

Sondre · Accepted Answer · 2011-01-05 15:09:30Z

0

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
}

answered Jan 5, 2011 at 15:09

Sondre

1,89819 silver badges34 bronze badges

Comments

Silver Light · Accepted Answer · 2011-01-05 15:14:20Z

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

Collectives™ on Stack Overflow

Mysql query generation?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related