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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Regular Coder
    Join Date
    Aug 2013
    Posts
    142
    Thanks
    22
    Thanked 0 Times in 0 Posts

    query mysql database foreign key

    I have created a Models table and a Manufacturers table and put them into a database. The model table has a foreign key which references the primary key in the Manufacturer table.

    This is the query i used to create the model table:

    $sql=" CREATE TABLE Models(
    MOD_ID int AUTO_INCREMENT NOT NULL,
    MANU_ID int,
    MOD_Name varchar(40),
    PRIMARY KEY(MOD_ID),
    FOREIGN KEY(MANU_ID) REFERENCES Manufacturers(MANU_ID)
    )";

    What I want to know is that when I query the Models table using the query below, is the MANU_ID supposed to show up automatically in the Models table through the relationship I have indicated or do I need to indicate in the query below that MANU_ID is a foreign key from the Manufacturers table.

    $sql = "SELECT * FROM Models";
    $retrieval = mysql_query($sql, $connect);
    if(!$retrieval){
    die('Could not get data: ' . mysql_error());
    }
    while($row = mysql_fetch_array($retrieval, MYSQL_ASSOC))
    {
    echo
    "MOD ID: {$row['MOD_ID']} <br>".
    "MANU ID: {$row['MANU_ID']} <br>".
    "MOD Name: {$row['MOD_Name']} <br>";
    }
    echo "Fetched data successfully\n";

    At the moment when I query the Models table the MANU_ID doesn't show which makes me think that either I have not referenced it as a foreign key correctly in the query to create the Models table or my query to show all in the models table isn't correct.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    Why didn't you try it?

    The real question: Did you INSERT a MANU_ID value *INTO* the table when you added data? If yes, it will be there and *IF* both tables are INNODB tables (as opposed to MyISAM tables) then it will be guaranteed to be a valid value from the Manufacturers table.

    But nothing will automatically put that number in place. You had to put it there when you did the INSERTs.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    daniel0816 (08-22-2013)

  • #3
    Regular Coder
    Join Date
    Aug 2013
    Posts
    142
    Thanks
    22
    Thanked 0 Times in 0 Posts

    the query

    How do I change the query below in order to insert the MANU_ID into the models table from the Manufacturer table

    $sql="INSERT INTO Models(MOD_Name)
    VALUES
    ('$_POST[model]')"

    Thanks for help already really appreciate it.

  • #4
    Regular Coder
    Join Date
    Aug 2013
    Posts
    142
    Thanks
    22
    Thanked 0 Times in 0 Posts
    has anyone got any suggestions anything ive tried isn't the correct syntax

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    Code:
    $model = $_POST["model"];
    $manuid = $_POST["manu_id"];
    
    $sql="INSERT INTO Models(MOD_Name, MANU_ID) VALUES('$model',$manuid)";
    One more time. There is NOTHING MAGIC that will automatically insert *ANY* values into a table. YOUR CODE must find and insert the proper values for *ALL* fields. (The sole exceptions are auto_increment fields and field with default values, neither of which applies here.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    In PHP, you can also do the following:

    PHP Code:
    $sql="INSERT INTO Models(MOD_Name)
    VALUES
    ('{$_POST[model]}')" 
    Squiggly brackets MUST be around arrays, objects, etc, when inside double quotes.

  • #7
    Regular Coder
    Join Date
    Aug 2013
    Posts
    142
    Thanks
    22
    Thanked 0 Times in 0 Posts
    TBH Old Pedant auto increment does apply because MANU_ID is set to auto increment.

  • #8
    Regular Coder
    Join Date
    Aug 2013
    Posts
    142
    Thanks
    22
    Thanked 0 Times in 0 Posts
    no nvm it doesnt lol

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    Last comment correct. MANU_ID is auto_increment, but IN THE OTHER TABLE.

    That has no impact on the INSERT in this table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Regular Coder
    Join Date
    Aug 2013
    Posts
    142
    Thanks
    22
    Thanked 0 Times in 0 Posts
    Could i use the last insert id function which would retrieve the last generated manu_id and insert it into the models table? if so how would the code be structured.

    Thanks

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    Not only could you do that, you SHOULD.

    Easiest way to get it:
    Code:
    $sql = "INSERT INTO manufacturers ...";
    mysql_query($sql);
    $manuid = mysql_insert_id();
    And then the id is there to use for several inserts into the next table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    daniel0816 (08-23-2013)

  • #12
    Regular Coder
    Join Date
    Aug 2013
    Posts
    142
    Thanks
    22
    Thanked 0 Times in 0 Posts

    last insert id function

    This is my attempt at implementing the last insert id function which is to be entered into J_RefNum can anyone please tell me whats wrong with it.

    $sql="INSERT INTO dataRecSpec(J_RefNum, DRS_Name)
    VALUES
    ($jRefNum, $txt)";
    $jRefNum = mysql_insert_id();

    Much Appreciated thanks

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    You have it backwards:
    Code:
    $jRefNum = mysql_insert_id();
    $sql="INSERT INTO dataRecSpec(J_RefNum, DRS_Name) VALUES ($jRefNum, $txt)";
    Computer languages are *LINEAR*.

    You have to put the value int $jRefNum *BEFORE* you try to use it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    Regular Coder
    Join Date
    Aug 2013
    Posts
    142
    Thanks
    22
    Thanked 0 Times in 0 Posts

    mysql insert id

    Do I need to pass any parameters below because it still isn't working. There are no errors as such it just isn't displaying the J_RefNum from the job table.

    $jRefNum = mysql_insert_id(parameters here???);
    $sql="INSERT INTO dataRecSpec(J_RefNum, DRS_Name)
    VALUES
    ($jRefNum, $txt)";

    Thanks again.

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,128
    Thanks
    80
    Thanked 4,556 Times in 4,520 Posts
    mysql_insert_id() only works if you have *JUST COMPLETED* an INSERT operation on some table. With the SAME connection. You can not close and reopen the connection.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    daniel0816 (08-30-2013)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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