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?

asked Jun 2 at 5:43

nectar
6729

54% accept rate

1

By not mixing the code and the data – Col. Shrapnel Jun 2 at 5:46

1

@Col. Shrapnel: Great. So, could you provide an example? – cherouvim Jun 2 at 5:49

@nectar: can you write the plain query manually, without php? so - do that, then split sql string into constant and repetitive parts. after this: replace repetitive parts with loop and construct them with php. – zerkms Jun 2 at 5:52

1

check stackoverflow.com/questions/779986/… – volody Jun 2 at 5:58

@nectar: can you write the plain query manually, without php? so - do that, then split sql string into constant and repetitive parts. after this: replace repetitive parts with loop and construct them with php.

mario · Answer 1 · 2010-06-02 05:55:19Z

up vote 1 down vote

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

answered Jun 2 at 5:55

mario
3,5341520

i bet OP used bulk insert for some performance reasons, but this would work too. – zerkms Jun 2 at 5:59

cherouvim · Answer 2 · 2010-06-02 06:17:32Z

up vote 1 down vote

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.

edited Jun 2 at 6:17

answered Jun 2 at 5:53

cherouvim
8,346840

1

strings in php are concatenated with "." and ";" in the end of query for mysql_query() is a wrong char due to it accepts one and only one query. and the latest: foreach is more handy in this case. – zerkms Jun 2 at 5:56

thanks zerkms. It's been 5 years... – cherouvim Jun 2 at 5:57

It is not working echo $sql; is displaying 0. – nectar Jun 2 at 6:06

@nectar: I had a couple of typos which I corrected. – cherouvim Jun 2 at 6:18

@cherouvim :ya I got that , final $sql is coming fine but whet I call mysql_query($sql); , it is not inserting data into db.why? – nectar Jun 2 at 6:28

show 1 more comment

Dan Heberden · Answer 3 · 2010-06-02 06:18:35Z

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

So, two things: (1) He's trying to do multiple rows of inserts at once with a single SQL statement. Your solution doesn't do that, I don't think. (2) Any answer should note that the asker's given code is ripe for SQL injection attacks. (Your proposed solution has this same flaw.)
The OP makes no notes of multiple rows - the question is vague. Pins could be a typo, one column, who knows what. the OP makes no mention if he/she is sanitizing his data or not; if the OP does any search re: sql on this site, he/she is sure to find plenty of examples (hell, even some of my answers with tips for him/her)

How to create sql insert query dynamically in mysql

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql mysql-query or ask your own question.

Hello World!

How to create sql insert query dynamically in mysql

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql mysql-query or ask your own question.

Hello World!

Related