How to use foreign keys with PHP

Question

So, I understand how to create foreign keys, and I know what the purpose of FK is. But I have a problem in understanding how to use them. I asked a question regarding foreign keys HERE (click the link)

Here is what I did:

CREATE TABLE user(
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE items(
  i_id INT(11) NOT NULL AUTO_INCREMENT,
  name TINYTEXT NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  PRIMARY KEY (i_id)
);

CREATE TABLE user_purchase(
  i_id INT(11) NOT NULL,
  name TINYTEXT NOT NULL,
  id INT(11) NOT NULL,
  FOREIGN KEY (i_id) REFERENCES items(i_id),
  FOREIGN KEY (name) REFERENCES items(name),
  FOREIGN KEY (id) REFERENCES user(id)
);

Now my question is: how can I get the most out of it using PHP? From the link above, people have suggested that it is useful to use only one foreign key in the user_purchase table, but what if I want multiple columns? Why don't we use multiple foreign keys for different columns of the same table?

mysql php. , , PHP , , MYSQL. .

"" "". , , , - , .., .

.

Answer 1

/

, , , , FK. .

, , , .

:

, . "user_purchase.i_id items". , , .

, FOREIGN KEY:

CREATE TABLE user(
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE items(
  i_id INT(11) NOT NULL AUTO_INCREMENT,
  name TINYTEXT NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  PRIMARY KEY (i_id)
);

CREATE TABLE user_purchase(
  i_id INT(11) NOT NULL,
  name TINYTEXT NOT NULL,
  id INT(11) NOT NULL,
);

, - . , user (id), , items (i_id), name. :

  user              user_purchase    items
| id  username |    | id  i_id |    | i_id  name            price |
| 23  john     |    | 55   10  |    |  10   chocolate bar    3.42 |
| 55  mary     |    | 70   10  |    |  33   mobile phone    82.11 |
| 70  fred     |    | 70   33  |    |  54   toothpaste       8.67 |
                    | 55   10  |    |  26   toy car          6.00 |
                    | 70   26  |

. user_purchase, , . , :

select * from user_purchase p
join user u on (p.id=u.id)
join items i on (p.i_id=i.i_id)

, .

, , :

insert into user_purchase (id,i_id) values (23,99)

-, . id=23, i_id=99. RDBMS , . .

, . FOREIGN KEY (i_id) REFERENCES items(i_id) user_purchase, RDBMS : i_id , items.i_id, . , , , .

, , select , , FK. , FK, RDBMS, .

... , ? ?

: ? , . :

 user_purchase                   items
| id  i_id  name           |    | i_id  name            price |
| 55   10   chocolate bar  |    |  10   chocolate bar    3.42 |
| 70   10   chocolate bar  |    |  33   mobile phone    82.11 |
| 70   33   mobile phone   |    |  54   toothpaste       8.67 |
| 55   10   toothpaste     |    |  26   toy car          6.00 |
| 70   26   toy car        |

? 55 , ? , , . , name , .

, , , PRIMARY KEY(i_id,name) items ( UNIQUE(i_id,name) , ), FOREIGN KEY(i_id,name) REFERENCES items(i_id,name). , (i_id, name), items, user_purchases. , , , , i_id , ( name).

. , . person(id,name) parent(person,father,mother), :

 person             parent
| id  name    |    | person  father  mother |
| 14  John    |    |   21      14      59   |
| 43  Jane    |    |   14      76      43   |
| 21  Mike    |
| 76  Frank   |
| 59  Mary    |

, parent person. , : - , person. , , , person parent, , - .

Answer 2

. , , , :

select u.username
from items i
join user_purchase up on i.i_id = up.i_id
join user u on u.id = up.id
where i.name = "Some product name"

. , user_purchase, id i_id - ​​ .

name user_purchase. name - item, - . , items.

Answer 3

, , . , .

, 3 user 5 items.

user

id  |  username |  password 
 1      abc         123
 2      def         456
 3      qwe         987 

items

i_id  |  name   |  price 
 1      item 1    6
 2      item 2    8
 3      item 3    11 
 4      item 4    3
 5      item 5    14  

user_purchase

CREATE TABLE user_purchase ( i_id INT (11) NOT NULL, id INT (11) NOT NULL, FOREIGN KEY (i_id) (i_id), FOREIGN KEY (id) user (id) );

. .

i_id |  id  
 1      1      
 1      2
 2      2      
 3      3      

, 1 1, 2 1, 2, 3 3.

. MySQL JOIN

SELECT B.user_name,C.name AS item_name,C.price
FROM user_purchase A 
JOIN user B ON A.id = B.id
JOIN items C ON A.i_id = C.i_id

A.id = B.id
A.i_id = C.i_id

Answer 4

php , . () php. , php, - , sql-, ( DELETE-).

"name" "table user_purchase". .

Answer 5

/-:

- - name user_purchase - , items i_id.

, user_purchase, , , name JOIN, , . , , , FOREIGN KEY items.

- : - .

- . , , , , ( , ) - -, . , FOREIGN KEY items ( "", ).