PHP Array

NaniG · April 26, 2011

Hi 2 all,

My problem is like this....

Table Name : Test

keys1 keys2

801 801

802 802

803 803

804 804

805 805

806 806

How i can compare column1 values with column2 values (compare each and every value with in the test table by using MySQL select query.)

for example :-

select * from test where keys1='801' and Keys2!='801';

select * from test where keys1='801' and Keys2!='802';

select * from test where keys1='801' and Keys2!='803';

select * from test where keys1='801' and Keys2!='804';

select * from test where keys1='801' and Keys2!='805'; etc....

select * from test where keys1='802' and Keys2!='801';

select * from test where keys1='802' and Keys2!='802';

select * from test where keys1='802' and Keys2!='803';

select * from test where keys1='802' and Keys2!='804'; etc....

Please any one help me out from this problem.

Thanx in adv.

Psycho · April 26, 2011

Please explain what EXACTLY you are trying to accomplish? If you want ALL the records categorized by the key1/key2 values, then you should do just one SELECT query with an appropriate ORDER BY. Then you can display the results categorized by their key1/key2 values in the PHP logic.

SELECT * FROM test ORDER BY keys1, keys2

Pikachu2000 · April 26, 2011

Sounds like homework to me.

NaniG · April 27, 2011

Thanx for rply the replies,

I want to calculate the sum of score value in between Keys1 and Keys2 and if keys1 value is equal to keys2 value then the score seems to 0.

otherwise it should be calculate the sum of score.

Please advise me.

Psycho · April 27, 2011

I want to calculate the sum of score value in between Keys1 and Keys2 and if keys1 value is equal to keys2 value then the score seems to 0.

otherwise it should be calculate the sum of score.

I have no clue what you are wanting. Provide some examples of the data that would be in the database and the output you want generated.

fugix · April 27, 2011

I am rather confused as to what you are trying to accomplish as well

NaniG · April 27, 2011

Hi to all,

It was a VoIP project. I wanted to calculate the duration of the each call made by end user. All the details are stored in the database with Date and Time, Source, Destination and Call Duration.

now my intention is to calculate the sum of the duration which the end user made the calls with some condition like

1) with in the group numbers, the calls are free

2) with in the group extensions, call are free

3) with in the group, the numbers to extension is also free (vise versa).

Example :-

Numbers : 1234567890, 2345678910, 3456789120, 4567891230

Extension : 101, 102, 103, 104

In database

Date Source Destination Status CallDuration(sec)

2011-04-27 11:14:35 1234567890 0101010101 Answered 50

2011-04-26 09:10:20 1234567890 0201040506 Answered 50

2011-04-26 08:14:14 2345678910 1234567890 Answered 50

2011-04-25 11:14:35 1234567890 0101010101 Answered 50

2011-04-24 11:14:35 1234567890 0101010101 Answered 50

2011-04-23 08:14:14 101 1234567890 Answered 50

2011-04-22 11:14:35 103 101 Answered 50

2011-04-21 11:14:35 101 103 Answered 50

2011-04-21 11:14:35 101 2564114526 Answered 50

2011-04-21 11:14:35 1234567890 104 Answered 50

Now i have to calculate the sum of the call duration made by the end user. conditions are applied.

Psycho · April 27, 2011

OK, so where do keys1 and keys2 come from? If you are wanting to know the duration of calls made by a user (I assume the 'source' field), then just do a GROUP BY on the source column. Or, you can get the duration of calls for each source to each destination by using a combined GROUP BY clause on the two fields.

You still didn't provide the 2nd half of my request

Provide some examples of the data that would be in the database and the output you want generated.

So, I am just assuming all of this.

Here is an example of how you would get the combined duration for each unique source/destination combination

SELECT source, destination, SUM(CallDuration) as total_duration FROM table GROUP BY source, duration

NaniG · April 27, 2011

Provide some examples of the data that would be in the database

Date Source Destination Status CallDuration(sec)

2011-04-27 11:14:35 1234567890 0101010101 Answered 50

2011-04-26 09:10:20 1234567890 0201040506 Answered 50

2011-04-26 08:14:14 2345678910 1234567890 Answered 50

2011-04-25 11:14:35 1234567890 0101010101 Answered 50

2011-04-24 11:14:35 1234567890 0101010101 Answered 50

2011-04-23 08:14:14 101 1234567890 Answered 50

2011-04-22 11:14:35 103 101 Answered 50

2011-04-21 11:14:35 101 103 Answered 50

2011-04-21 11:14:35 101 2564114526 Answered 50

2011-04-21 11:14:35 1234567890 104 Answered 50

and the output was to generate like this....

select SUM(CallDuration) from table where Source='src' and Destination!='src';

src -> Numbers or Extenstions

I want get the sum of the calls duration other than the numbers and extenstion (ie out side numbers which are not in Grouped)

select SUM(CallDuration) from table where Source='1234567890' and Destination='0101010101'; (Outgoing from 1234567890 to 0101010101)

select SUM(CallDuration) from table where Source='0101010101' and Destination='1234567890'; (incoming from 0101010101 to 1234567890)

NaniG · April 27, 2011

Each Source value has to be compare with Destination value. If the Source value is equivalent to Destination value the sum of call duration is 0 other wise calculate the sum of the calls duration.

Psycho · April 27, 2011

Did you try the code I provided to see if it generates the results you are after?

NaniG · April 27, 2011

Let us assume, 'A' as a group with 5 Phone numbers. All the calls with the group (A) is FREE.

Ex:-

Group :- A

Telephone Numbers : 1234567890, 2345678910, 3456789120

Extensions : 101, 102, 103

if you make a call from 1234567890 to 234567890 Call cost / Duration is 0/FREE. or

if you make a call from 1234567890 to 101 Call cost / Duration is 0/FREE. or

if you make a call from 101 to 234567890 Call cost / Duration is 0/FREE.

if you make a call other then this numbers, then Group (A) will be charged.

i want to get this duration for the calls which are made out side of this group.

DavidAM · April 27, 2011

Do you have the Groups defined in a database table? And if so, can you post the table structure?

Psycho · April 27, 2011

Do you have the Groups defined in a database table? And if so, can you post the table structure?

Yeah, if you defined these groups in an associated table the process will be easy. Just use a group_id column and a number column (using the same ID for the numbers from the same group)

Example group_table showing data for a single group

group_id | number
1         1234567890
1         2345678910
1         3456789120
1         101
1         102
1         103

You could then modify the query like so

SELECT source, destination, SUM(CallDuration) as total_duration
FROM calls_table
JOIN group_table as g1 ON calls_table.Source = g1.number
JOIN group_table as g2 ON calls_table.Destination = g2.number
WHERE g1.group_id <> g2.group_id
GROUP BY source, duration

That *should* provide a list of every Source/Destination combination and the total duration of all calls WHERE the source and destination are not in the same group

DavidAM · April 27, 2011

You could then modify the query like so
SELECT source, destination, SUM(CallDuration) as total_duration
FROM calls_table
JOIN group_table as g1 ON calls_table.Source = g1.number
JOIN group_table as g2 ON calls_table.Destination = g2.number
WHERE g1.group_id <> g2.group_id
GROUP BY source, duration
That *should* provide a list of every Source/Destination combination and the total duration of all calls WHERE the source and destination are not in the same group

Or, if you really want the zeros:

SELECT source, destination, SUM(IF(g1.group_id = g2.group_id,0,CallDuration)) as total_duration
FROM calls_table
JOIN group_table as g1 ON calls_table.Source = g1.number
JOIN group_table as g2 ON calls_table.Destination = g2.number
GROUP BY source, duration

NaniG · April 29, 2011

Thanks DavidAM and mjdamato for reply,

SELECT source, destination, SUM(CallDuration) as total_duration

FROM calls_table

JOIN group_table as g1 ON calls_table.Source = g1.number

JOIN group_table as g2 ON calls_table.Destination = g2.number

WHERE g1.group_id <> g2.group_id

GROUP BY source, duration;

when i tried these query, i got an empty results rather than SUM(CallDuration).

Let me explain once again please

group_table:

username number

xyz 1234567890

xyz 2345678910

xyz 3456789120

xyz 101

xyz 102

xyz 103

calls_table:

Date Source Destination Status CallDuration(sec)

2011-04-27 11:14:35 1234567890 0101010101 Answered 50 ----> need duration

2011-04-26 09:10:20 1234567890 0201040506 Answered 50 ----> need duration

2011-04-26 08:14:14 2345678910 1234567890 Answered 50

2011-04-25 11:14:35 1234567890 0101010101 Answered 50 ----> need duration

2011-04-24 11:14:35 1234567890 0101010101 Answered 50 ----> need duration

2011-04-23 08:14:14 101 1234567890 Answered 50

2011-04-22 11:14:35 103 101 Answered 50

2011-04-21 11:14:35 101 103 Answered 50

2011-04-21 11:14:35 101 2564114526 Answered 50 ----> need duration

2011-04-21 11:14:35 1234567890 104 Answered 50

i want to get these SUM(CallDuration).

I used your query like these

SELECT source, destination, SUM(CallDuration) as total_duration

FROM calls_table

JOIN group_table as g1 ON calls_table.Source = g1.number

JOIN group_table as g2 ON calls_table.Destination = g2.number

WHERE g1.username <> g2.username

GROUP BY source, duration;

Please help me out as am getting headache on these issue.

Sorry on my language.

Psycho · April 29, 2011

First of all, that query would be failing since you are trying to group by columns called 'source' and 'duration' and the actual column names are 'Source' and 'CallDuration'.

Second, even if you fixed the names it would return an empty result set using the data you had above. The reason is that for the numbers that have no group code they would have a null value in the joined data.

By the way, based upon your stated logic, shouldn't that last record be included in your result set since 104 is not in the group list?

Anyway, after using that mock data I have a query that I think will work for you

SELECT source, destination, SUM(CallDuration) as total_duration
FROM calls_table
LEFT JOIN group_table as g1 ON calls_table.Source = g1.number
LEFT JOIN group_table as g2 ON calls_table.Destination = g2.number
WHERE g1.username <> g2.username
  OR g1.username IS NULL
  OR g2.username IS NULL
GROUP BY Source, destination

Results

source      destination  total_duration
101        2564114526   50
1234567890  0101010101   150
1234567890  0201040506   50
1234567890  104          50

If the Source values will always exist in the group table then you don't need the last OR statement.

Sign In

PHP Array

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information