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.

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

Re: Unusual Query Help Request

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

Re: Unusual Query Help Request

On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
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

Re: Unusual Query Help Request

cov wrote:
> On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
> 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 )

Re: Unusual Query Help Request

On Fri, 09 Nov 2007 22:20:38 -0600, Michael Austin
wrote:


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


Ahh, thanks... :-)

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

Re: Unusual Query Help Request

cov wrote:
> On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
> 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
==================

Re: Unusual Query Help Request

cov wrote:
> On Fri, 09 Nov 2007 22:20:38 -0600, Michael Austin
> wrote:
>
>
>> He meant what are the column names in the tables... along with the
>> datatype of each column (describe )
>
>
> 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
==================

Re: Unusual Query Help Request

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

>cov wrote:
>> On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
>> 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?

Re: Unusual Query Help Request

On Sat, 10 Nov 2007 07:43:13 +0100, cov wrote:

> On Sat, 10 Nov 2007 00:01:21 -0500, Jerry Stuckle
> wrote:
>
>> cov wrote:
>>> On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
>>> 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

Re: Unusual Query Help Request

On Sat, 10 Nov 2007 18:13:17 +0100, "Rik Wasmus"
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

Re: Unusual Query Help Request

cov wrote:
> On Sat, 10 Nov 2007 18:13:17 +0100, "Rik Wasmus"
> 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
>

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

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

Re: Unusual Query Help Request

Jerry Stuckle wrote in
news:57ydndMGpf3Tj6vanZ2dnUVZ_rzinZ2d@comcast.com:

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

Re: Unusual Query Help Request

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

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

Re: Unusual Query Help Request

Ana C. Dent wrote:
> Jerry Stuckle wrote in
> news:57ydndMGpf3Tj6vanZ2dnUVZ_rzinZ2d@comcast.com:
>
>> 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.

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

Re: Unusual Query Help Request - DESCRIBE.JPG (0/1)

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

>Table definitions would help...

Like these, correct?

Re: Unusual Query Help Request

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


>Table definitions would help...

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

http://home.nctv.com/ajns/DESCRIBE.JPG

Re: Unusual Query Help Request - DESCRIBE.JPG (0/1)

cov wrote:
> On Fri, 09 Nov 2007 16:31:09 -0500, Jerry Stuckle
> wrote:
>
>> Table definitions would help...
>
> Like these, correct?
>

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

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

Re: Unusual Query Help Request

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

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.

Re: Unusual Query Help Request

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


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


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