GROUP by question

seby

I have a mysql query and the results are being grouped by the time (in unix timestamp).

say there are three results (rows) under the same group, i'd like to add an some text just to 1 of those rows, but since its in a while loop the text get added to all 3 results... i can't figure it out and it shouldn't be too complicated.

any help?
Thanx.

batman

Post the code that you are using.

leatherback

Hi,

Could you be a bit mor specific:

Does the text have to be added to a database/file?
Does the text have to be added to a page-output?

You could select directly from the database:

Select .. from table WHERE ID=SomeIdentifier group by GroupingColum

OR you can do it in your while loop:

while (  $row =@ mysql_fetch_array($result))

  {

  if ($row[identifier] = "criterium")
    {
    // Add text now
     }
  }

seby

Here's the situation.
I want to group the articles by day. And the query is grouping them fine. But I want to add a Date Just to one of those articles that are in the same date...

$result = $db->sql("SELECT * FROM $news GROUP BY time DESC LIMIT $_GET[page], $pagelimit");
 while($row = mysql_fetch_row($result)) {

echo "$row[title] <br/> Written by $row[author] <br/> $row[text]";

}

say in that query there are 3 rows with the same date (day) i want to add "News for $date" on 1 of them, not all 3.

Hope that makes sense.

leatherback

Something like:


$result = $db->sql("SELECT * FROM $news GROUP BY time DESC LIMIT $_GET[page], $pagelimit"); 
$displayeddate = 0;
 while($row = mysql_fetch_row($result)) { 


  if ($displayeddate == 0)
    {
    echo "News for $row[date]";
    $displayeddate = 1;
    }

echo "$row[title] <br/> Written by $row[author] <br/> $row[text]"; 

}

seby

nope. its not adding the "News for $date" on 1 of the each groups.

example, i want it to look like this:

News for $date:
- group
- group
- group

and right now its doing it like this:

News for $date:
- group
News for $date:
- group
News for $date:
- group

seby

anyone have any other suggestions? this is making my eyes sore 🙁

HalfaBee

How do you know which row to change?

I think that is what is missing.

HalfaBee

seby

I am trying to sort my articles like seen on this site..

notice all articles that are posted on the same day are together under one date.

that is the problem, i dont know how to change just one ROW if it was posted on the same day as the other articles. and because its in a while loop it echos the date on all rows.

another example:

Monday's Articles:
all rows that are posted on monday.....

Tuesday's Articles:
all rows that are posted on tueday ...

etc, etc..

HalfaBee

You will have to put a WHERE clause in that defines the row you wish to change.

$result = $db->sql("SELECT * FROM $news WHERE somerow='some identifying item' GROUP BY time DESC LIMIT $_GET[page], $pagelimit");

The you should only get the row you want to change.

HalfaBee

seby

i figured it out.. leatherback was right on track, and John Holmes from php list pointed me in the right direction.. with some modifications:


$result = $db->sql("SELECT * FROM $news GROUP BY time DESC LIMIT $_GET[page], $pagelimit"); 
 while($row = mysql_fetch_array($result)) { 

$content[timedate] = date("l",$row['time']);

   $newsfor = "";
   if($content[timedate] != $old_dayofweek)
   {
     $newsfor = "<span class=\"newsdate\">News for $content['datefor']</span> <br/>";
     $old_dayofweek = $content[timedate];
   } 

echo "$newsfor $row[title] <br/> Written by $row[author] <br/> $row[text]"; 

}

batman

i figured it out

THen mark it as resolved. 🙂

seby

Originally posted by batman
THen mark it as resolved. 🙂

i did, the feature must be offline because of the Holiday because its not working 😃