[SOLVED] Sorting articles by section/topic with php and mysql

[K3nnnn]

Im currently learning Php and and mysql using various books, this site, and by disecting free third party scripts. On my test site im using a php page to display the five most recently submited articles, which are saved to a Mysql database. Here is the said code which querys the DB

// Execute the query to retrieve articles
[color=red]$result = $connector->query('SELECT ID,title,tagline,thearticle,datesubmited,section,poster FROM cmsarticles ORDER BY ID DESC LIMIT 0,5');[/color]

note ive left the Php tags out!

using the following to echo it

[color=red]// Get an array containing the results.
// Loop for each item in that array
while ($row = $connector->fetchArray($result)){

echo '<div class="content_header">';
echo '<img src="../img/greyscale_icons/article.gif" width="34" height="26" /> ';

echo '<a class="article_link" href="viewArticle.php?id='.$row['ID'].'">';
echo $row['title'] ;
echo '</a>';

echo ' ';
echo ' ';
echo 'posted under-';

echo '<span class="article_section">';
echo $row['section'] ;
echo '</span>';

echo '</div>';

echo '<span class="tagline">',$row['tagline'];
echo ' <img src="img/right_quote.png" alt="Quote" />';
echo '</span>';
echo '</p>';
echo $row['thearticle'];
echo '<br/><br/>';
echo 'posted by ';
echo '<span class="article_poster">',$row['poster'];
echo '</span>';
echo ' ';
echo $row['datesubmited'] ;[/color]

But i want to have seperate page for each section/topic the article was posted on, doing exactly the same thing (the latest five, decending order) but say i only wanted articles posted under Tutorials shown in last 5 decending order what query would i make to do this.

many thanks

[fenway]

Well, then you'll have to pull all of the sections out first, and then query each one for the latest 5.

[K3nnnn]

guessing this would be the correct way from what you are saying:

[code]// Execute the query to retrieve articles
$result = $connector->query('SELECT section,ID,title,tagline,thearticle,datesubmited,poster FROM cmsarticles ORDER BY ID DESC LIMIT 0,5');[/code]

opposed too

[code]// Execute the query to retrieve articles
$result = $connector->query('SELECT ID,title,tagline,thearticle,datesubmited,section,poster FROM cmsarticles ORDER BY ID DESC LIMIT 0,5');[/code]

am i correct in saying this, or is the code placement wrong, as you can guess im a noob

[fenway]

That only changes the order of the columns in the result set, nothing more.  I'm saying you need to get the list of the possible sections with a separate query first.

[K3nnnn]

This is what ive come up with but im not sure its ok, im not at my test server at the moment so i cannot test it if its not could you give me some more pointers please as im pulling my hair out [code]// Create an object (instance) of the DbConnector
$connector = new DbConnector();

//Execute the query to first retrieve the sections
$section = $connector->query('SELECT section FROM cmsarticles');

// Get an array containing the results of which section.
// Loop for each item in that array
while ($row = $connector->fetchArray($section)){


// Execute the query to retrieve articles
$result = $connector->query('SELECT ID,title,tagline,thearticle,datesubmited,section,poster FROM cmsarticles ORDER

BY ID DESC LIMIT 0,5');


// Get an array containing the results.
// Loop for each item in that array
while ($row = $connector->fetchArray($result)){[/code]

[fenway]

Sure, that would be ok, but you'll want to GROUP BY section.

[K3nnnn]

i feel you have now put me on the right track but when i test it on my server which im now at it throws up the following error


Parse error: parse error, unexpected $end in E:\blaa\bla\blah\Root\tutorials.php on line 79 here are my change to the query[code]
//Execute the query to first retrieve the sections
$section = $connector->query('SELECT section FROM cmsarticles GROUP BY section');

// Get an array containing the results of which section.
// Loop for each item in that array
while ($row = $connector->fetchArray($section)){


// Execute the query to retrieve articles
$result = $connector->query('SELECT ID,title,tagline,thearticle,datesubmited,section,poster FROM cmsarticles ORDER

BY ID DESC LIMIT 0,5');


// Get an array containing the results.
// Loop for each item in that array
while ($row = $connector->fetchArray($result)){[/code] what could the reason be for this apart from im a noob?

[fenway]

Well, you're not sure the section name from the outer query in your inner query... and I don't know which line 79 is.

[K3nnnn]

Thanks for the pointers Fenway ive now got the above problem sorted using the following [code]// Create an object (instance) of the DbConnector
$connector = new DbConnector();

//Execute the query to first retrieve the section names
$result = $connector->query('SELECT ID, title, tagline, thearticle, datesubmited, poster, section FROM cmsarticles WHERE section = "Tutorials"');

// Get an array containing the results of which section.
// Loop for each item in that array
while ($row = $connector->fetchArray($result)){[/code]


As you can see i first made a for query for ID,title,tagline etc...  from my article table where the section equals, which i then created an array of
cheers