php query is not running??

Question

$query="select * from fsb_profile where fsb_profile.profile_id=('select fsb_friendlist.friendlist_friendid from fsb_friendlist where friendlist_memberid='".$id."'')";
$sql=mysql_query($query);

while ($t = mysql_fetch_assoc($sql)) 
{
    echo "hai";
    echo $t["profile_name"];
}

this code in not running why?
error:-

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:\Project\fsbaroda\profile.php on line 186

Answer 1

I'm going to guess you have a query syntax error.

Answer 2

You are missing IN clause there i suppose, also no need for that single quote in sub-query:

$query="select * from fsb_profile where fsb_profile.profile_id IN 
 (select fsb_friendlist.friendlist_friendid from fsb_friendlist
 where friendlist_memberid='".$id."')";

Or try this even:

$query="select * from fsb_profile where fsb_profile.profile_id =
 (select fsb_friendlist.friendlist_friendid from fsb_friendlist
 where friendlist_memberid='".$id."')";

Also make sure that query runs successfully append mysql_error():

$sql=mysql_query($query) or die(mysql_error());

Answer 3

Your query is throwing an error and returning false rather than returning a valid result resource.

The reason your query is failing is because you have an extra single quotes ' wrapped around your subquery, these aren't needed.

$query="select * from fsb_profile
         where fsb_profile.profile_id= (
          select fsb_friendlist.friendlist_friendid from fsb_friendlist 
          where friendlist_memberid='".$id."')";

Answer 4

You either have a MySQL syntax error, or that query is returning 0 results. $sql in this case is NULL (or false). Try debugging your query in something like phpMyAdmin.

Answer 5

first always test the result from mysql_query. run this and let me know

$query="select * from fsb_profile where fsb_profile.profile_id=('select fsb_friendlist.friendlist_friendid from fsb_friendlist where friendlist_memberid='".$id."'')";
echo $query;

$sql=mysql_query($query);
if ($sql)
    {
while ($t = mysql_fetch_assoc($sql)) 
{
    echo "hai";
    echo $t["profile_name"];
}
   } else
   {
   echo mysql_error();
   }

Answer 6

mysql_query only returns a usable resource if the query is successful. If the query fails, it returns FALSE. You need to check the value returned by mysql_query before passing it to mysql_fetch_assoc.

In this particular case, your SQL query contains quotes which are not required. It should be:

$query = "select * from fsb_profile " .
    "where fsb_profile.profile_id=(" .
    "select fsb_friendlist.friendlist_friendid " .
    "from fsb_friendlist " .
    "where friendlist_memberid=" . $id .
    " LIMIT 1)";

There's no need for the quotes around your second SELECT statement, and if friendlist_memberid is numeric, you don't need quotes around $id either.

Update Your inner SELECT query is returning more than one row, so you need to use IN instead of = in your WHERE clause, or, as shown here, use LIMIT to ensure that only one row is returned.

Your code should therefore look more like this:

$query = "select * from fsb_profile " .
    "where fsb_profile.profile_id=(" .
    "select fsb_friendlist.friendlist_friendid " .
    "from fsb_friendlist " .
    "where friendlist_memberid=" . $id  .
    " LIMIT 1)";

if( $sql = mysql_query($query) ) {
    while ($t = mysql_fetch_assoc($sql)) {
        echo "hai";
        echo $t["profile_name"];
    }
} else {
    echo "Something went horribly wrong:\n" .
        mysql_error();
}

Something else worth mentioning: you should look into using prepared statements instead of dynamic SQL commands. It's often a safer alternative, and simpler than all the nasty escaping that you have to do otherwise. There are many tutorials around. Here's a couple to get you started: Prepared Statements in PHP and MySQLi and Developing MySQL Database Applications With PHP.