PHP提交MySQL查询时$your_model变量值无法被识别的问题

问题描述 #

提交MySQL查询时，其他变量（$odo、$time）均能正常识别，唯独来自HTML下拉列表的$your_model变量无法被MySQL识别。该下拉列表用于获取当前登录用户数据库中的车型：

• 在if(isset($_POST['odo']))语句前后通过echo输出，能看到$your_model的取值
• 提交里程读数后，MySQL可识别$odo和$time，但$your_model的值丢失
• 尝试过调整MySQL代码位置、给变量添加引号/大括号包裹等写法，均未解决问题

核心原因 #

代码中存在两个独立的表单：

1. 第一个表单用于选择车型并提交，此时$your_model会被存入$_POST
2. 第二个表单仅用于提交里程读数，未携带$your_model变量

当用户提交里程时，页面刷新发起新的POST请求，该请求中只有$odo，没有$your_model，导致变量丢失，最终MySQL语句中的$your_model为空值。

修复方案 #

方案一：在里程表单中添加隐藏字段传递$your_model #

在提交里程的表单里加入隐藏字段，将已选中的车型值一同提交：

echo <<<_END
        <form method='post' action='car_test.php?view=$view'>
                <!-- 添加隐藏字段传递选中的车型 -->
                <?php if(isset($your_model)): ?>
                <input type="hidden" name="selectAModel" value="$your_model">
                <?php endif; ?>
                <input type='text' id='odo' name='odo'>
                <label for='odo'>Current Odometer Reading</label><br>
                <input data-transition='slide' type='submit' value='Submit Odometer Reading'>
        </form>
_END;

方案二：合并两个表单（业务逻辑允许时） #

如果不需要分步提交，可将车型选择和里程输入合并为一个表单，用户一次完成选择和提交：

echo <<<_END
        <form method='post' action='car_test.php?view=$view'>
                <select name="selectAModel">
                <option value='Select Model'>Select A Model </option>
_END;
                foreach($selectedModels as $modelName) {
                        // 保持选中状态
                        $selected = isset($your_model) && $your_model == $modelName ? "selected" : "";
                        echo "<option value='$modelName' $selected> $modelName </option>";
                }

echo <<<_END
                </select>
                <br><br>
                <input type='text' id='odo' name='odo'>
                <label for='odo'>Current Odometer Reading</label><br>
                <input data-transition='slide' type='submit' value='Submit Odometer Reading'>
        </form>
_END;

额外优化：使用预处理语句防止SQL注入 #

原代码直接拼接变量到SQL语句中存在安全风险，建议改用MySQLi预处理语句：

if (isset($_POST['odo']) && isset($_POST['selectAModel']))
{
        $odo = sanitizeString($_POST['odo']);
        $your_model = sanitizeString($_POST['selectAModel']);
        if ($odo != "")
        {
                $time = time();
                // 预处理语句
                $stmt = $conn->prepare("UPDATE cars SET odo = ?, time = ? WHERE user = ? AND model = ?");
                $stmt->bind_param("siss", $odo, $time, $user, $your_model);
                $stmt->execute();
                $stmt->close();
        }
}

（注：需确保$conn是你的数据库连接对象）

完整修正代码（方案一） #

<?php
require_once 'header.php';

if (!$loggedin) die("</div></body></html>");

if (isset($_GET['view'])) $view = sanitizeString($_GET['view']);
else $view = $user;

if ($view != "")
{
        if ($view == $user) $name1 = $name2 = "Your";
        else
        {
                $name1 = "<a href='members.php?view=$view'>$view</a>'s";
                $name2 = "$view's";
        }

        echo "<h4>$name1 Car Portal</h4>";
        showProfile($view);

        echo "<br><br>";

        $car_query = "SELECT model FROM cars WHERE user='$user'";
        $car_result = queryMysql($car_query);
        $num_of_cars = $car_result->num_rows;

        $selectedModels = [];
        if ($num_of_cars > 0) {
                while ($row = mysqli_fetch_assoc($car_result)) {
                        $selectedModels[] = $row['model'];
                }
        }

        if (isset($_POST['selectAModel'])) $your_model = sanitizeString($_POST['selectAModel']);

        echo <<<_END
                <form method='post' action='car_test.php?view=$view'>
                        <select name="selectAModel">
                        <option value='Select Model'>Select A Model </option>
        _END;
                foreach($selectedModels as $modelName) {
                        $selected = isset($your_model) && $your_model == $modelName ? "selected" : "";
                        echo "<option value='$modelName' $selected> $modelName </option>";
                }

        echo <<<_END
                        </select>
                        <input type='submit' name='post_car' value='Submit Car Model'>
                </form>
        _END;

        echo "<br><br>";
        // 避免未选择车型时出现undefined变量
        $displayModel = isset($your_model) ? $your_model : "selected";
        echo "<div style='text-align:center;'><h3>Your $displayModel Odometer Reading</h3></div>";

        if (!isset($_POST['odo']) && isset($your_model))
        {
                echo "<span class='info' style='color:red'>You have not entered your $your_model current odometer reading yet</span><br>";
        }

        if (isset($_POST['odo']) && isset($_POST['selectAModel']))
        {
                $odo = sanitizeString($_POST['odo']);
                $your_model = sanitizeString($_POST['selectAModel']);
                if ($odo != "")
                {
                        $time = time();
                        queryMysql("UPDATE cars SET odo = '$odo', time = '$time' WHERE user = '$user' AND model = '$your_model'");
                }
        }

        echo <<<_END
                <form method='post' action='car_test.php?view=$view'>
                        <?php if(isset($your_model)): ?>
                        <input type="hidden" name="selectAModel" value="$your_model">
                        <?php endif; ?>
                        <input type='text' id='odo' name='odo'>
                        <label for='odo'>Current Odometer Reading</label><br>
                        <input data-transition='slide' type='submit' value='Submit Odometer Reading'>
                </form>
        _END;

        echo "<br>";

        date_default_timezone_get();

        if (isset($_GET['erase']))
        {
                $erase = sanitizeString($_GET['erase']);
                queryMysql("DELETE FROM car WHERE odo='$erase' AND user='$user'");
        }

        $query = "SELECT * FROM car WHERE user='$view' ORDER BY time DESC";
        $result = queryMysql($query);
        $num = $result->num_rows;

        for ($j = 0; $j < $num; ++$j)
        {
                $row = $result->fetch_array(MYSQLI_ASSOC);
                if ($row['odo'] != "" && $row['user'] == $user)
                {
                        echo date('M jS \'y g:ia:', $row['time']);
                        echo "<br><br>";
                        echo "<a href='car.php?view=" . $row['user'] . "'>" . $row['user']. "</a>";
                        echo "<br>";
                        echo "last oil change was at: " . $row['odo'] . "<br>";
                        $current_odo = (int) $row['odo'];
                        $eight_thousand = 8000;
                        $next_change = $current_odo + $eight_thousand;
                        echo "your next oil change is at: $next_change";
                        if ($row['user'] == $user)
                                echo "<br><br>[<a href='car.php?view=$view" . "&erase=" . $row['odo'] . "'>erase</a>]";
                        echo "<br><br><br>";
                }
        }
}
?>
</div><br>
</body>
</html>

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