如何将save_percentage函数适配到我的MySQL评分系统项目中？

开发评分系统项目（学习阶段），现有代码适配SQLite3，需修改为适配MySQL+XAMPP环境，核心是调整save_percentage函数。以下是现有代码：

前端提交代码 #

$('#percentage-form').submit(function(e){
    e.preventDefault();
    $('.pop_msg').remove()
    var _this = $(this)
    var total = $('#total').text()
        total = total.replace(/\%/gi,'')
        console.log(total)
    if(parseFloat(total) !== 100)
    {
        alert("Total Percentage must be 100%");
        return false;
    }
    var _el = $('<div>')
        _el.addClass('pop_msg')
    $('#uni_modal button').attr('disabled',true)
    $('#uni_modal button[type="submit"]').text('submitting form...')
    $.ajax({
        url:'./Actions.php?a=save_percentage',
        method:'POST',
        data:$(this).serialize(),
        dataType:'JSON',
        error:err=>{
            console.log(err)
            _el.addClass('alert alert-danger')
            _el.text("An error occurred.")
            _this.prepend(_el)
            _el.show('slow')
             $('#uni_modal button').attr('disabled',false)
             $('#uni_modal button[type="submit"]').text('Save')
        },
        success:function(resp){
            if(resp.status == 'success'){
                _el.addClass('alert alert-success')
                $('#uni_modal').on('hide.bs.modal',function(){
                    location.reload()
                })
            }else{
                _el.addClass('alert alert-danger')
            }
            _el.text(resp.msg)

            _el.hide()
            _this.prepend(_el)
            _el.show('slow')
             $('#uni_modal button').attr('disabled',false)
             $('#uni_modal button[type="submit"]').text('Save')
        }
    })
})

原Actions.php代码 #

<?php 

Class Actions{
    function save_percentage(){
        extract($_POST);
        $data = "";
        foreach($component_id as $k => $v){
            if(!empty($data)) $data .= ", ";
            $data .= "('$id','{$v}','{$percentage[$k]}')";
        }
        if(!empty($data))
        $this->query("DELETE FROM `component_subject_percentage` where `subject_id` = '{$id}'");
        $sql = "INSERT INTO `component_subject_percentage` (`subject_id`,`component_id`,`percentage`)VALUES {$data}";
        $insert = $this->query($sql);
        if($insert){
            $resp['status'] ='success';
            $resp['msg'] = "Data successfully saved";
        }else{
            $resp['status'] ='failed';
            $resp['msg'] = "Data fails to save. Error: ". $this->lastErrorMsg();
            $resp['sql'] = $sql;
        }
        return json_encode($resp);
    }
}
$a = isset($_GET['a']) ?$_GET['a'] : '';
$action = new Actions();
switch($a){

    case 'save_percentage':
        echo $action->save_percentage();
    break;
    default:
    // default action here
    break;
}

DBConnection.php代码 #

<?php
$con=mysqli_connect("localhost", "root", "", "resultgrading");
if(mysqli_connect_errno()){
    echo "Connection Fail".mysqli_connect_error(); 
}

1. 修复Actions类的数据库连接依赖 #

在Actions.php开头引入数据库连接文件，并让类初始化MySQL连接：

<?php
// 引入数据库连接文件
require_once 'DBConnection.php';

Class Actions{
    private $conn;

    // 构造函数初始化数据库连接
    public function __construct(){
        global $con;
        $this->conn = $con;
    }

    // ... 其他方法
}

2. 替换SQLite专属方法为MySQL（mysqli）方法 #

原代码使用的$this->query()、$this->lastErrorMsg()是SQLite类的专属方法，需替换为mysqli对应的函数，同时优化参数处理避免安全风险：
修改后的save_percentage函数：

function save_percentage(){
    // 直接获取POST参数，避免extract的变量覆盖风险
    $id = $_POST['id'] ?? '';
    $component_id = $_POST['component_id'] ?? [];
    $percentage = $_POST['percentage'] ?? [];

    $data = "";
    foreach($component_id as $k => $v){
        if(!empty($data)) $data .= ", ";
        // 转义参数防止SQL注入
        $subject_id = mysqli_real_escape_string($this->conn, $id);
        $comp_id = mysqli_real_escape_string($this->conn, $v);
        $percent = mysqli_real_escape_string($this->conn, $percentage[$k]);
        $data .= "('{$subject_id}','{$comp_id}','{$percent}')";
    }

    $resp = [];
    if(!empty($data)){
        // 先删除对应subject_id的旧数据
        $delete_sql = "DELETE FROM `component_subject_percentage` where `subject_id` = '".mysqli_real_escape_string($this->conn, $id)."'";
        mysqli_query($this->conn, $delete_sql);

        // 插入新数据
        $sql = "INSERT INTO `component_subject_percentage` (`subject_id`,`component_id`,`percentage`)VALUES {$data}";
        $insert = mysqli_query($this->conn, $sql);
        
        if($insert){
            $resp['status'] ='success';
            $resp['msg'] = "Data successfully saved";
        }else{
            $resp['status'] ='failed';
            $resp['msg'] = "Data fails to save. Error: ". mysqli_error($this->conn);
            $resp['sql'] = $sql;
        }
    }else{
        $resp['status'] ='failed';
        $resp['msg'] = "No data to save";
    }
    return json_encode($resp);
}

3. 关键修改点说明 #

• 移除extract($_POST)：该函数存在变量覆盖风险，直接从$_POST取参数更安全
• 添加mysqli_real_escape_string：转义用户输入，避免SQL注入（学习阶段也要养成安全编码习惯）
• 替换$this->query()为mysqli_query($this->conn, $sql)：使用mysqli的官方查询函数
• 替换$this->lastErrorMsg()为mysqli_error($this->conn)：获取MySQL的错误信息
• 新增空数据判断：避免没有提交数据时执行无效SQL

4. 前端代码无需修改 #

前端JS仅负责提交请求和处理响应，与数据库类型无关，保持原代码即可。

内容的提问来源于stack exchange，提问作者KHeisenberg