PHP Introduction | Database Access Using PDO | Adding Table Data with PDO
Here, we will explain how to add records. Adding records is also performed by executing an SQL query, just like retrieving records. However, there is a slight difference from record retrieval, so be careful.
When you execute a query that retrieves records, the record data is returned from the database. Adding a record simply writes a command, so there is no need to receive result data. For this kind of query execution, use a method called exec instead of query.
$variable = $pdo->exec(query);
The return value is an integer indicating the number of updated records. Since exec can perform various operations in addition to adding data, it tells you how many records were updated. If nothing changes, the return value is 0.
When adding a record, specify a query like the following as the argument.
"insert into table (column1, column2, ...) values (value1, value2, ...) "
There are other ways to write an insert statement, but this is probably the easiest to understand. After insert into table, write the column names to set in parentheses separated by commas, then add values, and write the values in another pair of parentheses separated by commas. At this time, the order of the column names and the values must match.
Now let us modify the previous example and implement a feature for adding records.
index.php
<?php
<?php
$result = "";
try {
$pdo = new PDO("mysql:host=localhost:3306;dbname=mysampledata;charset=utf8", "root","1234");
$statement = $pdo->query("select * from sampletable");
while($record = $statement->fetch(PDO::FETCH_ASSOC)){
$result .= "<tr>";
foreach($record as $column){
$result .= "<td>" . htmlspecialchars($column) . "</td>";
}
$result .= "</tr>";
}
} catch(PDOException $e){
$result = "#ERR:" . $e->getMessage();
}
$pdo = null;
?>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>sample page</title>
<style>
h1 { font-size:14pt;
padding:5px;
background-color:#AAFFFF; }
table tr td {
padding:5px;
background-color:#DDFFCC; }
</style>
</head>
<body>
<h1>Hello PHP!</h1>
<table>
<form method="post" action="./add.php">
<tr><td>NAME:</td><td><input type="text" name="name"></td></tr>
<tr><td>MAIL:</td><td><input type="text" name="mail"></td></tr>
<tr><td>TEL:</td><td><input type="text" name="tel"></td></tr>
<tr><td>MEMO:</td><td><textarea name="memo"></textarea></td></tr>
<tr><td></td><td><input type="submit" value="Add"></td></tr>
</form>
</table>
<hr>
<table>
<?php echo $result; ?>
</table>
</body>
</html>
add.php
<?php
$name = $_POST['name'];
$mail = $_POST['mail'];
$tel = $_POST['tel'];
$memo = $_POST['memo'];
try {
$pdo = new PDO("mysql:host=localhost:3306;dbname=mysampledata;charset=utf8", "root","1234");
$query = "insert into sampletable (name, mail, tel, memo) values ('$name', '$mail', '$tel', '$memo')";
$pdo->exec($query);
} catch(PDOException $e){
echo "<html><body><h1>ERR:" . $e->getMessage() + "</h1></body></html>";
}
$pdo = null;
header('Location: index.php');
Here, in addition to index.php, a new file named add.php is added. Access index.php, write a name, email address, phone number, and memo in the displayed form, and submit it. A new record is added.
add.php first retrieves the values submitted from the form into variables.
$name = $_POST['name'];
$mail = $_POST['mail'];
$tel = $_POST['tel'];
$memo = $_POST['memo'];
It then creates a query statement using these values and executes it with exec. The query statement is written by arranging the above values as follows.
$query = "insert into sampletable (name, mail, tel, memo) values ('$name', '$mail', '$tel', '$memo')";
The id value is not needed, so only the values for the four columns are inserted. When this is executed with exec, the record is added. After that, the page redirects to index.php, and when the list is displayed, the added record should appear.