0

After looking around on stackoverflow, I'm still having a little trouble understanding the one-to-many relationship in mysql. I have a request coming in from the user (form submission) which will be stored in one table. This is a dynamic form that lets the user add extra fields therefore those will be stored in a separate table. So in short, in my db design, there will be one table for the users with PRIMARY KEY AUTO INCREMENT and there will be another table for the hostnames PER user (multiple fields -array) and using a foreign key that references to the primary key in the user table. Sorry if this is long but trying to make this a good question.

Example:

User Table: (ONE) 

1. John Doe, blah, 11-12-15
2. Sally Po, blah, 11-14-15
3. John Doe, blah, 11-15-15

(these are three separate requests)
(numbers are primary key auto incr.)

Host Name Table: (MANY)
1. www.johndoe.com
1. www.johndoe2.com
1. www.johndoe3.com
2. www.sallypo.com
2. www.sallypo2.com

(these numbers (foreign key) should match the primary key for each request)

Code (Leaving out the actual queries + pretty sure I shouln't be using last_id):

$sql = "CREATE TABLE IF NOT EXISTS userTable (
id int AUTO_INCREMENT, 
firstName VARCHAR(30) NOT NULL,
date DATE NOT NULL,
PRIMARY KEY (id)
)";

//query

$sql = "CREATE TABLE IF NOT EXISTS hostNamesTable (
id int NOT NULL,
hostName VARCHAR(90) NOT NULL,
FOREIGN KEY (id) REFERENCES userTable(id)
)";
//query

$sql = "INSERT INTO userTable (firstName, date) 
VALUES ('$firstName', '$date')";
//query

$last_id = mysqli_insert_id();

for($i = 0; $i < sizeof($hostName); $i++){
    $sql = "INSERT INTO hostNamesTable (id, hostName)
    VALUES ('$last_id', '$hostName[$i]')";
    //query
}

What am I doing wrong? (is this the right way to go about it?)
note: I was trying to get the last_id of the user Table so that I can use it in the hostName table as the foreign key

EDIT: I'm using MySQLi with php

EDIT 2:
After the changes, this is the error I am getting now: Cannot add or update a child row: a foreign key constraint fails (d9832482827984hb28397429.hostNamesTable, CONSTRAINT hostNamesTable_ibfk_1 FOREIGN KEY (id) REFERENCES userTable (id))Error: INSERT INTO hostNamesTable (id, hostName, ) VALUES ('', 'secondhost.net')

--Looks like the $last_id isn't even being recorded?

EDIT 3: Started working. Not sure what it was but I think it was because of some type.

shapiro
  • 131
  • 10
  • 3
    you are missing a `$` here `sizeof($hostName)` – cmorrissey Nov 12 '15 at 15:00
  • 1
    Welcome to SO! Help us out a bit - you've given great context and code, but what's lacking is a description of any problem. Do you get an error? – random_user_name Nov 12 '15 at 15:01
  • And your instinct on `$last_id` is on point - you should name it meaningfully, such as `$user_id` – random_user_name Nov 12 '15 at 15:01
  • [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/q/12859942/3840840) – Sebastian Brosch Nov 12 '15 at 15:02
  • Hey thanks for point that out! I do get a foreign key constraint fails error but I'll make sure try it with adding in the dollar sign. So what is the point of having the foreign key in the first place when using $last_id (can't it just by any regular field?) – shapiro Nov 12 '15 at 15:05
  • I have added the error message above after making the changes – shapiro Nov 12 '15 at 16:00

2 Answers2

0

why dont you just add an extra column in the hostNames table which is called "ref_user" and contains the ID of the user you are reffering to? So you can use unique IDs in both tables.

Make a query like:

SELECT * FROM hostNames WHERE ref_user = (SELECT id FROM userTable WHERE <uniqueColumn> = <uniqueIdentifierOfUser>);

But the included request must return only one line from users.

Timo Widmaier
  • 29
  • 8
0

try adding mysqli $link as a parameter in your mysqli_insert_id 

$last_id = mysqli_insert_id($link);

i presume you have this somewhere in your code

$link = mysqli_connect("localhost", "mysql_user", "mysql_password", "mysql_db");

if this doesn't work, try using mysql LAST_INSERT_ID() function

$last_id = $mysqli->query("SELECT LAST_INSERT_ID() AS last_id")->fetch_object()->last_id;
David
  • 522
  • 3
  • 7