There is such a database structure.

Tables:

  • cats_arts - article categories
  • sites - sites where articles should be placed
  • articles - the articles themselves

Fields of the cats_arts table: id , name
Fields table sites : url , id
Fields of the articles table: id , site_id , category(id категории) ...

I need a request to get all the categories used on the site, I got such a request:

 $site = "http://test.com/"; $res = mysql_query("SELECT c.id,c.name FROM cats_arts c LEFT JOIN sites a ON a.url='$site' LEFT JOIN articles b ON b.site_id=a.id WHERE c.id=b.category");

But for some reason, such a request displays several times the same category (Help please ...

PS Is the SQL query correct, or is it still MySQL?

  • Yes html does not work here)) I have already seen 3 times for you <br> put two transfers - Sh4dow
  • I remembered, I need 2 spaces) - iproger
  • one
    SELECT DISTINCT ... - Gorets
  • This thing does not exclude the incorrectness of the request, because the load on mysql is more because of this, there will be 2k articles, and instead of two categories it will bring up 2k categories, after that it will exclude unnecessary, but thank you all very much for extreme ) - iproger

1 answer 1

 $site = "http://test.com/"; $res = mysql_query(" SELECT c.id, c.name FROM cats_arts AS c JOIN articles AS a ON (a.category = c.id) JOIN sites AS s ON (s.id = a.site_id) WHERE s.url='$site' ");

If I correctly understood your jumble of letters (such as articles - b, sites - a), then something like this should work. That is, we select categories that are parent for articles that are linked to sites, and select from them those that relate to the site with url = '$ site'.

  • Vseravno instead of 2 categories, displays these 2 three times (The connection is that there are 6 pieces of articles, so for him it is necessary to withdraw 6 items of categories (If there is no way out, then how can I do it through GROUP BY? - iproger
  • @mctrane, so add DISTINCT here and there will be what you need, I think) Or at the end of GROUP BY c.id - Sh4dow