SQL query help [Archive] - ForumsHQ

[Raybdbomb]

I'm working on a SQL (mysql used) query and I'm having some issues constructing it. I've been looking at it for a few hours which is too much time. Hopefully I can get some help on here.



I have 2 tables, one is `sites` and the other `posts`. There are MANY `posts` per `sites`. Here's how they're constructed:



mysql> desc posts;

+-------------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+--------------+------+-----+---------+----------------+

| id | int(11) | | PRI | NULL | auto_increment |

| site_id | int(11) | | | 0 | |

| title | varchar(50) | | | | |

| description | text | | | | |

| content | text | | | | |

| date | int(11) | | | 0 | |

| link | varchar(120) | | | | |

+-------------+--------------+------+-----+---------+----------------+

7 rows in set (0.00 sec)





and





mysql> desc sites;

+-------------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+--------------+------+-----+---------+----------------+

| id | int(11) | | PRI | NULL | auto_increment |

| rss_url | varchar(100) | | | | |

| title | varchar(40) | | | | |

| interval | int(11) | | | 0 | |

| lastupdated | int(11) | | | 0 | |

+-------------+--------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)





so `posts` has a foreign key that leads to the `sites` table.



The results of the query I wish to construct would be as follows:

5 (or some X number specified) posts PER site for every site.



The closest query I can construct is as follows:

SELECT s.rss_url, s.title, p.title AS post_title, p.description

FROM sites s, posts p

WHERE p.site_id = s.id

ORDER BY s.title

LIMIT 0,5



But I'm trying to make it so that there's 5 posts for each site, instead of 5 results total.



Any ideas?

[syntax53]

easiest way off the top of my head would be to do a for..each loop. 2 queries-- first would pull a list of site id's and then loop through each of them. inside the loop would be a query to pull 5 posts.

[Raybdbomb]

yea i've considered doing that, unfortunately that is very inefficient and bad practice.



i would use that as a last resort

[Raybdbomb]

Wargod helped me... we couldn't figure it out in 1 query, but we could with 2 queries and UNIONs...





$query = "SELECT id FROM sites WHERE 1";

$r = $db->get_results($query);

$q = "";

foreach($r as $s){

if($q != "") $q .= " UNION ";

$q .= " (SELECT id, site_id, title, description

FROM posts

WHERE site_id = '".$s->id."'

ORDER BY `date` DESC LIMIT 0,5) " ;

}

[syntax53]

Wargod helped me... we couldn't figure it out in 1 query, but we could with 2 queries and UNIONs...





$query = "SELECT id FROM sites WHERE 1";

$r = $db->get_results($query);

$q = "";

foreach($r as $s){

if($q != "") $q .= " UNION ";

$q .= " (SELECT id, site_id, title, description

FROM posts

WHERE site_id = '".$s->id."'

ORDER BY `date` DESC LIMIT 0,5) " ;

}



that's exactly what i said to do........ i don't think the union function is saving you any overhead.

[Raybdbomb]

but it is, every time you do a new query there's the overhead of connecting.. this is all through one stream