HTML, jQuery, PHP, and MySQL Basics for Creating a Web Application

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 :).

Before continuing, you should have a basic to intermediate knowledge of Linux, HTML, Javascript, MySQL, and PHP.  You should also at least understand the concepts of any programming language.

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.

Screen Shot 2014-03-22 at 6.35.46 AM

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.

Screen Shot 2014-03-22 at 5.00.28 AM

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.

Screen Shot 2014-03-22 at 5.04.18 AM

Now that we have a database created, we need to “use” that database so we can start creating tables.

Screen Shot 2014-03-22 at 5.07.05 AM

Creating a table, we must provide at least a name for the table, the column names, and the column data types at a minimal.

Screen Shot 2014-03-22 at 5.11.58 AM

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.

Screen Shot 2014-03-22 at 5.15.19 AM

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.

Screen Shot 2014-03-22 at 5.17.57 AM

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.

Screen Shot 2014-03-22 at 5.19.27 AM

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.

Screen Shot 2014-03-22 at 5.21.15 AM

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.

In a typical web tool or application, you have an index html or php file that will display content maybe or a form, a javascript file to bind user actions to buttons etc, and my preference, a PHP file for the server side work.  There are many useful javascript frameworks out there, but one of the more popular and one of my favorites is jQuery.  So in this example application, we will use jQuery.

Here is how I have setup the application’s file structure:

Screen Shot 2014-03-22 at 5.29.49 AM

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.

Moving on with the index.php. Here, we need to load in our app.js file.  Back in the day, the way that I learned how to include a javascript file was to include it within the <head> </head> tags.  These days, its best to include any external javascript files right before the end of the <body> tags.  So right before </body> like so:

Screen Shot 2014-03-22 at 5.34.38 AM

Before we start working on our app.js file, we must also include before it the jQuery framework.  You can obtain the jQuery framework from and put it in your js/ directory since its a javascript resource.

Screen Shot 2014-03-22 at 5.36.31 AM

Now include the jQuery javascript file in our index.php.  You are going to want to include it BEFORE the app.js because the app.js will contain the code for the web application we are building and will need to rely on the jQuery framework.

Screen Shot 2014-03-22 at 6.12.50 AM

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.

Screen Shot 2014-03-22 at 5.41.33 AM

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:

Screen Shot 2014-03-22 at 5.45.23 AM

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.

Screen Shot 2014-03-22 at 5.48.12 AM

Here in the PHP controller, you should create a switch case based on the value of the POST value “do”.  The first case we are building is “pullData”, so the javascript HTTP POST to the PHP Controller we created earlier has that value of “pullData” for the variable “do”.  That means, whatever is within the “pullData” case in PHP will be executed.  For good measure, it should die after performing that action as to not look at any of the rest of the PHP controller’s code.

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.

Screen Shot 2014-03-22 at 5.53.51 AM

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.

Screen Shot 2014-03-22 at 6.05.05 AM

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 :)

Screen Shot 2014-03-22 at 6.08.01 AM

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?

Screen Shot 2014-03-22 at 6.09.49 AM

Like mentioned earlier, “e” is the data that gets returned from the PHP controller.  So “e” in javascript in this instance is our $table variable in PHP that gets echoed out.

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:

Screen Shot 2014-03-22 at 6.14.02 AM

Now, using a javascript alert to display the return data is not the way we want to show it to users but is a good way for developers to see what is going to be returned from their PHP controller.  In addition, if you use FireBug in FireFox, which is in my opinion the BEST javascript debugger that EVERY developer should use, then you can use console.log(e) to just log the return data in the FireBug console.

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:

Screen Shot 2014-03-22 at 6.16.52 AM

If we expand it, and look at the “Post” tab, we can see the POST variables and there values:

Screen Shot 2014-03-22 at 6.17.49 AM

So clearly we can see that our POST has a “do” variable with the value “pullData” just like we wrote in our javascript.

Another cool feature of FireBug is the HTML tab which will show us the data that is getting returned from the PHP controller:

Screen Shot 2014-03-22 at 6.19.43 AM

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.

Screen Shot 2014-03-22 at 6.21.37 AM

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.

Screen Shot 2014-03-22 at 6.24.00 AM

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:

Screen Shot 2014-03-22 at 6.25.23 AM

While this is not the most exciting tool or web application, you should now know some good core concepts about how to leverage javascript and PHP to have them work together.  As a web developer these core concepts are used in many stacks and frameworks.

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.

 Download Full Source Code

Part 2: HTML, jQuery, PHP MySQL Basics in Creating Web Applications

























2 Responses to HTML, jQuery, PHP, and MySQL Basics for Creating a Web Application

  1. Really….
    Using mysql_xxx functions in 2014 – they are deprecated.
    PLease use wither mysqli_xxx functions or PDO objects

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>