How to insert Primary Key value of the primary table to the Foreign Key column of the child table in MySQL?

Question

Hello i am a beginner in PHP and MYSQL. I have made two tables. The Primary table 'customers' is:

id | name  |  place  |  email
 1   bob      berlin   bob@bob.com
 2   kary     dresden  kary@kary.com
 3   sam      zurich   sam@sam.com

My child table 'orders' where 'menu_id' is a foreign key is:

id  | menu_name | menu_id | date_of_order
 1    sandwich       2       2016-05-03
 2    fruits         3       2016-05-02
 3    pizza          1       2016-05-04
 4    salad          1       2016-05-06

Now with a simple LEFT JOIN i can see which orders are placed by which customer in which date.

But problem is when in case of adding a new user in my 'customers' table i cannot insert its 'id' into the 'menu_id' column as the foreign key.

After this suppose i do

INSERT INTO customers (name, place, email) VALUES ('joe', 'melbourne', 'joe@xyz.com')

and in the orders table i do

INSERT INTO orders(menu_name, menu_id, date_of_order)
VALUES('pizza', 'SELECT id FROM customers WHERE email = joe@xyz.com', '2016-05-09')

After this the 'orders' table looks like

id  | menu_name | menu_id | date_of_order
 1    sandwich       2       2016-05-03
 2    fruits         3       2016-05-02
 3    pizza          1       2016-05-04
 4    salad          1       2016-05-06
 5    pizza          0       2016-05-09

The menu_id in 'orders' table should be showing '4' instead of 0. I have also tried LAST_INSERT_ID() but still the value it gets is 0. I dont know why this happens. Please help

Answer 1

You problem is that you are doing this wrong

INSERT INTO orders(menu_name, menu_id, date_of_order) VALUES('pizza', 'SELECT id FROM customers WHERE email = joe@xyz.com', '2016-05-09')

should be

INSERT INTO `orders`(`menu_name`, `menu_id`, `date_of_order`) VALUES('pizza', (SELECT `id` FROM `customers` WHERE `email` = 'joe@xyz.com' limit 1), '2016-05-09');

So you basically trying to insert 'SELECT id FROM customers WHERE email = joe@xyz.com' as string which is converting to int (if you have it as int)

Create statments:

CREATE TABLE `customers` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(245) DEFAULT NULL,
    `place` varchar(245) DEFAULT NULL,
    `email` varchar(245) DEFAULT NULL,
    PRIMARY KEY (`id`)
)  ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

CREATE TABLE `orders` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `menu_name` varchar(245) DEFAULT NULL,
    `menu_id` int(11) DEFAULT NULL,
    `date_of_order` date DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK orders menu_id  customer id_idx` (`menu_id`),
    CONSTRAINT `FK orders menu_id  customer id` FOREIGN KEY (`menu_id`)
        REFERENCES `customers` (`id`)
        ON DELETE SET NULL ON UPDATE NO ACTION
)  ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

And you insertions:

INSERT INTO `customers` (`name`, `place`, `email`) VALUES ('bob', 'berlin', 'bob@bob.com');
INSERT INTO `customers` (`name`, `place`, `email`) VALUES ('kary', 'dresden', 'kary@kary.com');
INSERT INTO `customers` (`name`, `place`, `email`) VALUES ('sam', 'zurich', 'sam@sam.com');


INSERT INTO `orders`(`menu_name`, `menu_id`, `date_of_order`) VALUES('sandwich', (SELECT id FROM customers WHERE email = 'kary@kary.com' limit 1), '2016-05-09');
INSERT INTO `orders`(`menu_name`, `menu_id`, `date_of_order`) VALUES('fruits', (SELECT id FROM customers WHERE email = 'sam@sam.com' limit 1), '2016-05-09');
INSERT INTO `orders`(`menu_name`, `menu_id`, `date_of_order`) VALUES('pizza', (SELECT id FROM customers WHERE email = 'bob@bob.com' limit 1), '2016-05-09');
INSERT INTO `orders`(`menu_name`, `menu_id`, `date_of_order`) VALUES('salad', (SELECT id FROM customers WHERE email = 'bob@bob.com' limit 1), '2016-05-09');


INSERT INTO `customers` (`name`, `place`, `email`) VALUES ('joe', 'melbourne', 'joe@xyz.com');
INSERT INTO `orders`(`menu_name`, `menu_id`, `date_of_order`) VALUES('pizza', (SELECT id FROM customers WHERE email = 'joe@xyz.com' limit 1), '2016-05-09');

and result

mysql> select * from customers;
+----+------+-----------+---------------+
| id | name | place     | email         |
+----+------+-----------+---------------+
|  1 | bob  | berlin    | bob@bob.com   |
|  2 | kary | dresden   | kary@kary.com |
|  3 | sam  | zurich    | sam@sam.com   |
|  4 | joe  | melbourne | joe@xyz.com   |
+----+------+-----------+---------------+
4 rows in set (0.02 sec)

mysql> select * from orders;
+----+-----------+---------+---------------+
| id | menu_name | menu_id | date_of_order |
+----+-----------+---------+---------------+
|  1 | sandwich  |       2 | 2016-05-09    |
|  2 | fruits    |       3 | 2016-05-09    |
|  3 | pizza     |       1 | 2016-05-09    |
|  4 | salad     |       1 | 2016-05-09    |
|  5 | pizza     |       4 | 2016-05-09    |
+----+-----------+---------+---------------+
5 rows in set (0.02 sec)