PHP Implode Associative Array

Question

Hi All,

So I'm trying to create a function that generates a SQL query string based on a multi dimensional array.

Example:

function createQueryString($arrayToSelect, $table, $conditionalArray) {
$queryStr = "SELECT ".implode(", ", $arrayToSelect)." FROM ".$table." WHERE ";
$queryStr = $queryStr.implode(" AND ",$conditionalArray); /*NEED HELP HERE*/
return $queryStr;
}

$columnsToSelect = array('ID','username');
$table = 'table';
$conditions = array('lastname'=>'doe','zipcode'=>'12345');
echo createQueryString($columnsToSelect, $table, $conditions); /*will result in incorrect SQL syntax*/

as you can see I need help with the 3rd line as it's currently printing

SELECT ID, username FROM table WHERE lastname AND zipcode

but it should be printing

SELECT ID, username FROM table WHERE lastname = 'doe' AND zipcode = '12345'

Answer 1

You will have to write another function to process the $conditionalArray, i.e. processing the $key => $value and handling the types, e.g. applying quotes if they're string.

Are you just dealing with = condition? What about LIKE, <, >?

Answer 2

I'd advise against automated conditionals creation.
Your case is too local, while there can be many other operators - LIKE, IN, BETWEEN, <, > etc.
Some logic including several ANDs and ORs.

The best way is manual way.
I am always doing such things this way

if (!empty($_GET['rooms']))     $w[]="rooms='".mesc($_GET['rooms'])."'";
if (!empty($_GET['space']))     $w[]="space='".mesc($_GET['space'])."'";
if (!empty($_GET['max_price'])) $w[]="price < '".mesc($_GET['max_price'])."'";

Though if you still want it with this simple array, just iterate it using

foreach ($conditions as $fieldname => $value)...

and then combine these variables in the way you need. you have 2 options: make another array of this with field='value' pairs and then implode it, or just concatenate, and substr trailing AND at the end.

Answer 3

You're not actually imploding a multidimensional array. $conditions is an associative array.

Just use a foreach loop inside your function createQueryString(). Something like this should work, note it's untested.:

$terms = count($conditionalArray);
foreach ($conditionalArray as $field => $value)
{
    $terms--;
    $queryStr .= $field . ' = ' . $value;
    if ($terms)
    {
        $queryStr .= ' AND ';
    }
}

Answer 4

function implodeItem(&$item, $key) // Note the &$item
{
  $item = $key . "=" . $item;
}

[...]

$conditionals = array(
  "foo" => "bar"
);

array_walk($conditionals, "implodeItem");
implode(' AND ', $conditionals);

Untested, but something like this should work. This way you can also check if $item is an array and use IN for those cases.

Answer 5

Forgive me if its not too sexy !

 $data = array('name'=>'xzy',
              'zip'=>'3432',
              'city'=>'NYK',
              'state'=>'Alaska');


$x=preg_replace('/^(.*)$/e', ' "$1=\'". $data["$1"]."\'" ',array_flip($data));

$x=implode(' AND ' , $x);

So the output will be sth like :

 name='xzy' AND zip='3432' AND city='NYK' AND state='Alaska'