php - 更新到 MySQLi 并遇到一些问题

Question

所以我花了一些时间研究 MySQLi，但我在用新函数更新脚本时遇到了问题。此脚本用于动态下拉表单，使用通过 JS 发送给它的数据。您可以找到脚本的实时版本 here 查看我在说什么。我上下查看了我的代码，并将其与其他 MySQLi 示例进行了比较，但我不确定我哪里出错了。

现在，第一个下拉列表甚至不启动查询，PHP 所做的只是返回预定义的结果，因为第一个选项更简单。对我来说奇怪的是，当它根本不依赖 MySQLi 连接时，即使是第一个下拉菜单现在也不起作用。更新前都有效，仅供引用。

这是我的脚本:

$db = new mysqli($dbHost, $dbUser, $dbPass, $dbDatabase);

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

//prevents injections
//any order
isset($_GET['type'])?$type = urldecode($_GET['type']):"";
isset($_GET['source'])?$source = $db->real_escape_string(urldecode($_GET['source'])):"";
isset($_GET['range'])?$power = $db->real_escape_string(urldecode($_GET['range'])):"";
isset($_GET['setpoint'])?$setpoint = $db->real_escape_string(urldecode($_GET['setpoint'])):"";

//forms the query depending on what data is recieved through GET
//first option on the bottom; last option on the top to avoid conflicts 
if (isset($_GET['setpoint'])) {
    $query = "SELECT DISTINCT stp FROM meters WHERE sio='$range' AND pso='$power' AND stp='$setpoint' ORDER BY model";
} elseif (isset($_GET['power'])) {
    $query = "SELECT DISTINCT stp FROM meters WHERE sio='$range' AND pso='$power' ORDER BY model";
} elseif (isset($_GET['range'])) {
    $query = "SELECT DISTINCT pso FROM meters WHERE sio='$range' ORDER BY model";
} elseif (isset($_GET['source'])) {
    $query = "SELECT DISTINCT sir FROM meters WHERE sio LIKE '%$source%' ORDER BY sir";
}

//creates a result array from query results
isset($query)?$result = $db->query($query):"";

//outputs dropdown options dependent on what GET variables are set
//first option on the bottom; last option on the top to avoid conflicts
if (isset($_GET['setpoint'])) {
    while ($row = $result->fetch_assoc()) {
        echo "<option value='" . $row['stp'] . "'>" . $row['stp'] . "</option>";
    $result->free();
    }
} elseif (isset($_GET['power'])) {
    echo "<option>Please Choose Setpoint Options</option>";
    while ($row = $result->fetch_assoc()) {
        $row{'stp'} = ucfirst($row{'stp'}); //capitalizes the first letter; necessary?
        echo "<option value='" . $row['stp'] . "'>" . $row['stp'] . "</option>";
    $result->free();
    }
} elseif (isset($_GET['source'])) {
    echo "<option>Please Choose Input Range</option>";
    while ($row = $result->fetch_assoc()) {
        echo "<option value='" . $row['sir'] . "'>" . $row['sir'] . "</option>";
    $result->free();
    }
} elseif (isset($_GET['type']) && $_GET['type'] == "Digital") {
    echo "<option>Please Choose Input Source</option>";
    echo "<option value='RS232C'>RS232C</option><option value='RS422'>RS422</option><option value='RS485'>RS485</option><option value='current loop'>current loop</option>";
    $result->free();
} elseif (isset($_GET['type']) && $_GET['type'] == "Analog") {
    echo "<option>Please Choose Input Source</option>";
    echo "<option value='DC current'>DC Current</option><option value='DC voltage'>DC Voltage</option><option value='AC current'>AC Current</option><option value='AC voltage'>AC Voltage</option><option value='process'>Process</option><option value='thermocouple'>Thermocouple</option><option value='RDT'>rdt</option>";
    $result->free();
}

编辑:这是我使用已弃用方法的旧代码。

$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());

mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());


//prevents injections
//any order
isset($_GET['type'])?$type = urldecode($_GET['type']):"";
//$type = mysql_real_escape_string(urldecode($_GET['type']));
isset($_GET['source'])?$source = mysql_real_escape_string(urldecode($_GET['source'])):"";
isset($_GET['range'])?$power = mysql_real_escape_string(urldecode($_GET['range'])):"";
isset($_GET['setpoint'])?$setpoint = mysql_real_escape_string(urldecode($_GET['setpoint'])):"";

//forms the query depending on what data is recieved through GET
//first option on the bottom; last option on the top to avoid conflicts 
if (isset($_GET['setpoint'])) {
    $query = "SELECT DISTINCT stp FROM meters WHERE sio='$range' AND pso='$power' AND stp='$setpoint' ORDER BY model";
} elseif (isset($_GET['power'])) {
    $query = "SELECT DISTINCT stp FROM meters WHERE sio='$range' AND pso='$power' ORDER BY model";
} elseif (isset($_GET['range'])) {
    $query = "SELECT DISTINCT pso FROM meters WHERE sio='$range' ORDER BY model";
} elseif (isset($_GET['source'])) {
    $query = "SELECT DISTINCT sir FROM meters WHERE sio LIKE '%$source%' ORDER BY sir";
}

//creates a result array from query results
isset($query)?$result = mysql_query($query):"";

//outputs dropdown options dependent on what GET variables are set
//first option on the bottom; last option on the top to avoid conflicts
if (isset($_GET['setpoint'])) {
    while ($row = mysql_fetch_array($result)) {
        echo "<option value='" . $row{'stp'} . "'>" . $row{'stp'} . "</option>";
    }
} elseif (isset($_GET['power'])) {
    echo "<option>Please Choose Setpoint Options</option>";
    while ($row = mysql_fetch_array($result)) {
        $row{'stp'} = ucfirst($row{'stp'}); //capitalizes the first letter; necessary?
        echo "<option value='" . $row{'stp'} . "'>" . $row{'stp'} . "</option>";
    }
} elseif (isset($_GET['source'])) {
    echo "<option>Please Choose Input Range</option>";
    while ($row = mysql_fetch_array($result)) {
        echo "<option value='" . $row{'sir'} . "'>" . $row{'sir'} . "</option>";
    }
} elseif (isset($_GET['type']) && $_GET['type'] == "Digital") {
    echo "<option>Please Choose Input Source</option>";
    echo "<option value='RS232C'>RS232C</option><option value='RS422'>RS422</option><option value='RS485'>RS485</option><option value='current loop'>current loop</option>";
} elseif (isset($_GET['type']) && $_GET['type'] == "Analog") {
    echo "<option>Please Choose Input Source</option>";
    echo "<option value='DC current'>DC Current</option><option value='DC voltage'>DC Voltage</option><option value='AC current'>AC Current</option><option value='AC voltage'>AC Voltage</option><option value='process'>Process</option><option value='thermocouple'>Thermocouple</option><option value='RDT'>rdt</option>";
}

Answer 1

您正在 while 循环内释放您的 $result。这将导致循环在第二次迭代时失败。

既然您要释放所有 if 中的结果，为什么不在最后做一次呢？

...
} elseif (isset($_GET['type']) && $_GET['type'] == "Analog") {
    echo "<option>Please Choose Input Source</option>";
    echo "<option value='DC current'>DC Current</option><option value='DC voltage'>DC Voltage</option><option value='AC current'>AC Current</option><option value='AC voltage'>AC Voltage</option><option value='process'>Process</option><option value='thermocouple'>Thermocouple</option><option value='RDT'>rdt</option>";
}

$result->free();

但这并不能解释为什么模拟和数字仍然不能工作..