Insert various data into a mysql column

Asked

Viewed 600 times

0

Good afternoon Guys, I’m cracking my head open about how I make this insert in the bank, it’s kind of like this...

I have a product, which needs to be priced more than one supplier, to compare prices. But my supplier quantity depends on the registered quantity, I don’t have a fixed number.

So far I have two tables, a supplier tabale, where I register the suppliers, and a table products, where I register the products and relate to the supplier table, through the foreign key id_supplier.

Thank you since!.

php html mysql database mysqli

1 answer

1

You’ll need an associative table. Since a product needs to relate to many suppliers and a supplier can have many products.

In this associative table you can have one the columns ID, ID_PROD, ID_FORN, PRECO.

That way, you can solve your problem. For each price offered by each supplier, you can fill out this table with the product ID, supplier ID and the price of that item.

TIP: The item_product table is usually created. In this item, you can even have the amount of product, but I do not know if it is interesting for you at the moment.

  • Andrew Ribeiro, let me see if I understood, in this case, I would create the table item_product, with the fields id, id_prod, id_forn, price. Where id_prod relates to the id field of my products table, and id_forn relates to the id field of my suppliers table. Then I would have to delete my foreign key that I already have in the products table, right?

  • now it looks like this... my item_products table is with id_product relating the product table and id_supplier relating the vendor table. I put a Join Inner to display the data in php, but it’s not working.. 

$query = "SELECT * FROM item_products
INNER JOIN supplierONsupplier id. =item_products.id_supplier
INNER JOIN materialsONmaterials id.=item_products.id_products";
$dados = mysqli_query($con, $query) or die(mysqli_error());
$linha = mysqli_fetch_assoc($dados);
$total = mysqli_num_rows($dados);


  • That’s exactly it. You will delete the foreign key from the product table because you now have an associative table. Dai the select would look something like this: select * from item_produto join produto on produto.id = item_produto.id_produto join fornecedor on fornecedor.id = item_produto.id_fornecedor; Try running this SQL directly in the database console, it should work. If your error persists, create another question specifically about the language, in the PHP case.

Browser other questions tagged php html mysql database mysqli

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