SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to make foreign key linking in PHP/MYSQL?

    Hey..guys! I have INSERTstatement here which have two foreign keys that need to refer to other tables.How can I achieve it?

    Code:
    $sql = sprintf("INSERT INTO `reserve` (`reserve_datetime`, `seat_qty`, `ref_code`, `movie_id`, `member_id`) VALUES ('%s', '%s', '%s', '1', '1')", $date, $noOfSeat, $refCode);
    movie_id and member_id are foreign keys that linked to movie and member tables respectively.Thanks for your help..

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i imagine a situation where Betty User wants to reserve some seats for Some Movie

    how does your front end know which user it is? how does your front end know which movie it is?

    the user_id would typically come from a session variable after logging in (and retrieving the user record from the database), and the movie_id would typically come from a dropdown menu value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    user use email address to login become session value.
    movie title is selected by ticking its showtime(radio button) in a table.
    so how can refer them in sql stament?

  4. #4
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if I refer member_id as below,the insert statement can't work...

    Code:
    $foreign1 = "SELECT member_id FROM member WHERE 
                 email = \"" . $_SESSION['gmemberid'] . "\" ";
                 
    $sql = sprintf("INSERT INTO `reserve` (`reserve_datetime`, `seat_qty`, `ref_code`, `movie_id`, `member_id`) VALUES ('%s', '%s', '%s', '1', '$foreign1')",
    $date, $noOfSeat, $refCode);
    mysql_query($sql);

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    um, why are you comparing email to gmemberid in your first query?

  6. #6
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using email as member id,am I correct?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you are using the email as sessionid, and also as member_id, then something here is not right:
    Code:
    $foreign1 = "SELECT member_id FROM member WHERE 
                 email = \"" . $_SESSION['gmemberid'] . "\" ";
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry.I am using email as session id but I named it as gmember_id
    The real member_id is the primary key and auto-increment value in database

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, that's good

    but then what does "the insert statement can't work" mean?

    [moving thread to php forum]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The data is not inserted into database,not even a single column,any error in the query statement?

    Code:
    $foreign1 = "SELECT member_id FROM member WHERE 
                 email = \"" . $_SESSION['gmemberid'] . "\" ";
                 
    $sql = sprintf("INSERT INTO `reserve` (`reserve_datetime`, `seat_qty`, `ref_code`, `movie_id`, `member_id`) VALUES ('%s', '%s', '%s', '1', '$foreign1')",
    $date, $noOfSeat, $refCode);
    mysql_query($sql);

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try your query outside of php first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what did you mean?

  13. #13
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    do it in PHPMyAdmin or any mysql client - with test data, of course, not PHP variables.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  14. #14
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, i try to put this query in phpmyadmin,the first line is having error.

    Code:
    $foreign1 = SELECT member_id FROM member WHERE 
    email = "abc@gmail.com ";
    
    INSERT INTO `reserve` (`reserve_datetime`, `seat_qty`, `ref_code`, `movie_id`, `member_id`) VALUES ('2007-10-12 23:00:00', '3', '299192', '1', '$foreign1')";

  15. #15
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    don't put the php variables in it. Enter an example value for $foreign1:
    Code sql:
    INSERT INTO `reserve` (`reserve_datetime`, `seat_qty`, `ref_code`, `movie_id`, `member_id`) VALUES ('2007-10-12 23:00:00', '3', '299192', '1', '1101');
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do the SELECT yourself, to find that member's member_id

    then hardcode it into your INSERT

    note: if the column is numeric, don't put the value in quotes

    also, remove the silly backticks, mysql does not need them and they are hard to code
    Code:
    INSERT 
      INTO reserve 
         ( reserve_datetime
         , seat_qty
         , ref_code
         , movie_id
         , member_id ) 
    VALUES 
         ( '2007-10-12 23:00:00'
         , 3
         , '299192'
         , 1
         , 937 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, here it go


    There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem
    ERROR: Unclosed quote @ 149
    STR: "
    SQL:

    INSERT INTO `reserve` (`reserve_datetime`, `seat_qty`, `ref_code`, `movie_id`, `member_id`) VALUES ('2007-10-12 23:00:00', '3', '299192', '1', '1')"


    SQL query:

    INSERT INTO `reserve` (`reserve_datetime`, `seat_qty`, `ref_code`, `movie_id`, `member_id`) VALUES ('2007-10-12 23:00:00', '3', '299192', '1', '1')"

    MySQL said:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"' at line 1

  18. #18
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    you left a double-quote on the end. Run r937's query suggestion.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  19. #19
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    foreign key can't simply update,right?Now having conflicts between primary and foreign key

    #1452 - Cannot add or update a child row: a foreign key constraint fails (`movie_ticket_booking/reserve`, CONSTRAINT `reserve_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `member` (`member_id`))

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you cannot add a row for a member_id that doesn't exist

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yaya.i overlook it.however what wrong when I put it in php script


Bookmarks

Posting Permissions

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