PHP MySQL Updating table row not actually updating, no errors

Question

I've got a table where the user has the option to edit each row, which updates the row in the database. I'm not getting any errors with this code, it's returning "Updated record successfully" but nothing is actually getting updated in the database.

If anyone could identify the issue here I would greatly appreciate it.

table.php:

<html>

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Flight Table</title>
    <!-- Scripts -->
    <script src="js/jquery-3.1.1.min.js"></script>
    <script src="js/bootstrap.min.js"></script>
    <script type="text/javascript" src="modify_records.js"></script>
    <!-- CSS -->
    <link href="css/font-awesome.min.css" rel="stylesheet" type="text/css">
    <link href="css/bootstrap.css" rel="stylesheet">
</head>

<body>

    <div class="section">
        <div class="container">
            <div class="row">
                <div class="col-md-12">
                    <table class="table table-striped table-bordered table-hover" id="table">
                        <thead>
                            <tr>
                                <th>Edit</th>
                                <th>ID</th>
                                <th>Date</th>
                                <th>Aircraft</th>
                                <th>Nature of flight</th>
                                <th>Authorised By</th>
                                <th>Duration</th>
                            </tr>
                        </thead>

                        <?php 

                        include 'config.php';
                        $conn= new mysqli($servername, $username, $password, $dbname); 
                        if ($conn->connect_error) { 
                        die("Connection failed: " . $conn->connect_error); } 

                        $sql = "SELECT * FROM tbl_flights;"; 
                        $result = $conn->query($sql); 

                        ?>

                        <tbody>
                            <?php while ($row=$result->fetch_assoc()) { ?>
                            <tr>
                                <?php echo "<td align='center'><a href='edit_form.php?flight_id=" . $row['flight_id'] . "'>Edit</a></td>"; ?>
                                <td>
                                    <?php echo $row['flight_id']; ?>
                                </td>
                                <td>
                                    <?php echo $row['flight_date']; ?>
                                </td>
                                <td>
                                    <?php echo $row['aircraft_id']; ?>
                                </td>
                                <td>
                                    <?php echo $row['flight_nature']; ?>
                                </td>
                                <td>
                                    <?php echo $row['auth_by']; ?>
                                </td>
                                <td>
                                    <?php echo $row['auth_duration']; ?>
                                </td>
                            </tr>
                            <?php } ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</body>
</html>

edit_form.php:

<html>

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Edit</title>
    <!-- Scripts -->
    <script src="js/jquery-3.1.1.min.js"></script>
    <script src="js/bootstrap.min.js"></script>
    <script type="text/javascript" src="modify_records.js"></script>
    <!-- CSS -->
    <link href="css/font-awesome.min.css" rel="stylesheet" type="text/css">
    <link href="css/bootstrap.css" rel="stylesheet">
</head>

<body>

<?php

if (isset($_GET['flight_id']) && is_numeric($_GET['flight_id'])) {
    // get the 'id' variable from the URL
    $flight_id = $_GET['flight_id'];

    include 'config.php';
    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $sql    = "SELECT * FROM tbl_flights WHERE flight_id = $flight_id";
    $result = $conn->query($sql);
    $row    = $result->fetch_assoc();
}

?>

    <form action="action.php" method="post">
        <p>
            <input type="hidden" name="flight_id" id="flight_id" value="<?php echo $row['flight_id']; ?>">
        </p>
        <p>
            <label for="flight_date">Date:</label>
            <input type="text" name="flight_date" id="flight_date" value="<?php echo $row['flight_date']; ?>">
        </p>
        <p>
            <label for="aircraft_id">Aircraft:</label>
            <input type="text" name="aircraft_id" id="aircraft_id" value="<?php echo $row['aircraft_id']; ?>">
        </p>
        <p>
            <label for="flight_nature">Nature of Flight:</label>
            <input type="text" name="flight_nature" id="flight_nature" value="<?php echo $row['flight_nature']; ?>">
        </p>
        <p>
            <label for="auth_by">Auth By:</label>
            <input type="text" name="auth_by" id="auth_by" value="<?php echo $row['auth_by']; ?>">
        </p>
        <p>
            <label for="auth_duration">Auth Duration:</label>
            <input type="text" name="auth_duration" id="auth_duration" value="<?php echo $row['auth_duration']; ?>">
        </p>
        <input type="submit" value="Save">
    </form>
</body>
</html>

action.php:

<?php

// database connection
include 'pdo_config.php';
try {
$conn = new PDO($dsn, $user, $pass, $opt);

// post data
$flight_id = $_POST['flight_id'];
$flight_date = $_POST['flight_date'];
$aircraft_id = $_POST['aircraft_id'];
$flight_nature = $_POST['flight_nature'];
$auth_by = $_POST['auth_by'];
$auth_duration = $_POST['auth_duration'];

// prepare statement and bind parameters
$stmt = $conn->prepare("UPDATE tbl_flights 
                        SET (flight_date = :flight_date, aircraft_id = :aircraft_id, flight_nature = :flight_nature, auth_by = :auth_by, auth_duration = :auth_duration)
                        WHERE flight_id = :flight_id");

$stmt->bindParam(':flight_id', $flight_id);
$stmt->bindParam(':flight_date', $flight_date);
$stmt->bindParam(':aircraft_id', $aircraft_id);
$stmt->bindParam(':flight_nature', $flight_nature);
$stmt->bindParam(':auth_by', $auth_by);
$stmt->bindParam(':auth_duration', $auth_duration);

// execute statement
$stmt->execute();

// success or error message
echo "Updated record successfully.";
}
catch(PDOException $e)
{
echo "Error: " . $e->getMessage();
}

$conn = null;

That's not a valid UPDATE SQL statement. Please see the docs for the proper syntax. — Jonnix
– Jonnix, Commented Nov 23, 2016 at 14:47
To the best of my recollection, the UPDATE syntax should UPDATE <tablename> SET <column> = <newvalue>,<column> = <newvalue>,... WHERE <your conditions>; — FDavidov
– FDavidov, Commented Nov 23, 2016 at 14:48
Any reason you are mixing mysqli and PDO? Note that you have an sql injection problem in the mysqli section: $_GET['flight_id'] — jeroen
– jeroen, Commented Nov 23, 2016 at 14:50
@ jeroen To be honest it's just me being careless, this was rushed and isn't the final code, I'll make it consistently PDO once it is. What should I use instead of $_GET? — sinesine
– sinesine, Commented Nov 23, 2016 at 14:54
I corrected the UPDATE statement but it is still not updating anything? — sinesine
– sinesine, Commented Nov 23, 2016 at 14:57

Funk Forty Niner · Accepted Answer · 2016-11-23 15:25:37Z

1

Your UPDATE syntax is incorrect. There shouldn't be () brackets for SET:

UPDATE tbl_flights 

SET (flight_date = :flight_date,
aircraft_id = :aircraft_id, 
flight_nature = :flight_nature, 
auth_by = :auth_by, 
auth_duration = :auth_duration)

WHERE flight_id = :flight_id

Change it to the following:

UPDATE tbl_flights 

SET flight_date = :flight_date,
aircraft_id = :aircraft_id, 
flight_nature = :flight_nature, 
auth_by = :auth_by, 
auth_duration = :auth_duration

WHERE flight_id = :flight_id

As per the manual:

http://dev.mysql.com/doc/refman/5.7/en/update.html

Example from the manual:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

As you can see, there are no () following SET.

edited Nov 23, 2016 at 15:25

answered Nov 23, 2016 at 15:17

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP MySQL Updating table row not actually updating, no errors

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related