Reputation: 183
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
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