PHP Implode Associative Array

Question

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'

Generative

I see you are trying to create a SQL query builder. I'll tell you right now this is huge pain in the butt... By the way this will generate invalid SQL if your $conditionalArray is empty (since the WHERE clause will still be there)... And you are not accommodating LIKE, <>, etc. – Quid

Answer 1

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 ';
    }
}

Note: To prevent SQL injection, the values should be escaped and/or quoted as appropriate/necessary for the DB employed. Don't just copy and paste; think!

Shrieval

@aidan Actually, I do not. The point of the answer is to focus on the looping/logic required to accomplish the task. – Shrieval

Not you specifically, just a note to anyone planning on copying and pasting your code (because it's just asking for SQL injection). – Questionnaire

@Questionnaire Fair enough. Two very important points: One, if your intention is to help the future newbie you should provide more explanation in that note. E.g. by adding on the clause: because it's just asking for SQL injection. Two, quoting the value is not enough and not correct for the general case. The values can legitimately be digits and should be presented as such to the database. To prevent SQL injection, the values should be escaped and/or quoted as appropriate/necessary for the DB employed. – Shrieval

Answer 2

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.

Felske

Answer 3

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, <, >?

Jezabelle

Answer 4

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'

Stockbreeder

this will fail on array_flip when two different keys will have the same value. – Vair

Answer 5

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.

Boycie

what is mesc I can't see to find it in the MySQL/PHP manuals – Satirical

@Satirical that's mysql_real_escape_string – Boycie

Answer 6

I use a variation of this:

function implode_assoc($glue,$sep,$arr)
{
    if (empty($glue)) {$glue='; ';}
    if (empty($sep)) {$sep=' = ';}
    if (is_array($arr))
    {
        foreach ($arr as $k=>$v)
        {
            $str .= $k.$sep.$v.$glue;
        }
        return $str;
    } else {
        return false;
    }
};

It's rough but works.

Paprika

This actually leaves the $glue at the end. I use something similar currently, adding $str = substr($str , 0, -strlen($glue)); fixes the problem as expected. – Tedder

Answer 7

Here is a working version:

//use: implode_assoc($v,"="," / ")
//changed: argument order, when passing to function, and in function
//output: $_FILES array ... name=order_btn.jpg / type=image/jpeg / tmp_name=G:\wamp\tmp\phpBDC9.tmp / error=0 / size=0 / 

function implode_assoc($arr,$glue,$sep){
    $str = '';
    if (empty($glue)) {$glue='; ';}
    if (empty($sep)) {$sep=' = ';}
    if (is_array($arr))
    {
        foreach ($arr as $key=>$value)
        {
            $str .= $key.$glue.$value.$sep;
        }
        return $str;
    } else {
        return false;
    }
}

Pole

Answer 8

I know this is for the case of a pdo mysql type.. but what i do is build pdo wrapper methods, and in this case i do this function that helps to build the string, since we work with keys, there is no possible way to mysql inject, since i know the keys i define / accept manually.

imagine this data:

           $data=array(
            "name"=>$_GET["name"],
            "email"=>$_GET["email"]
);

you defined utils methods...

public static function serialize_type($obj,$mode){
$d2="";
if($mode=="insert"){
    $d2.=" (".implode(",",array_keys($obj)).") ";
    $d2.=" VALUES(";
foreach ($obj as $key=>$item){$d2.=":".$key.",";}
$d2=rtrim($d2,",").")";}

if($mode=="update"){
    foreach ($obj as $key=>$item){$d2.=$key."=:".$key.",";}    
}
return rtrim($d2,",");
}

then the query bind array builder ( i could use direct array reference but lets simplify):

  public static function bind_build($array){
     $query_array=$array;
     foreach ($query_array as $key => $value) { $query_array[":".$key] =   $query_array[$key]; unset($query_array[$key]); } //auto prepair array for PDO
return $query_array;    }

then you execute...

$query ="insert into table_x ".self::serialize_type( $data, "insert" );
$me->statement = @$me->dbh->prepare( $query ); 
$me->result=$me->statement->execute( self::bind_build($data) );

You could also go for an update easy with...

  $query ="update table_x set ".self::serialize_type( $data, "update" )." where id=:id";
    $me->statement = @$me->dbh->prepare( $query ); 

    $data["id"]="123"; //add the id 
    $me->result=$me->statement->execute( self::bind_build($data) );

But the most important part here is the serialize_type function

Shamrao

Answer 9

Try this

function GeraSQL($funcao, $tabela, $chave, $valor, $campos) {
    $SQL = '';

    if ($funcao == 'UPDATE') :
        //Formata SQL UPDATE

        $SQL  = "UPDATE $tabela SET ";
        foreach ($campos as $campo => $valor) :
            $SQL .= "$campo = '$valor', ";
        endforeach;
        $SQL  = substr($SQL, 0, -2);
        $SQL .= " WHERE $chave = '$valor' ";

    elseif ($funcao == 'INSERT') :
        //Formata SQL INSERT

        $SQL  = "INSERT INTO $tabela ";

        $SQL .= "(" . implode(", ", array_keys($campos) ) . ")";

        $SQL .= " VALUES ('" . implode("', '", $campos) . "')";         

    endif;

    return $SQL;
}

//Use
$data = array('NAME' => 'JOHN', 'EMAIL' => '[email protected]');
GeraSQL('INSERT', 'Customers', 'CustID', 1000, $data);

Locoism

You should describe what this does differently or better than the others answers. As it stands "Try this" is rather useless. – Andreasandree