CODEWITHSUNDEEP
phpmysqldate
Anna Riekic
Anna Riekic

Reputation: 738

php/mysql ordered random dates

I found this great function on SO whilst looking for a way to generate random dates between two fixed timestamps:

 function randomDate($start_date, $end_date)
{
    // Convert to timestamps
    $min = strtotime($start_date);
    $max = strtotime($end_date);

    // Generate random number using above bounds
    $val = rand($min, $max);

    // Convert back to desired date format
    return date('Y-m-d H:i:s', $val);
}

Source and credit

but I am looking for a way for the dates to be generated in order (start date to end date) as I have used it to generate dates to insert into a database.

The problem is my posts are ORDER BY id DESC and using the function "as is" being that they are random the dates end up out of sync.

ie:

post id 4 - date = 2010-07-11 14:14:10
post id 3 - date = 2012-02-22 18:23:21
post id 2 - date = 2011-03-17 13:52:47
post id 1 - date = 2011-08-14 15:33:50

and I need them to be in sync with the post id.

Now your thinking why not change the query to ORDER BY date DESC instead? ...well that would mess up 99% of code I have already written as there are other columns/rows dependent on it being ORDER BY id DESC and so ordering the dates when being inserted into the database is the only solution.

update:

this is what I tried using madfriend code but all dates are the same where have I gone wrong?

function randomDate($startdate, $enddate){
 $min = strtotime($startdate);
 $max = strtotime($enddate);
 $val = rand($min, $max);
 return date('Y-m-d H:i:s', $val);
}

$query = "SELECT * FROM foo";
$num   = mysql_num_rows(mysql_query($query));

$randate = randomDate('2010-07-12 09:13:40', '2012-06-12 09:13:40');

$dates = array($randate);
for ($i = 0; $i < $num; $i++) {
    $dates[] = randomDate($startdate, $enddate);
}
sort($dates);
while($date = array_shift($dates)) {

$update = "UPDATE foo SET date='{$date}'";
 mysql_query($update);

}

plus getting Notice: Undefined variable: startdate

Upvotes: 0

Views: 301

Answers (2)

lsl
lsl

Reputation: 4419

function randomDate($startdate, $enddate){
 $min = strtotime($startdate);
 $max = strtotime($enddate);
 $val = rand($min, $max);
 return date('Y-m-d H:i:s', $val);
}

$query = "SELECT * FROM foo";
$num   = mysql_num_rows(mysql_query($query));

$randate = randomDate('2010-07-12 09:13:40', '2012-06-12 09:13:40');

$dates = array($randate);
for ($i = 0; $i < $num; $i++) {
    $dates[] = randomDate($startdate, $enddate);
}
sort($dates);
while($date = array_shift($dates)) {

This query updates all rows in one go:

$update = "UPDATE foo SET date='{$date}' ";
 mysql_query($update);

}

Probably were wanting to use 

$update = "update foo set date='{$date}' where id = (select id from foo where date is not null order by id limit 1)";

(for that to work you need to set each date in the db to null before you start updating: update foo set date=null)

Also you shouldn't be using myslq_query..

Upvotes: 0

madfriend
madfriend

Reputation: 2430

I'm not quite sure whether you are talking about creation or modification of existing rows.

Updates: basic idea here is quite simple. First, count number of posts with SELECT COUNT(*) FROM your_posts_table query. After that:

// $num is number of posts
$dates = array();
for ($i = 0; $i < $num; $i++) {
    $dates[] = randomDate($startdate, $enddate);
}
sort($dates); // Sort dates in ascending order
while($date = array_shift($dates)) {
    // now $date won't be lower than it was in previous iterations.
    // use it to update your table
}

Insertions: If you are talking about insertions and want to make latest post date random but biggest, here's what you do:

  • First, select last added post date.
  • Second, call randomDate with $startdate set to the date of last added post.
  • Last, insert new row with this date.

Upvotes: 2

Related Questions