Geek asks and answers

MySQL is selected by different dates, with a limit by date, but not with records

What I'm trying to do is grab the records from the update table in the mysql database using php. Each entry has a time stamp attached to it. First, I grab 10 separate dates from a timestamp, create a new array using these 10 unique dates (I do this because fetchAll puts dates in a multidimensional array), and then another database call captures all the records that have these dates. Which can return more than 10 records. I want it to have 10 separate dates, not 10 separate timestamps.

For example, if 2012-03-25 appeared in the first call from different dates, it might find:

2012-03-25 00:58:53
2012-03-25 00:58:02
2012-03-25 00:57:20
2012-03-25 00:56:35
2012-03-25 00:55:58
2012-03-25 00:53:20
2012-03-25 00:52:32
2012-03-25 00:51:45

Since timestamps are different.

Here is the current code that I use that works.

$test = $dbh->getArray("SELECT DISTINCT DATE_FORMAT(added, '%Y-%m-%d') AS added FROM db_updates ORDER BY added DESC LIMIT 0,10");

foreach ($test as $entry) {
  list($date) = $entry;
  $output[] = '\''.$date.'\'';
}  

$test2 = $dbh->getArray("SELECT * FROM db_updates WHERE DATE_FORMAT(added, '%Y-%m-%d') IN (".implode(',', $output).") ORDER BY added DESC");

print_r($test2);

This returned 33 results.

, - , PDO, mysql php. :

public function getArray($query) {
  $result = $this->db->query("$query");
  return $result->fetchAll(PDO::FETCH_NUM);
}

100%, , . , , mysql, .

, Google, , .., .

+3
1

INNER JOIN, DATE(added) DATE_FORMAT(), DATETIME:

SELECT * 
FROM
  db_updates
  INNER JOIN (
    /* Subquery returns most recent 10 dates */
    SELECT DISTINCT DATE(added) AS added FROM db_updates ORDER BY added DESC LIMIT 0,10
  /* JOIN between dates */
  ) datelist ON DATE(db_updates.added) = datelist.added
ORDER BY db_updates.added DESC
+3

More articles:


All Articles