Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Nov 2006
    Location
    Malvern, PA
    Posts
    33
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Establishing Relationships Across MySQL Tables

    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

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

    PHP Code:
    /* 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" 

  • #2
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    I would look into stored procedures and transactions
    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;

  • #3
    New Coder
    Join Date
    Nov 2006
    Location
    Malvern, PA
    Posts
    33
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for the suggestion!! I'll check this out.

    Dan

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Brandoe85 View Post
    I would look into stored procedures and transactions
    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.

    Quote Originally Posted by dmilani View Post
    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

  • #5
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Quote Originally Posted by oesxyl View Post
    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.

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,063
    Thanks
    2
    Thanked 317 Times in 309 Posts
    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.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •