When you master building a static website, you are ready for the next big step in your web developer carrier: “Active website”. You can use PHP and SQL server to reach this goal. Of course, there are a lot of CMS services ready to use, just like WordPress, but still, it’s worth knowing how they working behind the scene. In this post, I show you, how to reach communication with SQL base using PHP. Let’s go 🙂
First of all, we need a LAMP structure
What the LAMP is? We can say, that is basic server stock that allows developers to build an active website. We can explain the abbreviation as Linux, Apache, MySQL, and PHP. You can download and run a program that will emulate this environment on your local computer host.
Thanks to that you can run SQL Server, Apache, and PHP as localhost and build a dynamic website in your environment before you publish it. That is all you need to start.
There are several programs that emulated LAMP stack on your desktop. I prefer XAMPP because it’s super easy to install and use.
What’s more, XAMPP has extra emulations ads-on to run popular CMS like WordPress, Drupal, PrestaShop, etc. You can find more about XAMPP here: https://www.apachefriends.org/download.html
I also wrote how to build your own WWW server on a virtual machine. So if you have a little more time, and want to check this out, here you find more info about this: https://krzysztofnyrek.pl/web-server-on-virtual-machine/
Let’s play with PHP
The communication process with MySQL via PHP looks like this:
- establishing a connection with MySQL and selecting a database,
- preparing query,
- execution query,
- obtaining the results and displaying them on the website,
- Repeat steps 2 to 4 until all the necessary data is obtained,
- disconnect from MySQL.
Looks nice and easy, especially because PHP has a dedicated function to do this kind of communication. But where is the catch?
If you want to collect some data from SQL databases, you need to login into these databases. Maybe it’s no big deal on your localhost, but on the Internet, you should do this carefully.
If you place login and password on your website, hackers use this to still your data and probably use your website to force some attack to internet users.
So first of all we need to keep login and password to SQL Databases safely. Thank’s to PHP we have these options. All you need to do is create two PHP files: one to keep login and password, and the second to connect to SQL Databasses.
Two PHP files to make connection
Let’s start with a PHP file that will keep login and password for us. In this file you need to keep only four variable:
- Serwer address;
- Databases name;
- User name;
- User password.
There is no big deal, how you call this file because in the main file you just recall it by the name.
Now the second file, with a little more PHP code.
In the beginning, you need to call file, where you stored information about SQL server connection and Databases that you want to use. After this, you can connect to the SQL server using the PHP function mysqli.
When the connection is open, you need to build a query and pass it to the SQL server. The answer needs to be stored in some variable. In this example, I used $result variable, and in the next step, I build a simple table to display results in a web browser.
I also use fetch_array PHP function to deal with SQL query results as an array and build for a loop that builds every table row. Htmlspecialchars function converts special characters to HTML entities, just for safety reasons.
Now you can build a simple connection to SQL base using PHP
In this post, I show you, the idea of using PHP to communicate with SQL Base. Of course, this is only the first step, but when you know how it works, you can better understand how to build a dynamic website.
For example, in WordPress, we have a page structure based on header block, content block, and footer block. All these blocks are building in the same way: there is PHP language that connecting to SQL Database, collects data, and builds HTML structure filed with grabbed data.
This is so easy to understand, and what’s more, when something goes wrong, you can easily discover what’s going on.