Comparing dates from Mysql and PHP

[rolyestemonio]

Hi, I am a PHP beginner, I am working this code and I need help, And kinda stack of it.
I have a database with 5 fields(id, date, time , title and description). This is what I want to do;
I want my script to go into the database fetch the date and compares dates if both days are equal, it should echo or read the dates as one and the titles and descriptions will be display with one dates that corresponds to the date taken from Mysql but when the dates are not equal it will display another dates. Your help is really highly appreciated guys.. Thanks in advance.

Example :

If dates are similar

November 9, 2010 Updates

Sample 1
This is a description.

Sample 2
This is a description.

Sample 3
This is a description.

But if the dates is not equal it will display another date category

November 8, 2010 Updates

Sample 1
This is a description.

Sample 2
This is a description.

[Caeon]

If I were you I would make the date column based off "UNIX_TIMESTAMP()". It will convert the date into seconds, which will eliminate the need for your "time" field. (Just use date() function to convert to normal text. http://us.php.net/manual/en/function.date.php)

An example: You were going to do a status feed that shows the last 7 days. First make a loop that goes through each of the past 7 days. Within the loop of each day, use SQL to check if there are any entries within those 24 hours and display them.

Functions you should look into: date(), strtotime()

PHP.net is an excellent resource for learning some handy functions.

Hope this helps!

[rolyestemonio]

How we are going to do that?I mean can you give me the sample codes?

[rolyestemonio]

Any help please? What should i do after this codes?

<?php
$result = mysql_query("SELECT * FROM tbl_localnews ORDER by local_date"); 
 while($rows = mysql_fetch_array($result) ){
 //If the dates in database is exist in many times.
//Display  it in one date category.
 if ( $rows > 1 ) { 
 ?>
  <h3 class="update">
  <em>News for <?php print $rows['date']; ?></em><em style="font-size:12px; float:right;">Updated <?php echo $rows['local_time'] ?></em></h3>
 <?php
  // If Not the same Display dates in another category.
  }else{?>
  <h3 class="update">
  <em>News for <?php print $rows['date']; ?></em><em style="font-size:12px; float:right;">Updated <?php echo $rows['local_time'] ?></em></h3>
  <?php 
 }
}
?>

[rolyestemonio]

If I were you I would make the date column based off "UNIX_TIMESTAMP()". It will convert the date into seconds, which will eliminate the need for your "time" field. (Just use date() function to convert to normal text. http://us.php.net/manual/en/function.date.php)

An example: You were going to do a status feed that shows the last 7 days. First make a loop that goes through each of the past 7 days. Within the loop of each day, use SQL to check if there are any entries within those 24 hours and display them.

Functions you should look into: date(), strtotime()

PHP.net is an excellent resource for learning some handy functions.

Hope this helps!

CAn you please give me a sample codes?I really confuse about it.

[Caeon]

See how this works for you.

<?

$today = strtotime("now");
$seven_days = 60*60*24*7; // Number of seconds for 7 days

$news_articles = mysql_query("SELECT * FROM tbl_localnews 
							 WHERE date >= $today-$seven_days 
							 ORDER by local_date");

while ($article = mysql_fetch_array($news_articles))
{
	$this_date = date('j', $feed['date']);
	
	// If the previous article has a different day than this article then place this header
	if ($this_date != $previous_date)
	{
		echo "<h3 class=\"update\">";
		echo 	"<em>News for $article[date]</em>";
		echo 	"<em style=\"font-size:12px; float:right;\">Updated $article[local_time]</em>";
		echo "</h3>";
	}
	
	$previous_date = $this_date;
	
	// Put the details of each article here. 
	echo "<div>$article[title]</div>";
	echo "<div>$article[description]</div>";
}

?>

Have a good one!

[rolyestemonio]

See how this works for you.

<?

$today = strtotime("now");
$seven_days = 60*60*24*7; // Number of seconds for 7 days

$news_articles = mysql_query("SELECT * FROM tbl_localnews 
							 WHERE date >= $today-$seven_days 
							 ORDER by local_date");

while ($article = mysql_fetch_array($news_articles))
{
	$this_date = date('j', $feed['date']);
	
	// If the previous article has a different day than this article then place this header
	if ($this_date != $previous_date)
	{
		echo "<h3 class=\"update\">";
		echo 	"<em>News for $article[date]</em>";
		echo 	"<em style=\"font-size:12px; float:right;\">Updated $article[local_time]</em>";
		echo "</h3>";
	}
	
	$previous_date = $this_date;
	
	// Put the details of each article here. 
	echo "<div>$article[title]</div>";
	echo "<div>$article[description]</div>";
}

?>

Have a good one!

It's not working bro. It display all in one category only.I think there's something wrong.
The previous date that are not equal display also in one category.
By the way using this one "UNIX_TIMESTAMP()" how we are going to diplay the dates only or the time only?

[Caeon]

You display the date using the date() function. http://us.php.net/manual/en/function.date.php

I realized that line 12 should be

$this_date = date('j', $article['date']);

[rolyestemonio]

You display the date using the date() function. http://us.php.net/manual/en/function.date.php

I realized that line 12 should be

$this_date = date('j', $article['date']);

I thinks there is something wrong to the that bro because display like this :

News for November 9, 2010 Updated 09:35:0

Title 1
This is a description
Title 2
This is a description 2
Title 3
This is a description 3
Title 4
This is a description 4
Title 5
This is a description 5
Title 6
This is a description 6
Title 7
This is a description 7
Title 8
This is a description 8

But supposedly it looks like this one :

News for November 9, 2010 Updated 09:35:0

Title 1
This is a description
Title 2
This is a description 2
Title 3
This is a description 3

News for November 8, 2010 Updated 09:35:0

This is a description 4
Title 5
This is a description 5
Title 6
This is a description 6

News for November 7, 2010 Updated 09:35:0

Title 7
This is a description 7
Title 8
This is a description 8

[Caeon]

Make sure that your date column is in the unix_timestamp format.

Change line 6 to:

$sql = "SELECT * FROM tbl_localnews WHERE date >= '$today-$seven_days' ORDER by local_date";
$news_articles = mysql_query($sql);
echo $sql;

Look at what the $sql statement prints out. Make sure that it is executing an integer in the WHERE segment. Maybe try putting single quotes around the '$today-$seven_days'. I am 99% sure it has to do with your sql statement or that you don't have your date column configured in the correct UNIX_TIMESTAMP format.

[rolyestemonio]

Thank you bro. I already solve it. I use this one.

$result = mysql_query("SELECT * FROM tbl_localnews ORDER BY date DESC"); // Newer dates first
$dates = array(); // This is for later...
$num = mysql_numrows($result);
$i = 0;
while($i < $num) {
        $row = mysql_fetch_assoc($result);
        // Format the timestamp to something readable, this one is "January 01, 2010"
        $date = date("M d, Y",$row['date']);
        $time = $row['time'];
        // If this row's date is not already in the dates[] array, add it along with the time it points to:
        if( ! (in_array($date,$dates))) {
                $dates[$date][$i] = $time;
        } else {
        /* If the date is already in there, we'll add this to that same date's key
         * and then create a multidimensional array for all the times that fall under that date */
                $dates[$date][($i+1)] = $time;
        }
        $i++;
}
// Loop through the first keys in the array - dates:
foreach($dates as $date => $value) {
        echo "<h3>" . $date . "</h3>";
        // Loop through the second keys in the array - times:
        foreach($dates[$date] as $date => $time) {
                echo $time . "<br />";
        }
}

Can I ask something?
I have something to know bro, is it possible to combine all the data in one fields and it will display separately?

Example:

I have 3 fields (id, description, link) and i store this in the ff. withe a separator "|":

description fields

This is a description1 | This is a description2 | This is a description3 | This is a description4

link fields

Link1 | Link2 | Link3 | Link4

And it will display like this :

1. This is a description1
Link1

2. This is a description2
Link2

3. This is a description3
Link3

4. This is a description4
Link 4

Thanks in advance to your help bro.

[Caeon]

I don't fully understand your question.

I see how this script saves the time for the event, but I don't see how it stores the rest of the info. But whatever gets you the results you need works.

[rolyestemonio]

Hi Bro,

Can I ask another question? I am really a beginner in PHP.
I have a problem regarding of archiving. How we are going to archives data by month?
I have 4 fields(id, dates, title,description). I want to archives my data by month in which you can see the Month automatically from database MySQL.

Example

Month retrieved from database

News for November
News for October
News for September

when you click each of this month it will directly open a page archives for each month.

Can anyone help me please. I really confuse how to do it. Thanks for advance guys.

[Caeon]

Have the dates column be in Unix timestamp format. Use sql to check if there are any entries between the span of the month.