Multiple INSERT queries

TheCase

Hi,

I need to make lots of INSERT queries and have set up the following code

          mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 1', '$_POST[1]', '$_POST[datedropdown]')");
          mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 1', '$_POST[12]', '$_POST[datedropdown]')");
          mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 1'', '$_POST[13]', '$_POST[datedropdown]')");

      mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 2', '$_POST[21]', '$_POST[datedropdown]')");
      mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 2', '$_POST[22]', '$_POST[datedropdown]')");
      mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 2', '$_POST[23]', '$_POST[datedropdown]')");

      mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 3', '$_POST[31]', '$_POST[datedropdown]')");
      mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 3', '$_POST[32]', '$_POST[datedropdown]')");
      mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 3', '$_POST[33]', '$_POST[datedropdown]')");

      mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 4', '$_POST[41]', '$_POST[datedropdown]')");
      mysql_query(" INSERT INTO `rolerota` (`role` ,`userid` ,`date`)  VALUES ('Value 4', '$_POST[42]', '$_POST[datedropdown]')");

This has made my script memory usage rise by 25,000 which is alot, im sure there is a better way to do this but I am not sure, any ideas

Thanks

Derokorian

This is a valid query:

INSERT INTO 
`rolerota`(`role` ,`userid` ,`date`)  

VALUES 
('Value 4', '$_POST[41]', '$_POST[datedropdown]'),
('Value 4', '$_POST[42]', '$_POST[datedropdown]'),
('Value 4', '$_POST[43]', '$_POST[datedropdown]'), 
(etc, etc, etc...

requiring a lot less seperate connections to the DB.

HTH

samuelcook

Agree with Derokorian. Just be sure to escape those POST values.

bradgrafelman

TheCase;10984217 wrote:
This has made my script memory usage rise by 25,000 which is alot

25,000 what? Sea urchins? Bananas?

Regardless, as others have mentioned you could accomplish what you're trying to do in a single SQL query. Also note that functions such as [man]mysql_free_result/man exist that give you a little more control over how memory is freed.

Derokorian

Ah yeah free_result always forget about that being available in non-improved. I'm used to $result->free(); Good call brad =D

TheCase

Thanks this works really well.

I only sometimes free_resources as sometimes it increasing the memory usage. PHP does this automatically anyway at the end of the script, am I correct?

Thanks