I cant get my update button to work, my SQL syntax isnt targeting the datebase properly, this is the syntax im using
$sql = ("UPDATE crud SET name='$name', last='$last', email='$email', birthday='$birthday' WHERE id=$id") or
die($mysqli->error);
i tried using different syntaxs but it doesnt update the database
Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
<!DOCTYPE html>
<html>
<head>
<title>Using PHP and a Database</title>
<style>
body {
font-family: Arial;
font-size: 14px;
}
.person {
width: 45%;
float: left;
border: 1px solid darkgray;
background-color: lightyellow;
padding: 10px;
margin: 10px;
box-sizing: border-box;
}
.person .photo {
float: left;
margin-right: 10px;
}
</style>
</head>
<body>
<form action="people-list.php" method="post">
<input type="hidden" name="id" value="<?php echo $id; ?>">
<p>
<label for="firstName">First Name:</label>
<input type="text" name="name" value="<?php echo $name; ?>" />
</p>
<p>
<label for="lastName">Last Name:</label>
<input type="text" name="last" value="<?php echo $last; ?>" />
</p>
<p>
<label for="emailAddress">Email Address:</label>
<input type="text" name="email" value="<?php echo $email; ?>" />
</p>
<p>
<label for="emailAddress">birthday</label>
<input type="text" name="birthday" value="<?php echo $birthday; ?>" />
</p>
<input type="submit" value="Submit">
<button type="submit" class="btn" name="save">save</button>
<button type="submit" class="btn" name="savechange">Save Changes</button>
</form>
<h1>Using PHP and a Database</h1>
<p>Here is a list of all the records in our people table:</p>
<?php
$id = 0;
// first, set general PHP error reporting and mysqli error reporting
// in order to be sure we will see every error in the script
ini_set('display_errors',1);
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// Connect to our MySQL server
$host = "localhost";
$user_id = "crud";
$password = "password";
$database = "crud";
$mysqli = new mysqli(
$host, $user_id, $password, $database
);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: ("
. $mysqli->connect_errno
. ") "
. $mysqli->connect_error
;
}
echo $mysqli->host_info . "n";
// Create the SQL command
if (isset($_POST['save']))
{
$name = $_POST['name'];
$last = $_POST['last'];
$email = $_POST['email'];
$birthday = $_POST['birthday'];
$sql = "INSERT INTO crud (name, last, email, birthday)
VALUES ('$name', '$last', '$email', '$birthday')";
}
if (isset($_GET['delete']))
{
$id = $_GET['delete'];
$sql = ("DELETE FROM crud WHERE id=$id") or die($mysqli->error());
}
if(isset($_GET['edit'])) {
$id = $_GET['edit'];
$results = $mysqli->query("SELECT * FROM crud WHERE id=$id") or die($mysqli->error());
if ($results){
$row = $results->fetch_array();
$name = $row['name'];
$last = $row['last'];
$email = $row['email'];
$birthday = $row['birthday'];
}
}
if(isset($_POST['savechange'])) {
$id = $_POST['id'];
$name = $_POST['name'];
$last = $_POST['last'];
$email = $_POST['email'];
$birthday = $_POST['birthday'];
$sql = ("UPDATE crud SET name='$name', last='$last', email='$email', birthday='$birthday' WHERE id=$id") or
die($mysqli->error);
$_SESSION['MSG_TYPE'] = "WARNING";
}
// $sql = ("UPDATE crud SET (name, last, email, birthday) VALUES ('$name', '$last', '$email', '$birthday') WHERE id=$id");
//if (isset($_GET['update']))
//{
//$id = $_GET['update'];
//$sql = ("SELECT FROM crud WHERE id=$id") or die($mysqli->error());
//}
if ($mysqli->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $mysqli->error;
}
$sql = "SELECT * FROM crud";
$results = mysqli_query($mysqli, $sql); // First parameter is just return of "mysqli_connect()" function
echo "<br>";
echo "<table border='1'>";
while ($row = mysqli_fetch_assoc($results)) { // Important line !!! Check summary get row on array ..
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['last'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['birthday'] . "</td>";
echo "<td><a href='people-list.php?delete=".$row['id']."'>delete</a></td>";
echo "<td><a href='people-list.php?edit=".$row['id']."'>update</a></td>";
//"htmlspecialchars()" function.
echo "</tr>";
}
$mysqli->close();
?>
</body>
</html>
sql
-- PHP Version: 7.2.1
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `crud`
--
-- --------------------------------------------------------
--
-- Table structure for table `crud`
--
CREATE TABLE `crud` (
`id` int(255) NOT NULL,
`name` varchar(100) NOT NULL,
`last` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`birthday` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `crud`
--
INSERT INTO `crud` (`id`, `name`, `last`, `email`, `birthday`) VALUES
(21, '', '', '', ''),
(22, '', '', '', ''),
(23, 'hhh', 'hhh', 'hh', 'jjj'),
(26, 'hi', 'ff', 'ff', 'dd'),
(27, '111', '222', '222', '222'),
(28, 'ddd', 'sss', 'sss', 'ddd'),
(29, 'hhh', 'hhh', 'hhh', 'hhh'),
(33, 'nnn', 'fffqf', 'fff', 'fff'),
(34, 'nnnn', 'nn', 'nnn', 'nnn'),
(35, 'ssaa', 'sss', 'sss', 'sss'),
(36, 'hi ', 'fff', 'fff', 'fff'),
(37, 'hh', 'hh', 'hh', 'hh');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `crud`
--
ALTER TABLE `crud`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `crud`
--
ALTER TABLE `crud`
MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Advertisement
Answer
This is just an example using your SQL. This takes no more time to code than what you started with.
<?php
error_reporting(E_ALL);
ini_set('display_errors', true);
$host = '127.0.0.1';
$db = 'test';
$user = 'admin';
$pass = 'howtoforge';
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try{
$con = mysqli_connect($host, $user, $pass, $db);
}catch (Exception $e){
echo $e->getMessage();
exit;
}
$name = 'NameF';
$last = 'O'Conner';
$email = 'none@none.com';
$birthday = '12/01/9999';
$id = '1';
try{
$stmt = $con->prepare('
UPDATE
crud
SET
name = ?,
last = ?,
email = ?,
birthday = ?
WHERE
id = ?;
');
$stmt->bind_param('ssssi', $name, $last, $email, $birthday, $id);
$stmt->execute();
}catch (Exception $e){
echo $e->getMessage();
exit;
}