?
Solved

Mysql order by day and time

Posted on 2009-02-16
3
Medium Priority
?
1,060 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:cntmedia1
2 Comments
 

Author Comment

by:cntmedia1
ID: 23656973
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 23667776
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month15 days, 5 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question