Hi,
Thanks for the reply.
The sites (deptsite1 deptsite2) on the department file are the numeric key
to the actual site file.
The query below works fine (in mysql & php) but how do I reference the
sitename for the joins that have happened?
Specifying
$result = mysql_db_query($dbName,$query);
while ($r=mysql_fetch_array($result))
echo "<td><font size=\"-1\">$r[sitename]</td>
in php returns the last sites name for all
I thought mabe you could say S1.sitename but that doesn't seem to work.
The last thought that occured to me is to scrap the numeric key from the
site file altogether & just have the name as the key.
I've only been playing with MySQL & PHP for a couple of weeks, I don't
know how to 'normalize' a table or what that means. Sorry.
Thanks again.
Ross
[EMAIL PROTECTED] wrote:
> >Ok, I've answered my own question but now have another.
> >
> >How do I reference the sitename for the 3 sites?
> >sitename returns the last sitename for all 3 I tried S1.sitename etc.
but it
> >doesn't work.
>
> Sir, in what way does it fail to work?
>
> >$query = "SELECT * FROM department LEFT JOIN sites S1 ON
> >deptsite1=S1.sitekey LEFT JOIN sites S2 ON deptsite2=S2.sitekey ORDER
BY
> >$order $dir";
>
> What's the $dir for? Does this statement fail in both PHP and the
> MySQL interface, or only in PHP?
>
> You can avoid dealing with aliases entirely if you normalize the
> department table.
>
> Bob Hall
>
> >$result = mysql_db_query($dbName,$query);
> > while ($r=mysql_fetch_array($result)) {
> > echo "<tr bgcolor=$colorvalues>
> ><td><font size=\"-1\">$r[deptdesc]</td>
> ></tr>
> ><tr bgcolor=$colorvalues>
> ><td><font size=\"-1\">$r[sitename]</td>
> ><td><font size=\"-1\">$r[deptphone1]</td>
> ><td><font size=\"-1\">$r[deptfax1]</td>
> ><td><font size=\"-1\">$r[deptemail1]</td>
> ><td><font size=\"-1\">$r[deptmobile1]</td></tr>
> ><td><font size=\"-1\">$r[sitename]</td>
> ><td><font size=\"-1\">$r[deptphone2]</td>
> ><td><font size=\"-1\">$r[deptfax2]</td>
> ><td><font size=\"-1\">$r[deptemail2]</td>
> ><td><font size=\"-1\">$r[deptmobile2]</td></tr>
> ><td><font size=\"-1\">$r[sitename]</td>
> ><td><font size=\"-1\">$r[deptphone3]</td>
> ><td><font size=\"-1\">$r[deptfax3]</td>
> ><td><font size=\"-1\">$r[deptemail3]</td>
> ><td><font size=\"-1\">$r[deptmobile3]</td>";
> >
> >
> >-----Original Message-----
> >From: Ross Goonan [mailto:[EMAIL PROTECTED]]
> >Sent: Friday, 8 June 2001 11:47
> >To: [EMAIL PROTECTED]
> >Subject: JOIN to the same table multiple times
> >
> >
>
> >###
> >Creating a Telephone / Information Directory with MySQL / PHP3
> >
> >People belong to a department & a site.
> >
> >Need to be able to:
> > List all people
> > List all people within a Department
> > List all people within a site
> > List all people within a Department & Site
> >
> >Have set up Department table with site1, site2 & site3 as the same
> >department
> >exists in multiple sites.
> >
> >When listing a site, I need to JOIN the site table multiple times to
get the
> > site name.
> >
> >SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey
> > - Works no worries
> >
> >SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey LEFT JOIN Site
ON
> > Site2=Sitekey
> > - Error 1066: Not unique table/alias 'Site'
> >
> >SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey LEFT JOIN Site
AS
> > Sitetable ON Site2=Sitekey
> > - Error 1052: Column 'Sitekey' in on clause is ambiguos
> >
>
> >###
> >rpm -qa | grep SQL
> >MySQL-3.23.33-1
> >MySQL-client-3.23.33-1
> >MySQL-devel-3.23.33-1
> >rpm -qa | grep sql
> >php-mysql-3.0.16-2bc
> >perl-Msql-Mysql-modules-1.2210-2
>
> >###
> >
> >People Table
> >----------------
> >Surname
> >Firstname
> >Department
> >site
> >
> >Department Table
> >----------------
> >Name
> >Site1
> >site2
> >site3
> >
> >Site Table
> >----------------
> >Sitekey
> >Sitename
> >
>
> >###
> >
> >
> >---------------------------------------------------------------------
> >Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> >To request this thread, e-mail <[EMAIL PROTECTED]>
> >To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
> >---------------------------------------------------------------------
> >Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> >To request this thread, e-mail <[EMAIL PROTECTED]>
> >To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
> Know thyself? Absurd direction!
> Bubbles bear no introspection. -Khushhal Khan Khatak
> MySQL list magic words: sql query database
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>#########################################################################
###
>#########################################################################
###
>#########################################################################
###
>#########################################################################
###
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
