I am working on MySQL database. I am new to it that is why I am facing a problem. The problem is populating the child table with foreign key which is referencing to the parent table. I have two tables
$uname = "root";
$pass = "";
//Making database connection
$con = mysqli_connect($sname,$uname,$pass,$db);
$t1 = "CREATE TABLE IF NOT EXISTS employee (
id smallint(5) unsigned AUTO_INCREMENT NOT NULL,
PRIMARY KEY (id)
$t2 = "CREATE TABLE IF NOT EXISTS borrowed (
ref int(10) unsigned NOT NULL auto_increment,
employeeid smallint(5) unsigned NOT NULL,
PRIMARY KEY (ref),
FOREIGN KEY (employeeid) REFERENCES employee(id) ON UPDATE CASCADE ON DELETE CASCADE
$i1 = "INSERT INTO employee VALUES(NULL,\"Nadeem\",\"Ahmad\",22)";
$i2 = "INSERT INTO borrowed VALUES(NULL,1,\"Ahmad\")";
Simple what I need is ; For example an employee with id 1. Who borrowed 3 books. So in the borrowed table the employeeId column will have three rows with values 1 and different books name. My point is how would I populate the employeeId column when I am inserting the data into it. Let say, John have have borrowed three books and have id 1 then how would I insert data to borrowed table with employeeId of john. I need the query for it. and also query to retrieve the books borrowed by john.
The foreign key is used to link two tables, indicating that the field in a column
borrowed, in your case) refers to the PRIMARY KEY of another table (
When you're inserting a new line in
borrowed, you have to indicate the user that is taking that book, to insert it in that line. You have to know the user that is doing it. If you have foreign key, you need the id of that user, which is supposed to be his unique identifier. To insert that John has taken a book, you need to know that John's id is 1.
If the user is already in your
employee table and you know his first and last name, you can get the id with a simple select...
SELECT id FROM employee WHERE first_name='John' AND last_name='Smith'
... and then you can do the insert with the id obtained.
If it's new user, you need to add the user first to
employee, then get the new id and then insert the new line in
borrowed, to do this without having to re-query to
employee table to get the new id, you can use the PHP
mysqli_insert_id function, that gives you the PRIMARY key of the last query. For example...
$con->query("INSERT INTO employee (first_name,last_name) VALUES ('Mark','Whatever')"); $newemployeeid = $con->insert_id; $con->query("INSERT INTO borrowed (employeeid,book) VALUES (".$newemployeeid.",'Awesome Book Title')");
I hope it helps