How to create sql insert query dynamically in mysql

Question

I am creating an application where I am generating pins dynamically based on user's input and storing them into mySql database.

$sql = "INSERT INTO tblpin ('pinId', 'ownerId', 'usedby', 'status') 
    VALUES
        for($i=0;$i<$npin;$i++)
        {
            ('$pin[$i]','$ownerid', 'Free', '1');
        }
    ;";

how can I do that?

Answer 1

Something like

$sql = sprintf( "INSERT INTO `tblpin` (`pinId`, `ownerId`, `usedby`, `status`) VALUES ('%s', '%s', '%s', '%s')",
    generatePIN($pin),
    mysql_real_escape_string($ownerId),
    mysql_real_escape_string($usedBy),
    mysql_real_escape_string( $status) );

or (edited for Conspicuous Compiler)

$pins = generatePINS($user); // ? however they're generated
foreach( $pins as $pin) {
    $sql = sprintf( "INSERT INTO `tblpin` (`pinId`, `ownerId`, `usedby`, `status`) VALUES ('%s', '%s', '%s', '%s')",
        $pin,
        mysql_real_escape_string($ownerId),
        mysql_real_escape_string($usedBy),
        mysql_real_escape_string( $status) );
        $result = mysql_query($sql);
}

where generatePIN is your function to make your pin based on whatever the heck you're basing it off of. or generatePINS returns an array of them

Answer 2

Try this:

$sql = "INSERT INTO tblpin ('pinId', 'ownerId', 'usedby', 'status') VALUES ";
for($i=0; $i<sizeof($pin); $i++) {
    if ($i>0)
        $sql .= ", ";
    $sql .= "('$pin[$i]', '$ownerid', 'Free', '1')";
}

Of course you need to escape the values of $pin in case they contain any characters which could mess with the SQL query.

Answer 3

$s = $pdo->prepare("INSERT INTO xy (a,b,c,d) VALUES (?,?,?,?)");
foreach ($pins as $i) {
   $s->execute($i,$ownerID,"free",1);
}