Using php how do you insert Data into multiple tables in MySQL

If I understand you correctly, you want to insert into all tables with only ONE QUERY?

As far as I know, PHP's mysql_query() function doesn't support multiple queries (insert, select, update etc), and as you are inserting into different tables with different fields/columns, you have to run a separate query for each.
I don't think there is a way to do what you want.

EG:
one_tbl doesn't have the field/column "games" so you cannot run an insert query on one_tbl table with the field/column name of "games" as it will have an error (the field doesn't exist in the table)

Again only as far as I know, UNION and JOIN aren't for insert.

You can of course use the same variables where the data is the same in all tables.
EG
one_tbl and two_tbl both have "casino_name" and you can use the var "$casino_name" in both queries as it is the same data.

As greg said, you don't need to worry about join statements when you insert your data. You will be running three separate queries for your three tables.

The first query will insert the hotel/casino into the first table. You will need an ID for that insert, so if you don't generate one yourself, have an auto_increment field, and get that id back using mysql_insert_id() after running the first insert query.

Then, when you insert the games and restaurants into their respective tables, be sure to include the hotel/casino ID in the insert rows, so you have a link to form the relationship.

Finally, when you select the data, we will use the left outer join. This type of join will allow you to select hotels, even if they don't have any games or restaurants.

The select query would be something like this (your table/field names may be different):

SELECT * FROM hotels LEFT OUTER JOIN restaurants ON hotels.id = restaurants.hotel_id LEFT OUTER JOIN games on hotels.id = games.hotel_id

If you haven't taken any database classes, understanding table relationships can be tricky at first. I would recommend reading through that Wikipedia page to get more of an understanding.

Hope this helps!

You might be able to setup a view with the appropriate joins, and insert into the view. I've tried it a few times, and it seems to work ok.

The data is going into the first table but not on the other tables. I dont know where I am going wrong please could you help? I need the three tables to be in relationship with one another.

The primary keys for casino_tbl == id
The primary key for games_tbl == games_id
The primary key for restaurant_tbl == rest_id

I would like the data in tables restaurant_tbl and games_tbl to get the same id as its data in casino_tbl.

Please could you let me know where I am going wrong.

Thanks

I would like the data in tables restaurant_tbl and games_tbl to get the same id as its data in casino_tbl.

That's not the best way to do it. In the last two tables, add another field called casino_id, and set that field to the id of casino_tbl. You can get that on the fly by running $casino_id = mysql_insert_id($conn); after executing the first query.

If the other two are failing, insert this code after the $exec statements:

if (!$exec) die(mysql_error());

Is there any error regarding not adding data to the two tables?
I.E. on screen or in a server error log? Usually the error logs are created in the directory where the script ran with the error, so wherever the file is where all that above PHP code is.

____________

Why do you have multiple restaurant names in the third table? If that's to house all the restaurants each casino has, then you shouldn't do it that way.

(Unless you know for certain they will only ever have 10 restaurants and no data required for each restaurant other then its name, then perhaps they way you already have is sufficient.)

At the moment you have up to rest_10. What if a casino comes along with 11 restaurants? You would have to add another column to your table and then go and alter all your PHP scripts to accommodate the new column name.
That's perhaps unlikely, but what if the max restaurants any casinos has is 7? You have wasted column names.

A better way is to have a single row for EACH restaurant, which also allows for more details on each restaurant.
I.E.

So your casino_tbl is all the data about the casino itself, opening times, total games, total restaurants etc (whatever you need depending on what you want to provide and output)
Then EACH ROW in the restaurant table is all the data about each restaurant and the "casino_name" also in each row ties it to the particular casino and data within the other two tables as they also have casino_name.

The added beauty of this method is you can house more data for each restaurant within each row.

etc

So querying the restaurant_tbl in a loop with the casino name will get all restaurants for that casino and each loop you have access to all the details for that restaurant.

If you don't have any data about the restaurant other than the name, then perhaps you might consider having them all in one row, but in ONE COLUMN.
Using a separator, usually a |. The when you query it with casino_name, you split the data from restaurant_name using the |.

Help needed!

How can I insert into mysql DB using PHP only if all form fields are coreect?

Mine is storing all the correct forms. But if I re-submit correct information, it says duplicate exists. It is not storing the corrected information. All those incorrect fields are left blank. Validations are working fine. Except it is not dispalying correct error message for duplicate username. Please somebody help me figure this out. I'm new to IT and PHP/MySQL.

Here is my code:

It is not storing missing information even if I correct it.

Thank you.

phphelpseeker

PLease any help!! I am a student trying to write my project using xampp and will like to know using php to create a single form to be able to insert data into 5 different tables in the same database created.Please any help i really need to do this as early as possible

Here is my php code i want that whenever i run my HTML page new enter will get stored in my database, please help me in this code ,here below is my PHP code.

Here is my php code i want that whenever i run my HTML page new enter will get stored in my database, please help me in this code ,here below is my PHP code.

You might be able to setup a view with the appropriate joins, and insert into the view. I've tried it a few times, and it seems to work ok.

This is great , thanks to you I got this information. I appreciate your work, thanks for taking this opportunity to discuss this, the thread is really helpful.

mysql> select * from customers;
+--------+---------+--------+--
| userID| fname | sname |
+--------+---------+--------+--
| 1 | Erick | nkonya|
| 3 | magreth | jay |
| 4 | amina | joseph |
| 5 | kenedy | john |
| 6 | martha | garet |
| 7 | masawe | mushi |
+--------+---------+--------+--
7 rows in set (0.00 sec)

mysql> select * from link;
+--------+----------+----
| userID | memberID |
+--------+----------+----
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
+--------+----------+-----
5 rows in set (0.00 sec)

mysql> select * from list;
+----------+--------+-------+--------+--------+------
|memberID| userID| title | fnem | midnem |
+----------+--------+-------+--------+--------+------
| 1 | 0 | Dr | madee| j |
| 2 | 0 | Ms | janeth | R |
| 3 | 0 | Ms | jane | m |
+----------+--------+-------+--------+-----

i have three tables CUSTOMERS,LIST,LINK... my problem is that i have a table called LINK that has the userID(which is the primary key for the CUSTOMERS table) and memberID(which is the primary key for the LIST table) when i insert information in the customers table am expecting the userID and the memberID in the LINK table to be filled with the values that appear in the CUSTOMERS table.But when i add the information there is no any connection between the three tables... WHAT is the appropriate query to accomplish the joint.

Thank you guys. Special thanks to pr0gr4mm3r. This was very helpful.

i am developing chat application..
what is my problem here by.

i am unable to passing messages to particular user.if am passing any message that store default on db and seeing same message in all what ever users logined on chat.

please give me solutions for it.