mysql_fetch_array() boolean error but with no description?

Question

Possible Duplicate:
mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

i know were not suppose to ask this question because its been asked before and theres an aswer to it on here. but i dont think this is the same thing. i've never come across a boolean error like that before with out it giving a description of the line number of where the error is?

Instead all im getting is this error message without any line information or other information to help me identify the problem?

here's the errors im getting word for word:

: mysql_fetch_array() expects parameter 1 to be resource, boolean given in on line : mysql_fetch_array() expects parameter 1 to be resource, boolean given in on line

It's strange because it normally tells the line number but clearly not in this case.

I am using this mysql function:

function account_perms() {
    global $connection;
    global $_SESSION;
    global $profile_id;
    $query = "SELECT *
        FROM ptb_permissions
        WHERE ptb_permissions.private_id = \"$profile_id\"
        AND ptb_permissions.user_id = ".$_SESSION['user_id']." ";

    $account_perms = mysql_query($query, $connection);
    confirm_query($query, $connection);
    return $account_perms;
}

and this php code:

<?php
$account_perms = account_perms();

while ($perms = mysql_fetch_array($account_perms)) {
    if ($perms['privellages'] == '1')  {          
         $photo = "data/private_photos/$profile[1]/pic1.jpg";
         if (!file_exists($photo)) {
              $photo = "data/photos/0/_default.jpg";
         }
         $thumb = "data/private_photos/$profile[1]/thumb_pic1.jpg";
         if (!file_exists($thumb)) {
              $thumb = "data/photos/0/_default.jpg";
         }
         if (logged_in()) {
              echo "<li><a href=\"$photo\" rel=\"shadowbox\" title=\"<strong>$profile[2]'s Photo's</strong>\"><img src=\"$thumb\" width=\"90\" height=\"90\" alt=\"<strong>{$profile[2]}'s Photos</strong>\"  /></a></li>";

         }
     }
}

$account_perms = account_perms();
while ($perms = mysql_fetch_array($account_perms)) {
     if ($perms['privellages'] == '0')  {
        $photo = "data/private_photos/0/_default.jpg";
                  if (!file_exists($photo)) {
                       $photo = "data/photos/0/_default.jpg";
                  }
                  $thumb = "data/private_photos/0/_default.jpg";
                  if (!file_exists($thumb)) {
                       $thumb = "data/photos/0/_default.jpg";
                  }
                  if (logged_in()) {
                       echo "<li><a href=\"$photo\" rel=\"shadowbox\" title=\"<strong>$profile[2]'s Photo's</strong>\"><img src=\"$thumb\" width=\"90\" height=\"90\" alt=\"<strong>{$profile[2]}'s Photos</strong>\"  /></a></li>";

                  } 
       }
}
?>

please can someone advise me why im getting this error.

Answer 1

ALLWAYS as a rule of thumb, when you encounter unknown error in your mysql (which btw, you should't be using) allways fetch the error using the built in function or die (mysql_error()) that is the purpose of it. In this case, I recommend you add it here

$account_perms = mysql_query($query, $connection) or die(mysql_error()); You clearly will see a message about the problem.

Answer 2

First of all your code is vulnerable for SQL Injection. You're including variable into your queries without escaping them. With an improper value in one of those variables, users will be able to override their permissions, or even delete everything in your database.

Please read my comment below your question to find out how to get your code more secure.

The problem you're having is that in $account_perms is not containing a result from the database. I see only two calls to mysql_fetch_array() in your code, so it should be easy to find in which case it's not working by adding some debug code:

$account_perms = account_perms();
echo 'first time using mysql_fetch';
var_dump($account_perms);

and the same for the other location where you're using mysql_fetch_array()

Also, because of the possible SQL injection vulnerability, you might debug your SQL and check if the generated SQL query is properly formatted. You can do so by putting this inside your account_perms() function:

// rest of code ..
var_dump($query);

$account_perms = mysql_query($query, $connection);

// etc..

[UPDATE] Based on comment by the OP below, the problem only occurs if the user is not logged in. Therefore, the problem is probably in this part:

SELECT *
    FROM ptb_permissions
    WHERE ptb_permissions.private_id = \"$profile_id\"
    AND ptb_permissions.user_id = ".$_SESSION['user_id']." ";

If the user is not logged-in, the 'user_id' key is probably not set in the session, therefore the resulting query will become this:

SELECT *
    FROM ptb_permissions
    WHERE ptb_permissions.private_id = \"$profile_id\"
    AND ptb_permissions.user_id = 

Which is invalid, causing no results to be returned from the database

It's best to add some mechanism in your code that checks if a user is logged in, and only then retrieve the information

In all cases, check if variables you're using are present and contain a proper value before using them.

for example, re-write the account_perms() functions, and pass the variables you're using as an argument, in stead of using global variables;

function account_perms($profile_id, $session_user_id) {
    // Convert both id's to integers (I'm assuming both are numbers here)
    // this also makes it safer
    // agains SQL injections (in this case)
    $profile_id      = (int) $profile_id;
    $session_user_id = (int) $session_user_id;

    if ($profile_id <= 0 || $session_user_id <= 0) {
        // no valid profile-id or no valid session-user-id
        // return false to indicate something was wrong
        // you may also use 'die()' with an error message
        // or Throw an Exception
        return false;
    }

    // we'll leave this global variable for now
    global $connection;

    if (!$connection) {
        die('No database connection found');
    }

    $query = "SELECT *
        FROM ptb_permissions
        WHERE ptb_permissions.private_id = " . $profile_id . "
        AND ptb_permissions.user_id = " . $session_user_id .";";

    $account_perms = mysql_query($query, $connection);
    confirm_query($query, $connection);
    return $account_perms;
}

Then inside your script:

if (!isset($_SESSION['user_id'])) {
   // user_id not found. we don't need to go any further
   die('no user-id in session');
}


$account_perms = account_perms($profile_id, $_SESSION['user_id']);

if (!$account_perms) {
    die('Unable to retrieve account-permissions from database');
}

This is just to illustrate how you can approach this issue