PHP - Generate a dynamic IF statement for mysql

Im struggling with this one. I have an array of keys/values of indeterminate size which I want to use to generate a dynamic if statement for a MySQL query.

My array looks like:

    $ar开发者_Go百科r = array("a" => "b", "c" => "d", "e" => "f");

Consider the final MySQL statement will look something like

    SELECT 
        IF(field1 = 'a', 'b', 
        IF(field1 = 'c', 'd', 
        IF(field1 = 'e', 'f', default_value))) AS value FROM tbl;

Im aware that I have to do a recursive array, but Im not sure how I modify the array so that it doesnt end up in an endless loop. Id like to invoke it like:

    $if_stmt = generate_if($arr, "default_value");

Where "default_value" is the default value if no other logical test equates to true. So far I have

    function generate_if(&$arr, $default_value = "default_value"){
        foreach($arr as $orig => $new){

        }
    }

and no idea how to proceed.. Any help VERY much appreciated.

If I understand well what you want :

Ugly way :

$ending = '';
$sql = 'SELECT ';
foreach ($arr as $orig => $new) {
    $sql    .= 'IF(field1 = ' . $orig . ', ' . $new . ',';
    $ending .= ')';
}

$sql .= $default_value . $ending . ' AS value FROM tbl';

Maybe you can tell us more about what you want to do because i'm not sure you're going into the right direction.

Considering that your SQL is correct:

function generate_if($arr, $default_value = "default_value") {
    echo 'SELECT';
    foreach($arr as $k => $v) {
        echo ' IF(field1 = "', $k,'", "', $v,'", ';
    }
    echo $default_value,
      str_repeat(')', count($arr)),
      ' AS value FROM tbl;';
}

I think you can avoid using references. They do NOT speed up performance.

I recommend the use of a CASE statement, instead of your convoluted IF/ELSE:

$sql = 'SELECT CASE ';
for($array as $if => $then) {
  $sql .= sprintf('WHEN field1 = \'%s\' THEN \'%s\' ',
    mysqli_real_escape_string($if),
    mysqli_real_escape_string($then));
}
$sql .= sprintf('ELSE \'%s\' END CASE AS value ',
  mysql_real_escape_string($default_value));
$sql .= 'FROM table1';