Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
jglicken
Forum Newbie
Posts: 22 Joined: Mon Jul 26, 2004 9:58 am
Post
by jglicken » Mon Jul 26, 2004 9:58 am
Code: Select all
+-------------------------+
| Tables_in_cdrdb |
+-------------------------+
| cdr |
| rates |
+-------------------------+
2 rows in set (0.00 sec)
mysql> describe cdr;
+-------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| calldate | datetime | | | 0000-00-00 00:00:00 | |
| src | varchar(80) | | | | |
| dst | varchar(80) | | | | |
| billsec | int(11) | | | 0 | |
| userfield | varchar(255) | | | | |
+-------------+--------------+------+-----+---------------------+-------+
16 rows in set (0.00 sec)
one row from cdr
| 2004-07-16 13:27:25 | jason | 13055551212 | 11 | |
there are more rows, but these are the pertinant onesl
mysql> describe rates;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| destination | varchar(25) | | | | |
| name | varchar(250) | | | | |
| rate | varchar(25) | | | | |
| connectCost | varchar(25) | | | | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
one row from rates
| 1 | Domestic | 00.005 | 00.005 |
My goal is to look at 'dst' from table cdr and match this on 'destination' from rates. if
match is found output = calldate, src, dst, name, (billsec * rate + connect cost).
my first problem is matching dst with destination. all dst with '1%' will have same rate, but
table will contain country codes of all countries so i need to match dst and destination
based on first few characters.
then i need to do the math.
should i create 1 big table with all information, will this make it easier?
thank you for your help.
jason
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Mon Jul 26, 2004 11:00 am
Code: Select all
SELECT a.`calldate`, a.`src`, a.`dst`, b.`name`, (a.`billsec` * b.`rate` + b.`connectCost`) `price`
FROM `cdr` a
LEFT JOIN `rates` b
ON a.`dst` = b.`destination`
jglicken
Forum Newbie
Posts: 22 Joined: Mon Jul 26, 2004 9:58 am
Post
by jglicken » Tue Jul 27, 2004 10:28 am
thank you so much, that gets the first part of my problem. I can get all the records based on src from cdr table with this query, but it only matches on exact matches from rates table.
Code: Select all
mysql> select * from rates;
+-------------+------------+--------+-------------+
| destination | name | rate | connectCost |
+-------------+------------+--------+-------------+
| 1 | Domestic | 00.005 | 00.005 |
| 511 | Lima Peru | 00.020 | 00.020 |
| 1111 | voicemail | 5 | 5 |
| 1305 | Miami, Fl. | 0.005 | 0.005 |
+-------------+------------+--------+-------------+
4 rows in set (0.00 sec)
I only get a match on voicemail '1111' because that is the exact number from dst on cdr. I need to match '13055551212' with '1305' in rates?
thank you in advance for your help.
Jason
jglicken
Forum Newbie
Posts: 22 Joined: Mon Jul 26, 2004 9:58 am
Post
by jglicken » Tue Jul 27, 2004 2:12 pm
Code: Select all
mysql> select a.calldate, a.src, a.dst, b.name, a.billsec, (a.billsec * b.rate + b.connectCost) totalcost from cdr a left join rates b on a.dst = b.destination regexp '^1';
this query returns all rows?
could it be that i am trying to make a match with a 1 character field?
jason
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Tue Jul 27, 2004 2:33 pm
I don't think the regexp is doing anything..
jglicken
Forum Newbie
Posts: 22 Joined: Mon Jul 26, 2004 9:58 am
Post
by jglicken » Wed Jul 28, 2004 2:09 pm
I can get the results I want matching all '1's from one table with '1' from the other table, but i am really stumped with getting my pattern matching working.
i can get the results individually for the match, but i don't know how to get the join to get me the results i need like '511' = '511*' ?
Code: Select all
mysql> select substring(dst,4) from cdr where dst like '011%' and src='erick';
+------------------+
| substring(dst,4) |
+------------------+
| 5114220165 |
| 584148681837 |
| 584142869237134 |
| 584142869237134 |
| 582869237134 |
| 582869237134 |
| 582869237134 |
| 582869237134 |
| 582869237134 |
| 5114229804 |
| 582869237134 |
| 5058885694 |
| 5058885694 |
| 5058885694 |
| 5058885694 |
+------------------+
15 rows in set (0.06 sec)
mysql> select * from rates where destination regexp '^ї2-9]';
+-------------+------------------+------+-------------+
| destination | name | rate | connectCost |
+-------------+------------------+------+-------------+
| 511 | Peru Lima | 0.1 | 0.05 |
| 58414 | Venezuela Mobile | 0.2 | 0.05 |
+-------------+------------------+------+-------------+
2 rows in set (0.00 sec)
this is what i have so far, but it always matches with my destination '1'.
Code: Select all
mysql> select a.calldate, a.src, b.destination, a.dst, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on a.dst regexp '^011' = b.destination where dst regexp '^011' and src='erick' order by 'calldate' desc;
+---------------------+-------+-------------+--------------------+--------------------+--------------+----------+-----------+
| calldate | src | destination | dst | substring(a.dst,4) | name | billsec | totalcost |
+---------------------+-------+-------------+--------------------+--------------------+--------------+----------+-----------+
| 2004-07-27 10:52:52 | erick | 1 | 0115058885694 | 5058885694 | Domestic USA | 00:00:00 | 0.05 |
| 2004-07-27 10:52:23 | erick | 1 | 0115058885694 | 5058885694 | Domestic USA | 00:00:00 | 0.05 |
| 2004-07-27 10:51:13 | erick | 1 | 0115058885694 | 5058885694 | Domestic USA | 00:00:00 | 0.05 |
| 2004-07-27 10:46:45 | erick | 1 | 0115058885694 | 5058885694 | Domestic USA | 00:00:00 | 0.05 |
| 2004-07-03 13:47:34 | erick | 1 | 011582869237134 | 582869237134 | Domestic USA | 00:00:48 | 0.09 |
| 2004-07-03 13:46:01 | erick | 1 | 0115114229804 | 5114229804 | Domestic USA | 00:01:50 | 0.14 |
| 2004-06-30 00:10:42 | erick | 1 | 011582869237134 | 582869237134 | Domestic USA | 00:19:16 | 1.01 |
| 2004-06-29 23:50:15 | erick | 1 | 011582869237134 | 582869237134 | Domestic USA | 00:00:51 | 0.09 |
| 2004-05-09 00:11:40 | erick | 1 | 011582869237134 | 582869237134 | Domestic USA | 00:03:18 | 0.22 |
| 2004-04-17 13:52:56 | erick | 1 | 011582869237134 | 582869237134 | Domestic USA | 00:01:44 | 0.14 |
| 2004-04-17 13:50:20 | erick | 1 | 011582869237134 | 582869237134 | Domestic USA | 00:00:00 | 0.05 |
| 2004-04-17 13:48:21 | erick | 1 | 011584142869237134 | 584142869237134 | Domestic USA | 00:00:00 | 0.05 |
| 2004-04-17 13:45:23 | erick | 1 | 011584142869237134 | 584142869237134 | Domestic USA | 00:00:00 | 0.05 |
| 2004-04-16 18:51:01 | erick | 1 | 011584148681837 | 584148681837 | Domestic USA | 00:01:16 | 0.11 |
| 2004-04-16 10:23:00 | erick | 1 | 0115114220165 | 5114220165 | Domestic USA | 00:00:03 | 0.05 |
+---------------------+-------+-------------+--------------------+--------------------+--------------+----------+-----------+
15 rows in set (0.07 sec)
thanks in advance,
Jason
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Wed Jul 28, 2004 2:33 pm
a.dst regexp '^011' will return true (1) or false(0)
jglicken
Forum Newbie
Posts: 22 Joined: Mon Jul 26, 2004 9:58 am
Post
by jglicken » Wed Jul 28, 2004 2:36 pm
but this one returns what i am looking for
Code: Select all
mysql> select * from rates where destination regexp '^ї2-9]';
+-------------+------------------+------+-------------+
| destination | name | rate | connectCost |
+-------------+------------------+------+-------------+
| 511 | Peru Lima | 0.1 | 0.05 |
| 58414 | Venezuela Mobile | 0.2 | 0.05 |
+-------------+------------------+------+-------------+
2 rows in set (0.00 sec)
do you have any suggestions on how to put my 2 queries together so destination matches dst and display the correct name?
thanks
Jaosn
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Wed Jul 28, 2004 2:49 pm
Code: Select all
select a.calldate, a.src, b.destination, a.dst, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on b.destination regexp concat('^', a.dst) where src='erick' order by 'calldate' desctry that.
jglicken
Forum Newbie
Posts: 22 Joined: Mon Jul 26, 2004 9:58 am
Post
by jglicken » Wed Jul 28, 2004 2:53 pm
Code: Select all
mysql> select a.calldate, a.src, b.destination, a.dst, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on b.destination regexp (concat('^', a.dst) where src='erick' order by 'calldate' desc;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where src='erick' order by 'calldate' desc' at line 1
i get an error?
jglicken
Forum Newbie
Posts: 22 Joined: Mon Jul 26, 2004 9:58 am
Post
by jglicken » Wed Jul 28, 2004 2:57 pm
Code: Select all
mysql> select a.calldate, a.src, b.destination, a.dst, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on b.destination regexp (concat('^', a.dst)) where src='erick' and dst like '011%' order by 'calldate' desc;
+---------------------+-------+-------------+--------------------+--------------------+------+----------+-----------+
| calldate | src | destination | dst | substring(a.dst,4) | name | billsec | totalcost |
+---------------------+-------+-------------+--------------------+--------------------+------+----------+-----------+
| 2004-07-27 10:52:52 | erick | NULL | 0115058885694 | 5058885694 | NULL | 00:00:00 | NULL |
| 2004-07-27 10:52:23 | erick | NULL | 0115058885694 | 5058885694 | NULL | 00:00:00 | NULL |
| 2004-07-27 10:51:13 | erick | NULL | 0115058885694 | 5058885694 | NULL | 00:00:00 | NULL |
| 2004-07-27 10:46:45 | erick | NULL | 0115058885694 | 5058885694 | NULL | 00:00:00 | NULL |
| 2004-07-03 13:47:34 | erick | NULL | 011582869237134 | 582869237134 | NULL | 00:00:48 | NULL |
| 2004-07-03 13:46:01 | erick | NULL | 0115114229804 | 5114229804 | NULL | 00:01:50 | NULL |
| 2004-06-30 00:10:42 | erick | NULL | 011582869237134 | 582869237134 | NULL | 00:19:16 | NULL |
| 2004-06-29 23:50:15 | erick | NULL | 011582869237134 | 582869237134 | NULL | 00:00:51 | NULL |
| 2004-05-09 00:11:40 | erick | NULL | 011582869237134 | 582869237134 | NULL | 00:03:18 | NULL |
| 2004-04-17 13:52:56 | erick | NULL | 011582869237134 | 582869237134 | NULL | 00:01:44 | NULL |
| 2004-04-17 13:50:20 | erick | NULL | 011582869237134 | 582869237134 | NULL | 00:00:00 | NULL |
| 2004-04-17 13:48:21 | erick | NULL | 011584142869237134 | 584142869237134 | NULL | 00:00:00 | NULL |
| 2004-04-17 13:45:23 | erick | NULL | 011584142869237134 | 584142869237134 | NULL | 00:00:00 | NULL |
| 2004-04-16 18:51:01 | erick | NULL | 011584148681837 | 584148681837 | NULL | 00:01:16 | NULL |
| 2004-04-16 10:23:00 | erick | NULL | 0115114220165 | 5114220165 | NULL | 00:00:03 | NULL |
+---------------------+-------+-------------+--------------------+--------------------+------+----------+-----------+
15 rows in set (0.06 sec)
i was missing a bracket
, but i get NULL results with this query?
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Wed Jul 28, 2004 3:00 pm
hmm make try
Code: Select all
select a.calldate, a.src, b.destination, a.dst, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on a.dst regexp (concat('^', b.destination)) where src='erick' and dst like '011%' order by 'calldate' desc
jglicken
Forum Newbie
Posts: 22 Joined: Mon Jul 26, 2004 9:58 am
Post
by jglicken » Wed Jul 28, 2004 3:04 pm
Code: Select all
mysql> select a.calldate, a.src, b.destination, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on b.destination regexp (concat('^', b.destination)) where src='erick' and dst like '011%' order by 'calldate' desc;
+---------------------+-------+-------------+--------------------+------------------+----------+-----------+
| calldate | src | destination | substring(a.dst,4) | name | billsec | totalcost |
+---------------------+-------+-------------+--------------------+------------------+----------+-----------+
| 2004-07-27 10:52:52 | erick | 1 | 5058885694 | Domestic USA | 00:00:00 | 0.05 |
| 2004-07-27 10:52:52 | erick | 511 | 5058885694 | Peru Lima | 00:00:00 | 0.05 |
| 2004-07-27 10:52:52 | erick | 58414 | 5058885694 | Venezuela Mobile | 00:00:00 | 0.05 |
| 2004-07-27 10:52:23 | erick | 1 | 5058885694 | Domestic USA | 00:00:00 | 0.05 |
| 2004-07-27 10:52:23 | erick | 511 | 5058885694 | Peru Lima | 00:00:00 | 0.05 |
| 2004-07-27 10:52:23 | erick | 58414 | 5058885694 | Venezuela Mobile | 00:00:00 | 0.05 |
| 2004-07-27 10:51:13 | erick | 1 | 5058885694 | Domestic USA | 00:00:00 | 0.05 |
| 2004-07-27 10:51:13 | erick | 511 | 5058885694 | Peru Lima | 00:00:00 | 0.05 |
| 2004-07-27 10:51:13 | erick | 58414 | 5058885694 | Venezuela Mobile | 00:00:00 | 0.05 |
| 2004-07-27 10:46:45 | erick | 1 | 5058885694 | Domestic USA | 00:00:00 | 0.05 |
| 2004-07-27 10:46:45 | erick | 511 | 5058885694 | Peru Lima | 00:00:00 | 0.05 |
| 2004-07-27 10:46:45 | erick | 58414 | 5058885694 | Venezuela Mobile | 00:00:00 | 0.05 |
| 2004-07-03 13:47:34 | erick | 1 | 582869237134 | Domestic USA | 00:00:48 | 0.09 |
| 2004-07-03 13:47:34 | erick | 511 | 582869237134 | Peru Lima | 00:00:48 | 0.13 |
| 2004-07-03 13:47:34 | erick | 58414 | 582869237134 | Venezuela Mobile | 00:00:48 | 0.21 |
| 2004-07-03 13:46:01 | erick | 1 | 5114229804 | Domestic USA | 00:01:50 | 0.14 |
| 2004-07-03 13:46:01 | erick | 511 | 5114229804 | Peru Lima | 00:01:50 | 0.23 |
| 2004-07-03 13:46:01 | erick | 58414 | 5114229804 | Venezuela Mobile | 00:01:50 | 0.42 |
now i get 1 result for each entry in my rates table. i think you are on the right track. any ideas?
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Wed Jul 28, 2004 3:09 pm
copy my entire query.. you didn't copy it right.