PHP and Mysql report

[lexer]

Hi

I'm starting programming PHP/Mysql and I want to get information from 3 tables (Calls,Estructure, rates) These are the tables:

Calls
Id Type Ext Date Hour Elapse Number
53 S 236 03/15 08:29 17 2050199
54 S 236 03/15 08:31 12 5070399
55 S 220 03/16 08:31 5 5938619
56 E 204 03/16 08:32 1
57 S 210 03/16 08:33 1 5419223
58 S 210 03/16 08:39 1 5419223
59 E 0 03/16 08:40 1

Estructure
Ext Name Deparment
0 Ope. admin
204 HelpDesk IT
210 IT IT
220 Ext220 ope
236 Modem ope
223 HelpDesk IT

Rates
Number Carrier Cost
04 Mobil 20
02 National 15
00 Long Dist 30
2 Local 10
3 Local 10
5 Local 10

The table Calls have information coming from a PBX (Incoming calls are E type and outgoing are S type), What I want It's to bill the calls according to tables rates and estructure, for example for the first row in table calls:

Type Ext Name Date Hour Elapse Cost Number Carrier
S 236 Modem 03/15 08:29 17 170 2050199 Local

Can I filter information for more than one parameter, For example:
*I just want S type calls then order by extension
*Can I put the registers from a table in variables, for manipulating, cross tables Calls with rates to get the cost (multiply cost with Elapse) and the carrier, cross Table calls with estructure to get the name and the deparment.

This is my PHP program:


<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_db", $con);

// Construct our join query
$query = "SELECT calls.Ext, estructure.name, rates.number, calls.elapse, rates.carrier, rates.cost, estructure.deparment, calls.number ".
"FROM calls , estructure , rates ".
"WHERE calls.Ext = estructure.Ext and calls.number = rates.number";

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['Ext']. " - ". $row['name']. " - ". $row['deparment']. " - ". $row['number']. " - ". $row['carrier']. " - ". $row['cost'];
echo "<br />";
}
?>

 

[jpadie]

it looks like your question is really mysql related. i'd recommend you to the mysql forum.

 

[Karl Blessing]

Agreed with jpadie, Also when you do post in the MySQL forum, you'll likely want to do some research into JOIN clause that will join tables based on their matching parts to help format a better result.

ie:

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
[/quote]

Karl Blessing
[URL unfurl="true"]http://www.karlblessing.com[/URL][attach_link]http://dev.mysql.com/doc/refman/5.0/en/join.html[/attach_link]