Home > Archive > PHP Language > November 2007 > Unusual Query of three tables
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Unusual Query of three tables
|
|
|
| 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
| |
| Jerry Stuckle 2007-11-10, 6:59 pm |
| cov 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
>
You've already asked this in comp.databases.mysql, which is where is
should be. But you haven't responded with the information we need to
help you.
This is a straight SQL question and has nothing to do with PHP.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
| |
|
| On Sat, 10 Nov 2007 11:39:57 -0500, Jerry Stuckle
<jstucklex@attglobal.net> wrote:
>You've already asked this in comp.databases.mysql, which is where is
>should be. But you haven't responded with the information we need to
>help you.
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 key set. Zero decimals and allow null not set. thanks
| |
| Jerry Stuckle 2007-11-10, 6:59 pm |
| cov wrote:
> On Sat, 10 Nov 2007 11:39:57 -0500, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>
>
> 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 key set. Zero decimals and allow null not set. thanks
>
As I said. This is not the correct newsgroup for MySQL questions.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
| |
|
| On Sat, 10 Nov 2007 15:18:40 -0500, Jerry Stuckle
<jstucklex@attglobal.net> wrote:
>As I said. This is not the correct newsgroup for MySQL questions.
Not sure I understand why you would say that when it is php code
interfacing with mysql. The form code is all php/html so how that
query were written accordingly and interact with mysql, would
seemingly be entirely a php matter.
| |
| AnrDaemon 2007-11-11, 7:59 am |
| Greetings, cov.
In reply to Your message dated Sunday, November 11, 2007, 07:01:35,
[color=darkred]
> Not sure I understand why you would say that when it is php code
> interfacing with mysql.
Make sure You getting proper data from database before trying to use it in PHP code.
So, go to comp.database.mysql and refine Your MySQL knowledge.
(Guess You know how to use MySQL without PHP)
--
Sincerely Yours, AnrDaemon <anrdaemon@freemail.ru>
| |
| Jerry Stuckle 2007-11-11, 7:59 am |
| cov wrote:
> On Sat, 10 Nov 2007 15:18:40 -0500, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>
>
> Not sure I understand why you would say that when it is php code
> interfacing with mysql. The form code is all php/html so how that
> query were written accordingly and interact with mysql, would
> seemingly be entirely a php matter.
>
Because your question is purely SQL related. There is nothing here
which is related to PHP or any other programming language.
First rule of newsgroups - determine where you problem is and post to
the appropriate newsgroup. Second rule - give enough information for
people to help you with your problem.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
| |
| Onideus Mad Hatter 2007-11-14, 7:00 pm |
| On Sat, 10 Nov 2007 20:01:35 -0800, cov <coverlandNS914@yahoo.com>
wrote:
>On Sat, 10 Nov 2007 15:18:40 -0500, Jerry Stuckle
><jstucklex@attglobal.net> wrote:
>
>
>Not sure I understand why you would say that when it is php code
>interfacing with mysql. The form code is all php/html so how that
>query were written accordingly and interact with mysql, would
>seemingly be entirely a php matter.
Just ignore Jerry, he's a senile old fart whose never accomplished
anything in his life. No newsgroups topic is set in absolute stone
(unless it's a moderated froup and even then not always). Any sort of
question that relates in some way to PHP is perfectly welcome here and
certainly a question regarding MySQL since the two are so often used
in conjunction with one another.
--
Onideus Mad Hatter
mhm ¹ x ¹
http://www.backwater-productions.net
http://www.backwater-productions.net/hatter-blog
Hatter Quotes
-------------
"You're only one of the best if you're striving to become one of the
best."
"I didn't make reality, Sunshine, I just verbally XXXXX slapped you
with it."
"I'm not a professional, I'm an artist."
"Your Usenet blinders are my best friend."
"Usenet Filters - Learn to shut yourself the XXXX up!"
"Drugs killed Jesus you know...oh wait, no, that was the Jews, my
bad."
"There are clingy things in the grass...burrs 'n such...mmmm..."
"The more I learn the more I'm killing my idols."
"Is it wrong to incur and then use the hate ridden, vengeful stupidity
of complete strangers in random Usenet froups to further my art?"
"Freedom is only a concept, like race it's merely a social construct
that doesn't really exist outside of your ability to convince others
of its relevancy."
"Next time slow up a lil, then maybe you won't jump the gun and start
creamin yer panties before it's time to pop the champagne proper."
"Reality is directly proportionate to how creative you are."
"People are pretty XXXXing high on themselves if they think that
they're just born with a soul. *snicker*...yeah, like they're just
givin em out for free."
"Quible, quible said the Hare. Quite a lot of quibling...everywhere.
So the Hare took a long stare and decided at best, to leave the rest,
to their merry little mess."
"There's a difference between 'bad' and 'so earth shatteringly
horrible it makes the angels scream in terror as they violently rip
their heads off, their blood spraying into the faces of a thousand
sweet innocent horrified children, who will forever have the terrible
images burned into their tiny little minds'."
"How  that you're such a poor judge of style that you can't even
properly gauge the artistic worth of your own efforts."
"Those who record history are those who control history."
"I am the living embodiment of hell itself in all its tormentive rage,
endless suffering, unfathomable pain and unending horror...but you
don't get sent to me...I come for you."
"Ideally in a fight I'd want a BGM-109A with a W80 250 kiloton
tactical thermonuclear fusion based war head."
"Tell me, would you describe yourself more as a process or a
function?"
"Apparently this group has got the market cornered on stupid.
Intelligence is down 137 points across the board and the forecast
indicates an increase in Webtv users."
"Is my .sig delimiter broken? Really? You're sure? Awww,
gee...that's too bad...for YOU!" `, )
| |
| BoneIdol 2007-11-20, 7:00 pm |
| 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.
| |
| Jerry Stuckle 2007-11-20, 7:00 pm |
| BoneIdol wrote:
> On Nov 10, 3:48 pm, cov <coverlandNS...@yahoo.com> wrote:
>
> 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
==================
| |
| BoneIdol 2007-11-20, 7:00 pm |
| On Nov 20, 3:01 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> BoneIdol wrote:
>
>
>
>
>
>
>
>
>
>
>
> 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.
>
>
>
>
>
>
>
>
>
> 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.
| |
| Jerry Stuckle 2007-11-20, 7:00 pm |
| BoneIdol wrote:
>
> 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.
>
No flaming. PHP is NOT MySQL and vice versa. There are many MySQL
experts - including people on the MySQL design team - who monitor
comp.databases.mysql. Many of them do not monitor this newsgroup.
The best place to get MySQL answers is in that group. I would say the
vast majority of the people here are amateurs with MySQL compared to the
people in that newsgroup.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
|
|
|
|
|