List:General Discussion« Previous MessageNext Message »
From:Colin Streicher Date:April 13 2010 1:03am
Subject:Re: INSERT INTO multiple tables
View as plain text  
Normally I would avoid getting into this sort of argument ( The 'OMG someone on teh
internets are 
wrong!!' argument)
But in this case, the solution ( still the first result in a google search) is far more
efficient than 
closing a connection so you can insert into another table. 
You are correct in that the example given doesn't do exactly what you are trying to do,
but a little 
imagination stretches it into exactly what you want. 

Exhibit A:
Here I have created 2 test tables in 'testdb' and inserted an A into the first.

mysql> create database testdb
    -> ;                     
Query OK, 1 row affected (0.08 sec)

mysql> connect testdb;
Connection id:    72  
Current database: testdb

mysql> create table test1( t1 VARCHAR(1) );
Query OK, 0 rows affected (0.13 sec)       

mysql> create table test2( t2 VARCHAR(1) );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 VALUES( 'A' ); insert into test2 VALUES('B');
Query OK, 1 row affected (0.08 sec)

Query OK, 1 row affected (0.00 sec)

mysql> select * from test1; select * from test2;
+------+
| t1   |
+------+
| A    |
+------+
1 row in set (0.02 sec)

+------+
| t2   |
+------+
| B    |
+------+
1 row in set (0.00 sec)
###
As you can see, a single statement is being used to insert into multiple rows, a clue this
can be 
done with a script as well... but I digress... we will get to that. 

Exhibit B
Here I have copied the final script from the page that I said contained the answer. 
Source is: http://www.hiteshagrawal.com/mysql/mysql-batch-insert-using-php
I've changed 2 things:
1. The connection info ( except the password obviously ) corresponds to my test setup
2. The query was changed to make sense for my test setup

<?php
$batchconnection = new mysqli('localhost', 'root', 'hunter2', 'testdb');
      if ($batchconnection->connect_error) {
          echo "Error Occurred While Connection To DataBase";
      }
      $sqlStatements = "insert into test1(t1) values('1');insert into test2(t2)
values('2');";

 $sqlResult = $batchconnection->multi_query($sqlStatements);

   if($sqlResult == true) {
       echo "Successfully Inserted Records";
   } else {
       echo "Some Error Occured While Inserting Records";
   }
?>

So then we execute it:
colin@somethingelse:~/phpcrap$ ./tester.php
Successfully Inserted Recordscolin@somethingelse:~/phpcrap$

That looks promising.

Exhibit C

Finally, lets see what our database looks like


mysql> select * from test1; select * from test2;
+------+
| t1   |
+------+
| A    |
| 1    |
+------+
2 rows in set (0.00 sec)

+------+
| t2   |
+------+
| B    |
| 2    |
+------+
2 rows in set (0.00 sec)


Oh wow... it worked!

Since it is your script, the way you ultimately do it is your option... 
I would suggest that you do not disconnect between inserts, this is terribly inefficient. 

Colin

On April 12, 2010 05:57:42 pm Gary wrote:
> Michael
> 
> Thank you for your response.  It gave me the idea how to solve this, and it
> seemed to have worked!
> 
> For those following hoping to see a solution, what I did was open the
> connection, insert into one table, closed the connection, closed the php
> script, and the data was inserted into 2 of the tables... The code looks
> like this:
> 
> $dbc=mysqli_connect('localhost','root','','test')or die('Error connecting
>  to MySQL server');
> 
> $query="INSERT INTO name(fname, lname)"."VALUES('$fname','$lname')";
> 
> $result=mysqli_query($dbc, $query)
> or die('Error querying database.');
> 
> mysqli_close($dbc);
> ?>
> 
> <?php
> 
> $dbc=mysqli_connect('localhost','root','','test')or die('Error connecting
>  to MySQL server');
> $query="INSERT INTO address (street, town, state,
> zip)"."VALUES('$street','$town','$state','$zip')";
> 
> $result=mysqli_query($dbc, $query)
> or die('Error querying database.');
> 
> mysqli_close($dbc);
> 
> ?>
> 
> It seems a bit redundant for php, but it seems to work.
> 
> If by the way anyone sees a problem with this solution, I would love to
>  read it.
> 
> Again, thank you for your response.
> 
> Gary
> 
> 
> "Michael Dykman" <mdykman@stripped> wrote in message
> news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fdd94@ style="color:#666">stripped...
> It is not a question of multiple tables, it is a question of multiple
> statements.  Most PHP configurations prohibit the application of more
> than one statement per call to execute.  This is generally thought to
> be a security issue as the vast majority of simple PHP-based SQL
> injection attacks only work on servers that allow multiple statements.
> 
> I haven't been deep in PHP land for a little while, but I think you
> will find the default driver/config is expressly preventing you from
> doing this.
> 
>  - michael dykman
> 
> On Mon, Apr 12, 2010 at 9:44 AM, Gary <gwpaul@stripped> wrote:
> > Seriously
> >
> > You should read your answers before you post, the SA link did not provide
> > the answer. Had you read the page you sent, you would notice it does not
> > apply to mulitple tables...
> >
> > Gary
> >
> >
> > "Colin Streicher" <colin@stripped> wrote in message
> > news:201004112310.16594.colin@ style="color:#666">stripped...
> >
> >> Seriously...
> >> I found the answer in the first result.
> >> http://lmgtfy.com/?q=mysqli+multiple+insert+statements
> >>
> >> Assuming mysqli, if you are using a different driver, then google that
> >>
> >> Colin
> >>
> >> On April 11, 2010 10:36:41 pm viraj wrote:
> >>> is it mysqli query or 'multi_query'?
> >>>
> >>> http://php.net/manual/en/mysqli.multi-query.php
> >>>
> >>> ~viraj
> >>>
> >>> On Sun, Apr 11, 2010 at 10:27 PM, Gary <gwpaul@stripped> wrote:
> >>> > I am experimenting with multiple tables, it is only a test that is
> my
> >>> > local machine only. This is the current code, which does not work ,
> I
> >>> > have tried to concatonate the insert statements. I have tried
> >>> > multiple $query variables, but it is just overwriting itself (only
> >>> > the last one gets inserted). I also tried writing the $query as an
> >>> > array, which got me
> >>> > an error message (saying it was expecting a string and I offered
> an
> >>> > array).
> >>> >
> >>> > Someone point me in the right direction?
> >>> >
> >>> > Gary
> >>> >
> >>> > <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
> >>> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
> >>> > <html xmlns="http://www.w3.org/1999/xhtml">
> >>> > <head>
> >>> > <meta http-equiv="Content-Type" content="text/html;
> charset=utf-8" />
> >>> > <title>Untitled Document</title>
> >>> > </head>
> >>> >
> >>> > <body>
> >>> >
> >>> > <form action="<?php echo $_SERVER['PHP_SELF'];?>"
> method="post">
> >>> >
> >>> > <label>First Name </label> <input name="fname"
> type="text" /><br
> >>> > /><br />
> >>> > <label>Last Name </label><input name="lname"
> type="text" /><br /><br
> >>> > />
> >>> > <label>Street Address </label><input name="street"
> type="text" /><br
> >>> > /><br />
> >>> > <label>Town </label><input name="town" type="text"
> /><br /><br />
> >>> > <label>State </label><input name="state" type="text"
> /><br /><br />
> >>> > <label>Zip Code</label><input name="zip" type="text"
> /><br /><br />
> >>> > <label>Telephone</label><input name="phone"
> type="text" /><br /><br
> >>> > /> <label>Fax</label><input name="fax"
> type="text" /><br /><br />
> >>> > <label>E-Mail</label><input name="email" type="text"
> /><br /><br />
> >>> > <label>Comments</label><br /><textarea
> name="comments" cols="100"
> >>> > rows="15"></textarea><br /><br />
> >>> >
> >>> > <input name="submit" type="submit" value="submit" />
> >>> > </form>
> >>> >
> >>> > <?php
> >>> >
> >>> > $fname=($_POST['fname']);
> >>> > $lname=($_POST['lname']);
> >>> > $street=($_POST['street']);
> >>> > $town=($_POST['town']);
> >>> > $state=($_POST['state']);
> >>> > $zip=($_POST['zip']);
> >>> > $phone=($_POST['phone']);
> >>> > $fax=($_POST['fax']);
> >>> > $email=($_POST['email']);
> >>> > $comments=($_POST['comments']);
> >>> > $REMOTE_ADDR=$_SERVER['REMOTE_ADDR'];
> >>> >
> >>> > $dbc=mysqli_connect('localhost','root','','test');
> >>> > $query="INSERT INTO address (street, town, state,
> >>> > zip)"."VALUES('$street','$town','$state','$zip')".
> >>> > "INSERT INTO comments(comments)"."VALUES('$comments')".
> >>> > "INSERT INTO
> >>> > contact(phone,fax,email)"."VALUES('$phone','$fax','$email')".
> "INSERT
> >>> > INTO name (fname, lname)"."VALUES('$fname','$lname')";
> >>> >
> >>> > $result = mysqli_query($dbc, $query)
> >>> > or die('Error querying database.');
> >>> >
> >>> > mysqli_close($dbc);
> >>> >
> >>> > ?>
> >>> > </body>
> >>> > </html>
> >>> >
> >>> >
> >>> >
> >>> > __________ Information from ESET Smart Security, version of virus
> >>> > signature database 5017 (20100411) __________
> >>> >
> >>> > The message was checked by ESET Smart Security.
> >>> >
> >>> > http://www.eset.com
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> > --
> >>> > MySQL General Mailing List
> >>> > For list archives: http://lists.mysql.com/mysql
> >>> > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> >>
> >> --
> >> It is easy to find fault, if one has that disposition. There was once a
> >> man
> >> who, not being able to find any other fault with his coal, complained
> >> that
> >> there were too many prehistoric toads in it.
> >> -- Mark Twain, "Pudd'nhead Wilson's Calendar"
> >>
> >> __________ Information from ESET Smart Security, version of virus
> >> signature database 5021 (20100412) __________
> >>
> >> The message was checked by ESET Smart Security.
> >>
> >> http://www.eset.com
> >
> > __________ Information from ESET Smart Security, version of virus
> > signature database 5021 (20100412) __________
> >
> > The message was checked by ESET Smart Security.
> >
> > http://www.eset.com
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> 

-- 
Q:	What's the difference between an Irish wedding and an Irish wake?
A:	One less drunk.
Thread
INSERT INTO multiple tablesGary11 Apr
  • Re: INSERT INTO multiple tablesviraj12 Apr
    • Re: INSERT INTO multiple tablesColin Streicher12 Apr
  • Re: INSERT INTO multiple tablesGary12 Apr
    • Re: INSERT INTO multiple tablesMichael Dykman12 Apr
  • Re: INSERT INTO multiple tablesGary12 Apr
    • Re: INSERT INTO multiple tablesColin Streicher13 Apr
    • Re: INSERT INTO multiple tablesChris W13 Apr
  • Re: INSERT INTO multiple tablesGary13 Apr
  • Re: INSERT INTO multiple tablesGary13 Apr