CODEWITHSUNDEEP
mysqlforeign-keys
user9080416
user9080416

Reputation:

mysql - connect two cells from different tables

I'm trying to do something that should be very basic but impossible to bypass that issue. I have 2 tables. The first one is a table that stocks the page architecture and the second one stocks some contents. Occasionally, I'd like to call some contents from my first table. I've tried to create a Foreign Key but that only displays the id of the content.

Here is the structure of my table 1 (page models)

1 idPrimaire int(11) Non Aucun(e) AUTO_INCREMENT

2 client varchar(100) utf8_general_ci Non Aucun(e)
3 nom_document varchar(100) utf8_general_ci Non Aucun(e)
4 type_page varchar(100) utf8_general_ci Non Aucun(e)
5 nom_page varchar(100) utf8_general_ci Non Aucun(e)
6 valeur_contenu int(11) Non Aucun(e)
7 ordre int(11) Non Aucun(e)

Here is the structure of my table 2 (general contents)

1 idPrimaire int(11) Non Aucun(e) AUTO_INCREMENT

2 nom_liste varchar(100) utf8_general_ci Non Aucun(e)
3 nom_contenu varchar(255) utf8_general_ci Non Aucun(e)
4 valeur_contenu text utf8_general_ci Non Aucun(e)
5 type_contenu varchar(100) utf8_general_ci Non Aucun(e)

So what I am looking for is to connect table 1 & 2 in order to be able to call some content from table1

Upvotes: 0

Views: 71

Answers (2)

Alexia Desouza
Alexia Desouza

Reputation: 393

You could try "natural join" if u want them combined based on related column, otherwise "full outer join" which returns all records when there is a match in either table1 or table2.

"inner join" will give you values matching on both the table.

Go through this they have explained everything here: https://www.w3schools.com/sql/sql_join_inner.asp

Upvotes: 0

S_R
S_R

Reputation: 1998

You need to have a foreign key on your second table so you can JOIN them together.

For example

Table 1

1 idPrimaire int(11) Non Aucun(e) AUTO_INCREMENT
2 client varchar(100) utf8_general_ci Non Aucun(e)
3 nom_document varchar(100) utf8_general_ci Non Aucun(e)
4 type_page varchar(100) utf8_general_ci Non Aucun(e)
5 nom_page varchar(100) utf8_general_ci Non Aucun(e)
6 valeur_contenu int(11) Non Aucun(e)
7 ordre int(11) Non Aucun(e)

Table 2

1 idPrimaire int(11) Non Aucun(e) AUTO_INCREMENT
2 client varchar(100) utf8_general_ci Non Aucun(e)
3 nom_document varchar(100) utf8_general_ci Non Aucun(e)
4 type_page varchar(100) utf8_general_ci Non Aucun(e)
5 nom_page varchar(100) utf8_general_ci Non Aucun(e)
6 valeur_contenu int(11) Non Aucun(e)
7 ordre int(11) Non Aucun(e)
8 table_1_id int(11)

Then in SQL you would write

 SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.idPrimaire = table_2.table_1_id

The join doesn't have to be left, it can be whatever join you need. Once these 2 tables are together, you can fetch them in php using an associative array then get the columns you need.

For example

$query = SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.idPrimaire = table_2.table_1_id;
$result = ($connection, $query);
while($row = mysqli_fetch_assoc($result)){
   echo $row['nom_page'];
}

Upvotes: 1

Related Questions