Need help with logic and maybe some coding...

[iPixel]

I have a database that stores a bunch of dates and numbers. This database table holds phone call records.

 

Here's an example of the table structure and data.

 

date                  unique          calls            usrid              type

====== ===    ======        ======      ======          ======

20-DEC-2009      5                  7                  123456          Incoming

20-DEC-2009      2                  5                  123456          Outgoing

20-DEC-2009      7                  12                123456          Total

 

As you can see the date is repeated 3 times for the 3 different types of calls.

 

How could using just 1 SELECT statement pull and display the data like you see below...

 

                  INCOMING                                                      OUTGOING

================================          ======================

Date                Unique        Calls          Total          Unique      Calls        Total

=========    ======      =====      ====          =======  =====    =====

20-DEC-2009    5                7                12              2                  5              7

 

 

So as you can see the "DATE" on shows once but the columns are displaying data from the 3 rows in the table but displaying it in 1 table row.

 

My main reason for doing this is to avoid going back and forth from the database and back.

This is just a seriously small scale of the #'s.

 

Thanks to all who help!

 

 

[cags]

Would it not make more sense to have a table structure of...

 

date

userid

unique_out

calls_out

unique_in

calls_in

20-DEC-20091234562557

 

You don't need to store the totals because they can easily be returns with a simple query, you only have one row of data pertaining to one person.

[atl_andy]

Something more manageable from a data perspective might be:

 

date                 userid unique_calls    total_calls    call_type

20-DEC-2009 123456 5                     7               incoming

20-DEC-2009  123456    2                        5                  outgoing

 

 

You would then do any calculations when data is selected from the db.

[iPixel]

Unfortunately, i was not the creator of the database, it's a pretty old one at that and i cannot change it... but i did figure out a way to do this with 2 sql statements.

 

Here's how...

 

 

// SELECT 1 = SELECT DISTINCT calldate FROM table WHERE calldate BETWEEN this AND that ORDER BY calldate ASC;

 

while(results1 = mysql_fetch_assoc(sql1))

      {

            // SELECT 2 = SELECT * FROM table WHERE calldate = 'results1[calldate]';

            while(results2 = mysql_fetch_assoc2(sql2))

                    {

                                  if(results2['calltype = "I"])

                                          {  do this }

                                  if(results2['calltype = "O"])

                                            { do this }

                                  if(results2['calltype = "T"])

                                            { do this }

                    }

        }

so basically in each { do this } i assign the value to a variable that i later display... works like a charm.