Jump to content

Group By and while loop


cobusbo
Go to solution Solved by Barand,

Recommended Posts

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>";


}

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by cobusbo
Link to comment
Share on other sites

 

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by cobusbo
Link to comment
Share on other sites

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
Link to comment
Share on other sites

  • Solution

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
  • Like 2
Link to comment
Share on other sites

 

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 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.