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
– Mike, Commented Sep 12, 2018 at 5:25
In your dbquery call, you are not specifying the type of the parameters like integer, string etc ? — Madhur Bhaiya
– Madhur Bhaiya, Commented Sep 12, 2018 at 5: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
– Mike, Commented Sep 12, 2018 at 5: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
– rolinger, Commented Sep 12, 2018 at 5: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
– Mike, Commented Sep 12, 2018 at 5:54

Andy Clapham · Accepted Answer · 2022-11-01 14:36:47Z

"?" 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.

Collectives™ on Stack Overflow

PHP parameter binding for mysql insert if not exist

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related