Verification With Two Tables

Asked

Viewed 87 times

0

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

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

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

I so far could not do anything about it, I was told about such a pivot table but I have no idea how to at least start it, since I do not know this pivot table and I am new with php and mysql

php mysql
  • 1

    Friend, the implementation made by you is a little 'complex'. To do what you want it will take a select in this column synchronizes and then treat these values outside of SQL, it will take work. The best you can do is: in the table accompaniments you create a column that will contain only 1 ID of the products table, then a select using INNER JOIN already solves what you want.

  • 1

    You can check the concept described by me at this link http://goo.gl/ezrwLk

  • In this case since it gets very complex, there’s a way I can make each id stay in a different column, like synchroniz1, synchroniz2, synchroniz3, synchroniz4 and so on, remembering that each time you add a new id it should also add a new column, has to do so?

  • 1

    Let me give you an example of what I said: http://pastebin.com/MBVxW7aQ

  • This example of yours is if it is just an id right? instead of several

  • 1

    This is the right way to implement what you want.

  • this will work if the prod_id field in the accompanying table is varchar?

  • 1

    A numerical data search is faster than a string search. The prod_id field has to have the same data type in both tables, because it is a ID (single data) it is advisable to use integer type. I do not advise you to change the data type of the prod_id

  • But even if I’m whole, can I put a comma in it? because it will separate the numbers by comma and in the implementation of new numbers I would use, "UPDATE xxx SET sync = CONCAT(sync, ",", new_number)"

  • 1

    He can’t, 'cause he’s the whole guy.

  • Is there any way I can do it the way I told you there? Create several columns? because if there is only one number, the system does not work, what I really need is that it has several numbers

  • 1

    To do as you said you first make a select in the table accompaniments, then with this data you can use the function explode() using the ',' delimiter and then treating the array and checking 1 to 1 if it is equal to the product ID you have.

Show 7 more comments

2 answers

2


Suppose:

Products:

  • 01 sugar
  • 02 Café
  • 03 Water
  • 04 Potato

Side dishes

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

Solution

First, you should make a query to return all the data of a purchase in the accompanying table.

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

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

Third, Make a new query relating the data of array created above with the ids of the products table

  • Have to explain a little better I did not understand the idea of while, and as for the query you, just do a basic select right?

  • @Alfredolima while is for the function to go through all the data in the column synchronizes in the record you want. If you run without it, only the first product will be shown

  • @Alfredolima The first select yes. Only him and the condition. The third step requires a relation, which would be accompaniments.id=products.id

  • Can you give me an example by doing favor of how I do that while?

0

You can use the mysql find_in_set function to already return the required result, so you don’t need to browse 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.

Browser other questions tagged php mysql

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