Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given when updating record

Question

I am updating a mysql table. i am getting an error as below

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\test\edit.php on line 232 Error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

The query seems not to be producing a result. I am passing an id to a function via a url but the variable seems to die although it seems to be in scope. What could be my error. The loop for the update is below. i have commented out some lines which i thought where a problem but they are fine. code in bold are the problem lines.

elseif(isset($_POST['editSelection']))
{ 
    // check if form is submitted
    //collect variables posted by form.
    $fixture_id = mysql_real_escape_string($_POST['fixture_id']);
    $goalkeeper = mysql_real_escape_string($_POST['goalkeeper']);
    $defender = mysql_real_escape_string($_POST['defender']);
    $fullback = mysql_real_escape_string($_POST['fullback']);
    $midfielder = mysql_real_escape_string($_POST['midfielder']);
    $wing = mysql_real_escape_string($_POST['wing']);
    $striker = mysql_real_escape_string($_POST['striker']);
    $sid = mysql_real_escape_string($_POST['sid']); // receive the selection_id which was posted from the hidden field in the editForm

    $sql = "SELECT * FROM `selections` WHERE selection_id = {$sid}";
    $data = mysql_query($sql);

   **while($rows = mysql_fetch_array($data))
     {
        $opponents = $rows['opponents'];
     }**

    //validate form by checking for empty strings that user might have submitted using strlen() php built-in method. If no empty string form processes
            //if(strlen($fixture_id)>0 && strlen($goalkeeper)>0 && strlen($defender)>0 && strlen($fullback)>0 && strlen($midfielder)>0 && strlen($wing)>0  && strlen($striker)>0 && strlen($selection_id)>0) {  // if form fields are not empty, update Selection record in database

    $sql = "UPDATE `selections` SET goalkeeper ='{$goalkeeper}' WHERE selection_id = {$sid}";                               
    $query = mysql_query($sql) or die("Error executing query ".mysql_error());
                            echo "Selection updated <br/><br/>";    
                            echo "<a href=\"team_selections.php\">Go back to Team Selections page </a>";    

        //}
    }


echo"<tr><td>Midfielder</td><td><select name=\"midfielder\">";
                    $sql = "SELECT name FROM `player` ";
                    $data = mysql_query($sql);
                            while($rows = mysql_fetch_array($data)){
                                echo "<option value={$rows['name']}>";
                                echo $rows['name'];
                                echo "</option>";
                            }

                    echo "</select>";
                    echo "</td></tr>";


                    echo"<tr><td>Wing</td><td><select name=\"wing\">";
                    $sql = "SELECT name FROM `player` ";
                    $data = mysql_query($sql);
                            while($rows = mysql_fetch_array($data)){
                                echo "<option value={$rows['name']}>";
                                echo $rows['name'];
                                echo "</option>";
                            }

                    echo "</select>";
                    echo "</td></tr>";

                    echo"<tr><td>Striker</td><td><select name=\"striker\">";
                    $sql = "SELECT name FROM `player` ";
                    $data = mysql_query($sql);
                            while($rows = mysql_fetch_array($data)){
                                echo "<option value={$rows['name']}>";
                                echo $rows['name'];
                                echo "</option>";
                            }

                    echo "</select>";
                    echo "</td></tr>";


                    echo "<tr><td></td><td><input type=\"hidden\" value=\"{$rows['selection_id']}\" name=\"sid\"></td></tr>"; // create hidden field with selection_id which enables the right selection to be edited
                    echo "<tr><td></td><td><input type=\"submit\" value=\"Update Selection\" name=\"editSelection\"></td></tr>";

                    echo "</table></form>";
            } //end of while loop
        }

Answer 1

Have you tried adding mysql_error() to see what error message you are getting? Change this:

$data = mysql_query($sql);

to this:

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

---

The message you are receiving is saying that the result of that query is a true/false, not a mysql "resource". MySQL resources are the normal response of a mysql query, and they can be "read" by using mysql_fetch_array or mysql_fetch_assoc, etc.

So if you are getting a true/false response, then that particular query isnt giving you the data you desire. Troubleshoot with me: why is that happening?

---

Try this:

"SELECT * FROM `selections` WHERE `selection_id` = '$sid'";

Also, echo out the value of $sid so you can see that it has something in it. A null return will also not work in mysql_fetch_array.

---

Also try echoing out the entire $_POST to see exactly what is being received:

echo '<pre>';
print_r($_POST);
echo '</pre>';

Answer 2

mysql_query() returns a result set if it works, or false if it doesn't work. The fact that you're getting complaints from mysql_fetch_array() about using a boolean where a result set is required means that the query has returned false (ie, it hasn't worked).

Yous should use something like:

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

to see what the actual error is though I'd be looking at something a little more robust in production code. Still, that should be enough to identify the immediate problem.

You may also want to actually output the query before trying to execute it in case, for example, there's a problem with $sid, such as it being empty, or it being a string where your query seems to desire a numeric value.

If it is a string, you'll want to surround {$sid} with single quote marks:

$sql = "SELECT * FROM selections WHERE selection_id = '{$sid}'";

If it's empty, you'll need to track down why, since that will give you the invalid query:

SELECT * FROM selections WHERE selection_id =

---

And, of course, you should be moving to the mysqli_* functions where possible since the mysql_* ones are deprecated.