mysql query returning null when not should to

Question

I use this PHP code to get value from my DB.

My problem is that i got null result.

I check for !empty($result) and for mysql_num_rows($result) but still i got null result.

I tested exactly the same query i use in my code on my phpmyadmin and it working.

The echo from $response["SQL"] is "good".

Here is my PHP code:

$result = mysql_query("SELECT * FROM workouts_wall WHERE workout_name = 'WO13' AND user = 'tomer2'") or die (mysql_error());


// mysql inserting a new row
 if (!empty($result))
 {
    if (mysql_num_rows($result) > 0) 
    {
        $response["SQL"] = "good";
    }
    else
    {
        $response["SQL"] = "bad";
    }
}

else    
{
    $response["SQL"] = "bad";
}   


$response["is null?"] = $result;
    // echoing JSON response
    echo json_encode($response);

Solved

I added this line to fix it:

$row = mysql_fetch_array($result);

Have you checked for any errors? `mysql_error()` may shed some light on your issue. — Kirk Backus, Jul 23 '13 at 19:34
Have you connected to the db? BTW, you should be using either `mysqli` or `PDO` - the `mysql` functions are not secure. — Adam Hopkinson, Jul 23 '13 at 19:35
I think that im connected i dont get any error from my mysql_query. Thanks for the advice about mysqli and PDO. — dasdasd, Jul 23 '13 at 19:37
@adam It's not that the `mysql_*()` functions are necessarily insecure (if implemented correctly), it's that they're deprecated. Bad idea to write code using functions that won't be there in the future. — WWW, Jul 23 '13 at 19:37
how come `$response["SQL"]` is `"good"` if you are getting `null`? — akonsu, Jul 23 '13 at 19:38
You are not checking the error you may be getting from the query. Try adding `mysql_error()` output into the `else` block of code when `$result` is being checked. — Mike Brant, Jul 23 '13 at 19:38
@akonsu i dont know how it is possible. Thats what my echo said. — dasdasd, Jul 23 '13 at 19:40

score 1 · Answer 1 · answered Jul 23 '13 at 19:40

1

You can't just return the mysql_query results. You must fetch the data.

$data = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
    $data[] = $row;

echo(json_encode($data));

//EDIT: Also a good idea when you are done...
mysql_free_result($result);

answered Jul 23 '13 at 19:40

Kirk Backus

4,776
4
32
52

when i try to get values from my result i gets null. If i do `$result["workout_name"]` and echo it i get null value – dasdasd Jul 23 '13 at 19:44
You can't do `$result["workout_name"]`, you need to fetch the row from your data first, and then do something like `echo $row["workout_name"]` – Kirk Backus Jul 23 '13 at 19:46
I tried `$row = mysql_fetch_array($result)) $response["check"] = $row["workout_name"];` and i still got null for my echo – dasdasd Jul 23 '13 at 19:51

score 0 · Answer 2 · edited May 23 '17 at 12:29

The mysql_query() function returns either a resource or a boolean. If your query is successful (as it probably is in this case), you're still only assigning the MySQL resource to your $response variable. You need to actually use something like mysql_fetch_array() or mysql_fetch_assoc() to extract the returned data.

Regarding not using the mysql_*() functions: Why shouldn't I use mysql_* functions in PHP?

score 0 · Answer 3 · answered Jul 23 '13 at 19:44

try this. hope it work:

$result = mysql_query("SELECT * FROM workouts_wall WHERE workout_name = 'WO13' AND user = 'tomer2'") or die (mysql_error());


    // mysql inserting a new row
     if (!empty(mysql_fetch_array($result)))
     {
        if (mysql_num_rows(($result) > 0) 
        {
            $response["SQL"] = "good";
        }
        else
        {
              $response["SQL"] = "bad";
        }
    }

    else    
    {
        $response["SQL"] = "bad";
    }   


    $response["is null?"] = $result;
        // echoing JSON response
        echo json_encode($response);

Marc B · Answer 4 · 2013-07-23T20:02:21.060

0

A mysql result which has no rows is NOT empty. It's a standard mysql result handle which simply happens to actually contain no rows. It's not an error, it's not an empty string, it's not an empty array... it's a standard result handle, like any other result handle.

Your code should be

$result = mysql_query($sql) or die(mysql_error()); // catch the REAL errors
if (mysql_num_rows($result) == 0) { 
   ... result set is empty
}

edited Jul 23 '13 at 20:02

answered Jul 23 '13 at 19:49

Marc B

356,200
43
426
500

Sorry, brainfart on my part. Should've been mysql_num_rows, not count. – Marc B Jul 23 '13 at 20:02

mysql query returning null when not should to

4 Answers4