oopster Posted May 21, 2008 Share Posted May 21, 2008 Hello A little problem with getting correct results, this is a bit of a MySQL question. I have 3 tabels, and a query: -- table structure CREATE TABLE tie( id_tie int NOT NULL auto_increment, post_id int NOT NULL, category_id int NOT NULL, PRIMARY KEY (id_tie) ) TYPE=INNODB; CREATE TABLE categorys ( id_category int(3) NOT NULL auto_increment, description varchar(50) NOT NULL, PRIMARY KEY (id_category) ) TYPE=INNODB; CREATE TABLE posts( id_post int(3) NOT NULL auto_increment, title varchar(30) NOT NULL, content text NOT NULL, PRIMARY KEY (id_post) ) TYPE=INNODB; -- query : to get all the categorys and under them 10 latest posts under that category SELECT categorys.description,tie.category_id,tie.post_id FROM tie,categorys, (SELECT * FROM posts LIMIT 10) as nl WHERE categorys.id_category = tie.category_id AND nl.id_post = tie.post_id The thing is, that selecting from posts will not give me post.title, and i would like to get that aswell. What should i add to this query ? Mezise showed the same thing with join, real complex stuff, but for me it's overkill, cos i try to keep it simple - so that me myself could understand . http://www.phpfreaks.com/forums/index.php/topic,197293.0.html And the reading part should be something like this <?php $intLastCategory = ''; $strOut = ''; $r = mysql_query('SELECT categorys.description,tie.category_id,tie.post_id FROM tie,categorys, (SELECT * FROM posts LIMIT 10) as nl WHERE categorys.id_category = tie.category_id AND nl.id_post = tie.post_id'); while ($strItems= mysql_fetch_array($r, MYSQL_ASSOC)) { if($intLastCategory !== $strItems['id_category']) { $intLastCategory = $strItems['id_category']; $strOut .= '<tr><th><b>'.$strItems['description'].'</b></th></tr>'; // category header } $strOut .= '<tr><td><a href="?view='.'.$strItems['id_post'].'.'">'.$strItems['title'].'</a></td></tr>'; // all posts under category } ?> Thank you! Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 21, 2008 Share Posted May 21, 2008 What was the point of your table tie? It'd be much easier to add a field called category_id to your posts table... Quote Link to comment Share on other sites More sharing options...
oopster Posted May 21, 2008 Author Share Posted May 21, 2008 Hello The idea is, that one post may be in many different categorys Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 21, 2008 Share Posted May 21, 2008 Hello The idea is, that one post may be in many different categorys Of course, yes. Not entirely sure why I didn't realise. Can't think of an easy way off the top of my head, but ill have a think. Quote Link to comment Share on other sites More sharing options...
phpzone Posted May 21, 2008 Share Posted May 21, 2008 To keep it simple, why not just first look up the categories with one query: SELECT * FROM categories Get this into a mysql result eg. $result with mysql_query, Iterate over them, selecting the last 10 posts and printing out while ( mysql_fetch_array( $result, MYSQL_ASSOC ) ) { $id_category = (int)$result['id_category']; $sql = "SELECT * FROM tie INNER JOIN posts ON id_post = post_id WHERE tie.category_id = $id_category ORDER BY id_post DESC LIMIT 10"; // ... iterate over results here printing post details ... } Quote Link to comment Share on other sites More sharing options...
oopster Posted May 21, 2008 Author Share Posted May 21, 2008 Hello That's the simplest of the simple. I could do that, but I would really like to do it with one query. It's a question of principle I'm sure there's just a small add to the query, but I'm not sure what. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.