PHP MySQL report system

Question

LRNPHP 0 Junior Poster in Training

13 Years Ago

Hi There,

I have 2 tables, one with dst codes and one with fees. I want to match the fees with the dst code. I've been trying a for loop but it's not working 100%.

What is the best way of matching dst code with the corresponding fee? If TABLE A (dst_code 011) matches TABLE B's (fee 011) then the cost will be 0.38 etc...

[U]TABLE A[/U]
dst_code 011
dst code 012
dst_code 013
 
[U]TABLE B[/U]
fee 011 0.38
fee 012 0.55
fee 013 0.55

Kind Regards

mysql php

3 Contributors
18 Replies
158 Views
12 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by LRNPHP

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

1) what are columns name of both tables?
2) Is you table 2 holding value as 'fee 011' (will it always have fee prefix in all records) or table 2 is holding 011, 012 like that

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

SELECT * FROM billing order by tbl_code
SELECT * FROM  fees order by tbl_fee

You run this 2 queries separately and post both exact results here (few top rows)

Edited 13 Years Ago by urtrivedi because: n/a

broj1 356 Humble servant

13 Years Ago

If you add an area code as a foreign key to billing table then this query might help you:

SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price FROM `fees` RIGHT JOIN billing ON `tbl_code`=`tbl_code_fk`

Note that table names are omitted from the query for simplicity. Also a field name for date is maybe not a good idea since it is mysql function name and can cause errors.

broj1 356 Humble servant

13 Years Ago

Apart from slight size increase you will get some redundancy since area code is stored in two fields (not a problem if area codes do not change, but you never know - from this perspective the tbl_id is better candidate for a foreign key) Your destination number could be without the area code, only the local phone number but I doubt you can change this now. You can add a foreign key with an UPDATE query so number of records should not be a problem if there arent too many area codes. You can prepare a php script to run the query.

Edited 13 Years Ago by broj1 because: n/a

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

Following query may work but this may cause to slow down performance.

SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price FROM  billing left JOIN `fees`  ON  substring(destination,1,length(tbl_code)) =tbl_code

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

broj1 356 Humble servant Featured Poster · Answer 1 · 2011-09-13T12:53:54+00:00

Introduce a foreign key in Table B which then have two fields: dst_code_fk with values 011, 012, 013... and fee with values 0.38, 0.55, 0.55... Then use join and if you want to find out a fee for dst_code 011 do it like this:

SELECT table_a.fee FROM table_a LEFT JOIN table_b ON table_a.dst_code=table_b.dst_code_fk WHERE table_a.dst_code='011'

The join type (left, right, inner, outer) depends of structure of your data. There are some nice examples of joins on the web.

LRNPHP 0 Junior Poster in Training · Answer 2 · 2011-09-13T12:57:15+00:00

1) what are columns name of both tables?
2) Is you table 2 holding value as 'fee 011' (will it always have fee prefix in all records) or table 2 is holding 011, 012 like that

Hi There,

1. table A name = billing
   table B name = fees

2. tbl_code, Field (This is the primary TABLE where all the data is stored)
        011
        012
        013

   tbl_fee, tbl_cost Field (This is a TABLE I created to calculate my Cost)
        011, 0.38
        012, 0,55
        013, 0,55

Hope this makes more sense. Thanks for you assistance.

Kind Regards

broj1 356 Humble servant Featured Poster · Answer 3 · 2011-09-13T13:08:52+00:00

I hope this is what you are looking for:

SELECT fees.tbl_cost FROM billing LEFT JOIN fees ON billing.tbl_code=fees.tbl_fee WHERE billing.tbl_code='011'

LRNPHP 0 Junior Poster in Training · Answer 4 · 2011-09-13T13:11:02+00:00

I hope this is what you are looking for:

SELECT fees.tbl_cost FROM billing LEFT JOIN fees ON billing.tbl_code=fees.tbl_fee WHERE billing.tbl_code='011'

Hi Broj1,

When I run your command it show the same result the whole time.

Kind Regards

urtrivedi 276 Nearly a Posting Virtuoso · Answer 5 · 2011-09-13T13:16:05+00:00

When I run your command it show the same result the whole time.

What do you mean by that, The query is perfect.

LRNPHP 0 Junior Poster in Training · Answer 6 · 2011-09-13T13:18:59+00:00

I hope this is what you are looking for:

SELECT fees.tbl_cost FROM billing LEFT JOIN fees ON billing.tbl_code=fees.tbl_fee WHERE billing.tbl_code='011'

Hi Broj1,

SELECT fees.tbl_cost FROM billing LEFT JOIN fees ON billing.tbl_code=fees.tbl_fee WHERE billing.tbl_code='011' limit 0,10; 

This is my output when running your MySQL query.

+---------------------+------+---------+---------+
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
+---------------------+------+---------+---------+

Thanks for everyone's assistance.

Kind Regards

LRNPHP 0 Junior Poster in Training · Answer 7 · 2011-09-13T13:25:37+00:00

What do you mean by that, The query is perfect.

Hi Urtrivedi,

SELECT fees.tbl_cost FROM billing LEFT JOIN fees ON billing.tbl_code=fees.tbl_fee WHERE billing.tbl_code='011' limit 0,10; 

This is my output when running your MySQL query.
+---------------------+------+---------+---------+
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
| 2011-08-16 18:20:54 | xxxx | Unknown |       7 |
+---------------------+------+---------+---------+

LRNPHP 0 Junior Poster in Training · Answer 8 · 2011-09-13T13:40:40+00:00

SELECT * FROM billing order by tbl_code
SELECT * FROM  fees order by tbl_fee
You run this 2 queries separately and post both exact results here (few top rows)

Hi Urtrivedi,

select * from billing limit 0,10;
+---------------------+-------------+---------+
| date                | destination | callsec |
+---------------------+-------------+---------+
| 2011-08-17 07:56:16 | 0116895596  |     253 |
| 2011-08-17 08:14:48 | 0124215665  |      61 |
| 2011-08-17 08:16:59 | 0116904299  |       7 |
| 2011-08-17 08:15:54 | 01144778000 |     112 |
| 2011-08-17 08:19:44 | 0124215665  |      19 |
| 2011-08-17 08:17:37 | 0152961509  |     185 |
| 2011-08-17 08:20:01 | 0136933833  |     228 |
| 2011-08-17 08:28:26 | 0118004607  |       0 |
| 2011-08-17 08:37:47 | 01242153    |       0 |
| 2011-08-17 08:38:53 | 0178014749  |       5 |
+---------------------+-------------+---------+
10 rows in set (0.00 sec)

select * from fees limit 0,10;
+--------+---------------+----------+----------+
| tbl_id | tbl_area      | tbl_code | tbl_cost |
+--------+---------------+----------+----------+
|   1269 | Aberdeen      |  049     |  0.55    |
|   1270 | Aberfeldy     |  058     |  0.55    |
|   1271 | Acornhoek     |  013     |  0.55    |
|   1272 | Addo          |  042     |  0.55    |
|   1273 | Adelaide      |  046     |  0.55    |
|   1274 | Aggeneys      |  054     |  0.55    |
|   1275 | Albertinia    |  028     |  0.55    |
|   1277 | Alettasrus    |  053922  |  0.55    |
|   1278 | Alexander Bay |  027     |  0.55    |
|   1279 | Alexandria    |  046     |  0.55    |
+--------+---------------+----------+----------+
10 rows in set (0.00 sec)

Kind Regards

broj1 356 Humble servant Featured Poster · Answer 9 · 2011-09-13T14:41:12+00:00

If I get it right you have two tables for billing phone calls. The fees table has area codes and cost for each area code with tbl_id being primary key, the billing table holds data about calls made. The thing is that the tables are not linked with the same data, which means you should have a foreign key in the billing table (eg. tbl_id or tbl_code if it is unique). The area code (tbl_code) is actualy part of destination field but since it is not the same lenght in all records, you can't use a mysql SUBSTRING function to extract it. I would add a foreign key to the billing table (an area code if it is unique or tbl_id). Then the JOIN can be made.

LRNPHP 0 Junior Poster in Training · Answer 10 · 2011-09-13T15:11:34+00:00

If you add an area code as a foreign key to billing table then this query might help you:
SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price FROM `fees` RIGHT JOIN billing ON `tbl_code`=`tbl_code_fk`
Note that table names are omitted from the query for simplicity. Also a field name for date is maybe not a good idea since it is mysql function name and can cause errors.

Hi Broj1,

How will this affect my current billing table? I have more than 20 000 records in the billing table all ready.

Kind Regards

LRNPHP 0 Junior Poster in Training · Answer 11 · 2011-09-13T16:44:33+00:00

Following query may work but this may cause to slow down performance.

SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price FROM  billing left JOIN `fees`  ON  substring(destination,1,length(tbl_code)) =tbl_code

Hi Urtrivedi,

I'm getting NULL values running your query.

+---------------------+-------------+---------+----------+-------+
| date                | destination | callsec | tbl_cost | price |
+---------------------+-------------+---------+----------+-------+
| 2011-08-16 18:20:54 | 8300        |       7 | NULL     |  NULL |
| 2011-08-16 18:21:49 | s           |       4 | NULL     |  NULL |
| 2011-08-16 18:22:00 | s           |      10 | NULL     |  NULL |
| 2011-08-16 18:25:06 | 8335        |       0 | NULL     |  NULL |
| 2011-08-16 18:25:52 | 8300        |       0 | NULL     |  NULL |
| 2011-08-16 18:31:25 | 8320        |       0 | NULL     |  NULL |
| 2011-08-16 18:42:39 | 0832301392  |       5 | NULL     |  NULL |
| 2011-08-16 18:50:06 | 0823283373  |       0 | NULL     |  NULL |
| 2011-08-16 18:54:54 | 8334        |       0 | NULL     |  NULL |
| 2011-08-16 21:54:58 | 8334        |       0 | NULL     |  NULL |
+---------------------+-------------+---------+----------+-------+
10 rows in set (0.00 sec)

Kind Regards

urtrivedi 276 Nearly a Posting Virtuoso · Answer 12 · 2011-09-13T16:53:05+00:00

SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price,
trim(substring(destination,1,length(tbl_code))) billcode , trim(tbl_code) feecode
 FROM  billing left JOIN `fees`  ON  trim(substring(destination,1,length(tbl_code))) =trim(tbl_code)

1) What is output of this query?

2) check Do you have code starting with 083 or 83 in fees table?

LRNPHP 0 Junior Poster in Training · Answer 13 · 2011-09-13T18:21:23+00:00

SELECT `date`,`destination`,`callsec`,`tbl_cost`,`callsec`*`tbl_cost` AS price,
trim(substring(destination,1,length(tbl_code))) billcode , trim(tbl_code) feecode
 FROM  billing left JOIN `fees`  ON  trim(substring(destination,1,length(tbl_code))) =trim(tbl_code)

1) What is output of this query?

2) check Do you have code starting with 083 or 83 in fees table?

Hi Urtrivedi,

I had a space before and after my tbl_code, removed spaces and now everything is working 100%.
Thanks for all your and Broj1's help in assisting me today. Have a great day.

Kind Regards