Posted by Jerry Stuckle on 11/20/07 15:01

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

[Back to original message]