Sort by date

[cordoprod]

Hi. I want to create a feed.

I know how to list all entries from a DB, but the date will be a bit corny.

 

Now its like this:

 

1 july

-------------

entry

 

1. july

--------

entry

 

2. july

------

entry

 

What i want to do is to join those who have the same date. Like the to 1 july to be like this:

 

1.july

----------

entry

entry

[Wuhtzu]

Have a look at this topic from yesterday:

 

http://www.phpfreaks.com/forums/index.php/topic,206449.0/topicseen.html

[cordoprod]

Have a look at this topic from yesterday:

 

http://www.phpfreaks.com/forums/index.php/topic,206449.0/topicseen.html

 

How exactly should i do that? There was something about links there? not to join them?

[cordoprod]

Ive been playin around with the code a bit... take a look.. see if you can help

 

<?
          $db = new DbConnector();
	  $db->connect();
	  
	  $sql = "SELECT * FROM travek_feed ORDER BY time DESC";
	  $result = $db->query($sql);
	  
	  $day = "";
	  
	  while($fetch = mysql_fetch_array($result)) {
	      if($day == "" and $current_day == "") $day = norsk_dato($fetch['time'], false);
		  elseif($day == $current_day)  $day = ""; ?>

            <table width="100%" border="0" cellspacing="0" cellpadding="2">
              <tr>
              <td><? echo $day; ?></td>
                <td><? echo date("H:i", $fetch['time']); ?></td>
                <td><? echo "<a href=\"?s=user&nick={$fetch['nick']}\">".$fetch['nick']."</a> ".$fetch['action']; ?></td>
              </tr>
            </table>
            
          <?
	  $current_day = norsk_dato($fetch['time'], false);
	  }
	  
	  ?>

 

Note that norsk_dato is a function to output this format in norwegian: Sunday 20. july 2008

 

Output:

Søndag 20. juli 2008  13:59  demo lastet opp videoen Roy Abelseth (gone too soon).

17:00 glenn blabla

23:14 hei iiejijji

23:14 glenn glenn ble venn med cairoli

 

It only outputs the first..

[cordoprod]

I think am getting closer to it now.. just a few bugs..

 

heres the code:

<?
          $db = new DbConnector();
	  $db->connect();
	  
	  $sql = "SELECT * FROM travek_feed ORDER BY time DESC";
	  $result = $db->query($sql);
	  
	  $day = "";
	  
	  echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">";
	  while($fetch = mysql_fetch_array($result)) {
	      if($day == "" and $current_day == "") $day = norsk_dato($fetch['time'], false);
		  elseif($day != $current_day)  $day = norsk_dato($fetch['time'], false); 
		  else $day = "";
		  ?>

            
              <? if($day != "") { echo "<tr><td colspan=2 style=\"border-bottom: 1px solid #000000;\"><br>$day</td></tr>"; } ?>
              <tr>
                <td><? echo date("H:i", $fetch['time']); ?></td>
                <td><? echo "<a href=\"?s=user&nick={$fetch['nick']}\">".$fetch['nick']."</a> ".$fetch['action']; ?></td>
              </tr>
          <?
	  $current_day = norsk_dato($fetch['time'], false);
	  }
	  
	  ?></table>

 

What's in the table: (id | nick | action | time)

1      glenn  glenn ble venn med cairoli                                      1211750067

2 hei iiejijji                                                                          1211750069

3 glenn blabla                                                                 1211814009

4 demo lastet opp videoen <a href="?s=video&nick=demo&fol... 1216555164

5 cordoprod har blitt kul.                                                         1211814009

 

Output:

Søndag 20. juli 2008

13:59 demo lastet opp videoen Roy Abelseth (gone too soon).

17:00 glenn blabla

 

Mandag 26. mai 2008

17:00 cordoprod har blitt kul.

23:14 hei iiejijji

 

Søndag 25. mai 2008

23:14 glenn glenn ble venn med cairoli

 

 

 

Some of them is not included and some of them does not match the date...

[cordoprod]

Now i saw that all 5 rows were there.. just some of them didn't match the day

[cordoprod]

Any ideas?

[cordoprod]

Do i need group by in my query?

[Barand]

You might find it easier to pull just the date part as a separate column

 

SELECT *, DATE(FROM_UNIXTIME(time)) as datepart ...

 

then (pseudocode)

lastdate=''
while fetch next row
   if datepart != lastdate
        output date
        lastdate = datepart
   end if
   output detail line
end while

[cordoprod]

You might find it easier to pull just the date part as a separate column

 

SELECT *, DATE(FROM_UNIXTIME(time)) as datepart ...

 

then (pseudocode)

lastdate=''
while fetch next row
   if datepart != lastdate
        output date
        lastdate = datepart
   end if
   output detail line
end while

 

What do you mean? Can you show a bit more detailed?

[cordoprod]

And what kind of coding is that?  ???

[cordoprod]

I think i'm on the wrong track here but something like this?

 

<?
          $db = new DbConnector();
	  $db->connect();
	  
	  $sql = "SELECT *, DATE(FROM_UNIXTIME(time)) as datepart FROM travek_feed";
	  $result = $db->query($sql);
	  
	  
	  $lastdate='';
	  while($fetch = mysql_fetch_array($result)) {
	     if ($datepart != $lastdate) { $datepart = norsk_dato($fetch['time'], false); $lastdate = $datepart; }
		  ?>

            <table width="100%" border="0" cellspacing="0" cellpadding="2">
              <? echo "<tr><td style=\"border-bottom: 1px solid #000000;\">$datepart</td></tr>"; ?>
              <tr>
                <td><? echo date("H:i", $fetch['time']); ?></td>
                <td><? echo "<a href=\"?s=user&nick={$fetch['nick']}\">".$fetch['nick']."</a> ".$fetch['action']; ?></td>
              </tr>
            </table>
            
          <?
	  }
	  
	  ?>

[cordoprod]

you know?

[cordoprod]

You might find it easier to pull just the date part as a separate column

 

SELECT *, DATE(FROM_UNIXTIME(time)) as datepart ...

 

then (pseudocode)

lastdate=''
while fetch next row
   if datepart != lastdate
        output date
        lastdate = datepart
   end if
   output detail line
end while

 

Should i use that code? I mean that isn't php? right?

[Bendude14]

When he said pesudo code he might he is just telling you what you have to do... and how to do it.

 

Try and look up the php to put this into practice

[cordoprod]

I think i'm on the wrong track here but something like this?

 

<?
          $db = new DbConnector();
	  $db->connect();
	  
	  $sql = "SELECT *, DATE(FROM_UNIXTIME(time)) as datepart FROM travek_feed";
	  $result = $db->query($sql);
	  
	  
	  $lastdate='';
	  while($fetch = mysql_fetch_array($result)) {
	     if ($datepart != $lastdate) { $datepart = norsk_dato($fetch['time'], false); $lastdate = $datepart; }
		  ?>

            <table width="100%" border="0" cellspacing="0" cellpadding="2">
              <? echo "<tr><td style=\"border-bottom: 1px solid #000000;\">$datepart</td></tr>"; ?>
              <tr>
                <td><? echo date("H:i", $fetch['time']); ?></td>
                <td><? echo "<a href=\"?s=user&nick={$fetch['nick']}\">".$fetch['nick']."</a> ".$fetch['action']; ?></td>
              </tr>
            </table>
            
          <?
	  }
	  
	  ?>

 

I tried something here. no success

[Bendude14]

What errors are you getting?

 

$sql = "SELECT *, DATE(FROM_UNIXTIME(time)) as datepart FROM travek_feed";

 

i do not think you need the comma after *

[cordoprod]

What errors are you getting?

 

$sql = "SELECT *, DATE(FROM_UNIXTIME(time)) as datepart FROM travek_feed";

 

i do not think you need the comma after *

 

Output:

23:14  glenn glenn ble venn med cairoli

23:14 hei iiejijji

17:00 glenn blabla

13:59 demo lastet opp videoen Roy Abelseth (gone too soon).

17:00 cordoprod har blitt kul.

 

It does not join the days, and the $datepart is empty.