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

They have: 20 posts

Joined: Dec 2008

I have three tables:

"INSERT INTO one_tbl (id, hotel_name, street)
VALUES('', '".$hotelName."', '".$street."',)";

"INSERT INTO two_tbl (games_id, hotel_name, roulette,)
VALUES('', '".$games_id."', '".$hotel_name."', '".$bj."')";

"INSERT INTO three_tbl (rest_id, hotel_name, rest_name1,)
VALUES('".$rest_id."', '".$hotel_name."', '".$rest1."')";

What I would like to do is get all the data from a form and post it in multiple tables but still refer to the entry as a whole. eg.. A hotel and casino, the first table would be the details of the company, the second table would be the games they offer and the third table would be the restaurants in the casino. I would like the casino name to correspond to all the databases.

I have read about JOIN() and outer joins and union() commands. But i do not know how to implemeted this in my php script.

Please could you steer me in the right direction.

Thanks

greg's picture

He has: 1,581 posts

Joined: Nov 2005

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.

They have: 1 posts

Joined: Jul 2015

It was great information for everyone to inrease the knowledge for MySQL

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

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! Smiling

They have: 4 posts

Joined: Jan 2011

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.

teammatt3's picture

He has: 2,102 posts

Joined: Sep 2003

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.

They have: 20 posts

Joined: Dec 2008

<?php
   
   
// Hotel and casino details from casino update form
   
$hotelName = $_POST['hotel_name'];
   
$street = $_POST['street'];
   
$city = $_POST['city'];
   
$state = $_POST['state'];
   
$zip = $_POST['zip'];
   
$country = $_POST['country'];
   
$tel = $_POST['tel'];
   
$fax = $_POST['fax'];
   
$website = $_POST['website'];
   
   
// Games from the casino update form
   
$slots = $_POST['slots'];
   
$roulette = $_POST['roulette'];
   
$bj = $_POST['bj'];
   
$punto = $_POST['punto'];
   
$poker = $_POST['poker'];
   
$tcp = $_POST['tcp'];
   
$spanish = $_POST['spanish'];
   
$dice = $_POST['dice'];
   
$cardroom = $_POST['cardroom'];
   
   
// Resaurant in the casino update form
   
$rest1 = $_POST['rest1'];
   
$rest2 = $_POST['rest2'];
   
$rest3 = $_POST['rest3'];
   
$rest4 = $_POST['rest4'];
   
$rest5 = $_POST['rest5'];
   
$rest6 = $_POST['rest6'];
   
$rest7 = $_POST['rest7'];
   
$rest8 = $_POST['rest8'];
   
$rest9 = $_POST['rest9'];
   
$rest10 = $_POST['rest10'];
   
   
// connect to mysql
   
$conn = mysql_connect("localhost", "username", "password")
            or die(
"ERR: Connection");
   
   
// connect to database       
   
$db = mysql_select_db("casino_list", $conn)
          or die(
"ERR: Database");
         
   
// create mysql query
    // Insert a row of information into the table "casino_tbl"
   
$sql = "INSERT INTO casino_tbl (id, hotel_name, street, city, zip, state, country, telephone, fax, website)
    VALUES('', '"
.$hotelName."', '".$street."', '".$city."', '".$zip."', '".$state."', '".$country."', '".$tel."', '".$fax."', '".$website."')"
   
   
// execute query
   
$exec = mysql_query($sql, $conn);
   
   
$sql_1 = "INSERT INTO games_tbl (games_id, hotel_name, slots, roulette, bj, punto-banco, poker, three-card-poker, spanish, craps, card-room)
    VALUES('', '"
.$hotelName."', '".$slots."', '".$roulette."', '".$bj."', '".$punto."', '".$poker."', '".$tcp."', '".$spanish."', '".$dice."', '".$cardroom."')";  
   
   
// execute query
   
$exec = mysql_query($sql_1, $conn);     
     
     
$sql_2 = "INSERT INTO restaurant_tbl (rest_id, hotel_name, rest_name1, rest_name2, rest_name3, rest_name4, rest_name5, rest_name6, rest_name7, rest_name8, rest_name9, rest_name10)
    VALUES('', '"
.$hotelName."', '".$rest1."', '".$rest2."', '".$rest3."', '".$rest4."', '".$rest5."', '".$rest6."', '".$rest7."', '".$rest8."', '".$rest9."', , '".$rest10."')";

   
// execute query
   
$exec = mysql_query($sql_2, $conn);
   
   
        echo (
"The following data has been added to casino_list");
?>

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

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

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

They have: 121 posts

Joined: Dec 2008

In addition to pr0gr4mm3r's suggestion, if you NEED all three to succeed to keep your db sane, wrap all your inserts in a transaction, and if one (or more) of the three fail, roll back your inserts.

And... For the love of all that is beatiful in this world, sanitize the user input before doing the inserts!

Cheers,
Shaggy

greg's picture

He has: 1,581 posts

Joined: Nov 2005

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.

restaurant_tbl -->
rest_id, hotel_name, rest_name

Then say casino XYZ has 5 restaurants, there will be 5 rows in the restaurant table, each row with each of the restaurant names and the casino name.

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.

ROW1 -->
casino_name = XYZ
restaurant_name = ABC
restaurant_opening_time = 0900

ROW2 -->
casino_name = XYZ
restaurant_name = DEF
restaurant_opening_time = 0830

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 |.

They have: 2 posts

Joined: May 2009

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:

<?php
$db
= mysql_connect(localhost, 'DBUsername', 'DBpassword') or die('Error: ' . mysql_error());
mysql_select_db('DBname') or die('Could not select database');

$firstname = $_POST['firstname']; 
$lastname = $_POST['lastname'];
$username = $_POST['username'];
$password = $_POST['passowrd'];
$str = $_POST['street'];
$city = $_POST['city'];
$state = $_POST['state'];
$zipcode = $_POST['zipcode'];
$phone = $_POST['phone'];
$email = $_POST['email'];
if ((isset(
$_POST['firstname'])) && (isset($_POST['lastname']))){
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
}else {
echo
"Please enter your firstname and lastname in the fields provided<br/>";
}
if(isset(
$_POST['username']) && strlen($_POST['username']) >= 6 && strlen($_POST['username']) <=32){
$sql = "SELECT * FROM Accounts WHERE Username='mysql_real_escape_strings($username)'";
$query = mysql_query($sql) or die(mysql_error());
if(
mysql_num_rows($query) < 1)
{
$username = $_POST['username'];
}else{
$username = NULL;
echo
"Username already exists. Please enter a different username.<br/>";   
}
}
if(isset(
$_POST['password']) && strlen($_POST['password']) >= 6 && strlen($_POST['password']) <= 15 ){
$password = md5($_POST['password']);
}else{
$password = NULL;
echo
"Please enter a valid alphanumeric password that is atleast 6 characters long.<br/>";   
}
if(isset(
$_POST['street'])){
$str = $_POST['street'];
}else{
$street = NULL;
echo
"Please enter a valid Street name.<br/>";   
}
if(isset(
$_POST['city'])){
$city = $_POST['city'];
}else{
$city = NULL;
echo
"Please enter a valid City name.<br/>";   
}
if(isset(
$_POST['state'])){
$state $_POST['state'];
}else{
$state = NULL;
echo
"Please select a state from the drop down menu name.<br/>";   
}
if (
preg_match("/^[0-9]{5}([0-9]{4})?$/i", $zipcode)) {
$zipcode = $_POST['zipcode'];
} else {
$zipcode = NULL;
echo
"Zip Code is invalid.<br/>";
}
if(
ereg('^[2-9]{1}[0-9]-{2}[0-9]{3}-[0-9]{4}$', $phone)) {
$phone = $_POST['phone'];
} else {
$phone = NULL;   
echo
"Please enter a valid phone number.<br/>";
}
if(
preg_match("/^[^0-9][a-zA-Z0-9_]+([.][a-zA-Z0-9_]+)*[@][a-zA-Z0-9_]+([.][a-zA-Z0-9_]+)*[.][a-zA-Z]{2,4}?$/i",$email)){
$email = $_POST['email'];
} else {
$email = NULL;
echo
"Please enter a valid E-Mail address.<br/>";
}
$sqli = "INSERT INTO Accounts (Firstname, Lastname, Username, Password, Street, City, State, Zipcode, `Primary Phone`, Email) VALUES ('$firstname', '$lastname', '$username', '$password', '$str', '$city', '$state', '$zipcode', '$phone', '$email')";
$ins = mysql_query($sqli) or die(mysql_error());
echo
"You are registered successfully.<br/>";
?>

They have: 2 posts

Joined: May 2009

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

Thank you.

phphelpseeker

She has: 44 posts

Joined: Dec 2010

<?php
// sample new data to insert into our db
// -------------------------------------
$name = 'Jim';
$gender = 'm';
$site = 'www.google.com';
// -------------------------------------

// Inserting the data into users
// -----------------------------
$sql = "INSERT INTO users VALUES (NULL,'$name','$gender')";
$result = mysql_query( $sql,$conn );

#  if the user submitted a website
if( isset($site) )
{
 
#  get the user id 
 
$user_id = mysql_insert_id( $conn );

 
#  and insert the website details
 
$sql = "INSERT INTO website VALUES (NULL,'$site',$user_id)";
 
$result = mysql_query( $sql,$conn );
}
mysql_free_result( $result );
// -----------------------------

?>

They have: 1 posts

Joined: Oct 2011

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

They have: 2 posts

Joined: Jan 2012

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.

<?php
$con
= mysql_connect("localhost","root","ozone2412");

if(!
$con){
   die(
"could not connect: ". mysql_error());
}
mysql_select_db("gurneet",$con);
$sql="INSERT INTO login(Username , Password, Id, Role) VALUES ('$_POST['username']','$_POST['password']','$_POST['id']','$_POST['role']')";
or die(
"data not inserted");

$exec=mysql_query($sql,$con);

$sql_1="INSERT INTO login(Username , Password , Id ,Role) VALUES ('$_POST['username']','$_POST['password']','$_POST['id']','$_POST['role']')";

$exec=mysql_query($sql_1,$con);

if(!
mysql_query($sql,$con)){
  die(
"ERROR: ".mysql_error());
}
echo
"1 record added";

mysql_close($con);
?>

They have: 2 posts

Joined: Jan 2012

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.

<?php
$con
= mysql_connect("localhost","root","ozone2412");

if(!
$con){
   die(
"could not connect: ". mysql_error());
}
mysql_select_db("gurneet",$con);
$sql="INSERT INTO login(Username , Password, Id, Role) VALUES ('$_POST['username']','$_POST['password']','$_POST['id']','$_POST['role']')";
or die(
"data not inserted");

$exec=mysql_query($sql,$con);

$sql_1="INSERT INTO login(Username , Password , Id ,Role) VALUES ('$_POST['username']','$_POST['password']','$_POST['id']','$_POST['role']')";

$exec=mysql_query($sql_1,$con);

if(!
mysql_query($sql,$con)){
  die(
"ERROR: ".mysql_error());
}
echo
"1 record added";

mysql_close($con);
?>

They have: 3 posts

Joined: Jan 2012

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.

They have: 10 posts

Joined: Mar 2012

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.

They have: 1 posts

Joined: Apr 2012

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.

They have: 1 posts

Joined: Sep 2013

Ricky nkonya wrote:
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.

They have: 1 posts

Joined: Sep 2013

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

They have: 1 posts

Joined: Jan 2015

<?php

//create connection into a database then
include 'conn.php';
$sql="INSERT INTO tablename (first,second) VALUES ('$_POST['first']','$_POST['second']')';
$result=mysql_query($sql);

// if successfully insert data into database, displays message "
Successful".
if(
$result){
echo "
Successful";
echo "
<BR>";
}

else {
echo "
ERROR";
}
?>

AttachmentSize
insert.png 16.23 KB

They have: 1 posts

Joined: Apr 2015

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.

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.