I always wanted to create a tutorial on how to insert data to a database table, so here it is.
First you want to create a basic configuration file of some sort.
Here’s the one I created called config.php and it is located in the same directory as the other files.
[php]<?php
error_reporting(E_ALL);
ini_set(‘display_errors’, 1);
define(‘DATABASE_HOST’, ‘local_host_name’);
define(‘DATABASE_NAME’, ‘myCMS’);
define(‘DATABASE_USERNAME’, ‘username’);
define(‘DATABASE_PASSWORD’, ‘password’);
define(‘DATABASE_TABLE’, ‘myBlog’);
[/php]
Here’s an file called install.php that creates the database and database table:
[php]<?php
require_once ‘config.php’; // Configuration file for turning error reporting and connection strings to database:
/*
- I think the following is pretty self explanatory and I think think the index.php file helps you on how to insert
- data into a database table better.
*/
try {
$conn = new PDO(‘mysql:host=’ . DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = “CREATE DATABASE IF NOT EXISTS myCMS”;
$conn->exec($sql);
$sql = “use myCMS”;
$conn->exec($sql);
$sql = “CREATE TABLE IF NOT EXISTS myBlog (
ID int(11) AUTO_INCREMENT PRIMARY KEY,
title varchar(30) NOT NULL,
comment text NOT NULL)”;
$conn->exec($sql);
echo “DB created successfully”;
} catch (PDOException $e) {
echo $sql . “
” . $e->getMessage();
}[/php]
And lastly the file (index.php) to insert user’s data from a HTML Form into a database table called myBlog.
[php]<?php
require_once ‘config.php’; // Configuration file for turning error reporting and connection strings to database:
/*
- The first thing to do is to make sure you have a database named myCMS and a database table named myBlog.
- You can run the install file that will create the database and database table by running install.php if you want
- or you can create the database and database table yourself.
*/
/*
- Establish a database connection.
/
$db_options = [
/ important! use actual prepared statements (default: emulate prepared statements) /
PDO::ATTR_EMULATE_PREPARES => false
/ throw exceptions on errors (default: stay silent) /
, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
/ fetch associative arrays (default: mixed arrays) */
, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
];
$pdo = new PDO(‘mysql:host=’ . DATABASE_HOST . ‘;dbname=’ . DATABASE_NAME . ‘;charset=utf8’, DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);
/*
- Check to see if user has clicked on the submit button.
*/
$submit = filter_input(INPUT_POST, ‘submit’, FILTER_SANITIZE_FULL_SPECIAL_CHARS);
if (isset($submit) && $submit === “submit”) {
/*
* Grab User’s Responses from Form.
/
$title = htmlspecialchars($_POST[‘title’]);
$comment = htmlspecialchars($_POST[‘comment’]);
/
* Insert Into Database Table myBlog.
*/
$query = ‘INSERT INTO myBlog(title, comment) VALUES (:title, :comment)’;
$stmt = $pdo->prepare($query);
$result = $stmt->execute([’:title’ => $title, ‘:comment’ => $comment]);
if ($result) {
header("Location: index.php");
exit();
}
}
?>
PHP Comment Tutorial
* {
box-sizing: border-box;
}
body {
background-color: #ccc;
padding: 0;
margin: 0;
}
form#commentForm {
display: block;
width: 100%;
max-width: 400px;
height: auto;
background-color: #fff;
padding: 15px;
margin: 20px auto;
}
form#commentForm fieldset { border: 1px solid #336699;}
form#commentForm legend {
font-family: Arial, Helvetica, sans-serif;
font-size: 1.2em;
color: #336699;
padding: 0 5px;
}
form#commentForm label {
float: left;
display: block;
width: 100%;
max-width: 140px;
height: 30px;
font-family: Arial, Helvetica, sans-serif;
font-size: 1.0em;
line-height: 30px;
color: #336699;
text-align: left;
padding: 0;
}
form#commentForm input {
clear: right;
display: block;
width: 100%;
max-width: 280px;
height: 30px;
border: 1px solid #336699;
outline: none;
font-family: Arial, Helvetica, sans-serif;
font-size: 1.0em;
color: #2e2e2e;
padding: 0 5px;
margin-bottom: 10px;
}
form#commentForm label.textBox {
clear: both;
text-align: left;
font-size: 1.2em;
padding: 0;
margin-top: 20px;
}
form#commentForm textarea {
resize: none;
border: 1px solid #336699;
outline: none;
clear: both;
display: block;
width: 100%;
max-width: 360px;
height: 300px;
font-family: Arial, Helvetica, sans-serif;
font-size: 1.0em;
line-height: 1.5;
color: #2e2e2e;
padding: 10px;
}
form#commentForm input[type=submit] {
-moz-border-radius: 8px;
-webkit-border-radius: 8px;
border-radius: 8px;
-moz-box-shadow: 2px 2px 3px rgba(46, 46, 46, 0.4);
-webkit-box-shadow: 2px 2px 3px rgba(46, 46, 46, 0.4);
box-shadow: 2px 2px 3px rgba(46, 46, 46, 0.4);
outline: none;
border: none;
float: right;
display: block;
width: 100%;
max-width: 120px;
height: 40px;
cursor: pointer;
background-color: #4484CE;
font-family: Arial, Helvetica, sans-serif;
font-size: 1.0em;
color: #fff;
text-transform: capitalize;
margin-top: 20px;
}
form#commentForm input[type=submit]:hover {
background-color: #2e2e2e;
color: #ffa;
}
Comment Form
Title
Comment
[/php]
It’s a very simple tutorial, it’s not flashy, and it probably could had been coded different way. Though every coder programs differently, but that is what makes programming fun and interesting. This should give anyone a basic start on how to get started with interacting with MySQL with PHP and I plan on writing tutorials on reading data from a table, updating and deleting from a table in the near future.
Hope this Helps ~ John