更新mysql数据库中的记录时出错

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"Midfielder"; $sql = "SELECT name FROM player "; $data = mysql_query($sql); while($rows = mysql_fetch_array($data)){ echo ""; echo $rows['name']; echo ""; }

                    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

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.