Unusual Query Help Request

I have a query where I'm attempting to pull data from 3 different
tables using php and mysql. I had hoped to have a unique identifier
to help ensure referential integrity but it appears that unique number
won't always be a possibility so I won't bank on it at all.

I have two like fields in these two tables 'area' and 'equipment' and
though there is a possibility of having two different pieces of
equipment within two areas called by the same thing, the possibility
of having two pieces of equipment called the same thing if I can link
to that 'area' column is impossible, hence 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 rows within the three tables.

------------------------------------------
<?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 for any replies.
cov

cov wrote:
> I have a query where I'm attempting to pull data from 3 different
> tables using php and mysql. I had hoped to have a unique identifier
> to help ensure referential integrity but it appears that unique number
> won't always be a possibility so I won't bank on it at all.
>
> I have two like fields in these two tables 'area' and 'equipment' and
> though there is a possibility of having two different pieces of
> equipment within two areas called by the same thing, the possibility
> of having two pieces of equipment called the same thing if I can link
> to that 'area' column is impossible, hence 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 rows within the three tables.
>
> ------------------------------------------
> <?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 for any replies.
> cov
>

Table definitions would help...

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

On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
<jstucklex@attglobal.net> wrote:

>Table definitions would help...

Table type is innoDB, character set is utf-u unicode, and fields are
varchar. Each table the same where 'type' is concerned and 'area' is
always the column next to 'equipment'. Table's were originated using
default settings with phpMyAdmin 2.6.2 thanks

cov wrote:
> On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>
>> Table definitions would help...
>
> Table type is innoDB, character set is utf-u unicode, and fields are
> varchar. Each table the same where 'type' is concerned and 'area' is
> always the column next to 'equipment'. Table's were originated using
> default settings with phpMyAdmin 2.6.2 thanks

He meant what are the column names in the tables... along with the
datatype of each column (describe <tablename> )

On Fri, 09 Nov 2007 22:20:38 -0600, Michael Austin
<notknown@thistime.inf> wrote:


>He meant what are the column names in the tables... along with the
>datatype of each column (describe <tablename> )


Ahh, thanks... :-)

table names are 'conveyors', 'motors', 'equipcontacts'

cov wrote:
> On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>
>> Table definitions would help...
>
> Table type is innoDB, character set is utf-u unicode, and fields are
> varchar. Each table the same where 'type' is concerned and 'area' is
> always the column next to 'equipment'. Table's were originated using
> default settings with phpMyAdmin 2.6.2 thanks
>

Which isn't your table definitions, and tells me absolutely nothing
about your problem.

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

cov wrote:
> On Fri, 09 Nov 2007 22:20:38 -0600, Michael Austin
> <notknown@thistime.inf> wrote:
>
>
>> He meant what are the column names in the tables... along with the
>> datatype of each column (describe <tablename> )
>
>
> Ahh, thanks... :-)
>
> table names are 'conveyors', 'motors', 'equipcontacts'
>

Which still tells me nothing...

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

On Sat, 10 Nov 2007 00:01:21 -0500, Jerry Stuckle
<jstucklex@attglobal.net> wrote:

>cov wrote:
>> On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
>> <jstucklex@attglobal.net> wrote:
>>
>>> Table definitions would help...
>>
>> Table type is innoDB, character set is utf-u unicode, and fields are
>> varchar. Each table the same where 'type' is concerned and 'area' is
>> always the column next to 'equipment'. Table's were originated using
>> default settings with phpMyAdmin 2.6.2 thanks
>>
>
>Which isn't your table definitions, and tells me absolutely nothing
>about your problem.

Perhaps you could help. How might I find the table definitions?

On Sat, 10 Nov 2007 07:43:13 +0100, cov <coverlandNS914@yahoo.com> wrote:

> On Sat, 10 Nov 2007 00:01:21 -0500, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>
>> cov wrote:
>>> On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
>>> <jstucklex@attglobal.net> wrote:
>>>
>>>> Table definitions would help...
>>>
>>> Table type is innoDB, character set is utf-u unicode, and fields are
>>> varchar. Each table the same where 'type' is concerned and 'area' is
>>> always the column next to 'equipment'. Table's were originated using
>>> default settings with phpMyAdmin 2.6.2 thanks
>>>
>>
>> Which isn't your table definitions, and tells me absolutely nothing
>> about your problem.
>
> Perhaps you could help. How might I find the table definitions?

DESCRIBE tablename;
--
Rik Wasmus

On Sat, 10 Nov 2007 18:13:17 +0100, "Rik Wasmus"
<luiheidsgoeroe@hotmail.com> wrote:


>DESCRIBE tablename;

Each table has an id field that is an INT - this field auto-increments
and is the primary key field for each table. All others are varchar
25 limit w/no other key set. Zero decimals and allow null not set.
thanks