Posted by cov on 11/10/07 15:48

I have a php query where I'm attempting to pull data from 3 different
tables between a php form and mysql db. I had hoped early on to use a
unique identifier to help ensure referential integrity between table
data but it appears that unique number isn't viable so I going a
different route.

I have two similar columns in these tables 'area' and 'equipment' that
I'd like to use as the unique identifier (when used together ) since
though there is a possibility of having two different pieces of
equipment within two different areas named by the same thing, the
possibility of having two pieces of equipment named the same thing
within two diffent areas won't happen SO if I can link the 'area'
column with 'equpment', I'll have my unique id

Below is what my existing code looks like that works but doesn't link
the 'area' column of a table to the 'equipment' column. Didn't notice
a problem until several entries to the db. Now I can see that I must
link the two columns within the individual tables to form a unique
identifier for those particular table columns within the different
three tables of the db.

<?php
require_once('generic_connect.php');
$DBname = "Equipment";
$area = $_POST['area'];

mysql_connect($DBhost, $DBuser, $DBpass) or die("Unable to connect to
host $DBhost");
mysql_select_db($DBname) or die("Unable to select database $DBname");

$query = "SELECT conveyors.equipname, conveyors.equipno,
conveyors.mcc, conveyors.gb, conveyors.lube, conveyors.gbsize,
conveyors.brgtype, conveyors.brgqty, motors.hp, motors.frame,
motors.amps, motors.rpm, equipcontacts.equipmanu,
equipcontacts.smodel, equipcontacts.sserial, equipcontacts.vendphone
FROM conveyors, motors, equipcontacts
WHERE conveyors.equipname = motors.equipname and
conveyors.equipname = equipcontacts.equipname ";
if ($area != "All") $query .= "and (conveyors.area='$area' or
motors.area='$area' or equipcontacts.area='$area')";
$result = mysql_query($query);
----------------------------------

I would have hoped that linking the two columns within each table may
be as simple as:
WHERE conveyors.area.equipname = motors.area.equipname and
conveyors.area.equipname = equipcontacts.area.equipname ";

but no such luck. My tables are 'conveyors', 'motors' and
'equipcontacts'.

thanks
cov

[Back to original message]