This answer is quite involved, see below.
Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="ISO-8859-1" MIME-Version: 1.0 To: php-db@xxxxxxxxxxxxx Date: Sun, 11 Jul 2004 19:31:21 -0700 From: "Marcjon" <marcjon@xxxxxxxxxxx> Message-Id: <1089599481.18091.200157611@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
I know this issue comes up alot, I've looked through the archives
looking for a solution, but none of them are what I want. I have two
tables with share a common coloumn, username. One is the user table.
It's used to store the user's password, username, email and so on. The
other table is for the user's profile (if they choose to have one). It
contains msnm, aim, yahoo etc address, birthdate and other stuff like
that. So far I have this query:
"SELECT
forumusers.email AS email,forumusers.username AS username,forumusers.level AS level,
forumusers.lastlogon1 AS lastlogon1,forumusers.lastlogon2 AS lastlogon2,forumusers.settings1 AS settings1,forumusers.confirmationcode AS confirmationcode,
forumuserprofiles.sex AS sex, forumuserprofiles.birthdate AS birthdate,
forumuserprofiles.address_msnm AS address_msnm, forumuserprofiles.address_aim AS address_aim,
forumuserprofiles.address_yahoo AS address_yahoo, forumuserprofiles.address_icq AS address_icq
FROM ".$godlyness['database_database'].".forumusers,".$godlyness['database_database'].".forumuserprofiles ".$filterbu."
ORDER BY username
"
You need to use LEFT OUTER JOIN syntax which will match rows on a key, and return NULL where there is no match for the same key in another table. BTW How does your query below work ????? There's no 'WHERE' filter !
So - lets try a stripped down version of your 1st query :
SELECT forumusers.*, forumuserprofile.* FROM forumusers, forumuserprofile WHERE forumusers.username = '".$selected_username."' AND forumusers.username = forumuserprofile.username
This query matches all forumusers with the specified $selected_username, and all forumuserprofiles which *also* match that username. Now, you need to modify this query to recover the NON-MATCHING (ie, empty) user profiles as well, this is where LEFT OUTER JOIN comes in :
SELECT forumusers.*, forumuserprofile.* FROM forumusers LEFT OUTER JOIN forumuserprofile ON forumusers.username = forumuserprofile.username WHERE forumusers.username = '".$selected_username."'
So, here we're still selecting the forumuser with username $selected_username, but we *also* do a left join and include any rows where there is a match as well as there is not a match for forumusers.username = forumuseprofile.username. If there is *no* match, all the columns from forumuserprofile will be NULL, if there *is* a matching forumusers.username = forumuserprofile.username, then the columns from forumuserprofile will contain values.
HTH Neil
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
