Posted by BoneIdol on 11/20/07 15:39

On Nov 20, 3:01 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> BoneIdol wrote:
> > On Nov 10, 3:48 pm, cov <coverlandNS...@yahoo.com> 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
>
> > Sorry to break this to you, but to get that to work robustly you NEED
> > a Primary Key in all tables.
>
> > The problem with using WHERE to select from multiple tables is that it
> > only works properly in a one-to-one relationship. If even one of the
> > where statements returns no results the query fails with no results.
>
> Not at all. This type of join is quite often used for a many-to-many
> relationship, where table1 is a link table. It may very well have no
> primary key. Or, at most, the primary key would be the two items being
> linked.
>
>
>
> > The way to do this would be to assign a primary key in each table and
> > a foreign key in the other 2 tables linking to your main table. Then
> > use a LEFT JOIN clause in your query, so you'd get something like...
>
> > SELECT main_table.attribute, table1.attribute, table2.attribute FROM
> > main_table
> > LEFT JOIN table1 ON main_table.primary_key = table1.foreign_key
> > LEFT JOIN table2 ON main_table.primary_key = table2.foreign_key
> > WHERE main_table.attribute = Value
>
> > The beauty of doing it this way is that you can also group together
> > any results from the joined tables, so you could get the number of
> > comments on a news article by going...
>
> > SELECT COUNT(comments.comment_id) AS comments, news.news_title FROM
> > news
> > LEFT JOIN comments ON comments.news_id = news.news_id
> > GROUP BY news.news_id
>
> > Anyway hope that helps. I would have given you an example that used
> > your table structure, but I really don't have the time or energy to
> > work out how your tables are put together.
>
> > Also Jerry, stop being so stuck up about this. For most people MySQL
> > and PHP are synonymous. Rather than flame you should have just ignored
> > this topic as you are clearly incapable of posting anything remotely
> > helpful on the subject.
>
> They are two separate products. And shitty answers like yours are
> exactly why I recommend they go to a group where the MySQL experts are.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

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.

[Back to original message]