cobusbo Posted September 20, 2015 Share Posted September 20, 2015 Hi I have the following fields in my database table id - int topic - varchar news - varchar What im trying to do is group the topics ex topic1 - 123 topic2 - 987 topic1 - 456 topic2 - 654 I want to show it as Topic 1 * 123 * 456 Topic 2 * 987 * 654 seems like I'm going wrong somewhere... $sql = "SELECT * FROM News GROUP BY topic"; $result1 = mysql_query($sql, $db)or die($sql."<br/><br/>".mysql_error()); while($myrow = mysql_fetch_array($result1)) { print $myrow['topic'] . " - " . $Editnews . "<br>"; print $myrow['news'] . " - " . $Editnews. "<br>"; } Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 20, 2015 Share Posted September 20, 2015 GROUP BY in the query doesn't do that. it consolidates the rows having the same group value into a single row. it's primarily used when you want to use aggregate functions (sum, count, ...) on the data in the group. to do what you are asking, you would order (use ORDER BY topic) the rows in the result set to get the rows for the same topic together, then when you are outputting the data, each time the topic value changes you would close out the previous topic section and start a new topic section. Quote Link to comment Share on other sites More sharing options...
cobusbo Posted September 20, 2015 Author Share Posted September 20, 2015 (edited) GROUP BY in the query doesn't do that. it consolidates the rows having the same group value into a single row. it's primarily used when you want to use aggregate functions (sum, count, ...) on the data in the group. to do what you are asking, you would order (use ORDER BY topic) the rows in the result set to get the rows for the same topic together, then when you are outputting the data, each time the topic value changes you would close out the previous topic section and start a new topic section. I'm getting the output 1 - Edit News 99 - Edit News 1 - Edit News 100 - Edit News 2 - Edit News 1000 - Edit News 2 - Edit News 2000 - Edit News How to show it as 1 * 99 * 100 2 * 1000 * 2000 $sql = "SELECT * FROM News ORDER BY topic"; $result1 = mysql_query($sql, $db)or die($sql."<br/><br/>".mysql_error()); while($myrow = mysql_fetch_array($result1)) { print $myrow['topic'] . " - " . $Editnews . "<br>"; print $myrow['news'] . " - " . $Editnews. "<br>"; } Edited September 20, 2015 by cobusbo Quote Link to comment Share on other sites More sharing options...
ginerjm Posted September 20, 2015 Share Posted September 20, 2015 If 1 and 99 and 1000 and 2000 all are the same field and are NUMERIC (not text) that will be difficult. Quote Link to comment Share on other sites More sharing options...
cobusbo Posted September 20, 2015 Author Share Posted September 20, 2015 If 1 and 99 and 1000 and 2000 all are the same field and are NUMERIC (not text) that will be difficult. I used 1 and 2 as the topics and 99 and 100 as the description of 1(topic) and 1000 and 2000 description of 2(topic) Quote Link to comment Share on other sites More sharing options...
cobusbo Posted September 20, 2015 Author Share Posted September 20, 2015 I'm getting the output How to show it as 1 * 99 * 100 2 * 1000 * 2000 $sql = "SELECT * FROM News ORDER BY topic"; $result1 = mysql_query($sql, $db)or die($sql."<br/><br/>".mysql_error()); while($myrow = mysql_fetch_array($result1)) { print $myrow['topic'] . " - " . $Editnews . "<br>"; print $myrow['news'] . " - " . $Editnews. "<br>"; } Any assistance please? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 20, 2015 Share Posted September 20, 2015 ... then when you are outputting the data, each time the topic value changes you would close out the previous topic section and start a new topic section. prev = ''; while fetch next row if prev != topic output topic prev = topic endif output news endwhile Quote Link to comment Share on other sites More sharing options...
cobusbo Posted September 21, 2015 Author Share Posted September 21, 2015 prev = ''; while fetch next row if prev != topic output topic prev = topic endif output news endwhile Thank you @Barand I got it working $sql = "SELECT * FROM News ORDER BY topic"; if (in_array($testip, $admin)) { echo $addnew . "<br><br>"; $lasttopic = ''; while($myrow = mysql_fetch_array($result1)) { $id = $myrow["id"]; $topic = $myrow["topic"]; if ( $lasttopic != $myrow['topic'] ) { print "<b>" . $myrow['topic'] . "</b> - " . $Edittopic . " | " . $adddescr . " | " . $deletetopic . "<br>"; $lasttopic = $myrow['topic']; } print "<p>* " . $myrow['news'] . " - " . $Editnews . " | " . $delete . "<br></p>"; } Just a question since im ordering it by Topic how will I add Descending order by id to show the latest topic and description at the top? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 21, 2015 Share Posted September 21, 2015 (edited) ORDER BY topic, id DESC or, if you want topics descending too, ORDER BY topic DESC, id DESC Edited September 21, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
cobusbo Posted September 21, 2015 Author Share Posted September 21, 2015 (edited) ORDER BY topic, id DESC or, if you want topics descending too, ORDER BY topic DESC, id DESC The thing is it still order the topics column first, I want to sort the ID column first and then the topics column, but if I change it to ORDER BY id DESC, topic my topics and descriptions isn't grouped no more? Edited September 21, 2015 by cobusbo Quote Link to comment Share on other sites More sharing options...
Barand Posted September 21, 2015 Share Posted September 21, 2015 Exactly. Quote Link to comment Share on other sites More sharing options...
cobusbo Posted September 21, 2015 Author Share Posted September 21, 2015 Exactly. So there isn't a way to sort it by ID? Here Is my current display page... 100%!!! - Edit Topic | Add Descr | DEL * Test description - Edit Descr | Del barand - Edit Topic | Add Descr | DEL * test 2 - Edit Descr | Del * test - Edit Descr | Del Hahaha - Edit Topic | Add Descr | DEL * ???? - Edit Descr | Del jogom - Edit Topic | Add Descr | DEL * naaaaa - Edit Descr | Del moo - Edit Topic | Add Descr | DEL * meee - Edit Descr | Del Test Heading - Edit Topic | Add Descr | DEL * Hmmmm""" - Edit Descr | Del * Test - Edit Descr | Del Database Table Layout ID Topic Description Date 2 Test Heading Test 1442826471 3 100%!!! Test description 1442826546 8 Test Heading Hmmmm""" 1442826530 9 Hahaha ???? 1442827332 11 moo meee 1442842709 12 jogom naaaaa 1442843031 13 barand test 1442845391 14 barand test 2 1442845636 I want it to show like barand - Edit Topic | Add Descr | DEL * test - Edit Descr | Del * test 2 - Edit Descr | Del jogom - Edit Topic | Add Descr | DEL * naaaaa - Edit Descr | Del moo - Edit Topic | Add Descr | DEL * meee - Edit Descr | Del Hahaha - Edit Topic | Add Descr | DEL * ???? - Edit Descr | Del Test Heading - Edit Topic | Add Descr | DEL * Hmmmm""" - Edit Descr | Del * Test - Edit Descr | Del 100%!!! - Edit Topic | Add Descr | DEL * Test description - Edit Descr | Del So basically I want my topics column to be ordered DESC according to ID and my News column ordered ASC Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted September 21, 2015 Solution Share Posted September 21, 2015 I think I see what you want now. You need to find the highest id associated with each topic and sort on that. $sql = "SELECT topic , description , topid FROM news INNER JOIN ( SELECT topic , MAX(id) as topid FROM news GROUP BY topic ) tid USING (topic) ORDER BY topid DESC, id ASC"; $res = $db->query($sql); $prevtopid = ''; while ($row = $res->fetch_assoc()) { if ($prevtopid != $row['topid']) { echo "<b>{$row['topic']}</b><br>"; $prevtopid = $row['topid']; } echo " - {$row['description']}<br>"; } Results barand - test - test 2 jogom - naaaaa moo - meee Hahaha - ???? Test Heading - Test - Hmmmm 100%!!! - Test description 2 Quote Link to comment Share on other sites More sharing options...
cobusbo Posted September 21, 2015 Author Share Posted September 21, 2015 I think I see what you want now. You need to find the highest id associated with each topic and sort on that. $sql = "SELECT topic , description , topid FROM news INNER JOIN ( SELECT topic , MAX(id) as topid FROM news GROUP BY topic ) tid USING (topic) ORDER BY topid DESC, id ASC"; $res = $db->query($sql); $prevtopid = ''; while ($row = $res->fetch_assoc()) { if ($prevtopid != $row['topid']) { echo "<b>{$row['topic']}</b><br>"; $prevtopid = $row['topid']; } echo " - {$row['description']}<br>"; } Results barand - test - test 2 jogom - naaaaa moo - meee Hahaha - ???? Test Heading - Test - Hmmmm 100%!!! - Test description Thank you, I figured another way aswell just now. I added another field in my database named rank(numbers) and add a a new value each time into the field each time I create a topic and changed my display loop to work with the rank instead of the topics. But I'm going to use the above code Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.