Hello. There is a tovari table, it has an id_company column, this column contains the ID of the companies that sell this product, I write down the ID in this column with an array of 1, 3, 7, then I bring these companies to the page:

$directID = mysql_fetch_assoc(mysql_query("SELECT * FROM `company` WHERE `id` = '".intval($_GET["id"])."' LIMIT 1")); $company_bay_tov = explode(",", $directID['id_company']); $catalog_company = mysql_query("SELECT * FROM company WHERE id IN (" . implode(", ", $company_bay_tov) . ") LIMIT 6"); while ($cc_bay = mysql_fetch_assoc($catalog_company)) { //разметка }

And now I need to display on the company’s page the products it sells. Company ID is defined by the variable $ directID ['id']

What should be the request in my case?

  • one
    In my opinion, 5 minutes ago, there was the same question: ru.stackoverflow.com/questions/532311/… BUT in general, the answer to your "no" question. it is impossible to store id in the relational database separated by commas because it is difficult to write queries, they are not optimizable. SQL is not designed for this. expand the lists in the link tables then all the work with the database will be much easier and with much better speed. - Mike
  • And in your first query, do you have the exact company table, not tovari? It's not entirely clear why there are two requests from the company. - Nikola Tesla

1 answer 1

The most correct option is to revise the database structure. The many-to-many relationship is implemented through an additional tamblice: tov_com_rel (id_tovari, id_company). Your 1,3,7 array turns into three entries in this table. According to this table, the products that the company sells are elementary. And also, companies that sell goods.

But, if the correct option does not fit, then ...

There is nothing to check, I write "blindly," but something like this:

 mysql_query("SELECT * FROM tovary WHERE LOCATE(CONCAT(',','".$directID['id']."',','), CONCAT(',',id_company,',')) > 0");

that is, we add commas to the required id along the edges and look for a substring in the id_company list, to which commas are also added.

  • In MySQL, you cannot add strings with the + operator, only with the concat function. Yes, and find_in_set is intended for just such cases, about which it is written in the answer to the second similar question, the link to which is in the comments above. I didn’t start to close this one because I’m waiting for the author’s reaction, all of a sudden it’s not at all like that :) - Mike
  • @Mike, it means that I apologized for the "blind" answer for nothing :) and initially wrote with concat, and then I was confused by some kind of confusion: (I’ll fix it now. Find_in_set does exactly the same thing. The search for a substring is less tied to the SQL dialect, and allows you to use any separators - Nikola Tesla
  • LOCATE can be replaced with another more popular INSTR and exchange arguments with places. But all this, of course, does not apply to this particular task. - Nikola Tesla