Page 1 of 1
need ADD page to INSERT new ID into two tables
Posted: Wed Jul 18, 2007 8:31 am
by gregorious
I got my PHP/MYSQL start about a year ago with K. Yank book "Build Database Driven Website", so I am
still new at coding PHP/MYSQL.
My current project is building a real estate website - almost done. The CMS has form pages that ADD / EDIT / DELETE residential records. The ADD page creates a new residential record in the database; and the EDIT and DELETE pages manage each residential record.
Each residential record has four pictures associated with that record. Adding pictures is done in the EDIT page. Under my design, the admin must first create a record using the ADD page.
--------ISSUE---------
The ADD page inserts a record into the RESIDENTIAL TABLE; RES_ID is the primary key.
I also need the ADD page to INSERT the newly created RES_ID into the IMAGES TABLE field IMAGE_RES_ID.
Code: Select all
<?php
if ($submit): // add new CONTENT from form below
$sqlinsert = "INSERT INTO residential SET
res_order='$resorder',
res_head='$reshead',
res_description='$resdescription',
res_price='$resprice'";
if (@mysql_query($sqlinsert)) {
echo("<p>New Residential property added!</p>");
} else {
echo("<p>Error adding new Residential property: " . mysql_error() . "</p>");
}
?>
My research and thoughts make this code (or something like it) look like a solution but my testing proves me wrong on its placement in the code and it syntax.
Code: Select all
$imginsert = "INSERT INTO image SET res_order= mysql_insert_id";
<--edited, changed table name to "image"
Any one see where I went off the road? Thanks in advance.
Greg
.
Posted: Wed Jul 18, 2007 8:43 am
by Gente
mysql_insert_id() php function can be interesting
found the solution
Posted: Fri Jul 20, 2007 11:13 am
by gregorious
okay, found the solution [for this coding style]. I inserted this code before the end of the closing PHP tag ?> and I got the newly created ID primary key from the RESIDENTIAL table to be INSERTED into the IMAGE table in the image_res_id field.
Code: Select all
$id = mysql_insert_id();
$imginsert = "INSERT IGNORE INTO image SET image_res_id=$id";
if (@mysql_query($imginsert)) {
echo("<p>New Residential ID added to Image table!</p>");
} else {
echo("<p>Error adding Residential ID into Image table! " . mysql_error() . "</p>");
}
?>
Why do these two FORM syntax differ?
Posted: Fri Jul 20, 2007 11:39 am
by gregorious
While doing my research on the issue of inserting data into more than one table with the same form, I came across another solution that was written in a different syntax of code.
I was wondering why it differs from the syntax I use.
My FORM syntax:
Code: Select all
<?php
if (isset($_POST['submit'])): // add new CONTENT from form below
$sqlinsert = "INSERT INTO residential SET
res_order='$resorder',
res_head='$reshead',
res_description='$resdescription',
res_price='$resprice'
if (@mysql_query($sqlinsert)) {
echo("<p>New Residential property added!</p>");
} else {
echo("<p>Error adding new Residential property: " . mysql_error() . "</p>");
}
?>
and other FORM syntax:
Code: Select all
<?php
if(isset($_POST['artists'])):
$artists = $_POST['artists'];
$bio = $_POST['bio'];
$gigdate = $_POST['gigdate'];
$gigtime = $_POST['gigtime'];
$price = $_POST['price'];
$venue_name = $_POST['venue_name'];
$venue_city = $_POST['venue_city'];
$venue_address = $_POST['venue_address'];
$venue_postcode = $_POST['venue_postcode'];
$venue_tel = $_POST['venue_tel'];
$sql = mysql_query("INSERT INTO `artists` ( `artists` , `bio` )
VALUES ('$artists', '$bio')");
$sql2 = mysql_query("INSERT INTO `listings` ( `gigdate` , `gigtime` , `price` )
VALUES ('$gigdate', '$gigtime', '$price')");
$sql3 = mysql_query("INSERT INTO `venue` ( `venue_name`, `venue_city`, `venue_address`, `venue_postcode`, `venue_tel` )
VALUES ('$venue_name', '$venue_city', '$venue_address', '$venue_postcode', '$venue_tel')");
if(@mysql_query($sql and $sql2 and $sql3))
{
echo '<p>New Listing!</p>';
}
else
{
echo '<p>Error:' . mysql_error() . '</p>';
}
?>
Posted: Fri Jul 20, 2007 5:46 pm
by feyd
This seems like a problem with SQL, not PHP per se.
good form?
Posted: Sat Jul 21, 2007 10:35 am
by gregorious
My original task was to insert a new ID into another TABLE using the same form. (ADD new record and INSERT new ID into seperate table). And while researching this issue, I ran across this thread:
http://www.sitepoint.com/forums/showthread.php?t=463592 The syntax was so different from what I had been writing; so I was wondering how out of date my coding syntax is?
Code: Select all
<?php
if(isset($_POST['artists'])):
$artists = $_POST['artists'];
$bio = $_POST['bio'];
$sql = mysql_query("INSERT INTO `artists` ( `artists` , `bio` ) VALUES ('$artists', '$bio')");
if(@mysql_query($sql))
{
echo '<p>New Listing!</p>';
}
else
{
echo '<p>Error:' . mysql_error() . '</p>';
}
?>
I would write the same form as:
Code: Select all
<?php
if (isset($_POST['submit'])):
$sqlinsert = "INSERT INTO artist SET artists='$artists', bio='$bio'
if (@mysql_query($sqlinsert)) {
echo("<p>New Listing!</p>");
} else {
echo("<p>Error" . mysql_error() . "</p>");
}
?>
The two differences I see are in the syntax of the mysql_query line. The VARIABLES and the FIELD names are expressed differently, and the FIELD names have ` (accent marks) as opposed to ' (single quotes). This sytle makes me wonder about my syntax - since the K. Yank book I learned from was a 2001 first edition. And I have received some advice regarding REGISTERED GLOBALS and learning to code with them OFF.
Posted: Sat Jul 21, 2007 11:05 am
by superdezign
Using backticks (`) can avoid issues with reserved words and is the appropriate way to specify tables and columns.
And yeah, never take one man's (the author of the book) advice as truth until you know for certain.