Jump to content

[SOLVED] Quick and Simple: Grouping datetime by date?


webmaster1

Recommended Posts

Hi All,

 

I have the following query to output data into a web page via PHP:

 

SELECT date FROM tablename

 

I want to group the results by date i.e. 14th, 15th or 16th

 

The column 'date' is of a datetime format.

 

Does anyone know how to do this?

 

 

<html>
<body>


<table><tr><th>Date</th></tr>

<?php
include("dbinfo.php");

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
//$query="SELECT date FROM zevasce";
$query="
SELECT DATE_FORMAT(date, '%a') AS 'Day of Week', count(1) AS 'Count'
FROM zevasce
WHERE date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY 1
ORDER BY DAYOFWEEK(date)
";

$result=mysql_query($query);
$num=mysql_num_rows($result);
mysql_close();

$i=0;
while ($i < $num) {

$date=mysql_result($result,$i,"date");
?>

<tr><td><? echo $date; ?></td></tr>

<?
$i++;
}
?>


 

Hmm, thanks but I get this:

 

Date

 

Warning: mysql_result() [function.mysql-result]: date not found in MySQL result index 4 in /xxx

 

Warning: mysql_result() [function.mysql-result]: date not found in MySQL result index 4 in /xxx

I just changed date to Day of Week where I'm defining the $date variable and it works.

 

The a bunch xRtopolis!!!  (just kidding about the name... :P)

 

Next quest: to populate it in a dropdown.

 

Quest thereafter: Populate a second dropdown based on the selection.

 

Quest thereafter: Show only the results based on the second drop down selection.

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.