List:General Discussion« Previous MessageNext Message »
From:Gary Date:April 13 2010 3:45pm
Subject:Re: INSERT INTO multiple tables
View as plain text  
Colin

Thank you for your reply.  I had previously found the page you linked in 
your original post, however at this stage of my development, my imagination 
creates error messages and not inovative solutions.

I have found some other issues that are presenting road blocks, once I solve 
them, I will try to apply your solutions to the original problem.

Thanks again for your reply.

Gary


"Colin Streicher" <colin@stripped> wrote in message 
news:201004122103.15609.colin@ style="color:#666">stripped...
>
> 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.
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 5023 (20100412) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 



__________ Information from ESET Smart Security, version of virus signature database 5025
(20100413) __________

The message was checked by ESET Smart Security.

http://www.eset.com




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