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 posted this at 16:44 — 18th March 2009.
He has: 1,580 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_tbldoesn't have the field/column "games" so you cannot run an insert query onone_tbltable 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_tblandtwo_tblboth have "casino_name" and you can use the var "$casino_name" in both queries as it is the same data.Signature links on this forum are NO-follow! - This means spam is futile!
pr0gr4mm3r posted this at 17:01 — 18th March 2009.
He has: 1,422 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_idIf 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!
PHP Starter
teammatt3 posted this at 17:44 — 18th March 2009.
He has: 2,076 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.
busman posted this at 10:59 — 19th March 2009.
They have: 21 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 posted this at 12:32 — 19th March 2009.
He has: 1,422 posts
Joined: Sep 2006
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());PHP Starter
Shaggy posted this at 16:30 — 5th May 2009.
They have: 114 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 posted this at 13:10 — 19th March 2009.
He has: 1,580 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 |.
Signature links on this forum are NO-follow! - This means spam is futile!
phphelpseeker posted this at 19:52 — 4th May 2009.
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/>";
?>
phphelpseeker posted this at 19:53 — 4th May 2009.
They have: 2 posts
Joined: May 2009
It is not storing missing information even if I correct it.
Thank you.
phphelpseeker