How To Count Top Highest Id Records

Yii 1.1 General Discussion
1

Hi guys,

Help me to solve my script, i’m new in yii.

I has two tables:

++++++++++++++++++

company (id, name, product, contact, telp, fax, state) as my main data table

++++++++++++++++++

and

++++++++++++++++++

countclick (id, id_company, id_user, created, ip)

+++++++++++++++++++

(1, 80, 5, ‘2014-04-30 06:00:08’, ‘::1’),

(2, 50, 2, ‘2014-04-30 06:01:08’, ‘::1’),

(3, 82, 4, ‘2014-04-30 06:03:04’, ‘::1’),

(4, 120, 1, ‘2014-04-30 06:04:24’, ‘::1’),

(5, 11, 1, ‘2014-04-30 06:07:07’, ‘::1’),

(6, 50, 5, ‘2014-04-30 06:07:22’, ‘::1’),

(7, 11, 4, ‘2014-04-30 12:11:41’, ‘::1’),

(8, 50, 3, ‘2014-04-30 12:11:56’, ‘::1’),

(9, 50, 1, ‘2014-04-30 12:12:24’, ‘::1’),

(10, 11, 2, ‘2014-04-30 12:12:48’, ‘::1’);

on company page, user will click data/company he want to choose then save each click count into countclick table.

then on my index page, i want to show 25 highest company are most clicked by collecting data from countclick.

please help me with the SQL statement, right now i’m using CDbCriteria to show and save count click and was run good.

I don’t have no idea how to show 25 highest clicked company.

thanks a lot

2

Is clickCount() for the purpose of keeping users to vote for the same company more than once?

Given your data you basically want:

company_id | count

50     |   4


11     |   3


80     |   1


82     |   1

120 | 1

Could I suggest adding a column to company ‘clickCount AS INTEGER’, Then is the clickCount->actionCreate() and a line to update the Company::clickCount attribute. See this link for an idea of how to do it.

3

Thanks jkofsky,

In this case, users can only "vote" company just once at the same time/day and perhaps same IP.

Countclick table purposes will collecting all click from users and store it as it is into Countclick table.

as we can see in Countclick table sorted by you, i want to use this count click to make top highest company list from it.

The link was good, and giving me more ideas to solved this case.

in the future i want to use those information to create some analytic function based on that table. it is why i dont want just collecting ‘the click’ only.

I’m searching on SQL statement for it, i thought the statement will near from using JOIN and GROUP BY but i don’t really sure yet.

4

Big thank you jkofsky,

Case SOLVED!

i’m using the idea you gave me before. So this is the logic process:

IF clicked:

Save users information (id_company, id_user, created, and IP) in clickcount table

Then

Save clicked by Ascending visits in company table.

5

Glad to help