call log: previous calls query

jkohns

I am trying to query a table containing call log information (caller id number, date, time) for all calls on a specific date, but also trying to query for the number of previous calls from each number for that day- so that for instance:

phone | date | time | previous calls
555-0123 | 2003-06-09 | 13:41 | 12

would be my result showing that 555-0123 had called on June 9 at 1:41 PM, but had ALSO called 12 times previously (throughout the history of the D😎

I'm using MySQL 4.1, so subselects are available, but I can't seem to make a subselect expression work right- I always get the same number for every result row???

Any help would be greatly appreciated- this is my missing puzzle piece!

laserlight

this is a little confusing.
how many tables are you using?
it cant be only 1 table, right?
otherwise there shouldnt be any problem.

jkohns

Yeah, that's my problem- it is only one table- I'm just missing the obvious I think...

I have a table of logged calls w/ a phone number and the date and time of the call. I want to query the table for all calls on a specific date, and in the same query get the number calls from each phone number I had recieved previous to that date

laserlight

Oh, then you have a poor table design.

Create 2 tables.
1 table will store the unique phone numbers, indexed by phone_id
The other will store each call made, indexed by call_id, having foreign key phone_id

As such, given a phone number, you obtain its phone_id from the phone_numbers table, then use this to obtain all the corresponding records in the phone_calls table.

laserlight

hmm... I didnt notice the "particular date" part.
Still, you could get all the phone_calls made on a particular day.
Store the phone_id in an array
Use array_unique(), then iterate through this array to extract from phone_calls again, this time based on phone_id rather than on date

still, that doesnt satisfy entirely though, since it takes more than 1 query.