PHP mysqli_fetch_array报错及INSERT功能失效问题求助

Hey there! Let's work through the problems in your code step by step to get that INSERT functionality working, and fix the error you're seeing.

1. Fix the mysqli_fetch_array() Error (Line 43) #

The immediate error you're getting happens because you're passing a SQL string directly to mysqli_fetch_array()—this function expects a mysqli_result object returned by mysqli_query(), not raw SQL.

Wrong Code: #

$query = "SELECT * FROM devicelocations WHERE deviceId = '$deviceId';";
$arr = mysqli_fetch_array($query); // ❌ Passing string instead of result set

Corrected Code: #

$query = "SELECT * FROM devicelocations WHERE deviceId = '$deviceId';";
$result = mysqli_query($connect, $query); // ✅ First execute the query to get a result set
$arr = mysqli_fetch_array($result); // Now pass the result set to fetch_array

2. Fix the Empty Result Check Logic #

Your current sizeof($arr) == 1 check is incorrect. When no rows are found for the deviceId, mysqli_fetch_array() returns false, not an array of size 0. A better approach is to check the number of rows returned by the query using mysqli_num_rows():

Corrected Check: #

$result = mysqli_query($connect, $query);
if (mysqli_num_rows($result) === 0) { // ✅ No matching deviceId, run INSERT
    error_log("No entry found for deviceId, executing INSERT.", 0);
    // INSERT query here
} else { // Matching deviceId exists, run UPDATE
    error_log("Entry exists for deviceId, executing UPDATE.", 0);
    // UPDATE query here
}

Since deviceId is your primary key, this query will return at most 1 row, so this check works perfectly.

3. Fix Syntax Errors in Your Code #

There's a typo in your required fields check: i sset($_POST['heading']) has an extra space between i and sset. This will cause the code to think the heading field is missing, even when it's provided.

Corrected Line: #

isset($_POST['j']) && isset($_POST['heading']) // ✅ Removed extra space

4. Critical: Fix SQL Injection Vulnerabilities #

Directly concatenating user input (from $_POST) into your SQL queries is extremely dangerous—it leaves your database open to SQL injection attacks. You should use prepared statements (parameterized queries) instead to sanitize input.

Example Prepared Statements: #

For INSERT:

$stmt = mysqli_prepare($connect, "INSERT INTO devicelocations (deviceId, buildingId, levelId, floorplanId, latitude, longitude, x, y, i, j, heading, probability, roundtrip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
// Bind parameters: "s" = string, "d" = double/decimal (adjust types as needed for your table)
mysqli_stmt_bind_param($stmt, "ssssddddddddd", $deviceId, $buildingId, $levelId, $floorplanId, $latitude, $longitude, $x, $y, $i, $j, $heading, $probability, $roundtrip);
$success = mysqli_stmt_execute($stmt);

For UPDATE:

$stmt = mysqli_prepare($connect, "UPDATE devicelocations SET buildingId = ?, levelId = ?, floorplanId = ?, latitude = ?, longitude = ?, x = ?, y = ?, i = ?, j = ?, heading = ?, probability = ?, roundtrip = ? WHERE deviceId = ?");
mysqli_stmt_bind_param($stmt, "ssssddddddddd", $buildingId, $levelId, $floorplanId, $latitude, $longitude, $x, $y, $i, $j, $heading, $probability, $roundtrip, $deviceId);
$success = mysqli_stmt_execute($stmt);

5. Fix Success Validation Logic #

Your current if ($query) check is useless because $query is a string, which will always evaluate to true. Instead, check the result of the query execution (the $success variable from prepared statements, or mysqli_query() return value):

Corrected Success Check: #

if ($success) {
    $response["success"] = 1;
    $response["message"] = "Entry successfully inserted or updated.";
} else {
    $response["success"] = 0;
    $response["message"] = "Failed to insert/update entry: " . mysqli_error($connect);
}
echo json_encode($response);

Full Corrected Code #

Here's the complete fixed code with all the above changes applied:

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    require 'connectiontest.php';
    createStudent();
}

$response = array();

function createStudent() {
    global $connect, $response;

    // Check for required fields (fixed typo)
    if (isset($_POST['deviceId']) && isset($_POST['buildingId']) && isset($_POST['levelId']) && isset($_POST['floorplanId']) && isset($_POST['latitude']) && isset($_POST['longitude']) && isset($_POST['x']) && isset($_POST['y']) && isset($_POST['i']) && isset($_POST['j']) && isset($_POST['heading']) && isset($_POST['probability']) && isset($_POST['roundtrip'])) {

        // Extract POST data
        $deviceId = $_POST['deviceId'];
        $buildingId = $_POST['buildingId'];
        $levelId = $_POST['levelId'];
        $floorplanId = $_POST['floorplanId'];
        $latitude = $_POST['latitude'];
        $longitude = $_POST['longitude'];
        $x = $_POST['x'];
        $y = $_POST['y'];
        $i = $_POST['i'];
        $j = $_POST['j'];
        $heading = $_POST['heading'];
        $probability = $_POST['probability'];
        $roundtrip = $_POST['roundtrip'];

        // Check if deviceId exists
        $checkQuery = "SELECT * FROM devicelocations WHERE deviceId = ?";
        $stmt = mysqli_prepare($connect, $checkQuery);
        mysqli_stmt_bind_param($stmt, "s", $deviceId);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);

        $success = false;
        if (mysqli_num_rows($result) === 0) {
            error_log("No entry found for deviceId, executing INSERT.", 0);
            // Insert new record
            $insertStmt = mysqli_prepare($connect, "INSERT INTO devicelocations (deviceId, buildingId, levelId, floorplanId, latitude, longitude, x, y, i, j, heading, probability, roundtrip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            mysqli_stmt_bind_param($insertStmt, "ssssddddddddd", $deviceId, $buildingId, $levelId, $floorplanId, $latitude, $longitude, $x, $y, $i, $j, $heading, $probability, $roundtrip);
            $success = mysqli_stmt_execute($insertStmt);
            mysqli_stmt_close($insertStmt);
        } else {
            error_log("Entry exists for deviceId, executing UPDATE.", 0);
            // Update existing record
            $updateStmt = mysqli_prepare($connect, "UPDATE devicelocations SET buildingId = ?, levelId = ?, floorplanId = ?, latitude = ?, longitude = ?, x = ?, y = ?, i = ?, j = ?, heading = ?, probability = ?, roundtrip = ? WHERE deviceId = ?");
            mysqli_stmt_bind_param($updateStmt, "ssssddddddddd", $buildingId, $levelId, $floorplanId, $latitude, $longitude, $x, $y, $i, $j, $heading, $probability, $roundtrip, $deviceId);
            $success = mysqli_stmt_execute($updateStmt);
            mysqli_stmt_close($updateStmt);
        }

        // Return response
        if ($success) {
            $response["success"] = 1;
            $response["message"] = "Entry successfully inserted or updated.";
        } else {
            $response["success"] = 0;
            $response["message"] = "Failed to process entry: " . mysqli_error($connect);
        }
    } else {
        // Required fields missing
        $response["success"] = 0;
        $response["message"] = "Required field(s) is missing";
    }

    mysqli_close($connect);
    echo json_encode($response);
}
?>

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