How do I return the numeric value from a database query in PHP?

Question

Hello, I am looking to retreive a numerical value from the database

function adminLevel()
{
   $q = "SELECT userlevel FROM ".TBL_USERS." WHERE id = '$_SESSION[id]'";
   return mysql_query($q, $this->connection);
}

This is the SQL.

I then wrote the following php/html:

    <?php 
    $q = $database->adminLevel();
    if ($q > 7)
    {
        ?>
        <a href="newleague.php">Create a new league</a>
        <?
    }
    ?>

The problem I have is that the userlevel returned isn't affecting the if statement. It is always displayed. How do i get it to test the value of userlevel is greater than 7?

Thanks

Answer 1

mysql_query returns a resource. You need to fetch data from that resource by using some of the mysql_fetch_* functions, such as mysql_fetch_row http://php.net/mysql_fetch_row

Answer 2

You are comparing a mysql resource with an integer, so you can expect strange results. Use mysql_result() to fetch one cell from this resource.

Answer 3

adminlevel() doesn't return an integer here. It returns a mysql resultset object containing a single row and a single column; the data point contained within happens to be an integer. Presumably, however PHP compares that object to integers happens to alwayus result in its being larger than 7. But it's not comparing the integer you wanted to compare. Try this:

function adminLevel()
{
   $q = "SELECT userlevel FROM ".TBL_USERS." WHERE id = '$_SESSION[id]'";
   $r = mysql_query($q, $this->connection);
   if ( $r and mysql_num_rows($r) ) {
       $s = mysql_fetch_assoc($r);
       return $s['userlevel'];
   } else {
       // error handling; your query failed or returned no rows
   }
}

Answer 4

mysql_query returns a resource. You then need to fetch the result from this. e.g.

function adminLevel()
{
  $q = "SELECT userlevel FROM ".TBL_USERS." WHERE id = '$_SESSION[id]'";
  $result = mysql_query($q, $this->connection);
  if ($row = mysql_fetch_assoc($result)) {
    $level = $row['userlevel'];
  } else {
    $level = 0; // some default value
  }

  // free the result resource after using it
  mysql_free_result($result);
  return $level;
}

If you think about it, in the general case a query can return multiple results so you need a way to retrieve each of these in turn (e.g. a while loop over mysql_fetch_assoc) but in this specific case as you are selecing the userlevel by id you will retrieve either 0 or 1 rows so we can use an if to check for a matching row.

See the documentation for mysql_query for more details.