Get started in web development with me — Part 5: PHP intro, Databases and SQL
Hi there, on part 4 we learned about HTML forms and installed PHP on our server. If you haven’t checked out parts 1, 2, 3 or 4 go ahead and take a look at them, they’ll give you a basic understanding about what we’re doing.
Now we’re going to use PHP to handle an HTML form. In other words, we’re going to write code to modify some things on our HTML page according to how many votes we receive from the forms. But first, let’s learn some PHP.
Lost in the series? Here are Part 1, Part 2, Part 3, Part 4 and Part 5.
Learning some PHP
Here’s our code to print “Hello World” using PHP:
You: *sarcastic clap* Nice, but that is not really useful, we could’ve just used simple HTML to write that.
Me: Ooooh so you wanna get serious? Okay.
In order to get a basic understanding about PHP syntax, I’ll use this tutorial (until, including, the Data Types’ section) as a guide, and I’ll use this one for form handling in PHP. Go ahead and read those as well.
Done? Good.
Now we can finally start working on our voting form from the last post.
Voting Form from the last post
Here’s the code for the voting form we’ve been working on :
What we want to do is to collect the votes using the form and present the number of likes and dislikes in the page.
The first thing we need to do is receive the information, so let’s create a PHP file to handle the form (I’ll call it vote.php):
$ sudo touch /home/gabriel/html/vote.phpAnd assign it as the form’s handler:
Now we need a way to store the number of people who liked and who disliked the page. But I think this is getting too hard already, let’s just make it redirect to a “thank you for voting” page without storing any information.
Thank you for voting!
Here’s how my vote.php file looks like:
Pretty simple. What if we wanted to stay on the same page instead of redirecting to another one? A way to do it would be to copy the entire page into another file:
$ cp index.html thanks.phpIn this new page we could replace the form with a message, so that it looks like only the form part of the page changed:
Finally, we change the form in index.html to point to thanks.php instead of vote.phpand done!
But it seems overkill to rewrite all the page’s content just to edit a tiny little bit of it. Let’s google this:
php change content of a div
A div tag in HTML is a tag that allows for isolation of specific parts of HTML code, take a look here.
Oooof, seems like we would be messing around with AJAX (and thus XML and Javascript), so we’d better not go into this just now.
Back to counting
Coming back to our problem: we need to count how many people liked or disliked our page.
You: Just store these numbers in two numerical (integer) variables.
Me: The problem with this is that there’s no way for a user to see how many other users have voted, he would only have access to how many times he’s voted (which is always one). Also, every time the server needed to reload or restart all the data would be lost (see data persistence).
I guess we’ll need a database after all. If you don’t know much about databases and basic SQL take a look at Appendix 1.
Now let’s learn how to use PHP with MySQL so we can store our number of likes and dislikes.
We don’t need to know how PHP creates databases or tables because we can just log in the database and create them directly using SQL.
Hmmm… But how would this work again?
Because we are using a relational database all the data needs to be structured in tables. How do we structure our database then? I’ll do this at Problem 1.
Great! We have a table to store the data, but how do we generate the user’s ID? I’ll solve this at Problem 2.
Using PHP to select from a database
Sweet, we can finally start building our voting section. But first, we need to know how to insert and look for data in a database using PHP. So I copied index.htmlto vote.php:
$ cp index.html vote.phpAnd added some PHP code:
You probably didn’t understand the query strings (the SELECT statements), if that’s the case I suggest you read the WHERE, COUNT and GROUP BY SQL tutorials.
This page throws an error:
Connection failed: Access denied for user 'root'@'localhost'Well, I shouldn’t be using the root account to access the database. Let’s dig into this at Problem 3.
But now it shows nothing, let’s enter the select query directly on our database:
MariaDB [herschel]> SELECT COUNT(liked) FROM vote WHERE liked=TRUE GROUP BY liked;
Empty set (0.00 sec)Of course! Our database is empty! Let’s manually insert some entries:
MariaDB [herschel]> INSERT INTO vote VALUES ('10.13.1.2', TRUE); Query OK, 1 row affected (0.12 sec)MariaDB [herschel]> INSERT INTO vote VALUES ('10.13.1.1', FALSE);
Query OK, 1 row affected (0.11 sec)MariaDB [herschel]> INSERT INTO vote VALUES ('10.13.1.5', FALSE);
Query OK, 1 row affected (0.07 sec)MariaDB [herschel]> INSERT INTO vote VALUES ('10.13.1.100', TRUE);
Query OK, 1 row affected (0.07 sec)MariaDB [herschel]> SELECT * FROM vote;
+-------------+-------+
| id | liked |
+-------------+-------+
| 10.13.1.2 | 1 |
| 10.13.1.1 | 0 |
| 10.13.1.5 | 0 |
| 10.13.1.100 | 1 |
+-------------+-------+
4 rows in set (0.00 sec)
Now retry:
$ sudo nginx -s reloadStill nothing… Maybe the PHP code isn’t running, let’s print something and see if it appears on the page (if it does, then our select statement is wrong, otherwise there’s something wrong with the PHP code itself):
// vote.php
//...
echo "Hello There";
//...It prints “Hello There”, this means there’s something wrong about how PHP is getting the results of the SQL query.
Going back to the PHP select tutorial it looks like the result of a query comes as an abstract object containing the rows we selected. This seems right because if we do the query using pure SQL directly on the database this is what we get:
MariaDB [herschel]> SELECT COUNT(liked) FROM vote WHERE liked=TRUE GROUP BY liked;
+--------------+
| COUNT(liked) |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)It returns us a table! So what we need to do is get the first row of this table and print it into our page:
Now what I see is:
Hello There!
There are Array people who liked the page
There are Array people who disliked the page
Wooops! My guess is that our variables $yes and $no are storing rows, but every row in a table has a list of columns. Of course in this case the row has only one column, but it’s still an array with one value. This array is indexed using column names, so I’m guessing what we’ll use to index the array is COUNT(liked):
$yes = $yes_result->fetch_assoc()["COUNT(liked)"];
$no = $no_result->fetch_assoc()["COUNT(liked)"];Hello There!
There are 2 people who liked the page
There are 2 people who disliked the page
YESSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS! IT WORKS MOTHERF —
Sorry, I get a little too excited sometimes. Now we need to insert the data into the table.
Using PHP to insert to the database
We already have the code to connect to the database and the code to get the user’s IP address. I’ll take another look at this tutorial and code something:
This is what appears on our page:
Error: Unknown column 'yes' in 'field list'Oh yeah. What I tried to do was to insert the value submitted by the form into the “liked” field of the table. I need to insert TRUE if the form submits “yes” and FALSE otherwise, here’s the change I’ll do:
YESSSSSSSSSSSSSSSSSS! IT WORKS!
I’ll just remove the query check because it produces some useless output for the user. We’re all done for the day.
Thanks again
We worked well today! Our website is really getting some sweet features.
If you are new to computer stuff this post might be very frustrating for you (in the sense that you can, and should, have a lot of trouble with this load of new stuff). This is important, allow yourself to experience your mistakes and learn from them.
An important thing to keep in mind is to always try to have as much fun as possible. If you feel stressed, go for a walk and come back later. If you don’t know what’s going on, ask for help, it can’t hurt.
Appendix 1: MySQL Database and persistent data storage
First of all let me say that although I have used SQL and worked with databases before, I’m not familiar with MySQL or MariaDB, so I’ll be using this tutorial as a guide.
By now you should have come across this MySQL thing. As did I. If you have no clue of what MySQL is, take a look at their about page.
First, we need to install MySQL and its PHP modules:
$ sudo apt-get install mysql-server # install mysql server
...
$ mysql_secure_installation # configure mysql
...
$ sudo apt install php7.3-mysqlNow that we have everything set up, I’ll share with you a bit of what I know about databases.
Relational databases and tables
There are some people out there who would kill me for saying this, but I’ll take my chances: relational databases are just a bunch of tables (like the ones we use in spreadsheets) linked together.
You probably have seen a table in a spreadsheet (such as Google Sheets) before. Tables in a databases look just like the ones in spreadsheets.
You: Then why does everybody use databases and not spreadsheets
Me: First of all, accesses and searches in databases aremuch faster than in spreadsheets. Also, it’s common to cross (join) information from several tables to get what you are looking for — which is not possible (or, if so, very hard) in spreadsheets.
Suppose you and me just opened a shoe store together. Let’s create a database to hold the price information of all the shoes we currently sell.
First, we log in MySQL:
$ sudo mysql -u root -pThen we create a new database and change the working space to that database, I’ll call it shoe_store:
MariaDB [(none)]> CREATE DATABASE shoe_store;
...
MariaDB [(none)]> use shoe_store;Now we create a table named shoes to store the name and price of our shoes, take a quick look at the MySQL data types to understand the INTEGER and the VARCHAR types:
MariaDB [shoe_store]> CREATE TABLE shoes (
-> name varchar(40),
-> price integer,
-> );
Query OK, 0 rows affected (0.34 sec)What we just used to create the table and the database is SQL (Structured Query Language). Now let’s fill up our table with some shoes using INSERT:
MariaDB [shoe_store]> INSERT INTO shoes (name, price) VALUES ('all_star', 30);
Query OK, 1 row affected (0.06 sec)MariaDB [shoe_store]> INSERT INTO shoes (name, price) VALUES ('cute_flip_flop', 10);
Query OK, 1 row affected (0.07 sec)MariaDB [shoe_store]> INSERT INTO shoes (name, price) VALUES ('not_so_cute_flip_flop', 2);
Query OK, 1 row affected (0.03 sec)
Finally, we can SELECT items from our tables:
MariaDB [shoe_store]> SELECT name FROM shoes;
+-----------------------+
| name |
+-----------------------+
| all_star |
| cute_flip_flop |
| not_so_cute_flip_flop |
+-----------------------+
3 rows in set (0.00 sec)MariaDB [shoe_store]> SELECT price FROM shoes;
+-------+
| price |
+-------+
| 30 |
| 10 |
| 2 |
+-------+
3 rows in set (0.00 sec)MariaDB [shoe_store]> SELECT * FROM shoes;
+-----------------------+-------+
| name | price |
+-----------------------+-------+
| all_star | 30 |
| cute_flip_flop | 10 |
| not_so_cute_flip_flop | 2 |
+-----------------------+-------+
3 rows in set (0.00 sec)
If we want to look for specific items we can use WHERE:
MariaDB [shoe_store]> SELECT * FROM shoes WHERE name='all_star';
+----------+-------+
| name | price |
+----------+-------+
| all_star | 30 |
+----------+-------+
1 row in set (0.00 sec)MariaDB [shoe_store]> SELECT * FROM shoes WHERE price > 3;
+----------------+-------+
| name | price |
+----------------+-------+
| all_star | 30 |
| cute_flip_flop | 10 |
+----------------+-------+
2 rows in set (0.00 sec)
Problem 1: Structuring our database
I thought about doing something like this: a table in which every entry (row) is a user’s vote, it contains the user’s ID (a number that identifies the user) and a boolean (true or false) type called liked, which will be true if the user voted that he liked the page, and false otherwise. Then, to make the statistics we would sum how many trues and falses are there at the table. The table would look like this:
MariaDB [herschel]> CREATE TABLE test(id integer, liked boolean);
Query OK, 0 rows affected (1.46 sec)MariaDB [herschel]> INSERT INTO test VALUES (1, TRUE);
Query OK, 1 row affected (0.14 sec)MariaDB [herschel]> INSERT INTO test VALUES (2, TRUE);
Query OK, 1 row affected (0.18 sec)MariaDB [herschel]> INSERT INTO test VALUES (3, TRUE);
Query OK, 1 row affected (0.07 sec)MariaDB [herschel]> INSERT INTO test VALUES (3, FALSE);
Query OK, 1 row affected (0.07 sec)MariaDB [herschel]> SELECT * FROM test;
+------+-------+
| id | liked |
+------+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 3 | 0 |
+------+-------+
4 rows in set (0.05 sec)
Notice that TRUE values are stored as zeroes, and FALSE values as ones. But the user ID has to be unique (notice that I’ve inserted the user 3 twice), for that we use SQL constraints:
MariaDB [herschel]> CREATE TABLE test_2 (
-> id INTEGER UNIQUE,
-> liked BOOLEAN
-> );
Query OK, 0 rows affected (0.53 sec)MariaDB [herschel]> INSERT INTO test_2 VALUES (1, TRUE);
Query OK, 1 row affected (0.12 sec)MariaDB [herschel]> INSERT INTO test_2 VALUES (1, FALSE);
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
Now let’s make this test_2 table our new vote table, first we drop the original vote table:
MariaDB [herschel]> DROP TABLE vote;
Query OK, 0 rows affected (0.35 sec)Then we change the name of test_2 to vote using the alter table command:
MariaDB [herschel]> ALTER TABLE test_2
-> RENAME TO vote;
Query OK, 0 rows affected (0.27 sec)MariaDB [herschel]> show tables;
+--------------------+
| Tables_in_herschel |
+--------------------+
| vote |
+--------------------+
1 row in set (0.00 sec)
BOOM, now we have a table to store the votes! Now we save the changes and quit:
MariaDB [herschel]> quit
ByeHow polite.
Problem 2: Generating unique user IDs
Let’s google something:
generate user id nginx
Nope. What if we used the computer’s IP address to identify the user? I know it’s not perfect as multiple users can use the same PC, but it’s a start:
get client IP address php
Well well well, what do we have here?
Whatever you do, make sure not to trust data sent from the client.
$_SERVER['REMOTE_ADDR']contains the real IP address of the connecting party. That is the most reliable value you can find.
Does that work? Let’s see, I’ll set up a page in our server that tells us our IP:
If you access it from localhost you probably got something like ::1, but accessing from outside gives us a ‘normal’ IP address.
IT WORKS! But here’s the thing: now we’re using IP addresses, not integers, to identify the users, so we need to change the id field on our vote table to use strings instead of integers:
MariaDB [herschel]> ALTER TABLE vote
-> MODIFY id VARCHAR(15);
Query OK, 1 row affected (1.28 sec)
Records: 1 Duplicates: 0 Warnings: 0Great, now we can identify users and our tables are up to date.
Problem 3: Using proper users to access database
Let’s google the error we got:
Connection failed: Access denied for user ‘root’@’localhost’
Oh boy, this really seems to seal the deal, let’s try it then. First, we log in as root and create a new user:
$ sudo mysql -u root -p -h localhostMariaDB [(none)]> CREATE USER 'gabriel@localhost' IDENTIFIED BY 'my_secret_password';
Now we give the user gabriel access to the database herscheland quit:
MariaDB [(none)]> GRANT ALL PRIVILEGES ON herschel.* TO 'gabriel@localhost';
Query OK, 0 rows affected (0.02 sec)MariaDB [(none)]> quit
Bye
Let’s now try to log in as the new user:
$ mysql -u gabriel -p -h localhost
Enter password:
ERROR 1045 (28000): Access denied for user 'gabriel'@'localhost' (using password: YES)Crap, but I had an idea. I think what’s really happening is that my user gabriel, the OS (Linux) user, not the mysql user we just created, doesn’t have access to mysql. Let’s google a little bit more:
mysql only sudo has access to database
Strange, that’s exactly what we did…
OH MY GOD I FORGOT THE DAMN QUOTES!
The answer says:
CREATE USER 'francesco'@'localhost' IDENTIFIED BY 'some_pass';GRANT ALL PRIVILEGES ON shop.* TO 'francesco'@'localhost';
And I did:
CREATE USER 'gabriel@localhost' IDENTIFIED BY 'my_secret_password';GRANT ALL PRIVILEGES ON herschel.* TO 'gabriel@localhost';
The problem was that I typed this 'gabriel@localhost'instead of this 'gabriel'@'localhost'. Okay, now it works.
