Mysql query returning resource id #8 instead of desired value

Question

Hi I am trying to discover how to fix my query to return the correct result. Here is my query:

$selectShoeRatingQuery = "SELECT cast(round(AVG(rating)*2)/ 2 as decimal(10,1)) FROM rating WHERE shoe_id = '$_GET[id]'";
$shoeRating = mysql_query($selectShoeRatingQuery);

The query should return a number with one decimal place (3.5). It works fine when testing in PhpMyAdmin, however on my site it returns resource id #8.

The database connection all works fine.

Answer 1

mysql_query returns a resource. You need to get a row from it:

$query = mysql_query($selectShoeRatingQuery);
$row = mysql_fetch_row($query);
$shoeRating = $row[0];

And, unless you have no choice - don't use the mysql_ set of extensions! They're deprecated, and PDO et al. are better. And your query is vulnerable.

Answer 2

Quoting from Php Manual

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

you need to fetch it for that you can use

$row = MySQL_fetch_row($query);

if you dont use Prepared statements now atleast use mysql_real_escape_string

 'mysql_real_escape_string($_GET[id])'"

---

Warning

your code is vulnerable to sql injection you need to escape all get and post and the better approach will be using Prepared statement

Good Read

1. How to prevent SQL injection in PHP?
2. Are PDO prepared statements sufficient to prevent SQL injection?

---

Note

1. The entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_, is officially deprecated as of PHP v5.5.0 and will be removed in the future. So use either PDO or MySQLi

Good read

1. The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead
2. PDO Tutorial for MySQL Developers
3. Pdo Tutorial For Beginners

Answer 3

When invoked through PHP, a select query returns a Resource, which is essentially a pointer to the first row of the result. To fetch data, you will have to use mysql_fetch_array, which will get you the row referred to by the pointer.

After reading through the row, the pointer will auto increment to point to the next row.

So, your code will be something like this

$connection = mysqli_connect(...);
$query = ""; //put your query here
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
    //$row will contain the data of the row in an array format. Use it here.
}

Moreover, please don't use mysql functions as they are deprecated now. Use MySQLi, or PDO.

Answer 4

try,

SELECT round(AVG(rating) * 2.0) / 2.0 AS result
FROM rating
WHERE ....

Answer 5

$selectShoeRatingQuery = "SELECT cast(round(AVG(rating)*2)/ 2 as decimal(10,1)) as `num` FROM rating WHERE shoe_id = '$_GET[id]'";
$shoeRating = mysql_query($selectShoeRatingQuery);
$r = mysql_fetch_row($shoeRating);
echo $r['num'];// <--- alias

Answer 6

$selectShoeRatingQuery = "SELECT cast(round(AVG(rating)*2)/ 2 as decimal(10,1)) as
rating 
FROM rating WHERE shoe_id = '$_GET[id]'";

$shoeRating = mysql_query($selectShoeRatingQuery);
$result = mysql_fetch_array($shoeRating);
echo $result["rating"];