Unusual Query Help Request

[cov]

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('g eneric_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.equip name, conveyors.equip no,
conveyors.mcc, conveyors.gb, conveyors.lube, conveyors.gbsiz e,
conveyors.brgty pe, conveyors.brgqt y, motors.hp, motors.frame,
motors.amps, motors.rpm, equipcontacts.e quipmanu,
equipcontacts.s model, equipcontacts.s serial, equipcontacts.v endphone
FROM conveyors, motors, equipcontacts
WHERE conveyors.equip name = motors.equipnam e and
conveyors.equip name = equipcontacts.e quipname ";
if ($area != "All") $query .= "and (conveyors.area ='$area' or
motors.area='$a rea' or equipcontacts.a rea='$area')";
$result = mysql_query($qu ery);
----------------------------------

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

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

thanks for any replies.
cov

[Jerry Stuckle]

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('g eneric_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.equip name, conveyors.equip no,
conveyors.mcc, conveyors.gb, conveyors.lube, conveyors.gbsiz e,
conveyors.brgty pe, conveyors.brgqt y, motors.hp, motors.frame,
motors.amps, motors.rpm, equipcontacts.e quipmanu,
equipcontacts.s model, equipcontacts.s serial, equipcontacts.v endphone
FROM conveyors, motors, equipcontacts
WHERE conveyors.equip name = motors.equipnam e and
conveyors.equip name = equipcontacts.e quipname ";
if ($area != "All") $query .= "and (conveyors.area ='$area' or
motors.area='$a rea' or equipcontacts.a rea='$area')";
$result = mysql_query($qu ery);
----------------------------------

I would have hoped that linking the two columns within each table may
be as simple as:
WHERE conveyors.area. equipname = motors.area.equ ipname and
conveyors.area. equipname = equipcontacts.a rea.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.
js*******@attgl obal.net
=============== ===

[cov]

On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
<js*******@attg lobal.netwrote:

>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

[Michael Austin]

cov wrote:

On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
<js*******@attg lobal.netwrote:

>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)

[cov]

On Fri, 09 Nov 2007 22:20:38 -0600, Michael Austin
<no******@thist ime.infwrote:

>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'

[Jerry Stuckle]

cov wrote:

On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
<js*******@attg lobal.netwrote:

>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.
js*******@attgl obal.net
=============== ===

[Jerry Stuckle]

cov wrote:

On Fri, 09 Nov 2007 22:20:38 -0600, Michael Austin
<no******@thist ime.infwrote:

>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.
js*******@attgl obal.net
=============== ===

[cov]

On Sat, 10 Nov 2007 00:01:21 -0500, Jerry Stuckle
<js*******@attg lobal.netwrote:

>cov wrote:

>On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
<js*******@att global.netwrote :

>>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?

[Rik Wasmus]

On Sat, 10 Nov 2007 07:43:13 +0100, cov <co************ @yahoo.comwrote :

On Sat, 10 Nov 2007 00:01:21 -0500, Jerry Stuckle
<js*******@attg lobal.netwrote:

>cov wrote:

>>On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
<js*******@at tglobal.netwrot e:

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

[cov]

On Sat, 10 Nov 2007 18:13:17 +0100, "Rik Wasmus"
<lu************ @hotmail.comwro te:

>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