displaying results by day

[dant]

I've been looking around for about a week now and am still trying to find the answer that fits my needs. I've read the DATETIME section on mysql, and it was helpful, but still, hasn't fully answered the question.

How can I display results by highest count for each day? For instance, top ten searches by day sorted by count().

Here is the code I've come up with so far, it manages to understand the current date and display some of the searches for that day, yet, does not display from highest to lowest, though the count() output works just fine.

$sql = "SELECT keyword, count(keyword) AS keywordcount,curdate() as date FROM queries GROUP BY keyword ORDER BY date DESC LIMIT 0, 10";

Here is what the db table looks like
id (auto_increment)
ip
keyword
time (datetime)

I know you might suggest using DATE_FORMAT(time,%Y,%m,%d) but i just could not figure out how to get that work correctly, maybe a BETWEEN? I think maybe the datetime format is messing it up and I need to separate days and compare.

If anyone can help I would appreciate it.

[raf]

You need something like

$sql = "SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, keyword, count(*) AS keywordcount FROM queries GROUP BY datepart, keyword ORDER BY datepart DESC LIMIT 10";

If you want the count for the keyword/date combination, then you need to group on both. And you need to include them in the recordset to know what data the count is for.
With limit, if you don't start from a specific ofset, you can omit it.

For 4.1.1 and later, you can use date() to get the date for a datetime. In lower versions, you can use DATE_FORMAT(time, '%Y %m %d') <--- no commas between the types, but surrounded by quotes

The query above probably wount give you what you want, because of the limit clause. It will prbably only give you the 10 most frequent keywords from the last day (unless you have less the 10 lookups a day (which makes it quite pointless to analyze them).

[dant]

I searched around a little more, and went back to the manual. I came up with this, and it worked.

$sql = "SELECT keyword, count(keyword) AS keywordcount FROM queries WHERE TO_DAYS(time) = TO_DAYS(curdate()) GROUP BY keyword ORDER BY keywordcount DESC LIMIT 0, 10";

If there is a better way to do it, let me know, otherwise I'll work with this.

Thanks.

[raf]

Did you even read my post?

I thought you wanted
How can I display results by highest count for each day? For instance, top ten searches by day sorted by count().
:confused: :confused:

Your code will only return the keywords for the last day.

The code i posted should give you exactly what you need (well, if you take the note about limit into account).
I would use

$sql = "SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, keyword, count(*) AS keywordcount FROM queries GROUP BY datepart, keyword WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datepart) <= 5 ORDER BY datepart DESC, keywordcount desc LIMIT 100";

Which will first group the keywords per day, for the last five days and will then display them ordered by date with the highest count for that first day first.

[dant]

well I thought I had it, but I'll try your code, as you're probably right.

[dant]

neither of the select statements seemed to do the trick. The last one was an error.

With the first select statement I got a list of ten search results, however, if I expanded the search to 50 I noticed there were many searches that had a higher search count than the 'top ten'. Which tells me it didn't work.

[raf]

Originally posted by dant
The last one was an error.
Can you be more specific? Which error?

Originally posted by dant
With the first select statement I got a list of ten search results, however, if I expanded the search to 50 I noticed there were many searches that had a higher search count than the 'top ten'. Which tells me it didn't work.
It works alright. But i only took over your order by clause, which
only sorts on the date and not on the keywords-count.
If you want the count of keywords in descending order, then you just need to expand the order by clause:

$sql = "SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, keyword, count(*) AS keywordcount FROM queries GROUP BY datepart, keyword ORDER BY datepart DESC, keywordcount DESC LIMIT 50";

(i've set the cutof to 50) This first orders on the dates (last day first, and then, within each date, on the keywordcount.

[dant]

the example you gave me works much better, though it looks like the same results I had with my select statement when I set the limit to 50.

thanks for the help.

[dant]

I have another question involving the same subject, displaying by day.

I've created two select statements that will count and display the total searches for the day, and for yesterday. I'm trying to join the two same table searches for efficiency, yet, my solution with the UNION and while loop doesn't create the desired effect.

The code:

$sql="SELECT count(keyword) AS keywordcount FROM queries WHERE TO_DAYS(time) = TO_DAYS(curdate()) UNION SELECT count(keyword) AS yesterdaycount FROM queries WHERE TO_DAYS(time) = TO_DAYS(curdate()-1)";

if (!$r = mysql_query($sql))
{

die('Query Error: ' . mysql_error());

}



while($row = mysql_fetch_assoc($r)){

echo "total searches today: $row[keywordcount] | Yesterday: $row[yesterdaycount]";

}


With this code I get the data out of the table just fine, however, I end up with the data displaying like this.

total searches today: 23423 total searches today: 35354

I've tried dropping the while loop, but think that maybe this is more of a problem with the mysql than PHP. If the Mysql is fine, then I'll find a solution in PHP.

Thanks.

[raf]

You don't need a union for that, because that is exactly the functionality of group by --> group by on the date. So if 'count(keyword) AS keywordcount ' is the think your after then all you need for that is:

$select=("SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, count(keyword) AS keywordcount FROM queries GROUP BY datepart WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datepart) <= 1 ORDER BY datepart DESC");
$result = mysql_query($select, $link) or die('Queryproblem :' . mysql_error());
if (mysql_num_rows($result)== 0){
$error = ('No searches found for the last two days');
} else {
while($row = mysql_fetch_assoc($result)){
echo .... // each instance of the loop processes one day. You can place them after eachother in columns or with a bar between or ...
}
}

[dant]

how do I loop this and display yesterday and today? $row[keywordcount]? I'm not sure what I put in echo.


Also, what does $link mean?
$result mysql_query($select,$link)


Thanks,

Dan

[raf]

Originally posted by dant
how do I loop this and display yesterday and today? $row[keywordcount]? I'm not sure what I put in echo.

Depends on which dateformatformat you want them to be in. The easiesiest is just echoing the mysqldates but hats not realy pretty.

If you want to print something like
"Total number of searches today = 2458
Total number of searches yesterday = 3534"

then you can use

$today = date("Y-m-d"); // this should retur the day like mySQL returns it
$yesterday = (date("Y-m") . "-" . date("d")-1);
while($row = mysql_fetch_assoc($result)){
if ($row['datepart'] == $today){
echo ('Total number of searches today = ' . $row['keywordcount']);
} elseif ($row['datepart'] == $yesterday){
echo ('Total number of searches yesterday = ' . $row['keywordcount']);
}
}


Originally posted by dant

Also, what does $link mean?
$result mysql_query($select,$link)

Its the variable that stores the connectionid --> you can leave it out if you like. PHP will then use the last opened connection is picked.
You can see an example where link is used here
http://www.php.net/manual/en/function.mysql-num-rows.php

You can name it whatever you want, but $link is commonly used for the connectionID, $result for the resourceindicator, $row for a record etc...

[dant]

how should the total code look together? I've tried taking out certain parts of the code and replacing it with the second bit you gave me, but it's only producing errors. Did I leave something out here? I've got the select statement, the $result mysql_query, and [keywordcount] in two variables $yesterday and $today, and the while loop to display $rows...


$select=("SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, count(keyword) AS keywordcount FROM queries GROUP BY datepart WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datepart) <= 1 ORDER BY datepart DESC");

//mysql query
$result = mysql_query($select,$link) or die('Queryproblem :' . mysql_error());

//datepart variables
$today = date("Y-m-d");
$yesterday = (date("Y-m") . "-" . date("d")-1);

//while loop rows and echo today yesterday
while($row = mysql_fetch_assoc($result)){
if ($row['datepart'] == $today){
echo ('Total number of searches today = ' . $row['keywordcount']);
} elseif ($row['datepart'] == $yesterday){
echo ('Total number of searches yesterday = ' . $row['keywordcount']);
}
}

[raf]

What error do you get ?

The nly thing i see that could produce errors is the $link --> if you havent set the connectionID to it.

[dant]

I get this error.

Queryproblem :You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datepart) <= 1 ORDER BY date

[raf]

<edit>My bad. The group by clause of course needs to come behind the where clause.

So you need to change

$select=("SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, count(keyword) AS keywordcount FROM queries GROUP BY datepart WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datepart) <= 1 ORDER BY datepart DESC");

into

$select=("SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, count(keyword) AS keywordcount FROM queries WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datepart) <= 1 GROUP BY datepart ORDER BY datepart DESC");




</edit>
If that doesn't solve it, print out the executed query with

echo $select;
die ();

(insert it right after the $select=(" etc and then load the page in your browser.. Copy it and paste it here.)

[dant]

I tried the code and got this error

Queryproblem :Unknown column 'datepart' in 'where clause'


I also echo'd the $select like you suggested, here is the result.


SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, count(keyword) AS keywordcount FROM queries WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datepart) <= 1 GROUP BY datepart ORDER BY datepart DESC

[raf]

I ran a similar query on a db of mine. The problem is that the where clause is evaluate before the recordset-view is build. So this means also before the DATE_FORMAT.

The "order by" and "group by" clauses are exacuted after that the recordset is build (they are actuallyexecuted on recordset-view)


But it's easy to fix, since you can run the to_days on the initial variable and get the same result.

Like

$select=("SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, count(keyword) AS keywordcount FROM queries WHERE TO_DAYS(NOW( )) - TO_DAYS(time) <= 1 GROUP BY datepart ORDER BY datepart DESC");


I've tested it on my db and it didn't give an error then.

[dant]

I'm getting a blank screen now.

When I echo $result, I get a resource id#4.

[raf]

When you have checked that the sql statement is OK, then you need to take the

echo $select;
die ();

back out

The $result only contains a reference to the actual recordset (it contains the resource ID, in your case 4) If there is a value for $result, then this means that the query was executed without that mySQL returned an error.

[dant]

yeah, I understand that. I put the echo $select back in after the blank screen to see what it would do. I don't think the select statement is retrieving the rows.


This is the code I'm running that shows a blank screen.


//Find total searches for yesterday and today
$select=("SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, count(keyword) AS keywordcount FROM queries

WHERE TO_DAYS(NOW( )) - TO_DAYS(time)<= 1 GROUP BY datepart ORDER BY datepart DESC");


//mysql_query, today and yesterday variables
$result = mysql_query($select) or die('Queryproblem :' . mysql_error());


$today = date("Y-m-d");
$yesterday = (date("Y-m") . "-" . date("d")-1);

//while loop rows, echo today and yesterday variables
while($row = mysql_fetch_assoc($result)){
if ($row['datepart'] == $today){
echo ('Total number of searches today = ' . $row['keywordcount']);
} elseif ($row['datepart'] == $yesterday){
echo ('Total number of searches yesterday = ' . $row['keywordcount']);
}

}

[raf]

Do the normal debugging stuff:

- look at the source in your browser and make sure it' not because of a CSS thing
- make sure that there are rows that meet the condition --> copy paste the echoed $select to phpMyAdmin or so and run the query there to see if there are any records retieved (are there searches in the db for the last 2 days?)
- echo the $today and $yesterday and see if they are in the right format
- echo the records to see if ay ae selected, like

while($row = mysql_fetch_assoc($result)){
echo ('<br />' . $row['datepart'] . ' : ' . $row['keywordcount']);
}

- include the

if (mysql_num_rows($result)== 0){
$error = ('No searches found for the last two days');
} else {

(look at one of my previous posts)
- also, the 'time' column --> it's not a reserver word in mySQL but it is highlighted in my query-window ... In any case, a datetime variable shouldn't be called 'time'

[dant]

I didn't even think to do that, I just ran the select statement in phpmyadmin and it works just fine.


I added the while you just suggested and the displayed results are

2003 12 28 : 5936
2003 12 27 : 7213


Which is exactly what I went. So now all I have left to do is figure out how to assign $today and $yesterday with the appropriate results, and I can work with them.

Here's the code I have now.

//Find total searches for yesterday and today
$select=("SELECT DATE_FORMAT(time,'%Y %m %d') as datepart, count(keyword) AS keywordcount FROM queries

WHERE TO_DAYS(NOW( )) - TO_DAYS(time)<= 1 GROUP BY datepart ORDER BY datepart DESC");


//mysql_query, today and yesterday variables
$result = mysql_query($select) or die('Queryproblem :' . mysql_error());


$today = date("Y-m-d");
$yesterday = (date("Y-m") . "-" . date("d")-1);

//while loop rows, echo today and yesterday variables
while($row = mysql_fetch_assoc($result)){
echo ('<br />' . $row['datepart'] . ' : ' . $row['keywordcount']);
}

[raf]

Did you echo $today and $yesterday ?

I think the problem are the unnescecary -

change
$today = date("Y-m-d");
$yesterday = (date("Y-m") . "-" . date("d")-1);

into
$today = date("Y m d");
$yesterday = (date("Y m") . " " . date("d")-1);

and then you should be able to use

while($row = mysql_fetch_assoc($result)){
if ($row['datepart'] == $today){
echo ('Total number of searches today = ' . $row['keywordcount']);
} elseif ($row['datepart'] == $yesterday){
echo ('Total number of searches yesterday = ' . $row['keywordcount']);
}

}

to get your result