How to use the MySQL database for your PHP applications. Due to the importantness of this topic, we have divided the training course into 3 parts. In the first part (this tutorial), you will learn the basics of MySQL database and how to access and manage it with phpMyAdmin web-based software. We also define the tables needed for our online store project and try to add our products to the database. If you are familiar with these items, go to the end of this tutorial and see the next step. You can see a demo of what we will have after the end of this training course.
The database is an integral part of the application. All applications have data to display, and this data must be stored in a place where, firstly, security is ensured and, secondly, it be easy to manage. Especially when the amount of data is very high, data management will be one of the most important things, so that in large companies that have popular applications, data management has a special department and its own employees. To date, we have used a file called data.php to store data in our online store app project, but now we want to use a real database.
What you should know before starting?
In this tutorial, we will use the same project that we created in the previous tutorial. In the previous tutorial, we created an online store, but this store does not yet have a database. So you should have seen the previous tutorial and written or downloaded the source code. We suggest you see our previous tutorial, which is here, or download the source code here because you will need it to use the tutorial ahead.
Let's get started
We know that a database is a place to store data, but what is MySQL? MySQL is a database implementation and management software used in many projects and applications. In PHP, the most widely used database is MySQL, and most PHP-written applications now use MySQL as a database. A different version of this database has even been used in Android programming, and by learning it in the future, you will be able to learn Android programming much more easily. For more information about MySQL and its history and creators, go to this link on Wikipedia.
This database is now installed on your computer and running. When you install Wamp Server, this database is also installed and run. To start, run the following address:
http://127.0.0.1/phpmyadmin
As you know, by doing this, you are running an application that is located in the "PHPMyAdmin" folder in the www folder. But you haven't created this folder before, it was created at the time of installing Wamp Server, and of course, you can't see it. Phpmyadmin is a web application written in the PHP language that provides a web platform for managing MySQL databases. By running this app, you will see the login page that asks for your username and password. Enter root as the username and leave the password blank and try logging in. By entering the app, you will see its user interface. On the left, you will see the existing databases that have already been created. You have nothing to do with these things.
At the top of the screen is a menu, the first link is Databases. Click on it.
Notice the red dots. This is where you can create a database for your project. In the Database name field, enter the database name myshop and click the Create button.
Your database will be created and you will see the following page:
Notice the red dot. As you can see, your database named myshop has been added to the list. From now on, whenever you enter PHPMyAdmin, you have to click on myshop to enter the database management section. So remember this part. We have nothing to do with PHPMyAdmin at the moment. We return to our project. Create a new folder in www named database and move a copy of your previous project files from the bootstrap folder to this folder. Then run the address to make sure everything is working properly.
http://127.0.0.1/database/index.php
You see your store app. Now we want to connect to our database, myshop, via PHP. First, create a folder named config in the database folder. Then create a file named db.php in this folder and put the following code in it.
<?php
// used to connect to the database
$host = "localhost";
$db_name = "myshop";
$username = "root";
$password = "";
try {
$con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}
// show error
catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
?>
This code snippet will connect to the database via PHP using your database name, and then we can do other commands such as creating tables, deleting and adding data, and so on. Note line 4. Here we have used the database name we created earlier, and the username and password are the same as the one we used to log in to PHPMyAdmin. You do not need to know how this code works at the moment, we no longer have anything to do with this file, and wherever we need to connect to our database, we will only include this file in the project.
Now create a file named install.php in the database folder. This file is supposed to create the tables we need in the myshop database. Tables are where our data is stored and are different for each project. In each project, we create tables only once based on the need, and then the install.php file will no longer be useful to us. Once you've created the tables, all you need to do is add, edit, read, and delete data from the tables so that our app works properly. Notice these words, Create, Update, Read and Delete
If we put the first letters together, it becomes CURD
You'll see this phrase a lot in programming, so wherever you see it, it means the same operations of adding, editing, reading, and deleting data that we will do on the database so that our application can communicate with it and be able to perform its defined tasks. In fact, that's all we need to use the database.
Well, as we said, for each project we have to see first what tables we will need. This is one of the most important parts of creating a project, and if you know exactly what you need, your project will be completed much faster. Of course, you can change the tables later, but it will take a lot of time and extra work. You will definitely experience such a thing, because eventually your project may need to be changed in the future, so don't be afraid to make a mistake.
Regarding our project, if you pay attention, we have saved two data models in the data.php file. One for products and the other for pages.
So we can assume that we will need 2 tables for our project. Table of products and table of pages.
But what will be the columns of this table? For example, in the case of a product, look at the data.php file. We had a "title" that the phone name was stored in it. We had a "description" that contained the product description, a long text. We had a price at which the price of the phone was stored, a numeric value, and a photo in which the name of the product photo was stored. Note that the name of the product photo, not the photo itself, i.e. a text containing the product photo name, was saved in this variable. We copied the photo to the uploads folder.
These will be the columns of our product table! This means that our product table has 4 columns. Note the following table:
| title | description | price | photo |
|---|---|---|---|
| Apple iPhone X | Apple iPhone X smartphone. Announced Sep 2017. Features 5.8″ Super Retina OLED | 1000 | iphone.jpg |
| Samsung Galaxy A10 | Samsung Galaxy A10 Android smartphone. Announced Feb 2019. Features 6.2″ IPS LCD display, Exynos 7884 chipset | 300 | samsung.jpg |
| Xiaomi Mi Note 10 | Xiaomi Mi Note 10 Android smartphone. Announced Nov 2019. Features 6.47″ AMOLED display, Snapdragon | 700 | xiaomi.jpg |
| Nokia 7.2 Dual Sim | Nokia 7.2 TA-1196/DS 128GB 6GB RAM (GSM Only, No CDMA) Factory Unlocked No Warranty - 4G LTE International Model | 500 | nokia.jpg |
The whole data about our products are written in this table. We will need such a table in the database and that is all we need!
What about the pages? This table will be our pages:
| pages_title | pages_text |
|---|---|
| About us | I am a PHP programmer and this is an online shop application that I created with PHP and Bootstrap. Im trying to learn more about PHP and make this application even better! |
| Contact us | <p>You can reach me by email, I check my email every day and get back to you as soon as possible!</p> <p>Email: <strong>help[at]mojipo.com</strong></p> <p>Also you can find me on twitter! Follow @imojipo</p> |
Now we learn how to create these tables in our database, myshop. Copy the following code in the install.php file.
<?php
// Include database connection
include 'config/db.php';
// Creating Pages Table
try{
// insert query
$query_for_table_pages = "CREATE table `pages`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`title` varchar(100),
`body` TEXT
)";
// prepare query for execution
$stmt = $con->prepare($query_for_table_pages);
// Execute the query
if($stmt->execute()) {
echo "<p>Table pages created!</p>";
} else {
echo "<p>Error in creating pages table</p>";
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
// Creating Products Table
try{
// insert query
$query_for_table_products = "CREATE table `products`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`title` varchar(100),
`description` TEXT,
`price` int(10),
`photo` varchar(100),
`created` datetime NOT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
// prepare query for execution
$stmt = $con->prepare($query_for_table_products);
// Execute the query
if($stmt->execute()) {
echo "<p>Table products created!</p>";
} else {
echo "<p>Error in creating product table</p>";
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
?>
Then run the install.php file. If everything is correct, you can see the following:
This means that your tables have been created successfully. First, we look at the PHPMyAdmin and myshop database to see what happened.
As you can see, our 2 tables are made. Now let's see what happened in install.php codes that made these two tables.
First, we included db.php from the config folder. We've already said that we need to include this file wherever we need to connect to the database. Then we have two pieces of code, each of which makes up one of our tables. The first code snippet is for creating a page table. Try and Catch is a structure in PHP and has nothing to do with our database operations. In fact, this structure tries to do what it does in the Try section, and calls Catch if it is not successful. This structure is used in different places. We have nothing to do with this structure at the moment. Note the code in the try section. We first wrote a query and stored it in a PHP variable called $query_for_table_pages. A query is a way we interact with the MySQL database. It's actually a language related to MySQL that you're learning little by little.
The query used here tells MySQL to create a table named pages and three columns with the names id, title, and body. If you look a little higher, our page table had 2 columns. Here we have an additional column named id. the title is our pages_title and the body is our pages_text. We just changed its name to standardization, and you can even use the same names. What about the id column? This column is an essential column for accessing the data that we will add to the table in the future. In fact, this column is a numeric column that is added to the table every time we add data to the table, starting with the number 1. It's something like the ID we used when we added data to data.php. This ID is created automatically and will never be duplicated. Later, when we want to add data to our table, we become more familiar with id. once our query is ready, we prepare it and then execute it, and thus, our query is sent and executed for the database. Preparing and executing are part of the process that needs to be done, and you don't have to worry about how they work. Just know that you need to go through these steps to run a query. You will see these codes many times later and their appearance will not change. In fact, these codes should always be implemented in the same way. We put the execute step inside a condition. This means that if it is executed correctly, let us know and if not, let us know in another message. Pay attention to the messages. You already have seen these message by running the install.php file! Since the "try" is done correctly, PHP no longer reaches the Catch and goes to the next try.
The next try is to create a product table. Again, we wrote a query and saved it in a variable called $query_for_table_products. Like the previous one, this query says to create a table named products and assign 7 columns to it. But our product table only had 4 columns. Here, too, we first have an id column that is the same as before, and we've outlined the reason for using it. Then we have our familiar columns, namely title, description, price, and photo, which is supposed to store the data related to our products. Then we have a created column and a modified column. These two columns are actually extra columns and we could not use them. But we made the job a little more professional. The created column is supposed to store the exact date and time when we added the data. In this way, we can later find out the exact date and time a product has been added to our store. The modified column will also store the exact date and time we edited a product's information. These are the functional columns that you will learn about later.
Then, like the previous section, we prepared the query and then executed it and asked PHP to let us know the result. PHP also successfully created the table and gave us the message "Table products created!". That's all the install.php file has done for us. You've probably noticed that there's no data yet, and our products or store pages have not been added to the database, only the table that is supposed to store them has been created. We will teach you how to add products and pages in the next step, and we will add our products to the database by filling out a form. Also in an admin panel that we are going to build for our store!
Add data to database tables
Now we want to start adding our store products and pages to the tables we created earlier. To get started, create a folder named admin in the database folder. This folder is supposed to be the admin panel of our store. Where we add products and pages. Edit and delete.
Add pages about us and contact us to database
Create a new file named pages_create.php in the admin folder and put the following code in it.
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<title>Admin Panel</title>
<style>
footer {
padding-top: 3rem;
padding-bottom: 3rem;
}
footer p {
margin-bottom: .25rem;
}
</style>
</head>
<body>
<header>
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
<div class="container">
<a class="navbar-brand" href="#">Admin Panel</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarText" aria-controls="navbarText" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarText">
<ul class="navbar-nav mr-auto">
<li class="nav-item active">
<a class="nav-link" href="#">Dashboard</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Products</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Pages</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Setting</a>
</li>
</ul>
</div>
</div>
</nav>
</header>
<main class="bg-light">
<div class="jumbotron">
<div class="container">
<h1>Create Pages</h1>
</div>
</div>
<div class="container py-3">
<!-- html form to create page will be here -->
</div>
</main>
<footer class="text-muted">
<div class="container">
<p class="float-right">
<a href="#">Back to top</a>
</p>
<p>MyShop © YourDomain.com, The online shop which I created myself with ❤</p>
<p>Need help? <a href="https://www.mojipo.com/">Visit the homepage</a> or read our <a href="https://www.mojipo.com/myshop">getting started guide</a>.</p>
</div>
</footer>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
</body>
</html>
Then run the following address to make sure everything is in place.
http://127.0.0.1/database/admin/pages_create.php
As you can see, this page is supposed to be the page through which we want to add our pages to the database. I mean the contact page and about us. If you look closely at the code on this page, you'll see that we've added Bootstrap to an HTML page like our homepage, added a navbar at the top and a footer at the bottom, and are ready to move the required code from the box. Take the bootstrap tool and add it to the middle, <main>.
The following code is a form that is placed in a table and we created it using the Bootstrap Document Guidelines. Copy this code and replace it with <!-- html form to create product will be here --> that is in <main> section.
<!-- PHP insert code will be here -->
<a href="pages_read.php" class="btn btn-secondary float-right mb-2">Back to read pages</a>
<!-- html form here where the product information will be entered -->
<form action="pages_create.php" method="post">
<table class="table table-hover table-bordered">
<tr>
<td>Title</td>
<td><input type="text" name="title" class="form-control" /></td>
</tr>
<tr>
<td>Body</td>
<td><textarea name="body" class="form-control"></textarea></td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" value="Create" class="btn btn-primary" />
</td>
</tr>
</table>
</form>
This code adds an HTML form for entering page information into our file. Refresh the page and see the changes. We have a Title field and a Body field. These two fields are supposed to replace our fields in the pages database table. If you remember, we chose the same names there.
Now we need to make sure that when we click the Create button, the form information is somehow recorded in our database. If you see the code that created our form, you will find that we used the POST method to send the data, and we send the data to pages_create.php, which means the same file. The following is the code line:
<form action=" pages_create.php" method="post">
So when the user clicks Create, we need to get the form information in the same pages_create.php file. Now to get the information and then send it to the database, copy the following code instead of <!-- PHP insert code will be here -->
<?php
if($_POST){
// include database connection
include '../config/db.php';
try{
// insert query
$query = "INSERT INTO pages SET title=:title, body=:body";
// prepare query for execution
$stmt = $con->prepare($query);
// posted values
$title=htmlspecialchars(strip_tags($_POST['title']));
$body=htmlspecialchars(strip_tags($_POST['body']));
// bind the parameters
$stmt->bindValue(':title', $title);
$stmt->bindValue(':body', $body);
// Execute the query
if($stmt->execute()){
echo "<div class='alert alert-success'>Record was saved.</div>";
}else{
echo "<div class='alert alert-danger'>Unable to save record.</div>";
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>
Let's first look at the code snippet above. We first set a condition that says if $_POST was available, do some work for us. Remember that in the GET method we received information with $_GET. Now that we have sent the information with the POST method, this is how it will be received.
Then if the information is sent, ie $_POST is available, we first include db.php because we want to connect to the database. Then, as before, we used a try and catch. In the try section, we define a query that says insert title and body values into the pages table. The form of query writing shows how it works. Then we prepared, but this time before executing, we have to bind the titles and body values to the query.
We first defined the $title and $body variables and measured them with the POST values given by the form. Then we said to bind the value :title in the query with the value in the $title and also bind the value :body in the query with the value in the $body. And then we execute. If execution is successful, the appropriate message will be displayed, and if an error occurs, we will see an error message.
One point is that we used the two functions htmlspecialchars() and strip_tags(). These are functions in PHP that check the values sent by the user so that there is probably no malicious code inside it and if it is, they will neutralize it. This is a security tip, and from now on, whenever you want to receive data through the GET or POST method, use these two functions, otherwise, your application may be hacked!
Now let's add our first page to the app. run the following address.
http://127.0.0.1/database/admin/pages_create.php
Then write "about us" as the title and a text as the body. You can copy the same text from the data.php file.
Now click on Create. If everything is correct, you will see the message: Record was saved. This means that your data has been successfully stored in the database.
Now let's take a look at myshop database in PHPMyAdmin to see what happened.
Click on the pages marked in red.
As you can see, the information you entered in the form is stored here. Id, as we said before, is automatically set to 1.
Add the second page, contact us in the same way, and see it in PHPMyAdmin.
This time the id was automatically set to 2. You simply add 2 pages of your store to the database. Now we want to add products.
Add products to the database
Create a new file named products_create.php in the admin folder and put the following code in it.
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<title>Admin Panel</title>
<style>
footer {
padding-top: 3rem;
padding-bottom: 3rem;
}
footer p {
margin-bottom: .25rem;
}
</style>
</head>
<body>
<header>
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
<div class="container">
<a class="navbar-brand" href="#">Admin Panel</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarText" aria-controls="navbarText" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarText">
<ul class="navbar-nav mr-auto">
<li class="nav-item active">
<a class="nav-link" href="#">Dashboard</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Products</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Pages</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Setting</a>
</li>
</ul>
</div>
</div>
</nav>
</header>
<main class="bg-light">
<div class="jumbotron">
<div class="container">
<h1>Create Product</h1>
</div>
</div>
<div class="container py-3">
<!-- html form to create product will be here -->
</div>
</main>
<footer class="text-muted">
<div class="container">
<p class="float-right">
<a href="#">Back to top</a>
</p>
<p>MyShop © YourDomain.com, The online shop which I created myself with ❤</p>
<p>Need help? <a href="https://www.mojipo.com/">Visit the homepage</a> or read our <a href="https://www.mojipo.com/myshop">getting started guide</a>.</p>
</div>
</footer>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
</body>
</html>
Then run the following address to make sure everything is in place.
http://127.0.0.1/database/admin/products_create.php
As you can see, this page is supposed to be the page through which we want to add our products to the database. If you look closely at the code on this page, you'll see that we've added the Bootstrap to an HTML page like the previous page, added a Navbar at the top and a footer at the bottom, and are ready to pick the required code from the bootstrap toolbox and add it to the middle, <main>.
The following code is another form that is in a table, and we created it again using the Bootstrap Document Guidelines. Copy this code and replace with <!-- html form to create product will be here -->
<!-- PHP insert code will be here -->
<a href="products_read.php" class="btn btn-secondary float-right mb-2">Back to read products</a>
<!-- html form here where the product information will be entered -->
<form action="products_create.php" method="post" enctype="multipart/form-data">
<table class="table table-hover table-bordered">
<tr>
<td>Title</td>
<td><input type="text" name="title" class="form-control" /></td>
</tr>
<tr>
<td>Description</td>
<td><textarea name="description" class="form-control"></textarea></td>
</tr>
<tr>
<td>Price</td>
<td><input type="text" name="price" class="form-control" /></td>
</tr>
<tr>
<td>Photo</td>
<td><input type="file" name="photo" /></td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" value="Create" class="btn btn-primary" />
</td>
</tr>
</table>
</form>
This code adds an HTML form to our product page to enter product information. Refresh the page and see the changes. As you can see, there is a field for adding product photos. This means that we want to upload the product photo file via PHP and then save the name of the uploaded photo along with other information such as the name and description of the product in the database. Now we need to make sure that when we click on the Create button, the photo file is uploaded correctly, and then the form information is recorded in our database. If you see the code that created our form, you will find that we still used the POST method to send the data, and we send the data to the products_create.php address, which is the same file. The following is the code line:
<form action="products_create.php" method="post" enctype="multipart/form-data">
Now to receive the information and then send it to the database, replace the following code with <!-- PHP insert code will be here -->
<?php
if($_POST){
// check and upload photo first
// include uploader class
include '../libs/class.upload.php';
if ($_FILES["photo"]["error"] == 0) {
$handle = new Upload($_FILES['photo']);
if ($handle->uploaded) {
$handle->allowed = array('image/*');
$handle->Process('../uploads/');
if ($handle->processed) {
$photo = $handle->file_dst_name; // set $photo with file name
} else {
echo '<div class="alert alert-info">'.$handle->error.'</div>';
$photo = 'default.jpg'; // set $photo to default.jpg
}
$handle-> Clean();
} else {
echo '<div class="alert alert-info">'.$handle->error.'</div>';
$photo = 'default.jpg'; // set $photo to default.jpg
}
} else {
echo '<div class="alert alert-warning">Photo not selected!</div>';
$photo = 'default.jpg'; // set $photo to default.jpg
}
// then insert data to database
// include database connection
include '../config/db.php';
try{
// insert query
$query = "INSERT INTO products SET title=:title, description=:description, price=:price, photo=:photo, created=:created";
// prepare query for execution
$stmt = $con->prepare($query);
// posted values
$title=htmlspecialchars(strip_tags($_POST['title']));
$description=htmlspecialchars(strip_tags($_POST['description']));
$price=htmlspecialchars(strip_tags($_POST['price']));
// bind the parameters
$stmt->bindValue(':title', $title);
$stmt->bindValue(':description', $description);
$stmt->bindValue(':price', $price, PDO::PARAM_INT);
$stmt->bindValue(':photo', $photo);
// specify when this record was inserted to the database
$created=date('Y-m-d H:i:s');
$stmt->bindValue(':created', $created);
// Execute the query
if($stmt->execute()){
echo "<div class='alert alert-success'>Record was saved.</div>";
}else{
echo "<div class='alert alert-danger'>Unable to save record.</div>";
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>
Let's first look at the code snippet above. We first set a condition that says if $_POST was available, do some work for us. Remember that in the GET method we received information with $_GET. Now that we have sent the information with the POST method, this is how it will be received.
Then, if the information is sent, i.e. $_POST is available, this time, unlike the previous series, first performs the operation of uploading the product photo in PHP, and then records the information in the database. This means that we have two stages of work.
Step 1: Upload the photo by PHP, use a ready-to-use class!
To upload a photo to PHP, we can do what we need by writing about 15 lines of code, but we want to go one step further and use pre-created classes to do so. In this way, while writing less code, we will get better and more professional results. Pre-created classes are PHP files that a PHP programmer has previously written dozens and hundreds of lines of code for a specific purpose and then shared those files for others to use. So you can use and get results based on the document of the same class by including these files, without writing much code. There are many classes written for uploading files in PHP, but I always use class.upload.php, and I think it's one of the best. To view the document and download this class and more information, see the following link:
https://www.verot.net/php_class_upload.htm?lang=en-GB
I used version 03/08/2019, and since you may have come up with new versions of this class when you read this tutorial, I will put the file here for you to download.
Download the file and after extracting, create a new folder named libs in your project folder, ie database, and put the class.upload.php file in it. First, open this file and take a look at this file. About 5160 PHP code lines are here! It may take days or weeks if you or even I wanted to write such a class, but now we can easily use this file in our project. This file provides us with very complete facilities for managing file uploads in PHP projects. To view the features of this document class, see it at the following link:
https://github.com/verot/class.upload.php/blob/master/README.md
Using classes is one of the best things that can happen to you in PHP programming. Different classes have been created to do different things and you can find and use them according to your needs. Also, in the future, when you become more professional, you can write complex classes and share them with others.
Well, now back to our code, the products_create.php file. If $_POST information is available to upload a product photo, we first include the upload class file from the libs folder. Then we check if the photo file in the form was selected correctly. I mean:
$_FILES["photo"]["error"] == 0
Then we create an object from the Upload class. We introduce the “uploads” folder that we created earlier as the place to upload the photos to the class, and finally, we ask the class to upload the photo for us. Then, if the upload is successful, return the name of the photo file to the $photo variable, and if an error occurs, set the default.jpg value to the $photo variable. This variable is what we later save in our database as a photo column in the products table. Remember when we created the products table, we said that the name of the product photo will be in this column.
We simply uploaded our photo with a few lines of code and of course using a very professional class, and we have the name of photo ready to send to the database. The next step is to store the data in the database, which is exactly the same as what we did to add data to the pages table.
Step 2: Insert data in the products table
Like the previous series, we first included db.php. Then, in a try, we asked PHP to create a query for inserting data into the product table. Then we prepared the query. Then, as before, before executing, we first took the values sent by the form and "bind" it. If you remember, we had two other columns called created and modified. These lines prepare the amount created for us and bind it to be recorded in the database:
// specify when this record was inserted to the database
$created=date('Y-m-d H:i:s');
$stmt->bindValue(':created', $created);
In fact, to get the date and time, we used the date() function in PHP and put it in the $created variable. Then we sent it to the database. By now, you should have noticed that PHP has a number of pre-designed functions, each of which does a specific job. For example, the count() function or the htmlspecialchars() and strip_tags() functions, and now the date() function, from which we can take the date and time. To get acquainted with these functions, you need to search for their name to see how to use them. For example, the date('Y-m-d H: i: s') returns the date and time of this moment. There are usually ready-made functions for everything you want to do in PHP. Just use the art of searching. For example, here we knew we needed date and time for the created column. So it was enough to search:
How to get date and time in PHP
Many pages will teach you the date() function and how it works.
The modified value is also created automatically by updating the database table each time because that's what we wanted when we created the products table in the install.php file. See it there...
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Finally, we execute. Now let's try whether everything works properly or not. See the following address:
http://127.0.0.1/database/admin/products_create.php
Our form is ready to take the information about our products and register them in the database. Try entering the information for one of the phones in your store. To do this, you can copy the information from data.php and select the photo from the photos you downloaded in the previous tutorial from your desktop. For example, I first added the Apple iPhone X.
Then click the Create button. If everything is correct, you should see the image below, which means uploading photos and recording information in the database.
Now let's see if the photo is uploaded correctly. Take a look at the “uploads” folder. “iphone.jpg” has been added here. We selected this file from the desktop and PHP uploaded it to this folder. Now view your database in PHPMyAdmin.
Click on the products table marked in red.
You can see that your product, the Apple iPhone X, is listed in the database along with other information, including the photo name and creation date. ID is also automatically set to 1 as usual. Now try to add the rest of your products in the same way and see them in PHPMyAdmin. I added all the products here and my PHPMyAdmin was like this.
As you can see, all products have been added. Check the id column for how it is automatically set from 1 to 4.