need ADD page to INSERT new ID into two tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gregorious
Forum Commoner
Posts: 34
Joined: Wed Aug 23, 2006 9:55 am
Location: Orlando, FL

need ADD page to INSERT new ID into two tables

Post 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

.
Last edited by gregorious on Wed Jul 18, 2007 8:49 am, edited 1 time in total.
User avatar
Gente
Forum Contributor
Posts: 252
Joined: Wed Jun 13, 2007 9:43 am
Location: Ukraine, Kharkov
Contact:

Post by Gente »

mysql_insert_id() php function can be interesting
User avatar
gregorious
Forum Commoner
Posts: 34
Joined: Wed Aug 23, 2006 9:55 am
Location: Orlando, FL

found the solution

Post 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>"); 
}  
 ?>
User avatar
gregorious
Forum Commoner
Posts: 34
Joined: Wed Aug 23, 2006 9:55 am
Location: Orlando, FL

Why do these two FORM syntax differ?

Post 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>'; 
} 
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

This seems like a problem with SQL, not PHP per se.
User avatar
gregorious
Forum Commoner
Posts: 34
Joined: Wed Aug 23, 2006 9:55 am
Location: Orlando, FL

good form?

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

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