Unusual Query of three tables

[cov]

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

[Jerry Stuckle]

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

[cov]

On Sat, 10 Nov 2007 11:39:57 -0500, Jerry Stuckle
<js*******@attglobal.netwrote:

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

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 key set. Zero decimals and allow null not set. thanks

[Jerry Stuckle]

cov wrote:

On Sat, 10 Nov 2007 11:39:57 -0500, Jerry Stuckle
<js*******@attglobal.netwrote:

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

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 key set. Zero decimals and allow null not set. thanks

As I said. This is not the correct newsgroup for MySQL questions.

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

[cov]

On Sat, 10 Nov 2007 15:18:40 -0500, Jerry Stuckle
<js*******@attglobal.netwrote:

>As I said. This is not the correct newsgroup for MySQL questions.

Not sure I understand why you would say that when it is php code
interfacing with mysql. The form code is all php/html so how that
query were written accordingly and interact with mysql, would
seemingly be entirely a php matter.

[AnrDaemon]

Greetings, cov.
In reply to Your message dated Sunday, November 11, 2007, 07:01:35,

>>As I said. This is not the correct newsgroup for MySQL questions.

Not sure I understand why you would say that when it is php code
interfacing with mysql.

Make sure You getting proper data from database before trying to use it in PHP code.
So, go to comp.database.mysql and refine Your MySQL knowledge.
(Guess You know how to use MySQL without PHP)
--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

[Jerry Stuckle]

cov wrote:

On Sat, 10 Nov 2007 15:18:40 -0500, Jerry Stuckle
<js*******@attglobal.netwrote:

>As I said. This is not the correct newsgroup for MySQL questions.

Not sure I understand why you would say that when it is php code
interfacing with mysql. The form code is all php/html so how that
query were written accordingly and interact with mysql, would
seemingly be entirely a php matter.

Because your question is purely SQL related. There is nothing here
which is related to PHP or any other programming language.

First rule of newsgroups - determine where you problem is and post to
the appropriate newsgroup. Second rule - give enough information for
people to help you with your problem.

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

[Jerry Stuckle]

BoneIdol wrote:

>
Ok I admit that I shot my mouth off regarding it only working properly
with a one-to-one relationship, but since it doesn't give any results
if a where clause isn't met it is (usually) a hinderance. Aside from
that, I feel I gave an informed and useful response, and I simply
refuse to fuel your infantile flaming.

No flaming. PHP is NOT MySQL and vice versa. There are many MySQL
experts - including people on the MySQL design team - who monitor
comp.databases.mysql. Many of them do not monitor this newsgroup.

The best place to get MySQL answers is in that group. I would say the
vast majority of the people here are amateurs with MySQL compared to the
people in that newsgroup.

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