This is a go-to stack of technologies for many web developers. Whether your go-to is PHP, Python, Perl, or any of the other programming language, being able to store, update, and delete data in a database is essential. You don’t need to be a database guru to create a database, create tables within, and put the data infrastructure to use in a web or desktop application. Yes, in a corporate enterprise environment you are going to “phone a DBA” at times to optimize your web application by using database indexes in your query, but if you are a developer, or have the passion to become a developer, you should learn the basics. For this session, I will be working on a CentOS server that has Apache, MySQL, PHP, and the PHP MySQL extension so that PHP can put MySQL to use.
So assuming that you already have a CentOS or other similar Linux flavored server with the appropriate packages installed, lets get started. As a developer, there is only so much database administration that you should know in a minimal expected capacity environment. If your environment expects to receive massive flows of internet traffic, you might want to dive deeper than what I am going to provide below, hire someone who has a focus in database administration, or phone a DBA friend :).
In order to get started with MySQL, you should know how to access MySQL in Linux from the command line. Of course the MySQL service should already be running…
mysql -u <username> -p
<username> – This will be your MySQL username
Note: When you hit enter, you will be prompted to enter your password for the associated username you used.
Now, if you see what I see, you have successfully authenticated. Lets say you were logging into a database to do some work or analyze some data that was new to you. You can use the “show databases;” command to display a list of databases. Don’t forget the “;” semicolon at the end as that is how MySQL understands the end of whatever command you are entering.
So, while we have some databases already available, we are going to create a new one so run “create database <name>;”. Where <name> is the name you want to give your database.
Now that we have a database created, we need to “use” that database so we can start creating tables.
Creating a table, we must provide at least a name for the table, the column names, and the column data types at a minimal.
Here, we have just created a table named ‘tbl_example’ and gave it two columns. The first column is “ID” and it has a data type of Integer. The second, we called it “data” and the type is varchar which is text, but has the limit of a 255 character max. So if you intend to store data that is more than 255 characters long, use TEXT type instead of VARCHAR.
Moving along, we currently have no data in our table as you can see by running a “SELECT” query on the table.
In this SELECT query we are asking MySQL to return ALL the columns by using the * from the tbl_example that we just created which of course is empty.
Now, lets put some data in our newly created database table with an INSERT query.
We just told MySQL to insert a row into the tbl_example table, said which columns we wish to put data in, and provided the values to put into those fields. Cool… So lets run the SELECT * query we did earlier to validate that our data made it.
If all went well with the INSERT query, the SELECT query will now display the row of data. So that we have some dummy data, lets insert some additional rows. In MySQL CLI, you can utilize the up arrow-key on the keyboard to run the previous commands.
Now that we have some dummy data to work with, lets jump into the PHP side of things so that we can pull, add, and manage our data from a web interface.
Here is how I have setup the application’s file structure:
Within the root directory we have our index.php file, a js directory that contains our app.js, and a php directory that contains our Controller.php.
Lets dive into app.js and start writing some jQuery. The first jQuery we are going to write is a simple function that runs when the document is ready.
Ok so when the document is ready, we want the browser to perform something…
What do we want it to do? Well, for this example we are going to perform an HTTP Post to our php/Controller.php file. Typically in a web application utilizing an Apache, jQuery, PHP, and MySQL stack, you are going to make many different calls to your php/Controller.php.
For this example:
We are doing a POST and the first parameter required is the controller file which we have provided the path of “php/Controller.php”. The second parameter is data we can pass to the Controller. I am only passing “do” with the value of “pullData”. The third parameter is the return function where e is the return.
Lets jump into our PHP controller in php/Controller.php.
So, what should the HTTP POST return? In this example, we are going to connect to our local database, run a SELECT * MySQL query to pull all the data, and return it to the front end to display an html table containing the data.
Call me old fashioned, but this is how i learned to use PHP and MySQL :) 1 million ways to skin a cat as they say. When it becomes deprecated, I will learn the *newer* way.
I am going to break down each line for you:
1. mysql_connect – here we are connecting to our MySQL database. We need to provide a username and password.
2. mysql_select_db – just like when we are at the MySQL CLI, we need to “use” a particular database to then have access to its tables.
3. $sql = – this is our MySQL query statement string. in this example, we are doing a SELECT * on the table ‘tbl_example’ so it should return ALL of the data.
4. $q = – here we are running our mysql_query function and passing the $sql statement to the function.
5. $rows = – the mysql_fetch_assoc will create an associative array containing the data that our MySQL query returns.
6. mysql_close() – this function will close the connection with the MySQL database.
Technically our “pullData” case is not returning ANYTHING at this point. We have a $rows variable that contains an array of the data, but we need to return it to the front-end of the application for display to the user.
A few things were added to the PHP controller:
1. $table – a variable was created to store the HTML that’s going to be returned to the front-end.
2. do-while – a do while loop will run through every single $row of data that got returned from our MySQL query.
3. $table .= – in php a “.=” will append, so within the do-while loop we keep appending a new HTML table row containing the $row data.
Now all that’s left, is to echo $table
At this point, if we load our index.php in a web browser, jQuery framework gets included, app.js gets included and makes an HTTP POST to php/Controller.php with a do value of “pullData”. The PHP controller connects to the MySQL database, runs a query to get all the data, builds an HTML table of the data, and returns it to the front end. Or does it? Remember our app.js?
At present, all its doing is alerting the return data so if everything was done correctly without any syntax problems, and you have the sufficient privileges on your MySQL database table, this is what happens when you load the page:
Utilizing FireBug, you can see the HTTP Post’s that happen when you visit a page so it can be useful in debugging and reverse engineering. For example, our web application displays like this in FireBug:
If we expand it, and look at the “Post” tab, we can see the POST variables and there values:
Another cool feature of FireBug is the HTML tab which will show us the data that is getting returned from the PHP controller:
As you can see, its returning the data that we put into our MySQL database earlier.
Now, still the HTML table being returned from the HTTP POST is not being displayed to our users how we want. Its time to load that return into our page’s HTML. Within the <body> </body> tags, lets create a <div> that will contain the data.
I created an HTML div container and gave it an id attribute of “data”. Utilizing this id, we will load the return data into the container. We will do this in app.js.
To load the data into our div, we use the .html method and pass “e” which is our return data. Now if I refresh index.php in my browser, the data from the database is displayed like so:
Understanding these core concepts in web development, it allows you to also dissect websites and web applications created by others. Just like how I explained the major benefits of using FireBug, you can see all the POST’s being done to the backend and its return data. Lets say you wanted to scrape data from a website and load it into your own database. You can certainly use FireBug to dissect where the back-end calls are going, what variables are being passed, and start doing POST’s and loading the data into your own database.
I know this course was long but I hope you readers find it beneficial in starting to create web applications. Feel free to comment :) I may screen record this at some point depending on the response from readers. In addition, I will be following up this tutorial with another tutorial where we will create an HTML form in our index.php and when the user submits the form, it will add the data to the database, and refresh the HTML table to display the newly created record.