MySQL query retrieving all categories

There is such a database structure.

Tables:

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

Fields of table cats_arts: id, name
Fields of the sitestable: url, id< br>
Fields of the articlestable: id, site_id, category(id )

I need a query to retrieve all the categories used on the site, so I got this query:

$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 query displays the same category several times( Please help…

P.S.Correct SQL query, or is it MySQL?


Answer 1, authority 100%

$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 understand your jumble of letters (like articles – b, sites – a), then something like this should work. That is, we select the categories that are the parent of the articles that are linked to sites, and select those that belong to the site with url=’$site’.

Leave a reply

Please enter your comment!
Please enter your name here