0

I have to insert different values in to five different tables. i have used five insert query one by one, i don't know is it correct method or not. so i need exact method to insert values.

i mentioned my current method below.

mysql_query("insert into table1(mainid,subid,name,actdate,service1,service2,service3,validdate) values ('mid01' , 'sid01' , 'servname' , '11/20/2014' , 'example1' , 'example3' , 'example3' , '30/20/2014' )");

mysql_query("insert into table2(mainid,subid,name,actdate,total,balance,validdate) values ('mid01' , 'sid01' , 'servname' , '11/20/2014' , '1000' , '1000' , '30/20/2014')");

mysql_query("insert into table3(mainid,subid,name,actdate,total,balance,validdate) values ('mid01' , 'sid01' , 'servname' , '11/20/2014' , '1000' , '1000' , '30/20/2014')");

mysql_query("insert into table4(mainid,subid,name,actdate,total,balance,validdate) values ('mid01' , 'sid01' , 'servname' , '11/20/2014' , '1000' , '1000' , '30/20/2014')");

mysql_query("insert into table5(mainid,subid,name,actdate,validdate) values ('mid01' , 'sid01' , 'servname' , '11/20/2014' , '30/20/2014')");
Piruthvi
  • 35
  • 2
  • 9
  • 2
    [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – Davide Pastore Nov 20 '14 at 12:29
  • 1
    Pity the poor, abused data types. – Strawberry Nov 20 '14 at 12:29
  • nothing wrong for 5 queries, when you worried about optimization you should read [Speed of INSERT Statements](http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html) – Gowri Nov 20 '14 at 12:31
  • `vlues` that is part of your first query; error. If that is your actual code, it's an error. If it's a typo, fix it `values` – Funk Forty Niner Nov 20 '14 at 12:31
  • Sorry, i don't know about mysql_*function. i know basics of mysql only. can you explain about this? – Piruthvi Nov 20 '14 at 12:31
  • @Piruthvi: [read the red box](http://php.net/mysql_query), that's all you need to know: `mysql_*` is deprecated, so don't use it. – Elias Van Ootegem Nov 20 '14 at 12:33
  • @MattHolbrook-Bull OP wants to insert into 5 different tables, not the one. Your duplicate question link doesn't apply. You can delete the comment or find another that is suited for the question. – Funk Forty Niner Nov 20 '14 at 12:37
  • @Piruthvi Ok, so you have removed the comma `'30/20/2014' ,`, now please tell us what the problem is. – Funk Forty Niner Nov 20 '14 at 12:43
  • @Fred-ii- extra comma has removed, thank you. – Piruthvi Nov 20 '14 at 12:43
  • @Piruthvi You're welcome. Now, what seems to be the problem. You only ask if it's the right method; have you tried it? If so, are you checking for errors and if you are, what methods are you using? – Funk Forty Niner Nov 20 '14 at 12:46
  • @Fred-ii- if using 5 insert queries at a time will cause some problem or not? i mean processing speed or server speed or some other like some values inserted wrongly. – Piruthvi Nov 20 '14 at 12:47
  • @Fred-ii- i am using same queries in localhost, its all working fine. but i am worrying about when it comes in online. if many users accessing at same time mean i thing may be prob will occur. that's why i am asking is this correct method or better than this method there? – Piruthvi Nov 20 '14 at 12:50
  • @Piruthvi I don't see a problem with it. You could switch to `mysqli` functions and use [**multi_query**](http://php.net/manual/en/mysqli.multi-query.php) which will give you slightly better performance as will PDO. As far as "if values are wrongly inserted", checking for errors using `mysql_error()` or `mysqli_error($connection)` will signal if it's a DB error. An additional method is using [**error reporting**](http://php.net/manual/en/function.error-reporting.php) – Funk Forty Niner Nov 20 '14 at 12:53
  • @Piruthvi In regards to people accessing the same script at the same time, you will need to set one of your columns (usually and id column) as a primary key and auto_increment. That will ensure the data is entered as unique. – Funk Forty Niner Nov 20 '14 at 12:55
  • @Fred-ii- I don't know that much about multi_query, i know basics of mysql only, anyway i will learn about that, thank you very much for your help. – Piruthvi Nov 20 '14 at 12:57
  • @Piruthvi You're welcome. The principal is the same. SQL is SQL. It doesn't care which MySQL API you use, just as long as you have an AI with primary key. All should be good. – Funk Forty Niner Nov 20 '14 at 12:58

2 Answers2

3

If you still use mysql functions it's the solution. If you were using mysqli functions you could use mysqli_multi_query to do all the queries in just one transaction

Serpes
  • 672
  • 4
  • 14
  • You can see the improvements in the link that is in the first comment of your questions – Serpes Nov 20 '14 at 12:36
  • 1
    unfortunately I don't see a comment about performance on link, but I found nice [article on so](http://stackoverflow.com/a/11122128/430112) – Gowri Nov 20 '14 at 12:40
  • @gowri i am worrying about performance only, its working fine in localhost, but its come in live i think this method cause performance problem or some other error. that's why i am asking is this correct method or not? – Piruthvi Nov 20 '14 at 12:54
0

Yes, every insert is independent query. You can syntax like one query "query1;query2;query3..." too.

Also I recommend you do not use mysql_query. Also for 'actdate' you can use datetime's type. For mainid it's not good practice to have strings (character).

Kristiyan
  • 1,655
  • 14
  • 17