Page 1 of 1

PHP SQL JOIN NOT WORKING

Posted: Mon Jan 14, 2013 7:08 am
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

Re: PHP SQL JOIN NOT WORKING

Posted: Mon Jan 14, 2013 12:23 pm
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?

Re: PHP SQL JOIN NOT WORKING

Posted: Tue Jan 15, 2013 4:22 am
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)

Re: PHP SQL JOIN NOT WORKING

Posted: Tue Jan 15, 2013 2:47 pm
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.

Re: PHP SQL JOIN NOT WORKING

Posted: Wed Jan 16, 2013 5:39 am
by jonnyfortis
Thanks for your help, sorted it. I was calling the wring table in the database. My mistake.