php - [PHP]编辑记录，更新mysql查询

Question

我有一个如下所示的表格:

survey_id | question_id | option_one  | option_two|  ... option_ten|
    1     |      1      |    Yes      |    No     |        ...     |
    1     |      2      |    True     |   False   |        ...     |

这是我在更新表格之前将编辑的选项保存到二维数组中的代码:

   $store = array();

    $num = 0;
    foreach ($_POST['question_id'] as $option) {

        echo"<br/>";
        $i = 0;
        foreach ($option as $key) {

            $store[$num][$i] = $key;

            $i+=1;
        }

        //$question_num += 1;
        $num+=1;
    }

当我print_r($store)时，它包含所有编辑的内容，这意味着它可以工作。但是，当我尝试更新时:

foreach ($store as $key2 => $option2) {

        if ($option2 != NULL) {

            mysql_query(sprintf($sql,mysql_real_escape_string($option2[0]), mysql_real_escape_string($option2[1]), mysql_real_escape_string($option2[2]), mysql_real_escape_string($option2[3]), mysql_real_escape_string($option2[4]), mysql_real_escape_string($option2[5]), mysql_real_escape_string($option2[6]), mysql_real_escape_string($option2[7]), mysql_real_escape_string($option2[8]), mysql_real_escape_string($option2[9]), mysql_real_escape_string($option2[10]), mysql_real_escape_string($option2[11]), mysql_real_escape_string($option2[12]), mysql_real_escape_string($option2[13])));

            $key2+=1;
        }

         $sql = "UPDATE option_table SET option_one='%s', option_two='%s', option_three='%s', option_four='%s', option_five='%s', option_six='%s', option_seven='%s',  option_eight='%s', option_nine='%s', option_ten='%s', option_eleven='%s', option_twelve='%s', option_thirteen='%s', option_fourteen='%s'
          WHERE survey_id= '$survey_id' AND question_id= '$key2'";


    }

它无法正确更新。请帮我。谢谢!

Answer 1

据我猜测，survery_id 和 question_id 是整数。不要使用引号括住查询中的值。另外，您可以在设置 $sql 之前执行查询。您应该修改代码如下:

if ($option2 != NULL) {
    $sql = "UPDATE option_table SET option_one='%s', option_two='%s', option_three='%s', option_four='%s', option_five='%s', option_six='%s', option_seven='%s',  option_eight='%s', option_nine='%s', option_ten='%s', option_eleven='%s', option_twelve='%s', option_thirteen='%s', option_fourteen='%s'
              WHERE survey_id= $survey_id AND question_id= $key2";
    mysql_query(sprintf($sql,mysql_real_escape_string($option2[0]), mysql_real_escape_string($option2[1]), mysql_real_escape_string($option2[2]), mysql_real_escape_string($option2[3]), mysql_real_escape_string($option2[4]), mysql_real_escape_string($option2[5]), mysql_real_escape_string($option2[6]), mysql_real_escape_string($option2[7]), mysql_real_escape_string($option2[8]), mysql_real_escape_string($option2[9]), mysql_real_escape_string($option2[10]), mysql_real_escape_string($option2[11]), mysql_real_escape_string($option2[12]), mysql_real_escape_string($option2[13])));

    $key2+=1;
}

旁注:停止使用已弃用的 mysql_* 函数。请改用 MySQLi 或 PDO。