CODEWITHSUNDEEP
phpmysql
ak85
ak85

Reputation: 4264

Show most popular QUERY_STRING

I have a page that shows data from my database. Before the user sees the data they have a few select boxes so they can choose to filter the data in a number of different ways eg to chose to show data over a certain period of time. and the url looks similar to the below as I am using the get method.

http://www.example/com?report=2&days=7&etc=2

This all works fine and the users can filter the data to get the different results that they want. I have been viewing my analytics account and can see that some queries are more popular than others. How can I let the users know which queries are the most popular (eg most viewed over the last 24 hours). Currently I have made a manual html table that I update in the morning based on the Analytics data.

eg

<table>
<tbody>
<tr><th>query</th><th>link</th></tr>
<tr><td>Show data from last 7 days</td><td><a href="http://www.example/com?report=2&days=7&etc=2">View</a></td></tr>
</tbody>
</table>

I thought about adding a table called count which has id, query, timestamp and saying 

$a='report=';
$b=$_SERVER['QUERY_STRING'];
if($b == $a . $report['id'])
{}
else
{//Insert id, query, timestamp into the database}

This way I can do a count for a certain time period on queries that have the additional sorting paremeters. Is this the best way to acheive this?

Upvotes: 1

Views: 155

Answers (3)

FrancescoMM
FrancescoMM

Reputation: 2960

I think you should create a table with timestamp, report, days, etc and use those as a key.

This is much safer, if:

  • anytime parameters may be sent by POST (form) they won't be in the url.

  • anytime some of them might come from COOKIES or other sources they won't be in the url.

  • you decide to add other options to the URL that may change from page to page but won't modify the kind of report shown, like styling options such as &viewkind=2&colorset=3 or add random to invalidate cache like &rnd=83732 or anything else you may decide in the future.

  • anything else added to the url like hashes #name may interfere (jumping to named anchor in half bottom of page will be considered a different report)

  • also, forging a full URL to attack your database is probably much easier than forging the single integer or string name parameters

  • also your site may be called in different ways that make the url different. With www in front or without, with https if secure. Some parameters may have default values, so for instance not specifying days might be the same as, say, days=1 so the report might be the same but URL different.

Upvotes: 2

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

Create a table that stores the query-part of the url and the timestamp. It needs no other fields as you'll get a running sum of the latest calls.

Then get something like 

select url, 
       count(*) as reqs 
  from queries 
 where ts > now() - interval 24 hour 
 group by url 
 order by reqs desc

to get the most requested urls for the last 24 hours.

Upvotes: 2

Tudor Constantin
Tudor Constantin

Reputation: 26861

I'd go a bit more and take into consideration the followings:

http://www.example/com?report=2&days=7&etc=2

is basically the same as:

http://www.example/com?report=2&etc=2&days=7

with regards to what it expresses, but the algorithm you mentioned will record them as 2 different reports because $_SERVER['QUERY_STRING']; is different.

Also, from your apps business logic perspective, extra parameters might not be used, but present:

http://www.example/com?report=2&etc=2&days=7&utm_source=feed

So, I'd do a little processing on the params that form the key for query:

  1. sort by param name
  2. Only take into consideration the params that actually matter

Upvotes: 2

Related Questions