...

View Full Version : Establishing Relationships Across MySQL Tables



dmilani
07-31-2008, 07:35 PM
Hi all,

I am in the process of overhauling a registration database. The first iteration utilized a flat database and was pretty awkward to use. I am trying to fix this and have expanded to three tables: Contacts, Teams, Registration

My current dilemma is how to post data from a form to these tables and maintain the relationships. Here is what I have so far but it uses the mysql_insert_id function. In searching for suggestions about how to do this it seems that this function is a controversial one. re: this discussion (http://codingforums.com/archive/index.php?t-94437.html)

What is the standard practice to accomplish this? Am I close? ;)


/* accept and convert values from form */
$league = $HTTP_POST_VARS['league'];
$name = $HTTP_POST_VARS['tname'];
$club = $HTTP_POST_VARS['club'];
$age = $HTTP_POST_VARS['age'];
$fname = $HTTP_POST_VARS['fname'];
$lname = $HTTP_POST_VARS['lname'];
$role = $HTTP_POST_VARS['role'];
$fallleague = $HTTP_POST_VARS['fallleague'];
$division = $HTTP_POST_VARS['division'];
$reqdiv = $HTTP_POST_VARS['reqdiv'];
$day = $HTTP_POST_VARS['day'];
$eve = $HTTP_POST_VARS['eve'];
$cell = $HTTP_POST_VARS['cell'];
$email = $HTTP_POST_VARS['email'];
$misc = $HTTP_POST_VARS['misc'];
$date = $HTTP_POST_VARS['date'];
$WinI = $HTTP_POST_VARS['WinI'];
$WinII = $HTTP_POST_VARS['WinII'];


/* open a connection to the DB */
include_once("dbconnect.php");


/* set the date variable */
$date = date("m.d.y");


/* Define insert for contacts table */
$sql1 = "INSERT INTO contacts VALUES
('$email', '$fname', '$lname', '$day', '$eve', '$cell' , '$role', 'contactID')";

/* exec contacts insert */
$result1 = @mysql_query($sql1) or die("Error #" . mysql_errno() . ":" . mysql_error() );


/* debug confirmation for contacts insertion */
echo "first insert worked" ;


/* retrieve and define contactID */
$contactID = mysql_insert_id();


/* Define insert query for teams table */
$sql2 = "INSERT INTO teams VALUES
('teamID', '$name', '$age', '$club', '$contactID', '$fallleague', '$falldiv')";

/* exec teams insert */
$result2 = @mysql_query($sql2) or die("Error #" . mysql_errno() . ":" . mysql_error() );


/* debug confirmation for contacts insertion */
echo "second insert worked" ;


/* retrieve and define teamID */
$teamID = mysql_insert_id();


/* Define insert query for registration table */
$sql3 = "INSERT INTO registration VALUES
('regID', '$league', '$teamID', '$reqdiv', '$misc', '$division', '$session', '$date')";


/* exec registration insert */
$result3 = @mysql_query($sql3) or die("Error #" . mysql_errno() . ":" . mysql_error() );


/* debug confirmation for registration insertion */
echo "third insert worked" ;

Brandoe85
07-31-2008, 07:45 PM
I would look into stored procedures (http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html) and transactions (http://www.devshed.com/c/a/MySQL/Using-Transactions-In-MySQL-Part-1/4/)
I'm not a fan doing the sql process in php like that - put it into a procedure and let mysql handle it. The transaction will help ensure you don't get junk data in your tables; if one insert fails and the other relies on that insert you'll need to rollback the transaction.

Good luck;

dmilani
07-31-2008, 08:36 PM
Thanks for the suggestion!! I'll check this out.

Dan

oesxyl
07-31-2008, 10:35 PM
I would look into stored procedures (http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html) and transactions (http://www.devshed.com/c/a/MySQL/Using-Transactions-In-MySQL-Part-1/4/)
I'm not a fan doing the sql process in php like that - put it into a procedure and let mysql handle it. The transaction will help ensure you don't get junk data in your tables; if one insert fails and the other relies on that insert you'll need to rollback the transaction.

Good luck;
if I'm not wrong transactions work only with innodb, check this now because is an old info.


Thanks for the suggestion!! I'll check this out.

Dan
I'm not sure how it work in your case, but I would lock the tables before insert and unlock after I finish. I guess this way the values of mysql_last_id will not be affected.

regards

Inigoesdr
08-01-2008, 04:20 AM
if I'm not wrong transactions work only with innodb, check this now because is an old info.
MySQL supports transactions with InnoDB and BDB, yes.

CFMaBiSmAd
08-01-2008, 04:34 AM
Using mysql_insert_id() is the proper way of getting the last auto_increment id due to an INSERT query that you can then use in other tables.


Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.

The thread you linked to was talking about getting the highest current id and adding one to it before any data had actually been inserted (and fixing the actual value for that visitor), displaying it, and then expecting that value to be accurate given that concurrent visitors/inserts would render it invalid.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum