PHP Fundamentals: An Introduction

Published on May 16, 2012 under How-To, Web Development


Ever since I debuted my Content Management System a few weeks ago, I've gotten much positive feedback and some questions. Most of the feedback came from web designers who loved the idea of custom-built solutions and wanted to know more on how they could build their own CMS, or just be able to more effectively modify existing solutions to meet their clients' needs.

This is the start of a series where I'm going to dive into the "need to know this" ideas of PHP that I mainly use in much of my code work. As this series progresses, you'll learn how to pull and display data from a database, add new data in, edit it, and delete it when you want. I'm sure proficient PHP coders won't be interested in this series, but this is ideal if you're just diving in and want results faster.

What you need to already know

For these articles, I'm mainly aiming it at building PHP for websites. These fundamentals will also help if you're diving deeper into application development, but that will take you time and experience to get to that level. I do think you should know some basics on PHP and writing code in general. If you don't know to start and end PHP code with <?php and ?>, if you don't know what print and echo statements are, or what an if-else statement is, then take a little time to read up on the basics from a book or website.

Setting up a database

MySql and PHPMyAdminThere is a lot of work that can be done outside of a database, but much of what you are going to do will involve a MySQL database. Building something like a CMS for dynamic websites will involve sending and receiving data, so today I'm just going to get you a little familiar with how to set up and get a database ready for your needs.

You should be using a host that will give you control over your database and has an online application called PHPMyAdmin installed. You will find all this in the control panel of your web hosting account. Each company is different, so seek out a link or button on your control panel for MySQL databases. On 1and1 hosting, it's labeled "MySQL Administration". On GoDaddy, it's labeled "MySQL" under the Databases tab on the navigation.

From there, you make a new database. Just press whatever button they have and set one up. You'll have to give this database a name on some hosts, others will do it for you and simply ask for a password. Make sure to use a complicated password and write it down. Something like this I would not try to use any kind of familiar password. Just make up a combo of letters and numbers, and write it down.

Setting up a database table

It'll take around thirty minutes for your host to set up the database. Once you see it's there, you'll then go to said database via a program called PHPMyAdmin. Again, hosts will change it up as to how you'll get there. You might find links on the same page where you set up the database, or on its own section. Sometimes you'll have to enter in a username and password when you enter PHPMyAdmin.

When you're finally in, you'll see the left column will contain the name of your database as a whole. On the main column in the right will be your work area. I'll start off by clicking the text link on the left column (usually it's the name of the database). As you build database tables, you'll see them appear in the list that will show in front of you and on the left column.

Now then, you should see a spot where you can create a new database table. Give a name for your table and enter in a number of fields you might want. If you're not sure, don't worry. You can add and remove fields as you see fit.

The next screen is where you'll then actually make the table. In the Field column you place in the names of the fields you wish to create. I usually advise to use simple terms with no spaces or special characters. You should maintain this practice in table names and variables even.

A look at the fields when you set up a database table
A look at the fields when you set up a database table.
Click to enlarge

The Type and Length/Values columns are the important ones because you're going to designate what kind of data you'll be putting into the table. The drop-down menu contains a myriad of choices, and you should learn what they all mean...but for now here's the ones I generally use and why:

VARCHAR: This is for practically any kind of short text and numerical string data that you would want to put in. It could be someone's name, a title of a blog entry, a hyperlink to an image, anything 255 characters or shorter. You'll have to put in a length on the Length/Values column for this. Usually I put in 255 for most data, but sometimes I'll put in 3 to 5 if I'm using this field for a short item like a "yes" or "no" or perhaps a state abbreviation from a fill-out form.
TEXT: This is when you go beyond 255 characters. So I used a blog entry title example in VARCHAR explanation, but this is where you would put the actual entry. You can put up to 65 kb of data into a TEXT field, which is roughly 65,000 characters. Whenever you see me use a text area in any of my forms, the data is usually going into a field with TEXT as the type. You can even put HTML code into this field. Think of it as an open Notepad file with a 65,000 character limit. Note you do not have to put anything in the Length/Values field with this one.
DATE: It's as it's called. This is if you want to put in a specific date for whatever reason you have. Usually the format would be YYYY-MM-DD. Bear in mind when you use this, you'll have to use the PHP date() functions to convert this into formats you would rather display them as. So why use this over just a VARCHAR? It will allow the database to know this is a date, and be able to sort items by these dates. Like TEXT, you don't have to put anything in the Length/Values field.
INT: INT stands for Integer It's ideal for any needs you have involving numbers. I'll use it for when I make primary keys, or any other numeric data. In PHP you could use VARCHAR or CHAR for numbers as well, but again, it's better to tell the MySQL database exactly what kinds of data you enter in. With the Length/Values field, you enter in a number between 1 and 9, but this will more show how many integers can be held in this field. So an entry of 3 can hold values between -999 to 999, and an entry of 1 can only hold values between -9 and 9.
DECIMAL: If you plan on putting in items with a decimal (like a price or financial data), do not use INT. Use DECIMAL. In the Length/Values field you would put two numbers separated by a comma. The first number is the total amount of digits you can have in the number. The second number is where the decimal falls. So 5,2 would mean you can put numbers anywhere from -999.99 to 999.99.
TIMESTAMP: I will tell any of you who are going to be dealing with dates from a sorting standpoint, use TIMESTAMP over DATE. Believe me, when I put in two blog entries on the same day, my scripting and the database could not figure out which entry I put in first. With TIMESTAMP, you get the addition of time to the entry, so it will be in the format of YYYY-MM-DD HH:MM:SS. I've since moved to using timestamps and have not looked back.

At this point, the rest are all options. Collation is generally to define what kinds of characters would end up in VARCHAR or TEXT fields. I usually leave these blank and let PHPMyAdmin designate it. Attributes are only useful if you want a timestamp updated with the latest date/time on an entry if it is created or changed, or if you need a zerofill done on a number. So if you set up an INT with a 9-digit value and enter "12" as the data, it will go in as 000000012.

Null is merely there to decide if you'll accept an empty blank or not on a field. I generally keep things set at "not null". The Default field is if you want some kind of specific data entered when you fill out and make a full entry into the table. Extra mainly is for setting up an auto-increment on an integer. I will use this when I make a primary key for the table. As you place entries of data in the table, this will add 1 to the number you're incrementing automatically.

Using a key

The last three radio buttons left are for when you want to set up a Primary Key, an Index Key, or a Unique Key. Some have rationales on which one they prefer. I use Primary. What is a key for? It's a unique piece of information that will separate each entry from one another. Something that each specific entry in the table will have that no other will have. When I mentioned the auto-increment extra, I generally use it for my primary key. So each entry will be assigned a number that will identify it.

Unless you feel it's not necessary, you should generally set up some kind of key on every database table you have where you would want to be able to call up specific entry at any given time. Some will use dates as their key, or strings of text. I know that when I started using mod_rewrite to make permalinks, I'll call up data by using a combination of the date and permalink shown in the web address.

Building with premade sql code

For the future articles I will post, I'll include a text file with a load of SQL code and data in it. Most of the time when you deal with any third-party setups like Wordpress, the scripts will set up and install the database tables. However, sometimes you might find someone (like me) just handing you a file with the code to setup the tables and insert the data.

When you're on the main screen of a database and see the list of tables, you'll notice one of the tabs up top says "SQL". Click it and you'll see a text field and a place to upload a file. You can either copy and paste the code into the text field or select the file on your hard drive. When you press "go" the script will be run and it will set up your database and enter the data if it was included.

This is important to know because I will advise you when you're working with databases like this, you should occasionally back up your databases using the "Export" tab. If you happen to move to a new host or lose your data somehow, you would use the "SQL" area to put it all back in from your backup. Believe me, back in 2006 I went on a trip and my host accidentally destroyed my databases in a server migration. I regretted not having any backups, as it took me months to slowly recover.

Next time...

OK, we've gone down the long, but short tutorial on setting up MySQL databases and tables. Next time we're going to run a sql file to make a table with data, then I'll show you how to pull and display this data using PHP.

Have you ever used a database before? Would you ever try making a database-driven website?

Tags: php fundamentals, php, mysql, technology, databases

comments powered by Disqus