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

[Jerry Stuckle]

Post by 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('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
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...

[cov]

On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle

Post by Jerry Stuckle
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]

Post by cov
On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle

Post by Jerry Stuckle
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

Post by Michael Austin
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]

Post by cov
On Fri, 09 Nov 2007 22:20:38 -0600, Michael Austin

Post by Michael Austin
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...

[Jerry Stuckle]

Post by cov
On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle

Post by Jerry Stuckle
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.

[cov]

On Sat, 10 Nov 2007 00:01:21 -0500, Jerry Stuckle

Post by Jerry Stuckle

Post by cov
On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle

Post by Jerry Stuckle
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]

Post by cov
On Sat, 10 Nov 2007 00:01:21 -0500, Jerry Stuckle

Post by Jerry Stuckle

Post by cov
On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle

Post by Jerry Stuckle
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;

[cov]

On Sat, 10 Nov 2007 18:13:17 +0100, "Rik Wasmus"

Post by Rik Wasmus
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

[Jerry Stuckle]

Post by cov
On Sat, 10 Nov 2007 18:13:17 +0100, "Rik Wasmus"

Post by Rik Wasmus
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

No, issue the DESCRIBE command for each table and paste the output here.

[Ana C. Dent]

Post by Jerry Stuckle
No, issue the DESCRIBE command for each table and paste the output here.

REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.

You can lead some folks to knowledge, but you can't always make them think.

[cov]

On Sun, 11 Nov 2007 00:36:17 GMT, "Ana C. Dent"

Post by Ana C. Dent
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
You can lead some folks to knowledge, but you can't always make them think.

Is this ng run by a small group of 'experts' who have no tolerance for
anyone else who hasn't achieved a level of expertise or respect to be
considered part of existing prominent 'peer group'? Is a response
such as yours in any way better than the 'spam', 'trolling' etc so
often frowned upon on usenet these days? Will your flaming me in any
way achieve any positive outcome or just result in a back and forth
negative reciprocation until someone capitulates or just goes away?
If my posts seem entirely too elementary for you to the point where
they upset you, please do us both a great service and skip by onto the
higher level of posting satisfaction that brings you here in the first
place.

[Jerry Stuckle]

Post by Ana C. Dent

Post by Jerry Stuckle
No, issue the DESCRIBE command for each table and paste the output here.

REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
REDUNDANCY is the best way to teach idiots.
You can lead some folks to knowledge, but you can't always make them think.

That was completely uncalled for. Obviously the op is not familiar with
MySQL commands. Not everyone who posts here is an expert.

[cov]

On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle

Post by Jerry Stuckle
Table definitions would help...

Like these, correct?

[Jerry Stuckle]

Post by cov
On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle

Post by Jerry Stuckle
Table definitions would help...

Like these, correct?

No, just copy and past the output into your message.

[cov]

On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle

Post by Jerry Stuckle
Table definitions would help...

Couldn't post the attachment directly to the group... Such as this,
correct?

Loading Image...

[Michael Austin]

Post by 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('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
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

now that we have seen you table definitions - there are several ways to
possibly concatenate the colums when doing your searches like ANSI
Standard concatenate using the double "pipe" symbol:

area.conveyors||equipname.conveyors = area.motors||equipname.motors
and area.conveyors||equipname.conveyors = area.equipcontacts ||
equipname.equipcontacts "

Also see the Mysql docs for concat statement.

[cov]

On Mon, 12 Nov 2007 20:28:31 GMT, Michael Austin

Post by Michael Austin
now that we have seen you table definitions - there are several ways to
possibly concatenate the colums when doing your searches like ANSI
area.conveyors||equipname.conveyors = area.motors||equipname.motors
and area.conveyors||equipname.conveyors = area.equipcontacts ||
equipname.equipcontacts "
Also see the Mysql docs for concat statement.

Thank you very much - appreciate the help. :-)