Mysql order by day and time

Take a look at the last post I just had...
It's spin off from this

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Frameworks/Q_24148965.html


I am looking to show the number of views per day.

I know I would have to sort by the date I have in the database.

The thing is I would like to also sort by the hour as well....


So a basic one that sorts out the 7 days of the week...
To show which day was the most popular.

Then also to show the top 5 hours of the day that were the most popular.

Thanks!

I posted what my table database (snapshot) looks like in the last post.
include("database_info.php");
 
//-------------------------
 
    mysql_connect($hostname, $mysql_login, $mysql_password) or die(mysql_error());
    mysql_select_db($database) or die(mysql_error());
 
//-------------------------
 
 
$last10 = mysql_query("SELECT * FROM  $tableName ORDER BY vistid DESC LIMIT 10");
 
 
echo "<table border='1'>";
echo "<tr> <th>Visit ID</th> <th>IP</th> </tr>";
 
while($row = mysql_fetch_array( $last10 )) {
        // Print out the contents of each row into a table
        echo "<tr><td>"; 
        echo $row['vistid'];
        echo "</td><td>"; 
        echo $row['ip'];
} 
 
echo "</table>";

Open in new window

cntmedia1Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
instead of DAYOFWEEK, use DATE_FORMAT() function:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

the format for the "day of the week" is %w, and for the hour (0-23): %H


$checkSundayViews = mysql_query
                    (
                     "
                     
                     SELECT
                     COUNT( DATE_FORMAT( `datetime`, '%w %H' ) )
                     AS sunday
                     
                        FROM $tableName
                        WHERE (
                        DAYOFWEEK( `datetime` )
                        ) =1
                                             
                     "
            
 
                    );

Open in new window

0
 
cntmedia1Author Commented:
This is what I was using from my older code...

But I have the table set up different this time... so this isn't going to work.

Just thought this would help people in the right direction

$checkSundayViews = mysql_query
                    (
                     "
                     
                     SELECT
                     COUNT( DAYOFWEEK( `datetime` ) )
                     AS sunday
                     
                        FROM $tableName
                        WHERE (
                        DAYOFWEEK( `datetime` )
                        ) =1
                                             
                     "
            
 
                    );

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.