CODEWITHSUNDEEP
phpmysql
sinesine
sinesine

Reputation: 183

PHP MySQL Updating table row not actually updating, no errors

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;

Upvotes: 1

Views: 46

Answers (1)

Funk Forty Niner
Funk Forty Niner

Reputation: 74232

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:

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.

Upvotes: 1

Related Questions