rendered paste bodyCDR table written by the CDR app
mysql> describe cdr;
+-------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| uniqueid | varchar(32) | | | | |
| userfield | varchar(255) | | | | |
| accountcode | varchar(20) | | | | |
| src | varchar(80) | | | | |
| dst | varchar(80) | | MUL | | |
| dcontext | varchar(80) | | | | |
| clid | varchar(80) | | | | |
| channel | varchar(80) | | | | |
| dstchannel | varchar(80) | | | | |
| lastapp | varchar(80) | | | | |
| lastdata | varchar(80) | | | | |
| calldate | datetime | | MUL | 0000-00-00 00:00:00 | |
| duration | int(11) | | | 0 | |
| billsec | int(11) | | | 0 | |
| disposition | varchar(45) | | | | |
| amaflags | int(11) | | | 0 | |
+-------------+--------------+------+-----+---------------------+-------+
Second table written by CID_REWRITE version 1.4
mysql> describe callerid;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| cid_id | int(11) | | PRI | NULL | auto_increment |
| cid_number | varchar(50) | | MUL | | |
| cid_name | varchar(50) | YES | | NULL | |
| display_name | varchar(50) | YES | | NULL | |
| action | char(1) | YES | | R | |
| address | varchar(100) | | | | |
| cid_source | char(1) | | | I | |
+--------------+--------------+------+-----+---------+----------------+
I want to build a nice php front end that show all numbers that exist in cdr.SRC (and if possible) cdr.DST but not in callerid.cid_number
Query result should be one column with SRC/DST (or two seperate ones for SRC and DST)
I can always it fix it by doing a lot of queries, but it's probably possible with one nice query :-)
bas@brijn.nu