Checking with Two Tables

0

I'm trying to create a system where I have two tables one call from acompanhamentos and the other produtos .

Within the accompanying table there is the sincroniza column where I write several numbers separated by a comma, and these numbers are related to the column id in the products table.

I need to do a check that takes this value in the sincroniza column and checks between the commas if there is an equal number in the produtos table if it exists it pulls the name of the product and displays the name instead of displaying the number .

So far I have not been able to do anything about it, they told me about such a pivot table but I have no idea how to at least start this, since I do not know this pivot table and I am new to php and to mysql

    
asked by anonymous 26.04.2016 / 17:42

2 answers

2

Suppose:

Products:

  • 01 Sugar
  • 02 Coffee
  • 03 Water
  • 04 Potato

Accompaniments

  • 02,01,03
  • 02,03
  • 03,01,04

Solution

First, you should make an inquiry to return all the data for a purchase in the accompanying table.

Second, put a while so that as long as there are "codes" in sincroniza , the function continues to run and the data played in array . Something like:

$array=explode(",",$dados_da_sincroniza);

Third, Retry query by relating the data of the array created above with the ids of the products table

    
26.04.2016 / 18:44
0

You can use the mysql find_in_set function to return the required result, so you do not have to go through a foreach by php.

SELECT
    *
FROM acompanhamentos a
INNER JOIN produtos p
ON FIND_IN_SET(p.id,a.sincroniza)

and of course adding your where clause if necessary.

    
24.11.2017 / 15:53