Posted by Jerry Stuckle on 11/10/07 16:39

cov wrote:
> 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
>

You've already asked this in comp.databases.mysql, which is where is
should be. But you haven't responded with the information we need to
help you.

This is a straight SQL question and has nothing to do with PHP.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]