Posted by BoneIdol on 11/20/07 14:33

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.

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.

[Back to original message]