PartisanEntity Posted October 26, 2010 Share Posted October 26, 2010 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! Link to comment https://forums.phpfreaks.com/topic/216875-question-about-breaking-up-mysql-results/ Share on other sites More sharing options...
litebearer Posted October 26, 2010 Share Posted October 26, 2010 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>"; } Link to comment https://forums.phpfreaks.com/topic/216875-question-about-breaking-up-mysql-results/#findComment-1126678 Share on other sites More sharing options...
AbraCadaver Posted October 26, 2010 Share Posted October 26, 2010 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>'; } Link to comment https://forums.phpfreaks.com/topic/216875-question-about-breaking-up-mysql-results/#findComment-1126689 Share on other sites More sharing options...
PartisanEntity Posted October 26, 2010 Author Share Posted October 26, 2010 Thanks so much to the both of you, working nicely, and an elegant solution Link to comment https://forums.phpfreaks.com/topic/216875-question-about-breaking-up-mysql-results/#findComment-1126742 Share on other sites More sharing options...
Psycho Posted October 26, 2010 Share Posted October 26, 2010 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. Link to comment https://forums.phpfreaks.com/topic/216875-question-about-breaking-up-mysql-results/#findComment-1126747 Share on other sites More sharing options...
PartisanEntity Posted October 26, 2010 Author Share Posted October 26, 2010 How do I get a separator <hr> into AbraCadaver's code? Or better said at what point would I have to insert it? Link to comment https://forums.phpfreaks.com/topic/216875-question-about-breaking-up-mysql-results/#findComment-1126753 Share on other sites More sharing options...
Psycho Posted October 26, 2010 Share Posted October 26, 2010 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"; } Link to comment https://forums.phpfreaks.com/topic/216875-question-about-breaking-up-mysql-results/#findComment-1126774 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.