Inner Join in MySQL query in php isn't working

Question

With this piece of code, if I remove the inner join from the query everything would work fine. Also, I tried the query from phpMyAdmin and it worked just fine.

Here is my code :

           $db = mysql_connect("localhost", USERNAME,PASSWORD);
     if (!$db)   {
        echo("<p>Error creating Database Connection</p>");
        exit;
      }

        $db_selected = mysql_select_db(DATABASE_NAMEPH,$db);

$sql = "SELECT * FROM user " +
"INNER JOIN gender ON user.id = gender.user_id "+
"INNER JOIN user_address ON user.id = user_address.user_id "+
"INNER JOIN address ON address.id = user_address.address_id;";


$result = mysql_query($sql,$db);
if(!$result){
 echo( "<p>Unable to query database at this time.</p>" );

}
$num_results = mysql_num_rows($result);
mysql_close($db);

while( $row = mysql_fetch_assoc($result)){

echo"   <form name='register' class='register'> ";
echo"   <fieldset class='row1'>";
echo"   <legend>".$row["fName"].", ".$row["lName"]."'s Personal Details</legend>          </fieldset>";
echo"   <p><label>Email:</label><data> ".$row["email"]."</data> </p>";
echo"   <p><label>First Name:</label><data> ".$row["fName"]."</data></p>";
echo"   <p><label>Last Name:</label><data> ".$row["lName"]."</data></p>";    
echo"   <p><label>Phone:</label><data> ".$row["phone"]."</data></p>";
echo"   <p><label>Street:</label><data> ".$row["street"]."</data></p>";
echo"   <p><label>City:</label><data> ".$row["city"]."</data></p>";
echo"   <p><label>State: </label><data>".$row["state"]."</data></p>";
echo"   <p><label>Zip Code:</label><data> ".$row["zip"]."</data></p>";
echo"   <p><label>Gender:</label><data> ".$row["name"]."</data></p>";
echo"   <p><label>Birthdate:</label><data> ".$row["bDate"]."</data></p>";
echo"   <p><label>Additional Info:</label><data> ".$row["info"]."</data></p>";
echo"   <p><label>Admin:</label><data> ".$row["flag"]."</data></p></fieldset></form>";


   }

?>
<h3 id="path">Number of users registered: <?php echo $num_results; ?></h3>

Here is the error message I get:

Unable to query database at this time.

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in
/Users/mjramahi/Sites/web/admin/users.php on line 129

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in 
/Users/mjramahi/Sites/web/admin/users.php on line 132

PHP uses a dot `.` for concatenation, not `+` as you have in your SQL statement. Using `+` is likely causing incorrect casting of strings to integers (zeros) and adding them up to an int 0 for your SQL statement. — Michael Berkowski, Jul 12 '13 at 17:27
What @MichaelBerkowski says. So always dump (or debug) to find the real query being created when trouble-shooting issues like this instead of assuming that what you copy and paste to phpMyAdmin is the same as the real query. Notwithstanding avoiding deprecated mysql_* functions... — markdwhite, Jul 12 '13 at 17:30
This question appears to be off-topic because the issue is a typo. — 000, Jul 12 '13 at 17:47
Possible duplicate [mysql_fetch_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select?answertab=votes#tab-top). — John Conde, Jul 15 '13 at 00:35

user4035 · Accepted Answer · 2013-07-12T17:35:09.340

1

Use dots to concat strings, not pluses. PHP treats your variable as integer and $sql becomes equal to 0 (check it!). Try this instead:

$sql = "SELECT * FROM user " .
"INNER JOIN gender ON user.id = gender.user_id ".
"INNER JOIN user_address ON user.id = user_address.user_id ".
"INNER JOIN address ON address.id = user_address.address_id;";

edited Jul 12 '13 at 17:35

answered Jul 12 '13 at 17:29

user4035

22,508
11
59
94

Should put "Use dota to concat strings" in front :P – Populus Jul 12 '13 at 17:32
hehe i'm just being picky XD – Populus Jul 12 '13 at 17:36

Inner Join in MySQL query in php isn't working

1 Answers1