php/mysql multiple select dillema

Question

I am trying to get some data out of my database and stuck at one of the SELECT operations. Basically I have 2 tables, users and invoices: - users contains 4 fields: id:, name, tel_no, notes - invoices contains 9 fields: id, account_id, invoice_date, caller, date, hour, destination, total_duration,type_of_service

The total_duration field contains the information about the duration of a user call and it's expressed in seconds. The type_of_service field contains info about the destination of the call ("Land line, Another Net, Call Special Number").

I would like to be able to know the sum of minutes the user managed to accumulate when he made calls to "Land line, Another Net, Call Special Number".

What I have so far is:

$query=mysql_query("SELECT id,name,tel_no FROM users UNION SELECT id,account_id,caller FROM invoices WHERE caller LIKE \"%tel_no\"");

which gives me info about the tel numbers from the users table which are found in the invoices table ... but don't know how to get the total minutes used ...

It may not help answer your question, but you should stop using mysql_* functions. They're being deprecated. Instead use PDO (supported as of PHP 5.1) or mysqli (supported as of PHP 4.1). If you're not sure which one to use, read this SO article. — Matt
– Matt, Commented Aug 10, 2012 at 14:03

Austin · Accepted Answer · 2012-08-10 15:21:52Z

1

Try using SUM:

$query=mysql_query("SELECT id,name,tel_no FROM users UNION SELECT id,account_id,caller, sum(total_duration) AS sum_total_duration FROM invoices WHERE caller LIKE \"%tel_no\"");

The value is accessed like so:

echo $returnedArray['sum_total_duration']

edited Aug 10, 2012 at 15:21

answered Aug 10, 2012 at 14:04

Austin

6,0442 gold badges26 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Mihai Iorga Over a year ago

assuming the total_duration is in seconds and not like 08:13. And you could use sum(total_duration) AS total_duration

Austin Over a year ago

Good catch - however it should be stored as seconds, by convention, just as all dates should be stored as DATETIME, etc.

Mihai Iorga Over a year ago

Why dates should be stored as timestamps when you can use MySQL's DATETIME realy well?

Austin Over a year ago

Typo, my point being that MySQL's features should be fully taken advantage of.

Mihai Iorga Over a year ago

ahh .. ok .., then you can edit your answer to add AS total_duration :)

|

Collectives™ on Stack Overflow

php/mysql multiple select dillema

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related