PHP SQL JOIN NOT WORKING

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

PHP SQL JOIN NOT WORKING

Post by jonnyfortis »

i have done this multiple times but i must of missed something and cant get it to work

i have a PHP Database with tables

1. LettingsTenApp
2. LFeProp

both have a colomn

propID

i am trying to join them based on username (which is the user email address)

Code: Select all

$colname_rsTenant = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_rsTenant = $_SESSION['MM_Username'];
}
mysql_select_db($database_Letting, $Letting);
$query_rsTenant = sprintf("SELECT * FROM LettingsTenApp, LFeProp WHERE tenEmail = %s AND LettingsTenApp.propID = LFeProp.propID", GetSQLValueString($colname_rsTenant, "text"));
$rsTenant = mysql_query($query_rsTenant, $hostLetting) or die(mysql_error());
$row_rsTenant = mysql_fetch_assoc($rsTenant);
$totalRows_rsTenant = mysql_num_rows($rsTenant);
 

if i delete the join LettingsTenApp.propID = LFeProp.propID and just have the tenEmail = %s then the filter works.

can anyone see where i am going wrong?

thanks
Last edited by Weirdan on Mon Jan 14, 2013 12:04 pm, edited 1 time in total.
Reason: added syntax highlighting
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP SQL JOIN NOT WORKING

Post by requinix »

The best way to do a JOIN is

Code: Select all

SELECT fields FROM first table
    JOIN second table ON some equality
    JOIN third table ON some equality
    JOIN ...

Code: Select all

SELECT * FROM LettingsTenApp JOIN LFeProp ON LettingsTenApp.propID = LFeProp.propID WHERE tenEmail = %s
Or, since I can't tell what each table is for or where the tenEmail comes from,

Code: Select all

SELECT * FROM LFeProp JOIN LettingsTenApp ON LFeProp.propID = LettingsTenApp.propID WHERE tenEmail = %s
Define "works". Are you getting too many rows? Not getting anything at all? Are the LFeProp rows optional or will there be at least one row for everything in LettingsTenApp?
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: PHP SQL JOIN NOT WORKING

Post by jonnyfortis »

Define "works". Are you getting too many rows? Not getting anything at all? Are the LFeProp rows optional or will there be at least one row for everything in LettingsTenApp?
sorry, when the

Code: Select all

"SELECT * FROM LettingsTenApp, LFeProp WHERE tenEmail = %s AND LettingsTenApp.propID = LFeProp.propID"
is applied when the user logs in neither the user details are displayed nor the property information. It should be showing the username and the property information

when the

Code: Select all

"SELECT * FROM LettingsTenApp WHERE tenEmail = %s"
is applied the user details are displayed including the property id (which is from the column in the LettingsTenApp table)
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP SQL JOIN NOT WORKING

Post by requinix »

Then there is no matching row in LFeProp. Check your data.
Or there is more than one matching row and thus you'll get more than one row in the results, and your code somewhere requires there be exactly one row.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: PHP SQL JOIN NOT WORKING

Post by jonnyfortis »

Thanks for your help, sorted it. I was calling the wring table in the database. My mistake.
Post Reply