Question about breaking up MySQL results

[PartisanEntity]

Hi all,

 

In order to teach myself php I am developing a simple cms system to display my graphic design work. Basically I am working on developing a simple portfolio website powered by a custom made CMS.

 

So far, I can create new posts which are being stored in a MySQL db.

 

My index page runs a sql query to get the latest posts and sorts them by category.

 

Currently the results look like this:

 

Title1

Category A

Text

 

Title2

Category A

Text

 

Title3

Category B

Text

 

Title4

Category B

Text

 

The code to generate this looks like this:

 

$content = mysql_query("SELECT * FROM posts ORDER BY category, date DESC");

while($row = mysql_fetch_array($content))
  {
  echo '<h2 class="post_title">'.$row['title'].'</a></h2>';
  echo '<p class="cat">category: '.$row['category'].'</p>';
  echo '<p class="post_body">'.$row['body'].'</p>';
  echo '<p class="image">image: '.$row['image'].'</p>';
  echo '<p class="lowerlefttext_metainfo">by <span class="lowerlefttext_author">'.$row['author'].'</span> on '.$row['date'].'</p>';
  }

 

What I would like however is this:

 

Category A

 

Title 1

Text

 

Title 2

Text

 

Category B

 

Title 3

Text

 

Title4

Text

 

I am not sure how to do this elegantly without having to code an sql query per category, which I don't think is efficient?

 

Thank you!

[litebearer]

My personal preference...

$content = mysql_query("SELECT * FROM posts ORDER BY category");
while($row = mysql_fetch_array($content)){
$category = $row['category'];
echo $category . "<br>";
$sql2 = "SELECT * FROM posts WHERE category = '$category' ORDER BY date DESC";
$result = mysql_query($sql2);
while($row2 = mysql_fetch_array($result)) {
	echo "    " . $row2['title'] ."<br>";
	echo "    " . $row2['body'] . "<br>";
	echo "    " . $row2['image'] . "<br>";
	echo "    " . $row2['author'] ."  on " .$row2['date'] . "<br><br>";
}
echo "<hr>";
}

[AbraCadaver]

I would probably do it as litebearer has shown, but an easy fix:

 

$content = mysql_query("SELECT * FROM posts ORDER BY category, date DESC");

$category = '';

while($row = mysql_fetch_array($content))
{
  if($category != $row['category'])
  {
    echo '<p class="cat">category: '.$row['category'].'</p>';
    $category = $row['category'];
  }
  echo '<h2 class="post_title">'.$row['title'].'</a></h2>';
  echo '<p class="post_body">'.$row['body'].'</p>';
  echo '<p class="image">image: '.$row['image'].'</p>';
  echo '<p class="lowerlefttext_metainfo">by <span class="lowerlefttext_author">'.$row['author'].'</span> on '.$row['date'].'</p>';
}

[PartisanEntity]

Thanks so much to the both of you, working nicely, and an elegant solution

[Psycho]

Please follow AbraCadaver's example.

 

@litebearer, never do queries within loops such as that. It puts a huge load on the server and will eventually cause timeouts and other problems. Just get all the records in one query and process the results accordingly such as AbraCadaver showed.

[PartisanEntity]

How do I get a separator <hr> into AbraCadaver's code? Or better said at what point would I have to insert it?

[Psycho]

How do I get a separator <hr> into AbraCadaver's code? Or better said at what point would I have to insert it?

 

Um, where do you want it? Assuming you want it after each category, this should work for you.

//Query for the data
$query = "SELECT `title`, `body`, `image`
          FROM `posts`
          ORDER BY `category`, `date` DESC";
$content = mysql_query($query) or die(mysql_error());

$current_category = false;
while($row = mysql_fetch_array($content))
{
    //Check if new categories
    if($current_category != $row['category'])
    {
        //If not first category add horizontal rule
        if($current_category!==false)
        {
            echo "<hr />\n";
        }
        //Set and display current category header
        $current_category = $row['category'];
        echo "<p class=\"cat\">category: {$current_category}</p>\n";
    }
    //Display record
    echo "<h2 class=\"post_title\">{$row['title']}</h2>\n";
    echo "<p class=\"post_body\">{$row['body']}</p>\n";
    echo "<p class=\"image\">image: {$row['image']}</p>\n";
}