Help make a select

Question

When placing an order, I must save her (and the customer’s) data in the database, right? But in doing so, I must also store some customer information in the order table? Since I will want to list on a screen which customer made a certain order.

My tables:

tabela cliente 
-nome
-morada
-datanasc
-sexo
-pais
-cidade
-telemovel
-user
-senha

tabela encomenda
-id
-tamanho
-disponiblidade
-preco 

I have to add a field to my table encomenda, to be able to list orders for a cliente?

Answer 1

Responding briefly, yes you need to add a customer identifier to your table encomenda, this identifier needs to be a coluna table cliente that will never happen again. In this situation we use chaves primárias and usually with auto incremento.

Now want to know more about the relationship between the tables? Take a look at this excellent article from Thiago Belem.

Content taken from blog do Thiago Belem.

Table Relationship in Mysql

The relationship of tables is necessary when we have more than one table with information that can and needs to be crossed, for example: categories and products... Each record in the products table will be linked to a record of the categories table.

Just so you know, there are three levels of relationship: our example will be a 1:N (it is said "one for N" or "one for many") where each category (1) contains one or more products (N)... There is also the 1:1 where each record of a table (1) is linked to one and only one record of another table (1)... And there’s another level of relationship, more complex, which is the N:N where one or more records of a table (N) are related to one or more records of another table (N), that would be the example of two tables "products" and "tags" where a product has several tags and several products belong to a tag.

I will not delve too deeply into the subject... I will only talk about the most common relationship (1:N) and give examples of how to work with them.

For our example today we will use two tables, called "categories" and "products":

CREATE TABLE `categorias` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `nome` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM;
 
CREATE TABLE `produtos` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `categoria_id` INT NOT NULL ,
  `nome` VARCHAR( 255 ) NOT NULL ,
  `preco` DECIMAL( 10,2 ) NOT NULL
) ENGINE = MYISAM;

And let’s enter some data for example:

-- Extraindo dados da tabela `categorias`
INSERT INTO `categorias` VALUES(1, 'Camisetas');
INSERT INTO `categorias` VALUES(2, 'Canecas');
 
-- Extraindo dados da tabela `produtos`
INSERT INTO `produtos` VALUES(1, 1, 'Camiseta Social', 15.00);
INSERT INTO `produtos` VALUES(2, 1, 'Camiseta Regata', 11.99);
INSERT INTO `produtos` VALUES(3, 2, 'Caneca Grande', 12.00);

Note that in the products table we have a "special" column, which is the "categoria_id" (INT)... It is who will help to make the relation of the two tables... In this column will enter the ID of the category to which the product belongs... That is: the two T-shirts belong to the category "T-shirts" (ID 1) and the third product (the Big Mug) belongs to the category "Mugs" (ID 2) and it is in the column "categoria_id" that we store these Ids that identify the categories.

This field responsible for the relationship is usually called foreing key (fk) or "foreign key".

But what is the use of this "relationship"?

Without using the relationship you could take all the products and then get the category information with a second query, like this:

<?php
// Consulta que pega todos os produtos
$sql = "SELECT * FROM `produtos` ORDER BY `nome` ASC";
$query = mysql_query($sql);

while ($produto = mysql_fetch_assoc($query)) {
  // Aqui temos o array $produto com todos os valores do produto

  // Consulta para pegar os dados da categoria:
  $sqlC = "SELECT * FROM `categorias` WHERE `id` = " . $produto['categoria_id'];
  $queryC = mysql_query($sqlC);
  $categoria = mysql_fetch_assoc($queryC);

  echo 'Titulo: ' . $produto['nome'] . '';
  echo 'Preço: ' . $produto['preco'] . '';
  echo 'Categoria: ' . $categoria['nome']. '';
  echo '<hr />';
}

So far so good... there’s no sin in it... But imagine that you have a store with 1000 products (which is not much), would be executed 1 query for all products and within the loop (while) would be executed another 1000 queries to get the name of the category to which the product belongs... That is, 1001 consultations, which is absurd.

The magic of relationship

Now let’s set up a consultation that DE UMA SÓ VEZ will take the data of each product and also the name of the category... With this we will reduce our 1001 queries to... one! No mysteries, no sub-consultations, no consultations within the while()! :D

But before showing the script I will help you understand how the relationship is made... Before our consultation that only picks the products was like this:

SELECT * FROM `produtos` ORDER BY `nome` ASC

Your translation would be: SELECT ALL TABLE COLUMNS produtos ORDAINED BY nome ASCENDENTLY

Now we will use a new Mysql "word" which is JOIN (translation: "unite") and serves to join results from two tables.. ;)

There are three types of JOIN but I won’t talk about the other two because they are VERY little used... Let’s talk about “INNER JOIN” that requires that there is a record that corresponds to the relationship in the two tables, that is: if there is a product without category or the category does not exist in the table categories that product is omitted from the results.

Our consultation will be like this:

SELECT `produtos`.* FROM `produtos`
  INNER JOIN `categorias` ON `produtos`.`categoria_id` = `categorias`.`id`
  ORDER BY `produtos`.`nome` ASC

Your translation would be: SELECT all columns [from the product table] of the TABLE produtos UNITING THE TABLE categorias WHERE THE COLUMN categoria_id [of the product table] is equal to column id [of the category table] ORDERED BY nome [product table] ASCENDINGLY

A nossa “regra de relação” acontece ali entre o ON e o ORDER BY, dizemos que a relação entre as tabelas usará como referencia a coluna “categoria_id” da tabela “produtos” sendo igual a coluna “id” da tabela “categorias”… If you were to use any WHERE it would enter after the ON and before the ORDER BY.

For those who have not yet understood, the ON is like the WHERE of a normal consultation... It is the rule of the relationship.

Note that now we need to use a different format to identify the columns using: tabela.coluna... This is necessary because now we are working with two tables.

As our query is not yet taking the name of the category... we do this by adding another field in the SELECT part, so:

SELECT `produtos`.*, `categorias`.`nome` FROM `produtos`
  INNER JOIN `categorias` ON `produtos`.`categoria_id` = `categorias`.`id`
  ORDER BY `produtos`.`nome` ASC

Now we’re also taking the column value "nome" of the record found (by the list) in the table "categorias".

Only now we have a new problem... In the two tables there is a column called "name", and when we are there in PHP, inside while, we would have no way to identify from which table we take the information (see the next image), because the two would be $produto['nome']... We then need to rename this new field that we added to the search, so:

SELECT `produtos`.*, `categorias`.`nome` AS categoria FROM `produtos`
  INNER JOIN `categorias` ON `produtos`.`categoria_id` = `categorias`.`id`
  ORDER BY `produtos`.`nome` ASC

Now the result of categorias.nome will be present in the results as "category" and not "name"... I know it seems complicated at first but you will understand now.

And finally, we’ll make one more modification, to avoid using tabela.coluna we can also rename the tables, and thereby decrease the size of the query:

SELECT p.*, c.`nome` AS categoria FROM `produtos` AS p
  INNER JOIN `categorias` AS c ON p.`categoria_id` = c.`id`
  ORDER BY p.`nome` ASC

In that case p will represent the table “produtos” and c will represent the “categorias”.

I know it sounds like a bigger, more complicated... But you will make Mysql work much less if you do so, with JOINS, than do a second query inside while... This is the most correct way to query when we need information from more than one table. Now let’s go to our new PHP script which, no doubt, is much more practical and efficient:

<?php
// Consulta que pega todos os produtos e o nome da categoria de cada um
$sql = "SELECT p.*, c.`nome` AS categoria FROM `produtos` AS p INNER JOIN `categorias` AS c ON p.`categoria_id` = c.`id` ORDER BY p.`nome` ASC";
$query = mysql_query($sql);

while ($produto = mysql_fetch_assoc($query)) {
  // Aqui temos o array $produto com todos os dados encontrados
  echo 'Titulo: ' . $produto['nome'] . '';
  echo 'Preço: ' . $produto['preco'] . '';
  echo 'Categoria: ' . $produto['categoria']. '';
  echo '<hr />';
}

The other types of Joins

There are also two other types of JOIN: LEFT JOIN and the RIGHT JOIN:

If we used the LEFT JOIN all products would be returned, regardless of whether they are linked to an existing category (in the categories table) or not.

Already the RIGHT JOIN would be exactly the opposite: would be returned all products belonging to existing categories and also the name of the other categories that has no link with any product.

The use of these other types of JOIN is very rare and I don’t think it’s worth philosophizing about them while learning about relationships.

And the relationship with more than two tables?

Just for example, this would be the query that takes the products, the categories and the name of the user who registered the product and filtering only by the active products:

SELECT p.*, c.`nome` AS categoria, u.`nome` AS usuario FROM `produtos` AS p
  INNER JOIN `categorias` AS c ON p.`categoria_id` = c.`id`
  INNER JOIN `usuarios` AS u ON p.`usuario_id` = u.`id`
  WHERE (p.`ativo` = 1) ORDER BY p.`nome` ASC

Source: http://blog.thiagobelem.net/relacionamento-de-tabelas-no-mysql/

Answer 2

In your case we have a relationship N to N between the tables. The ideal would be to create an intermediate table that would tie the two, which would contain the primary keys of the related lines. That’s what you usually do when you have this kind of situation.

An example:

    CREATE TABLE CLIENTE(

        ID INT AUTO_INCREMENT,
        NOME VARCHAR(30),
        CONSTRAINT CNS_PK_CLIENTE PRIMARY KEY(ID)

    );

    CREATE TABLE COMPRA(

        ID INT AUTO_INCREMENT,
        VALOR DOUBLE(8,2),
        CONSTRAINT CNS_PK_COMPRA PRIMARY KEY(ID)

    );

    CREATE TABLE CLIENTE_COMPRA(

        DATA_COMPRA DATETIME NOT NULL,
        ID_CLIENTE INT,
        ID_COMPRA INT,
        CONSTRAINT CNS_FK_CLIENTE FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTE(ID),
        CONSTRAINT CNS_FK_COMPRA FOREIGN KEY(ID_COMPRA) REFERENCES COMPRA(ID),
        CONSTRAINT CNS_PK_CLIENTE_COMPRA PRIMARY KEY(ID_CLIENTE, ID_COMPRA)

    );