Reading query data

[oopster]

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!

[GingerRobot]

What was the point of your table tie? It'd be much easier to add a field called category_id to your posts table...

[oopster]

Hello

 

The idea is, that one post may be in many different categorys

[GingerRobot]

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.

 

 

[phpzone]

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 ...

}

[oopster]

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.