Display MySQL Results by Date

Display MySQL results by date

Here is teh PHP code:

$query = mysql_query("SELECT date, query FROM table6 ORDER BY date DESC LIMIT 20");
$group_date = null;
while ($row = mysql_fetch_assoc($query)) {
    if ($group_date !== substr($row["date"], 0, 10)) {
        $group_date = substr($row["date"], 0, 10);
        echo "<h1>$group_date</h1>\n";
    }
    echo "${row['query']}<br>\n";
}

Output:

2012-11-18

Tom

Michael

2012-11-17

Erik

John

2012-11-16

Larry

Kate

Note that while this code "groups" rows by one column, it can easily be extended to group rows by multiple columns. Left as an exercise.

PHP MYSQL Display results where day is todays date

You should be able to just specify your current date in the query (i.e. no need to calculate in PHP). This would also give you more consistant time handling in case web server and MySQL server have different timezones.

If Day is datetime or timestamp field use this:

SELECT * FROM Persons WHERE Day LIKE CONCAT(CURRENT_DATE(),'%')

If Day is date field use this:

SELECT * FROM Persons WHERE Day = CURRENT_DATE()

MySQL Select Date Equal to Today (having datetime as the data type)

SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d') 
FROM users 
WHERE DATE(signup_date) = CURDATE()

PHP display mysql result based on date picker input

You have to change the loop in this way:

while( $row = mysql_fetch_array($select_page_view) )
{
    (...)
}

In your original syntax:

$row = mysql_fetch_array($select_page_view);
(...)
while( $row )
{
    (...)
}

$row is fetched one, then it doesn't change, so the loop doesn't stop.

Displaying only data from a mysql database that is from todays date

WHERE DATE(`date`) = DATE(NOW())

PHP MYSQL - Display dates saved as 'date' type from MYSQL Database

You need to use something like mysqli or pdo to execute your query and extract the data. Here's the general idea, using mysqli:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

$dates_sql = "SELECT UNIX_TIMESTAMP(datetime) AS tstamp FROM employee_datetable LIMIT 7";
$result = $mysqli->query($dates_sql);

while($row = $result->fetch_array()) {
    $FormattedPhpDate = date('M d, Y', $row['tstamp']);
    echo "Date: " . $FormattedPhpDate . "<br/>";
}

Note: Since you are using UNIX_TIMESTAMP in your query there is no need to convert the date to a timestamp using strtotime()

---

For more info see documentation for mysqli, specifically mysqli_query and mysqli_fetch_array

mySQL query -- sort by date

@Gonzalo is close but not quite there. You need to put each table into subqueries and alias each column you want in the final result. If one table doesn't have a column that you need from another use a literal and alias it.

(SELECT eventName, eventTime, foo FROM t1)
UNION ALL
(SELECT evntName AS eventName, evntTime AS eventTime, NULL AS foo FROM t2)
ORDER BY eventTime DESC

Put any WHERE clauses inside the subqueries.

---

Related Topics