What's the best way to insert multiple rows into a mysql database using php?

Question

The php code below get's the results from a form and inserts them into a table.

I have to used this table structure where each row corresponds to a different value from the form eg First Name.

I've written the code below but it's cumbersome. Can you help me with a better way? Thanks heaps!

$lists      = $_POST['form']['lists'][0];
$first_name = $_POST['form']['first_name'];
$last_name  = $_POST['form']['last_name'];
$idu        = $db->insertid();

$db->setQuery("INSERT INTO #__rsmail_subscriber_details (`IdList`, `FieldName`, 
`FieldValue`, `IdSubscriber`) VALUES ('" . $db->getEscaped($lists) . "', 'First Name'
, '" . $db->getEscaped($first_name) . "', '" . $db->getEscaped($idu) . "')");

$db->query();

$db->setQuery("INSERT INTO #__rsmail_subscriber_details (`IdList`, `FieldName`, 
`FieldValue`, `IdSubscriber`) VALUES ('" . $db->getEscaped($lists) . "', 'Last Name'
, '" . $db->getEscaped($last_name) . "', '" . $db->getEscaped($idu) . "')");

$db->query();

Just curious - why are setQuery and query separated into 2 functions — zerkms
– zerkms, Commented Jul 6, 2011 at 3:26
Thanks heaps for the answer zerkms! Works great. I've just copied in the code from another site. How do I combine setQuery and query? — Regan Shepherd
– Regan Shepherd, Commented Jul 6, 2011 at 3:38

zerkms · Accepted Answer · 2011-07-06 03:24:55Z

8

You can perform bulk insert:

INSERT INTO table (field1, field2) VALUES ('val1', 'val2'), ('val3', 'val4'), ...

In your case it is something like:

$db->setQuery("INSERT INTO #__rsmail_subscriber_details (`IdList`, `FieldName`, 
`FieldValue`, `IdSubscriber`) VALUES ('".$db->getEscaped($lists)."', 'First Name'
, '".$db->getEscaped($first_name)."', '".$db->getEscaped($idu)."'), ('".$db->getEscaped($lists)."', 'Last Name'
, '".$db->getEscaped($last_name)."', '".$db->getEscaped($idu)."')");

answered Jul 6, 2011 at 3:24

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aleksey Korzun · Accepted Answer · 2011-07-06 03:27:23Z

0

To answer your SQL question:

INSERT INTO `table` (foo, bar)
         VALUES (1, 2),
                (3, 4),
                (5, 6),
                (7, 8)

In regards to your PHP code, burn it and start over. It reeks of security issues and bad practices.

answered Jul 6, 2011 at 3:27

Aleksey Korzun

6904 silver badges7 bronze badges

5 Comments

zerkms Over a year ago

Uhm, which security issues?

zerkms Over a year ago

@StackOverflowNewbie: XSS has nothing to do with databases.

Aleksey Korzun Over a year ago

Obvious direct DB insertion with $_POST data? You need to escape and bind variable if you are going to directly pipe it into the database in such manner.

zerkms Over a year ago

@Aleksey Korzun: where do you see direct insertion?? He sanitized the data with $db->getEscaped() method, didn't he. And you're a little wrong, he need either escape or bind variables into a prepared statement.

Aleksey Korzun Over a year ago

Because we all know that if you use framework's escape methods you will never get exploited. There is no chance of mistakes in current or future revisions of the frame work. There is no chance of middle man attack overwriting that method within the package. And we also know that prepared statements are never needed if you already escaping $_POST data. /sarcasm.

Collectives™ on Stack Overflow

What's the best way to insert multiple rows into a mysql database using php?

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related