Association of categories in PHP and MYSQL

Asked

Viewed 240 times

-4

I am developing a site that has items, an item can have more than one category, how do I link these categories with this single product?

mysql

  • You will need 3 tables, one for items, another for categories and the third will have the item id and category id fields, then just do an Inner joins to get all the information.

2 answers

-2


You should do a 1-to-N relationship, you should use this when a table needs to relate to multiple records from another table.

You will have the item table and the category table and one more table that you can call categoria_item. In it you will relate the other two tables.

Example of the categoria_item table:

categoria_item
- id_item
- id_categoria

  • understood, but as I will apply this in PHP, I am actually starting and I would also like to know how I take the categories from the bank and put in checkbox forms, so when I select the categories to which the item belongs, already send to the bank the changes.

  • 1

    This is simple friend, when you go to save the item you will send all the categories that the guy selected and with this you will make a loop and each iteration of the loop you will save the data in the table categoria_item.

-2

From what I can understand, you want to make a query between the tables correct!? Following this reasoning you can try the next:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Cria uma conexão
$conn = new mysqli($servername, $username, $password, $dbname);
// Verifica a conexão
if ($conn->connect_error) {
    die("Erro de conexão: " . $conn->connect_error);
} 


// Monta o comando SQL para fazer a consulta
$sql = "SELECT i.nome, i.preco, c.nome as nomecategoria FROM item i INNER JOIN categoria c ON c.id = i.id_categoria";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Saída dos dados a cada registro
    while($row = $result->fetch_assoc()) {
        echo "Item nome: " . $row["nome"]. " - preço: " . $row["preco"]. " - Categoria: " . $row["nomecategoria"]. "<br>";
    }
} else {
    echo "Sem resultados.";
}

// Encerra a conexão
$conn->close();
?>

I hope I’ve helped!
Reference: http://www.w3schools.com/php/php_mysql_select.asp

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.