Learn Web Development – The Super Simple Way

Learn web development by learning databases

This is where things get a little more complicated. but not because we want to make it more difficult. We’re learning how to use MySQL, the best relational open source data storage engine that works seamless with PHP. It’s as if MySQL and PHP were designed to work together.

* SQL stands for Structured Query Language.

Here’s what we need to continue to learn web development.

  1. We already have MySQL installed since we installed WAMP.
  2. We will need to learn some basic SQL commands to get, post, and manipulate data.
  3. We need to figure out how to make PHP connect to MySQL.

So, let’s start writing some code. Start with a fresh file. Erase everything except for our opening and closing PHP tags.

At the beginning of our file, connect to our database:

<?php
$db = mysqli_connect("127.0.0.1", "root", "", "test");
?>

This tells PHP to attempt to connect to MySQL. MySQL resides on the localhost or 127.0.0.1 server. This is your local machine. We’ve seen this before when we were setting up our hosts file.

If you installed WAMP properly, your PHP code should be able to connect to the MySQL database.

Refresh your browser and you will see nothing. This is good. This means that we weren’t given an error, and no news is good news.

There’s this tool that I use for MySQL databases. It’s called SQLYog. The community version is free and pretty full of features. If you want to use it for database syncing later down the road, I suggest getting the ultimate version for $179 currently. This can let you tunnel using SSH, and sync databases across multiple environments.

If you want to learn web development, and make something of it, later down the road you may have to spend a little extra cash on domain names, hosting, and some tools. But, I’ll show you how you can keep it low cost.

Back to our database. After you successfully install and run SQLYog, open it up and create your first connection using the same parameters as above

MySQL Host Address: localhost
          Username: root
          Password: (blank by default)
              Port: 3306 (by default)

And create your first connection. Voila! (if everything went according to plan)

Notice that after you connect, you will have some databases showing up on the left side. Your databases, or schemas, hold tables, which in turn hold rows, which in turn hold fields with data. Notice the test database that comes standard when you installed WAMP.

Our connection from PHP was set up to connect to this database. You can play around with this database. Let’s create a table. Expand the test database by clicking the plus sign next to it. Notice that there are no Tables yet. Add one by right clicking on Tables and selecting Create Table. Name the table (Table Name) task. This is your task table. Notice below the header lines that asks for columns. Column Name, Data Type, Length, etc. This is where we’re going to list our tasks table fields.

Enter the following two fields for now:

task_id
task_description

Make task_id of data type int, check the PK (primary key). Not Null?, Unsigned?, and Auto Incr? checkboxes will automatically be checked.

int means that the index field will be an integer. This is so that MySQL can find the data fast. Searching for a text value is much, much slower, so we create an index that’s easy to search. We saw indexes in PHP when we created our array.

PK means that this field will be a primary key in the table.

Not Null? means that we won’t accept a null value. There has to be a value.

Unsigned? just means that we won’t accept negative numbers. This is a good thing because now we can use twice as many integer values on the positive side.

Auto Incr? tells MySQL that whenever a new record is entered, the primary key will be automatically incremented by one based on the last / highest index ID.

Now we understand the fields, let’s make the task_description of data type varchar (variable character length – basically, a string of characters), length of 255 meaning that we can type up to 256 characters for the task (this includes spaces, and it’s 0 based), Not Null? checked and done. Notice that we didn’t bother with PK?Unsigned?, or Auto Incr?. This is because first, it’s not an integer, so unsigned doesn’t make sense, and we don’t want to make it a primary key since we already have one, plus, we’re not looking at indexing this value. This is simply our value of data that is to be shown to the user.

We have our task table. That’s great! Let’s populate it. Right click on the newly created table on the left side, and select “Open Table“. This will show you the results with “task_id” stating (Auto) and “task_description” stating (NULL).

Let’s enter our values from before into our task table. No need to enter the task_id as it will be automatically entered.

task_description
-----------------------
pick up kid from school
drop off laundry
go to the gym

As you enter the data, it’s automatically saved when you move on to the next field.

Learn web development by including SQL / MySQL in your arsenal of knowledge.

Now, let’s get our data from PHP. Back in our PHP file, continue to write:

<?php
$db = mysqli_connect("127.0.0.1", "root", "", "test");

$result = $db->query("SELECT * FROM task");
while ($row = $result->fetch_object()) {
    echo '<div>' . $row->task_description . '</div>';
}
$result->close();
$db->close();
?>

Let’s understand what was done here.

  1. The first line connects to our database. Done.
  2. The second line has a query to pull data, select all data (*) from the task table and store it in our $result variable.
  3. The next 3 lines basically loop the result data and echo out the results. Notice how we use the actual database field name to push out the specific data of that row.
    1. The first of three lines sets the first row into our $row variable.
    2. The second echoes out the field value for task_description, wrapped in some “divs”.
    3. The last line simply loops back to the beginning and checks the next record.
  4. The following line closes our result set.
  5. And the last line shuts down the connection to the database.

It’s important to know that PHP and MySQL do not persist database connections.

And that’s it.

Notice that even though we had some <div> elements in our output, they didn’t output to the screen. That’s because web browsers interpret those as HTML tags. We can get more into this later.

Next, to learn web development, let’s do some HTML. Just a little for now. Nothing too extravagant. Just so we can understand how HTML works together with other languages to form web pages which in turn form websites.