Subtracting Values of fields

[willis30]

I have a DB with a table called building which is simply a list of building names with id's and another table with the fields ports_used, total ports. I would like to have another field called ports_free that will take the total ports and subtract ports_used for each building. (Using PHP)

So I would have say
Building A
10 ports
2 used
-------
There are 8 ports free in Building A

Any help would be appreciated.

[BigDaddy71]

Originally Posted by willis30

I have a DB with a table called building which is simply a list of building names with id's and another table with the fields ports_used, total ports. I would like to have another field called ports_free that will take the total ports and subtract ports_used for each building. (Using PHP)

So I would have say
Building A
10 ports
2 used
-------
There are 8 ports free in Building A

Any help would be appreciated.

Some databases will let you use math in your SQL statement, so you could do something like the following:

UPDATE building SET ports_free = (ports - ports_used) WHERE name = 'Building A';

You would have to use the above SQL statement every time you changed the number of ports being used (i.e. the ports_used field in the database). Otherwise you'd have to write some PHP code that figures out how many ports are free and then update the table.

Personally I wouldn't bother with the ports_free field. It's redundant as you can easily figure out how many ports are free with some simple math in your PHP code.

[willis30]

I agree that I should just be able to do it using php but for some reason I couldnt get it to even give me and error when trying that, so I thought I would take another approach.

Care to help with the PHP code?

All I need is
ports_total - ports = ports_free
for each record that has the same building associated with it. so I can have the total number of ports free for each building

Example
Building A
Room1 has 10 ports 2 being used
Room2 has 5 ports 4 being used = Building A has 9 ports free

Building B
Room1 has 10 ports 3 being used
Room2 has 5 ports 1 being used = Building B has 11 ports free

Hope that makes since.

[BigDaddy71]

Originally Posted by willis30

I agree that I should just be able to do it using php but for some reason I couldnt get it to even give me and error when trying that, so I thought I would take another approach.

Care to help with the PHP code?

All I need is
ports_total - ports = ports_free
for each record that has the same building associated with it. so I can have the total number of ports free for each building

Example
Building A
Room1 has 10 ports 2 being used
Room2 has 5 ports 4 being used = Building A has 9 ports free

Building B
Room1 has 10 ports 3 being used
Room2 has 5 ports 1 being used = Building B has 11 ports free

Hope that makes since.

Okay, quick-and-dirty PHP code here...

<?php
$con = mysql_connect("host","user","password");
mysql_select_db("dbname");
$sql = "SELECT ports_total, ports_used FROM building WHERE building_name = 'Building A';
$result = mysql_query($sql,$con);
list($ports_total,$ports_used) = mysql_fetch_row($result);
$ports_free = $ports_total - $ports_used;
?>

That is the code needed to figure out how many ports free in MySQL. I'm sure there are other ways but that's the 60 second version.