警告：mysql_fetch_array（）期望参数1是资源，布尔给定7 [重复]

Question

This question already has an answer here:

• mysql_fetch_array()/mysql_fetch_assoc()/mysql_fetch_row()/mysql_num_rows etc… expects parameter 1 to be resource or result 32 answers

I've been writing a php code for a search engine, and I thought my query is fine, but I'm stuck for hours instead because it says: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in ..... on line 129,

Code:

if(isset($_POST['hanapin'])){

    $staff = "select e_id,e_fname,e_mi,e_lname,e_fin_cm,department,job
        from employees where ".$_POST['tableya']."
        like ".$_POST['whatever']."% order by e_lname"; //line 129

    $result = mysql_query($staff);  

    while($staff_rows = mysql_fetch_array($result)){

        echo "
        <tr>
        <td>".$staff_rows['e_id']."</td>
        <td>".htmlspecialchars_decode($staff_rows['e_lname'])."</td>
        <td>".htmlspecialchars_decode($staff_rows['e_fname'])."</td>
        <td>".htmlspecialchars_decode($staff_rows['e_mi'])."</td>
        <td>".htmlspecialchars_decode($staff_rows['e_fin_cm'])."</td>
        <td>".htmlspecialchars_decode($staff_rows['department'])."</td>
        <td>".htmlspecialchars_decode($staff_rows['job'])."</td>
        </tr>
    ";
    }

}

Help will be much appreciated.

</div>

Answer 1

Firstly, I've edited your post slightly to make the code more readable as the formatting was a bit off.

Secondly, your SQL query is very prone to SQL injection attacks as you are directly using POST variables in the query without first sanitising them. You should always sanitise variables before using them in queries. If you're expecting an integer, I suggest you do it as follows:

$var = (isset($_POST['var']) ? (int)$_POST['var'] : null);

And strings as follows:

$var = (isset($_POST['var']) ? mysql_real_escape_string($_POST['var']) : null);

Thirdly, the mysql_*() functions have been deprecated and will be removed from a future version of PHP. It is currently recommended that you use the mysqli_*() functions or class, or even better the PDO library.

And lastly, regarding your error, MySQL is returning an error number as your query is not valid. Your statement should read as follows:

$staff = "SELECT `e_id`, `e_fname`, `e_mi`, `e_lname`,
                 `e_fin_cm`, `department`, `job`
          FROM `employees`
          WHERE {$fieldname} LIKE {$fieldvalue}
          ORDER BY `e_lname` ASC";

This, in conjunction with the following checking on those fields, should work:

$fieldname = (isset($_POST['tableya'])
                 && in_array($_POST['tableya'], array(
                     'e_id', 'e_fname', 'e_mi', 'e_lname',
                     'e_fin_cm', 'department', 'job'
                 )) ? mysql_real_escape_string($_POST['tableya']) : null);

$fieldvalue = (isset($_POST['whatever'])
                ? '\'' . mysql_real_escape_string($_POST['whatever']) . '%\''
                : null);

if ($fieldname && $fieldvalue) {
    $sql = "SELECT `e_id`, `e_fname`, `e_mi`, `e_lname`,
                   `e_fin_cm`, `department`, `job`
            FROM `employees`
            WHERE {$fieldname} LIKE {$fieldvalue}
            ORDER BY `e_lname` ASC";
    $result = mysql_query($sql);
    if ($result) {
        while ($row = mysql_fetch_assoc($result)) {
            // output data
        }
        mysql_free_result($result);
    } else {
        // Query was invalid
        print('MySQL error: [' . mysql_errno() . '] ' . mysql_error());
    }
} else {
   print('Invalid field name or value.');
}