need help with while statement

acidbox

Here is what I am doing.

I am using a mysql database to log each time a user uses a section on my website.

So, for each time a user enters a certain seciton on the website, a record is created in the database. It stores the user's name, the account number that the user is a part of, and a 0 or 1 flag to determine if a user took advantage of the feature.

Lets say, for instance, there are 2 sections. For the 4 users of account number 15, they collectivley go to section one 3 times and section two 4 times, the database will have 7 records similar to the following:

15 joe 1 0
15 bob 1 0
15 bob 1 0
15 sam 0 1
15 ian 0 1
15 joe 0 1
15 ian 0 1

I would like to know how to make the script display the account number 1 time and then displays the total number of hits for each seciton.

So, basically, I would like it to say,

account number: 15
section 1 hits: 3
section 2 hits: 4

The reason I have to create a new record instead of just updating a single record is because for each account number, there are multiple users.

I was trying to use a while statement, but it would repeat the answer over and over for as many records that are in the db.

If anyone knows a better way to do this, I'd really like to know 😉..

Thanks again, and sorry for the long post.

kailien

Disclaimer: untested code! Try at your own risk ;-)

select count(*) as Section_1_hits from table_name where userID = "15" and section_1 = "1";

select count(*) as Section_2_hits from table_name where userID = "15" and section_2 = "1";

give these two sql statements a try:

acidbox

While that would work if I were to only want to see 1 account at a time, IE: 15, I want to list the contents of the entire table.

The table consists of more than one account number, so I want to select more than 1 account at one time.

acidbox

does anyone have any ideas for this? I still have no idea what to do...

acidbox

maybe this table will help clarify what i am trying to do:

| acct_num | user | sec_1 | sec_2 |

| 15 | joe | 1 | 0 |

| 15 | bob | 1 | 0 |

| 15 | sam | 0 | 1 |

| 15 | ian | 0 | 1 |

| 15 | joe | 0 | 1 |

| 15 | ian | 0 | 1 |

| 14 | ann | 0 | 1 |

| 14 | ann | 1 | 0 |

| 12 | ted | 1 | 0 |

I would like to know how to make the script display the account number 1 time for each account number aand then display the total number of hits for each seciton for that account number. The username is just for internal tracking.

So, basically, I would like the script to output,

account number: 15
section 1 hits: 3
section 2 hits: 4

account number: 14
section 1 hits: 1
section 2 hits: 1

account number: 12
section 1 hits: 1
section 2 hits: 0