PHP querying MySQL returning Multiple Rows with only one Date

Question

I always get confused when working with dates in PHP and MySQL. I looked through other posts, but couldn't find a solution,

I have a simple blog stored in a database that echos records when queried. I get multiple rows with multiple entries, but the date is the same on all of them.

The dates are stored with MySQL timestamp in a column called 'TimeStamp' and are formatted this way in the database: 2011-12-29 21:16:55 (for example).

Here's the query:

$set_select_sql = "SELECT * 
    FROM  `table` 
    ORDER BY  `table`.`TimeStamp` DESC 
    LIMIT 0 , 30";
$set_query = mysql_query($set_select_sql);
$HowMany=mysql_num_rows($set_query);
$time = date("g:i a", strtotime(mysql_result($set_query,0,'TimeStamp')));
$date = date("F j, Y ", strtotime(mysql_result($set_query,0,'TimeStamp')));
$blogpost = mysql_result($set_query,0,'blogpost');

mysql_data_seek($set_query, 0);
$r = 0;
while ($r <= $HowMany) {
    $row = mysql_fetch_assoc($set_query);
    if ($row == FALSE) {
    } else {
        extract($row);

        echo "It was $time on $date when I posted this.<br/>";
        echo "$blogpost"
    }
    $r++

Why am I only getting one date? What am I missing?

Thanks!

You are setting your time and date variables to the values of the first row. Then you loop through your records but you are not updating the time and date variables in your loop. — Jared
– Jared, Commented Dec 30, 2011 at 14:53
You can replace your if($row == FALSE){} with if($row !== FALSE) so you don't have to put your logic in an else — jprofitt
– jprofitt, Commented Dec 30, 2011 at 14:54

Jules · Accepted Answer · 2011-12-30 14:53:01Z

2

It is because you output $time and $date there which were NOT fetched within the loop of your rows, but right before it.

The solution would be to fetch these variables within the loop as:

while ($row = mysql_fetch_assoc($set_query)) {
   $time = date("g:i a", strtotime($row['TimeStamp']));
   $date = date("F j, Y ", strtotime($row['TimeStamp']));
   $post = $row['blogpost'];

   echo "It was " . $time . " on " . $date . " that I posted this. <br />";
   echo $post;
}

answered Dec 30, 2011 at 14:53

Jules

7,2236 gold badges28 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mckinselberg Over a year ago

Thanks for all the help. I knew it was something simple.

jeroen · Accepted Answer · 2011-12-30 14:52:39Z

0

You are only getting the date of the first row found with this line:

$date = date("F j, Y ", strtotime(mysql_result($set_query,0,'TimeStamp')));

To get the date for every row, you need to get the date in the while loop and not before.

answered Dec 30, 2011 at 14:52

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

Collectives™ on Stack Overflow

PHP querying MySQL returning Multiple Rows with only one Date

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related