I'm trying to sort through my click data and produce a list of the most clicked links. Once I've gathered the data and used a WHERE loop to count the clicks the next step is to present it sorted by click count in a descending format.

To produce this list, should I be trying to store the values in an array or should I create a temporary table to hold the data. I'm looking for speed and the most efficient method.

Any help or pointers in the right direction would be great. Cheers.

What? You first select * from links, then on each row you select number of clicks?

Ok. What I'm trying to do is everytime a user clicks on a site link in my directory it records this in a MYSQL table with fields: - URL, date/time created, and IP address.

Then, I'm trying to go this table and count how many clicks each URL gets within a specified time period ie. last 24 hours, last 7 days, last month etc.

Then I want to present this data in a new list sorted by the most number of clicks.

Should I therefore create a temporary MYSQL table to hold these new values or can it be held within an array. And, if so, how do I do it?

Unless you can be sure that the number of results is small enough that the number of people viewing the results simultaneously won't consume all available memory, you should probably use a well-constructed query instead of either of the options presented. As for the specifics, I don't know SQL super well, but if you included the data dictionary for the table, it'd be of great assistance - assuming that you name fields in an instructive fashion.

So at the moment you have 1 table e.g. clicks with fields URL, creation date and IP? And from it you want to create a report of form, e.g. URL - number of distinct IPs during the last 24 hours?

If all the above is true, I'd recommend you to change the layout of this table and add one more table named e.g. links. The links table should have following fields:
1. ID int not null auto_increment, primary key
2. Site name
3. Site URL
4. Anything else, like 'open in new window', 'make it bold', etc

Next you replace the URL in your current table with site_id from the newly created one. This will perform better not only on insert but also on select.

Now to get the report you should do:
1. select * from links
2. foreach ($links as $l) $links_indexed[$l->id] = $l;
3. select site_id, count(ip) as clicks from clicks group by site_id order by clicks desc where date_sub(now(), interval 24 hour) > added
4. foreach ($clicks as $c) {
$links_indexed[$c->site_id]->clicks = $c->clicks;
}

Something like that, yes. This will perform quite fine and can be easily optimized further by adding file cache.

Ok. Here's where I'm up to. I did as you said having already had some of the tables mentioned.

The links table was one that was already set up called tblBlog with the structure as you mentioned. The fields are;

id (int11) auto_increment PRIMARY NOT NULL
blogURL (varchar60)
....

The clicks table I have restructured as you mentioned and it is called tblBlogRank. It's fields are;

idRank (int8) auto_increment PRIMARY NOT NULL
datRank (datetime)
idBlog (int8) INDEX
sIP (varchar15) INDEX

idBlog in tblBlogRank now contains the relevant id from tblBlog.

Here's the code I added;

PHP Code:

$query="select id, blogURL from tblBlog";
    mysql_query($query);
    
    foreach ($links as $l){ 
        $links_indexed[$l->id] = $l;
        
        $nw_query="Select idBlog, count(sIP) as clicks from clicks group by idBlog order by clicks desc where date_sub(now(), interval 24 hour) > added";
        
        foreach ($clicks as $c) {
            $links_indexed[$c->idBlog]->clicks = $c->clicks;
            
            echo $blogURL;
        }
    } 


but I'm getting an error the foreach ($links as $l){ line as Invalid argument supplied for foreach().

Can you please tell me what I'm doing wrong and also how I'm supposed to echo this data?

Cheers - and thanks for being patient.

im sorry to bump your thread with this stupid question. but where did you get $links?
could you please var_dump($links) first before supplied it as an argument for foreach..

or maybe you mean this?
$links = mysql_query($query);

while($row = @mysq_fetc_assoc($links)){
$links_indexed[$row['id']] = $row['blogURL'];
}

also the code looks redundant to me.. sorry i did not understand your code that much.

scrobins, rtfm please how to get an array of records from the table, you code lacks only this

What?

he means to look up how to get an array of records from the table;
like brealmz showed you ... you have to use a cycle like this one

while($row = @mysq_fetc_assoc($links)){
$links_indexed[$row['id']] = $row['blogURL'];
}

to get the data from you're mysql tables

Originally Posted by mtishetsky

What? You first select * from links, then on each row you select number of clicks?

Basically it is not a good practice to select * from a table. Always select the columns which are needed - nothing more or nothing less.