[SOLVED] Show one result per category

[cheeseus]

Hello guys,

 

I am trying to display one (or two) result(s) from each of the dozen categories I have in the same table. I am afraid this is beyond my little knowledge of PHP and Mysql.

I wrote this:

$articles = mysql_query("SELECT art_title, section FROM az_articles WHERE issue = '$myrow[issue]' ORDER BY section DESC LIMIT 2",$connect);
		   while($row = mysql_fetch_array($articles))
		   {		   				   
		   echo "<table><tr><td>";
		   echo $row['art_title'];
		   echo " || ";
		   echo $row['section'];
		   echo "</td><tr></table>";
		   }

 

This is to display articles from the selected issue only. I realise I have to check how many categories there are (listed in a separate table, az_sections) and then from the original table, select one result per category and echo it.

I tried several things but none seemed to be getting anywhere near

I am not just looking for a piece of code, I'd like to understand how and why it's done.

Thanks for any help!

[marmite]

I'm not clear what you're trying to do. Do you want to pick a few items from the database at RANDOM?

[Fearpig]

Sorry this is really rough and will need some work....

$Issue = 45;
//or whatever issue you want to look at
$Records_Returned = 1;
//or however many records you want per category

SELECT * FROM tbl_Category 
while (result){ 

     $Category_ID=odbc_result($result,"Category_ID");
     $Category_Title=odbc_result($result,"Category_Title");

     echo "$Category_Title";

     SELECT * FROM tbl_article WHERE Issue = $Issue AND Category_ID = $Category_ID LIMIT '$Records_Returned'
     while (result2){ 

          $Article_Title=odbc_result($result,"Article_Title");

          echo "$Article_Title";
          }

}

 

Sorry... you'll really have to work on the syntax!!

Basically it pulls all of the categorys from the category table and under each category it lists articles which match that category. This won't pull random articles, just the top few depending on how they are listed in the database, but it shows you the basics.

[cheeseus]

Using your structure, I wrote this:

<?
$records_returned = 2;
$featured = mysql_query("SELECT * FROM az_sections",$connect);
while($arow = mysql_fetch_array($featured)) {
$section_id = odbc_result($featured,"section_id");
$section = odbc_result($featured,"section");

echo "$section"; }

$featured2 = mysql_query("SELECT * FROM az_articles WHERE issue = '$myrow[issue]' AND section_id = $section_id LIMIT '$records_returned' ");
while($aarow = mysql_fetch_array($featured2)) { 
$art_title = odbc_result($featured2,"art_title");

        echo "$art_title";
}

?>

 

Unfortunately, all I get is: "Fatal error: call to undefined function: odbc_result() in ...."

 

What I want to do is get one result from each of the various sections (categories) - doesn't matter whether that'll be the last/first/random result for this section (category).

Thanks!

[Fearpig]

Hello....

Off the top of my head I don't think you use odbc_result() when querying a MySQL database unless you have defined an ODBC connection to your database?

 

...and the second loop needs to be inside the first loop:

 

 

While(){

      Category

      while(){

              Article

              }

      }

[cheeseus]

hello, just managed to get it "quite close"

This is what I just tried:

 

<?php
$section_query = mysql_query("SELECT * FROM az_sections ORDER BY section_id ASC",$connect);
$article_query = mysql_query("SELECT * FROM az_articles WHERE section = section ",$connect);

$num = mysql_num_rows($section_query);

mysql_close();

$i=0;
while ($i<$num) { 

$section = mysql_result($section_query,$i,"section");
$article = mysql_result($article_query,$i,"art_title");

echo "<tr><td id=menucell class=nav>  <strong><a href=\"catview.php?section=$section\">$section</a>: $article</strong></td></tr>";

$i++;
}
?>

 

Now, it prints all the categories and then next to each category it prints one article name. So far so good. BUT... it takes a random article, and the article shown is NOT from the category it is shown next to

I guess I have to specify this in the WHERE statement?

[Fearpig]

You only have one loop and nothing that states "this article is from this section"

 

You can get rid of all of the counting rows and increasing a variable each time it makes a pass of the loop. I've just had it working (unfortunately on SQL!) try translating this into MySQL.....

 

<?php
//-----Connect and select everything from the category table-----
$sql_Category_Details="SELECT * FROM tbl_Category";
$Category_Details=odbc_exec($conn,$sql_Category_Details);
if (!$Category_Details)
          {exit("Error in Category SQL");}

while (odbc_fetch_row($Category_Details)) 
{
$Category_ID=odbc_result($Category_Details,"Category_ID");
$Category_Name=odbc_result($Category_Details,"Category_Name");

             //-------Write a Category Title----------------
echo "$Category_Name<br>";

             //-------Select 1 article that has the same category ID as the title we just wrote-----------
             $sql_Article_Details="SELECT * FROM tbl_Article WHERE Category_ID = '$Category_ID' LIMIT '1'";
             $Article_Details=odbc_exec($conn,$sql_Article_Details);
             if (!$Article_Details)
                    {exit("Error in Article SQL");}

             while (odbc_fetch_row($Article_Details)) 
             {
             $Article_Name=odbc_result($Article_Details,"Article_Name");
             
                          //----------Write the name of the article-------------
             echo "$Article_Name<br><br>";
                          }
             }
?>

 

I'm a bit of a noob! so there is probably a lot shorter way to write this but this does work honest guv!

[cheeseus]

Absolutely unbelievable -- I GOT IT! ))

 

Now, I know little about MySQL, and NOTHING about SQL. But I followed the structure you proposed:

First print the category, and then print an article name from that category.

My problem is, I don't know the syntax of programming languages - well, I am starting to get it, but not quite...

THANK YOU and THANK YOU to ALL who are patient enough to help newbies like me!

 

This is the final piece of code:

$result = mysql_query("SELECT * FROM az_issues ORDER BY issueid DESC LIMIT 7",$connect);
            while($myrow = mysql_fetch_array($result))
             {$issue_image = $myrow['issue_image'];
		   echo "<table width=580><tr><td align=left height=1 colspan=2 bgcolor=#000000></td></tr><tr><td class=issue align=left colspan=2><b>";
               echo $myrow['issue'];
		   echo "</b></td></tr><td align=left>";
		   echo '<img src ="'.$issue_image.'" border=1></td>';
		   echo "<td width=420>";
		   
		 $section_query = mysql_query("SELECT * FROM az_sections ORDER BY section_id ASC",$connect);
		 while($row = mysql_fetch_array($section_query))
		 {
		 	echo "<table><tr><td>";
			echo $row['section'];
			echo " :";

		 $article_query = mysql_query("SELECT * FROM az_articles WHERE issue = '$myrow[issue]' AND section = '$row[section]' ORDER BY art_id LIMIT 1",$connect);
		 while($rrow = mysql_fetch_array($article_query))
		 {
			echo $rrow['art_title'];
		 } 

		 	echo "</td></tr></table>";
		 }
		   echo "</td></tr></table>";
		 }