PHP - Remove part of string from another string

1

votes

I have the following function to create the SQL for an insert query:

 function insertQuery($data, $table) {
    $key = array_keys($data);
    $sql = "INSERT INTO " . $table . " (" . implode(', ', $key) . ") VALUES " ;
    $val = array_values($data);   
    $sql .= "('" . implode("', '", $val) . "');";

    return $sql;

  }

Normally, this works fine. However I would like to return a query containing the SQL command LAST_INSERT_ID(). When run through the function, quotes are added so it returns as 'LAST_INSERT_ID()'

Is there a simple way to remove the quotes, without removing the quotes from other items?

Any advice appreciated. Thanks.

phpsql

1

votes

$sql = str_replace("'LAST_INSERT_ID()'", 'LAST_INSERT_ID()', $sql);

1

votes

The problem is that your input parameters don't make a difference between values and functions. You need to find a way to make that difference obvious to your function. I expect you use your function like this:

insertQuery(array('name'=>'John', 'age' => 43), 'person');

How about something like this:

insertQuery(
    array(
        'name'   => 'John', 
        'age'    => 43, 
        'prevId' => array('LAST_INSERT_ID()')
    ), 'person');

function insertQuery($data, $table) {
    $keys = array_keys($data);
    $sql  = "INSERT INTO `" . $table . "` (`" . implode('`, `', $keys) . "`) VALUES ";
    $values = array_values($data);

    $sqlparams = array();

    foreach ($values as $val) {
        if (is_array($val)) {
            $val = $val[0];
        }   
        else {
            # Escape and quote
            $val = '"' . mysql_real_escape_string($val) . '"';
        }   
        $sqlparams[] = $val;
    }   

    $sql .= "(" . implode(", ", $sqlparams) . ");";

    return $sql;
}

I also included two bugfixes:

Without mysql_real_escape_string quoting is always wrong. A quote in your parameter will mess up your whole SQL query.
Added quotes around the table and the keys. This way you won't get errors for table and field names that are also keywords in SQL.

PHP - Remove part of string from another string

2 Answers