Sadly, my study has stalled for awhile as it was spent on Game Designing that SMBX game I created for the kids. Anyway, going back to studying, so far I have learned how to create an IV Drip Rate Calculator, a multiplication table using for loop, solve project Euler’s first easy question, use PHP to store data via Super Global Variable $_SESSION, and now I wanted to learn MySQL and how to use PHP to connect and manipulate MySQL’s Database. And using, mmtuts tutorial videos, I was able to understand and learn how to program MySQL to create a database and manipulate its tables using this tutorial.
Recently, I came upon mmtuts few videos that revolves around PHP and MySQL interaction, and I have compiled them on this post so that we don’t have to search them on YouTube anymore. I am going to apply this knowledge into practice, posting my notes here in case I forget them. Well, the thing about programming is that you need to keep on practicing them, otherwise, you’ll forget and you may have to start reviewing them all over again. After weeks of hiatus, I feel like I don’t know anything about PHP and that I might need to start from the very beginning, but thank god, I kept my notes on this blog. Now all I have to do is walk through them so I could remember. Anyway, going forward…
One of the couple things I did recently was create a MySQL database with PHPMyAdmin in localhost. Unfortunately, I haven’t included that tutorial in this blog yet so I couldn’t insert any reference as to how to setup a localhost server in your PC, as well as setup PHPMyAdmin. For that tutorial, you may have to reach out to Google using this link. Now, assuming everything is setup properly, what I did was activate and launch PHPMyAdmin. It should open in one of your browsers. On PHPMyAdmin’s console, I created a new database named altometa. In order to do that, click on New at the Side Menu, then enter a database name (in this case “altometa”) in the Database page, and click on Create button. Now that we have a database, I went on to the SQL menu, which opens up the query command page. Next, I entered the following commands:
CREATE TABLE DBUsers (
uid int(11) not null PRIMARY KEY AUTO_INCREMENT,
first_name varchar(256) not null,
last_name varchar(256) not null,
email_ad varchar(256) not null,
rank varchar(256) not null,
passkey varchar(256) not null
);
INSERT INTO DBUsers (first_name, last_name, email_ad, rank, passkey)
VALUES ('Anthony', 'Y', 'altometa@altometa.com', 'Admininstrator', 'admin');
To learn and better understand what I did, go to this tutorial. Technically, the command is sending an SQL query to create a table name “DBUsers” (will be turned into all lowercase “dbusers”), with columns for uid, first_name, last_name, email_ad, rank, and passkey. Int means Integer so for the uid, MySQL will always treat its content as an integer. Varchar is the type that tells MySQL to treat the characters as string. All of them should not be empty (thus not null), and uid should be used as the PRIMARY KEY and will automatically increment by 1 every time a new set of data is entered.
The next set of command lines (INSERT) will access first_name, last_name, email_ad, rank, and passkey, entering the values “Anthony”, “Y”, “altometa@gmail.com”, “Administrator”, and “admin” into those columns in the same order.
Now that we have a database with a primary user, we can begin creating PHP files. But before anything else, it is also important to set up a user account that has all the privileges over the entire MySQL databases. By default, there is username “root” that has no password but has all the privileges over MySQL, which was explained by mmtuts in his tutorial videos (first video). In my case, I decided to create another username “altometa”. In order to do that, go to PHPMyAdmin’s main dashboard, then select User accounts in the menu. In the User accounts page, there is a link called “Add user account”. Click that and the next page should be pretty self-explanatory. Enter a user name, select host name “Local” which will automatically set localhost in the Host name’s field, enter a password, retype password, activate all privileges by checking Global privileges, and under SSL, keep it at REQUIRE NONE then hit “Go” button.
As for the PHP side, the first thing I did was create a folder “includes” within htdocs (XAMPP). After that, I created the PHP file db_inc.php inside “includes” folder with the following content:
<?php $dbServername = "localhost"; $dbUsername = "altometa"; $dbPassword = "admin"; $dbName = "altometa"; $conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);
So what does it mean? Well, it’s telling PHP to connect into the database server, which in this case is localhost since we are using local. And I will be connecting into it using the User account “altometa”, with the password “admin”, then access database table name “altometa”.
Note that each MySQL query we do in PHP requires us to access MySQL, that is why we have to create this PHP file so that we could easily call it without having to type the code each time we create a query.
Well, this is it for now as I am rather running out of time. I actually did other PHP files a few weeks ago, those are PHP files that allows you to search the database, and insert data into the database. I will probably be developing them and will add them here next as well as explain how they work.
Follow me at:
