PHP parameter binding for mysql insert if not exist

Question

I am reading and referencing different posts on how to insert if not exists. This is a good thread but I need some additional help.

I need to add a new record with about 10 columns in it, but only insert it if two columns don't match. I also need to do parameter binding on it.

$query = "INSERT INTO Customers (col1,col2,col3,col4,col5,col6) 
SELECT * FROM (SELECT ?,?,?,?,?,?) AS tmp
WHERE NOT EXISTS (
    SELECT col1 from Customers WHERE uid=? AND pid=?
)   LIMIT 1;"
$results = dbQuery($query,(array($val1,$val2,$val3,$val4,$val5,$val6,$uid,$pid)) ;

What am I doing wrong here?

And here is dbQuery call:

function dbQuery($query,$data) {
  global $pdo ;
  $stmt = $pdo->prepare($query);
  $result = $stmt->execute($data) ;
  $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
  $error = $stmt->errorInfo() ; 
  if ($result == false) {
    var_dump($error) ;
  }
  return array($result,$error,$rows) ;
}

You can't use placeholders for column or table names. You will have to hard-code them into your script. — Mike, Sep 12 '18 at 05:25
In your dbquery call, you are not specifying the type of the parameters like integer, string etc ? — Madhur Bhaiya, Sep 12 '18 at 05:26
@MadhurBhaiya Well we don't exactly have the code for the `dbQuery` function. Maybe the function does that, or it is not necessary (as is the case with PDO). — Mike, Sep 12 '18 at 05:28
sorry, I just added the dbQuery....col1, col2,col3 are just sample column names...as opposed to writing out firstName,lastName,address1,address2, etc etc — rolinger, Sep 12 '18 at 05:30
@rolinger By placeholders, I mean binding (i.e. where you have the `?` characters). That can only be done for values, not for table or column names. — Mike, Sep 12 '18 at 05:54
@Mike - the thread I referenced is using $variables in the select where I have the ?,?,? at, thus I assume thats where the actual values/$vars are placed. — rolinger, Sep 12 '18 at 06:01
In the question you referenced, the variables will contain column names. — Mike, Sep 12 '18 at 06:04

score 0 · Answer 1 · answered Nov 01 '22 at 14:36

"?" parameter placeholders aren't named, they're passed by position, so you can't use them more than once. You could pass uid/pwd twice; but better is to used named parameters passed as a map (https://www.php.net/manual/en/pdostatement.execute.php)

$query = "INSERT INTO Customers (uid,pid,col3,col4,col5,col6) 
    SELECT * FROM (SELECT :uid, :pid, :val3, :val4, :val5, :val6) AS tmp
    WHERE NOT EXISTS (
        SELECT * from Customers WHERE uid = :uid AND pid = :pid
    )";
$results = dbQuery($query,(array("uid"=>$uid, "pid"=>$pid, "val3"=>$val3, "val4"=>$val4, "val5"=>$val5, "val6"->$val6)));

Note using the parameter array means all the parameters come through as strings, which may not be what you want. You would have to use bindParam to set specifically typed values.

Best of all would be a stored procedure that allows you to define parameters, use proper binding, and prevent any accidental datatype mismatches.

PHP parameter binding for mysql insert if not exist

1 Answers1