?
Solved

PHP MySQL mktime weekly data

Posted on 2011-04-25
13
Medium Priority
?
1,204 Views
Last Modified: 2012-06-21
I have a visitors log table..

id  int(11) NOT NULL
url  varchar(200) NULL
use  rvarchar(50) NULL
mktime  varchar(20) NULL

I want to create an admin page from where admin can choose the start date and end date and gets back the weekly hits for that period based on the mktime in the table
0
Comment
Question by:PranjalShah
  • 6
  • 5
  • 2
13 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 35464402
First of all if mktime is supposed to store date and time or date, then I would not use varchar on it.  Use datetime or date.  This way you can efficiently search by date.  If you use varchar then you will need to employ string to date functions on all records on the table before you can do the comparison and this will be really slow.  If you have mktime in datetime then the only thing you need to convert to datetime will be your start and end date parameters (two vs possible hundreds/thousands/(millions?)).

If that table already exists and has many records on it.  Do a one-time conversion.

alter table visitors add mktimetemp datetime;
update visitors set mktimetemp=str_to_date(mktime, '<formatting string see below>');
alter table visitors drop mktime; /*make sure mktimetemp is properly filled up first! */
alter table visitors add mktime datetime;
update visitors set mktime=mktimetemp;
alter table visitors drop mktimetemp;

Then you need to update your code that logs to this table

Refer to the following for the formatting string.
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

I would then adcd an index for mktime

Once you have this, assuming in your php code you have formed the start_date and end_date into a string you can easily make the query:

select * from visitors
where mktime between str_to_date(start_date,'formatting string') and str_to_date(end_date,'formatting string')

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 400 total points
ID: 35466663
Please read the article here about how to use PHP and MySQL with DATETIME information.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Your admin page would have a GET method form that takes two pieces of information from the client (start and end dates).  You can use strtotime() to turn these into a timestamp and date('c') to turn them into the ISO8601 representation.  Then use these ISO8601 strings in a query with BETWEEN, and you should be good to go!

HTH, ~Ray
0
 
LVL 8

Author Comment

by:PranjalShah
ID: 35470172
Thanks Ray and johanntagle,

Now I am able to get the start date and end date, I am not sure how to get the weekly/daily hits from the starting date to end date.

this is my example query to get the total hits from start date to end date

SELECT COUNT(DISTINCT mktimetemp) AS COUNT FROM visit_log_copy WHERE mktimetemp BETWEEN '2011-03-13' AND '2011-04-16'
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 24

Expert Comment

by:johanntagle
ID: 35471127
I think a count(*) will do since one row on the table translates to one hit right?
0
 
LVL 8

Author Comment

by:PranjalShah
ID: 35471158
Ya thats right but by doing that i will get the total count. I want to count it based on each week.
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 1600 total points
ID: 35471360
Oh weekly hits.  Then you need some grouping.  You can try the week() function.  See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_week

select week(mktimetemp) as week, count(*)
FROM visit_log_copy
WHERE mktimetemp BETWEEN '2011-03-13' AND '2011-04-16'
group by week(mktimetemp)
order by 1;

I guess you will want to display the starting dates for each week.  Unfortunately, there's no easy way to convert the output of week() back to a date.  There's probably a way, but in such a case it would be easier to have your php script determine the start date and end date of each week within the date range and just call the simple
SELECT COUNT(*) AS COUNT
FROM visit_log_copy WHERE mktimetemp BETWEEN start_date_of_week AND end_date_of_week;

If you need mysql to determine start_date_of_week AND end_date_of_week you can use something like (Assuming start of week is monday):
/*determine the week day of the entered start date, 0 is monday, 6 is sunday)
select weekday('2011-03-13');
/*determine the next coming sunday*/
SELECT DATE_ADD('2011-03-13', INTERVAL (6 - output_of_previous_sql) DAY);
(note I tried combining the two by putting the previous as subselect replacing output_of_previous_sql but Mysql won't accept it)

Then just use date_add to determine the next week start and end dates.

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35472748
the weekly hits -- is that the count value in "use"
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35474713
I want to count it based on each week. - not exactly sure what "it" is in that sentence.  Computers are not very good with pronouns.

Make a SELECT for all the rows that fit into the weeks, using the datetime string and BETWEEN, as we discussed elsewhere here at EE.  Then use the mysql_num_rows() function to get the number of rows that showed up in that week.

See http://php.net/manual/en/function.mysql-num-rows.php

You might want to read the man pages for all the mysql_ functions that are listed in the left sidebar of that page.  It would really give you a "leg-up" with questions like this one.

HTH, ~Ray
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 35479908
I think the mysql_num_rows function is useful if you want to both display actual contents of the rows and the total count of the retrieved row.  If you only want to display the count, then having the database retrieve all rows is very wasteful.  Ray, no offense meant but while the example given in the link you provided works, it is also an example of bad database practice.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35482828
... an example of bad database practice? --Yes, you could say that, but everything exists in a context.  At this point in the Author's learning curve about PHP and MySQL, almost any data base practice is better than no data base practice.  I am trying to guide our Author in the direction of something that would be successful, given that our Author obviously has no experience with PHP and MySQL.  Would I recommend this for a table with 600,000 rows?  No, I would probably use a different design pattern.  But if I had only a few thousand rows and I wanted to see the count of rows in my WHERE-filtered results set, I would expect that a SELECT against the index and mysql_num_rows() would not be meaningfully slower than SELECT COUNT.  By meaningfully slower, I mean something that a human being would notice in the response time, perhaps a second or two.  Since this is for an admin script, there will never be very much use of the script.  That tells me optimization is not as important as getting results.  

You can test this to see the differences, and I would be interested to see your test case.  In my experience, a far greater performance issue arises when programmers fail to index the columns used in WHERE, ORDER BY, GROUP BY, JOIN, etc.  Or when programmers do not use EXPLAIN SELECT on complicated queries.  Or when programmers do not normalize their data bases well.  But those issues only become evident with greater use.  In this case, optimizing might be something to think about once our Author understood the principles and had a working script.

And in the instant case, while I am willing to try to show the Author a way to use his technologies to get the results, the truly correct answer would almost certainly be, "Don't bother writing another 'invent the wheel' script - use Google Analytics (like millions of other web sites do)."
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 35483790
I agree that greater performance issues arise in the cases you mentioned.  However, my point is simple enough for an SQL beginner like PranjalShah to understand, so why not introduce it ASAP.   We know that when you do a query, there are resources involved in determining which rows match the query, reading the actual rows, then transferring the data between the database and the application, the last one a lot more evident when the database and the application are on different servers.  So why still use select * from table then use mysql_num_rows, which I believe uses the "nn rows in set" at the end of the query output (I hope it doesn't actually count the results itself) when you can just use select count(*).  The coding effort is practically the same - one line for the query, maybe one line to execute it, then one line to retrieve the result.  The resources involved in determining which rows match the query is the same, but the similarity ends there.  In select * you ask the database to read all rows.  In select count(*), I expect MySQL to just rely on the index and not read the actual tables anymore (I'm actually not sure if MySQL is smart this way, but in other databases like Oracle this is true - but even if MySQL will still refer to the table, it still it won't need to read the whole rows).  And then you need to transfer the retrieved data.  Given PranjalShah's table let's assume average row length is 100 bytes.  You match a thousand rows.  You're going to needlessly transfer 100KB versus just maybe 11 bytes for a standard int (plus a little overhead of course).  The difference doesn't sound much but again, given that the coding effort is the same, why use the less efficient one?  I will always use select count(*) or better yet, if possible, select count(primary_key_column) for any table, whatever size, so that anytime the table grows, performance is not impacted.  My philosophy is you design for performance where you immediately can, then of course do additional tuning later.

Peace.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35483820
why still use select * from table - No, I did not say that, and I would not recommend SELECT *

<quote>
I would expect that a SELECT against the index and mysql_num_rows() would not be meaningfully slower than SELECT COUNT
</quote>
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 35483927
I was referring to the link in your prior post that had select * as example..

But still, selecting against the index and using mysql_num_rows still involves returning more rows and data than needed.  Yes, for small result sets it won't be much slower, but again why bother with it when the coding effort to use select count is the same.

I rest my case.  Peace.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 9 hours left to enroll

850 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