inserting data into multiple tables at the same time

Hi y'all,

I wrote the following:

<?php
include ("connect.php");
$sql = "INSERT INTO advertiser_info_tbl (adv_name) values ('$adv_name')";
$sql = "INSERT INTO address_info_tbl (address, apt, city, state, zip) values
('$address',

'$apt', '$city', '$state', '$zip')";
if (mysql_query($sql, $conn)){
echo "record added!";
} else {
echo "something went wrong";
}
?>

This seems to work with no error messages. Unfortunately, it doesn't put
any data into the first table (advertiser_info_tbl). It only puts data in
the second table. Is this possible? I've seen that you can retrieve data
from multiple tables at the same time, so I figure there must be a way.

Any help would be appreciated.

Robert

On Apr 6, 8:45 pm, "Mr. Newt" <lektrikpuke@_yahoo.com> wrote:
> Hi y'all,
>
> I wrote the following:
>
> <?php
> include ("connect.php");
> $sql = "INSERT INTO advertiser_info_tbl (adv_name) values ('$adv_name')";
> $sql = "INSERT INTO address_info_tbl (address, apt, city, state, zip) values
> ('$address',
>
> '$apt', '$city', '$state', '$zip')";
> if (mysql_query($sql, $conn)){
> echo "record added!";} else {
>
> echo "something went wrong";}
>
> ?>
>
> This seems to work with no error messages. Unfortunately, it doesn't put
> any data into the first table (advertiser_info_tbl). It only puts data in
> the second table. Is this possible? I've seen that you can retrieve data
> from multiple tables at the same time, so I figure there must be a way.
>
> Any help would be appreciated.
>
> Robert

The second line should be .= As it is now the second query is simply
replacing the first one before you even run it.

When faced with problems like this echo out the value of $sql first to
make sure it has what you think it does.

"ZeldorBlat" <zeldorblat@gmail.com> wrote in message
news:1175913519.299306.232510@d57g2000hsg.googlegr oups.com...
> On Apr 6, 8:45 pm, "Mr. Newt" <lektrikpuke@_yahoo.com> wrote:
>> Hi y'all,
>>
>> I wrote the following:
>>
>> <?php
>> include ("connect.php");
>> $sql = "INSERT INTO advertiser_info_tbl (adv_name) values ('$adv_name')";
>> $sql = "INSERT INTO address_info_tbl (address, apt, city, state, zip)
>> values
>> ('$address',
>>
>> '$apt', '$city', '$state', '$zip')";
>> if (mysql_query($sql, $conn)){
>> echo "record added!";} else {
>>
>> echo "something went wrong";}
>>
>> ?>
>>
>> This seems to work with no error messages. Unfortunately, it doesn't put
>> any data into the first table (advertiser_info_tbl). It only puts data
>> in
>> the second table. Is this possible? I've seen that you can retrieve
>> data
>> from multiple tables at the same time, so I figure there must be a way.
>>
>> Any help would be appreciated.
>>
>> Robert
>
> The second line should be .= As it is now the second query is simply
> replacing the first one before you even run it.
>
> When faced with problems like this echo out the value of $sql first to
> make sure it has what you think it does.
>
I must be tired. I accidentally replied to Zeldor instead of the NG. Sorry
Zeldor.

Any way, I've tried replacing = with .= and it still doesn't work. I've
tried every
variation I can come up with and no joy. Another hint?

> I must be tired. I accidentally replied to Zeldor instead of the NG.
> Sorry Zeldor.
>
> Any way, I've tried replacing = with .= and it still doesn't work. I've
> tried every
> variation I can come up with and no joy. Another hint?

As Zeldor pointed out you must use .= otherwise the second query would
overwrite the first query in the variable. Also even if you did it correctly
the sql would be:-

INSERT INTO advertiser_info_tbl (adv_name) values ('$adv_name')INSERT INTO
address_info_tbl (address, apt, city, state, zip) values ('$address','$apt',
'$city', '$state', '$zip')

But obviously with the variables replaced. In this example how does mysql
know this is 2 statements? In fact it does not you should terminate a
statement with a ;.

1 other problem however if I remember correctly mysql_query can only handle
1 query at a time if you try more than 1 it will fail. I beleive
mysqli_query can handle 2 however.

>
> 1 other problem however if I remember correctly mysql_query can only
> handle 1 query at a time if you try more than 1 it will fail. I beleive
> mysqli_query can handle 2 however.

in fact just had a check and it seems mysqli_query cannot however
mysqli_multi_query is the function you would need to use however it is php 5
only.

ZeldorBlat wrote:
> On Apr 6, 8:45 pm, "Mr. Newt" <lektrikpuke@_yahoo.com> wrote:
>> Hi y'all,
>>
>> I wrote the following:
>>
>> <?php
>> include ("connect.php");
>> $sql = "INSERT INTO advertiser_info_tbl (adv_name) values ('$adv_name')";
>> $sql = "INSERT INTO address_info_tbl (address, apt, city, state, zip) values
>> ('$address',
>>
>> '$apt', '$city', '$state', '$zip')";
>> if (mysql_query($sql, $conn)){
>> echo "record added!";} else {
>>
>> echo "something went wrong";}
>>
>> ?>

the proper statement would be

$sql = "INSERT INTO advertiser_info_tbl (adv_name) values ('$adv_name');";
$sql .= " INSERT INTO address_info_tbl (address, apt, city, state, zip)
values ('$address', '$apt', '$city', '$state', '$zip')";


however, if it won't execute both in one go you could do the following:

$cmds = array();
array_push($cmds, "<sqlstatement1>");
array_push($cmds, "<sqlstatement2>");
foreach($cmds as $sql) {
mysql_query($sql) or die(mysql_error());
}

Obviously replace with real statements and put your error checking in.

"Tyno Gendo" <user@example.com> wrote in message
news:46178E14.8030002@example.com...
> ZeldorBlat wrote:
>> On Apr 6, 8:45 pm, "Mr. Newt" <lektrikpuke@_yahoo.com> wrote:
>>> Hi y'all,
>>>
>>> I wrote the following:
>>>
>>> <?php
>>> include ("connect.php");
>>> $sql = "INSERT INTO advertiser_info_tbl (adv_name) values
>>> ('$adv_name')";
>>> $sql = "INSERT INTO address_info_tbl (address, apt, city, state, zip)
>>> values
>>> ('$address',
>>>
>>> '$apt', '$city', '$state', '$zip')";
>>> if (mysql_query($sql, $conn)){
>>> echo "record added!";} else {
>>>
>>> echo "something went wrong";}
>>>
>>> ?>
>
> the proper statement would be
>
> $sql = "INSERT INTO advertiser_info_tbl (adv_name) values ('$adv_name');";
> $sql .= " INSERT INTO address_info_tbl (address, apt, city, state, zip)
> values ('$address', '$apt', '$city', '$state', '$zip')";
>
>
> however, if it won't execute both in one go you could do the following:
>
> $cmds = array();
> array_push($cmds, "<sqlstatement1>");
> array_push($cmds, "<sqlstatement2>");
> foreach($cmds as $sql) {
> mysql_query($sql) or die(mysql_error());
> }
>
> Obviously replace with real statements and put your error checking in.

Thanks for all the answers. Everyone has been very helpful.

I may be making my life harder than it needs to be. I'm writing a db for a
friend and have never done anything this big before. So, trying to be
fancy, I decided to try normalizing the data first. I decided to separate
the advertiser table from the address table, because I might add addresses
(like mailing addresses of customers) to the address table. So, that's why
I want to be able to access/write data in more than one table at a time. I
figured it can't be that hard, because every php example I've seen has
multiple tables and data must be related.

Anything I can read or direction in this area would be appreciated.

Thank you.

Robert

Mr. Newt wrote:
> "Tyno Gendo" <user@example.com> wrote in message
> I may be making my life harder than it needs to be. I'm writing a db for a
> friend and have never done anything this big before. So, trying to be
> fancy, I decided to try normalizing the data first. I decided to separate
> the advertiser table from the address table, because I might add addresses
> (like mailing addresses of customers) to the address table. So, that's why
> I want to be able to access/write data in more than one table at a time. I
> figured it can't be that hard, because every php example I've seen has
> multiple tables and data must be related.
>
> Anything I can read or direction in this area would be appreciated.
>
> Thank you.
>
> Robert

Well, if the address is related to the advertiser then you will first
want to INSERT the advertiser to get a unique ID for the advertiser,
then in the address table you will want to INSERT also including the ID
you get as a foreign key, so you can link between the two in future.

First execute the first query to insert advertiser, then call
mysql_insert_id() to get the inserted rows Primary KEY:

mysql_query(<new_advertiser_insert_statement>);
$newid = mysql_insert_id();

You should have a field set aside in your advertiser table which is an
autonumber, primary key, for example 'pkAdvertiserID' and in the address
table a similar field, call it fkAdvertiserID for foreign key to
advertiser table, type INT (but not auto_number in this table).

so..

[ADVERTISER_TABLE]
pkAdvertiserID INT AUTO_INCREMENT PRIMARY_KEY
....
....

[ADDRESS_TABLE]
pkAddressID INT AUTO_INCREMENT PRIMARY KEY
fkAdvertiserID INT NOT NULL
....
....

Be honest with you though, unless you have more than one advertiser with
the same address, you'd be better just keeping the address in the
advertiser table.

Same with customers, they are perhaps unlikely to have more than one
address? And if they do you probably want to have separate tables,
so.. customer table and customer_address table, like you have advertiser
table and advertiser_address table.

You will obviously know best though as you know the scope of the project :-D

Mr. Newt wrote:

>> Obviously replace with real statements and put your error checking in.

Trying to do multiple updates without checking for errors on each one
individually is, IMHO, sloppy programming. Doing it as a transaction
with commit/rollback is advisable.

> I may be making my life harder than it needs to be. I'm writing a db for a
> friend and have never done anything this big before. So, trying to be
> fancy, I decided to try normalizing the data first.

That's not being fancy. If there's one thing I've learned over the
decades, it's that designing the database correctly up front makes life
a lot easier in the long run.

> I decided to separate
> the advertiser table from the address table, because I might add addresses
> (like mailing addresses of customers) to the address table.

Good plan - there's no reason to have multiple address fields in
multiple tables. You're likely to see this go from a one-one
relationship to a one-many as someone eventually points out that a
company can have different addresses for billing, shipping and receiving
etc. Ditto customers.

> So, that's why
> I want to be able to access/write data in more than one table at a time.

Since updating joins is problematic (some databases don't support any
updatable joins), think of this as separate updates that are related by
foreign keys. To ensure they work as a unit, use transactions and
either commit or rollback as appropriate.

Mike

Michael Daly wrote:
> Mr. Newt wrote:
>
>>> Obviously replace with real statements and put your error checking in.
>
> Trying to do multiple updates without checking for errors on each one
> individually is, IMHO, sloppy programming. Doing it as a transaction
> with commit/rollback is advisable.

Agreed, transactions is the best way to go. So long as your database
supports them, worth reading up on them if it does, however I was just
going with what you were currently trying to do :)

I agree with Michael though, do go for transactions if you do want the
multiple table updates, if they are available.