INSERT INTO multiple tables

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

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Re: INSERT INTO multiple tables

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 wrote:
> I am experimenting with multiple tables, it is only a test that is my loc=
al
> machine only. This is the current code, which does not work , I have trie=
d
> 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
>
> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>
>
> />
>
>
>
>
>
>

Re: INSERT INTO multiple tables

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 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
> >
> > > > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
> >
> >
> >
> >
> >
> >
> >
> >
> >

Re: INSERT INTO multiple tables

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" wrote in message
news:201004112310.16594.colin@obviouslymalicious.com...
> 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 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
>> >
>> > >> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >

Re: INSERT INTO multiple tables

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 wrote:
> Seriously
>
> You should read your answers before you post, the SA link did not provide
> the answer. =A0Had you read the page you sent, you would notice it does n=
ot
> apply to mulitple tables...
>
> Gary
>
>
> "Colin Streicher" wrote in message
> news:201004112310.16594.colin@obviouslymalicious.com...
>> Seriously...
>> I found the answer in the first result.
>> http://lmgtfy.com/?q=3Dmysqli+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 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. =A0I have tried mult=
iple
>>> > $query variables, but it is just overwriting itself (only the last on=
e
>>> > gets inserted). I also tried writing the $query as an array, which go=
t
>>> > me
>>> > an error message (saying it was expecting a string and I offered an
>>> > array).
>>> >
>>> > Someone point me in the right direction?
>>> >
>>> > Gary
>>> >
>>> > >>> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>>> >
>>> >
>>> > -8" />
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >

Re: INSERT INTO multiple tables

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);
?>


$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" wrote in message
news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fdd94@mail.gm ail.com...
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 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" wrote in message
> news:201004112310.16594.colin@obviouslymalicious.com...
>> 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 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
>>> >
>>> > >>> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >

Re: INSERT INTO multiple tables

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

$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);
> ?>
>
> >
> $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" wrote in message
> news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fdd94@mail.gm ail.com...
> 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 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" wrote in message
> > news:201004112310.16594.colin@obviouslymalicious.com...
> >
> >> 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 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
> >>> >
> >>> > > >>> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >

Re: INSERT INTO multiple tables

I have no idea how you got here but there is no reason to do it that
way. This will work just fine and I do it every day in php.
However I don't use mysqli I still use ...
mysql_connect
mysql_select_db
mysql_real_escape_string
mysql_query

Don't forget to use the mysql_real_escape_string function to be sure sql
injection can't happen.


$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.');

$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);

?>


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);
> ?>
>
> >
> $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" wrote in message
> news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fdd94@mail.gm ail.com...
> 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 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" wrote in message
>> news:201004112310.16594.colin@obviouslymalicious.com...
>>
>>> 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 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
>>>>>
>>>>> >>>>> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>

Re: INSERT INTO multiple tables

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" wrote in message
news:201004122103.15609.colin@obviouslymalicious.com...
>
> 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
>
> > $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);
>> ?>
>>
>> >>
>> $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" wrote in message
>> news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fdd94@mail.gm ail.com...
>> 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 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" wrote in message
>> > news:201004112310.16594.colin@obviouslymalicious.com...
>> >
>> >> 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 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
>> >>> >
>> >>> > >> >>> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>> >>> >
>> >>> >
>> >>> > >> >>> > />
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >

Re: INSERT INTO multiple tables

Chris.

Thanks for your response. I read somewhere that the mysqli was better, so I
have been using it.

This exercise is an experiment, on my local machine only, so I ommitted any
escape functions. I am trying to create DB's with multiple tables, so it is
totally a learning exercise.

So hopefully I will learn from my mistakes.

Thanks for your help.

Gary

"Chris W" <4rfvgy7@cox.net> wrote in message
news:4BC47B0B.7020907@cox.net...
>I have no idea how you got here but there is no reason to do it that way.
>This will work just fine and I do it every day in php.
> However I don't use mysqli I still use ...
> mysql_connect
> mysql_select_db
> mysql_real_escape_string
> mysql_query
>
> Don't forget to use the mysql_real_escape_string function to be sure sql
> injection can't happen.
>
>
> > $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.');
>
> $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);
>
> ?>
>
>
> 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);
>> ?>
>>
>> >>
>> $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" wrote in message
>> news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fdd94@mail.gm ail.com...
>> 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 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" wrote in message
>>> news:201004112310.16594.colin@obviouslymalicious.com...
>>>
>>>> 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 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
>>>>>>
>>>>>> >>>>>> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>