There's plenty of resources out there on how to do certain things in Web Dev, which (hopefully) after this set of tutorials, you'll be able to use your tools and self-teach yourself new things. So yeah, all that ranting out of the way, because...on to actual coding! Sort of! Let's review our environment/tools so far:
1) Folder organization. Horribly easy to overlook, but organized folders (especially setup as MVC) makes finding files simplified.
2) Text Editor. I suggested Sublime Text 2. Setup the file manager to view all folders in the project directory.
3) File Repository. GitHub is where it's at.
4) A website. I didn't do a tutorial on this one - there's too many hosts out there to do an actual tutorial on. HOWEVER, this step is "easy", and actually, the hosting company can more than likely live-chat you through the steps to get your site going.
5) Terminal Client. We're using Git Bash for this, but this isn't the only option. A popular choice for SSH (among other things) is to use PuTTY
6) FTP Client. Yes, we're trying to avoiding using this on purpose. FTP is so 1990's. However, it's a good idea to have one installed for files not included in our repositories. FileZilla works great for this.
5) MySQL. Using the server path, username, and password, able to work with databases. Today's tutorial will cover this, however, SQLBuddy or phpMyAdmin is the tool of choice for this part.
Important note: Almost all of these are free. You shouldn't have to pay for these tools, except for SublimeText2. It's worth the cash, but to remain in the spirit of "free", an alternative is Programmer's Notepad (been a while since I used this one!). Using plain old Notepad is just...it's doable, but there's better tools for the job. Even a crap one with nothing but a file manager is going to save a ton of headache.
Man, it's like I'm purposely avoiding getting to the coding part...enough!
Yay! We finally get to work with some PHP. YES.
First, MySQL! Hopefully you've setup and created the database "test" on your website. If not, we're going to have problems. Well, *you're* going to have problems.
The majority of MySQL tutorials go about telling you how to throw something together. This is a tutorial on getting started in Web Development, however, so the more technical aspects will be left up to you. The good thing is that for the most part, setups and working with MySQL is rather simple (varies widely on the job!)
We have our "test" database and are ready to immediately jump in and start creating tables and fields all over the place, right!? WRONG.
1) Write out a database schema. Yes, WRITE. IT. OUT. Sure, it's fun to just whip up fields and tables as you need them, but in practice, that's a great way to cause a big mess, especially with larger databases and projects.
So the first step for our schema is to take a general overview of the entire project, and then map out what exactly we need to store into a database. Our example project is going to be a website membership system. Thinking about this all at once is a bit intimidating, but work on it one piece at a time, and you'll suddenly have a great system going.
Let's write this out!
Title: Website Membership System
Purpose: Users can create and manage their profiles and information through a website.
- Profile View
- Profile Edit
The reasoning behind each field:
"id" = the row id. This is a PRIMARY UNIQUE NON-NULL AUTOINCREMENT INDEX. Scary, huh? More defining:
PRIMARY = only one primary field per table, the "boss" of the whole thing.
UNIQUE = no duplicate values are allowed within this field
NON-NULL = when a field is empty, it's NULL. Not even "" is stored, it literally means there is nothing there.
AUTOINCREMENT = every time a new row is added, increase the counter by one. MySQL has a counter per field in these cases, so that if you create record #532, delete #532, and then create a new record, it will be id #533 (and you'll have a gap in id's between 531 & 533).
INDEX = in short, this means to "make it faster". Do not apply INDEX to every damn field in the table. Only index high-use fields.
"username" = the user id that is entered when logging in
"password" = the password matching the user id. Note that we'll handle encryption on the PHP side. NEVER EVER store passwords in plain-text! Seriously! NEVER!
"email_address" = I hope this one is obvious. By the way, I should note that I like to name fields with underscore separating words. An alternative is to camel-case, so that it would be "emailAddress". Whatever you use, BE CONSISTENT. We use this field to email the user in case of lost-passwords. If you use this for spam, I will punch you in the face.
"created" = the timestamp of when the record was created. It's sometimes nice to know when things are created.
"modified" = similar to created, but stores timestamp whenever the record is changed. This is great for monitoring activity.
Let's define a few of these datatypes:
"int" = integer. It's a number. The number in parenthesis (8) means that a number up to 8 digits can be stored.
"varchar" = variable character. a-z, 0-9, symbols, all that fun stuff.
Special note regarding the "int(11)" for "created" and "modified": We could use DATETIME and TIMESTAMP respectively, but the reason why we're not going to use that here is to avoid converting from PHP to/from MySQL. PHP uses timestamps for everything related to date and time. It's an integer (number) that counts the number of seconds since midnight on 1970Jan01GMT. That's when the world was created.
- MySQL has 3 date types: DATETIME, DATE, TIMESTAMP.
DATETIME = stores as YYYY-MM-DD HH:MM:SS
DATE = stores as YYYY-MM-DD
TIMESTAMP = DATETIME, but automatically updates to the current time every time the record is altered
- The issue would be that for every database call, we'd have to convert (handled through our PHP or in the SQL query itself). PAIN IN THE ASS.
- Although, it comes down to what you need to use these for. When storing as an "int", you lose all of MySQL's built-in time and date handling procedures. In this case, we're handling everything on PHP's side, so that's the reason for the int.
Quite a few words for so little so far, right? The major thing to know about databases is that it's more strategy than storage. Databases need to be easy to navigate. It is really easy to complicate a database to the point where not even the creator knows what is going on (common with "let's slap this on" developing).
Ok, here's another tool for your arsenal. The majority of web hosts supply phpMyAdmin with websites. You can see if it's already setup for you by appending http://
Well, that way you can access the login page directly without having to launch your webhost control panel every time. Also, this is more than likely the address you'll use when setting up your own database manager (like phpMyAdmin).
Regardless, in your website setup, you should have the following information:
- Username (it better be different than your website account login name!)
- Password (again, different, and complicated!)
Now, before we proceed, let's think about at least putting a manager on the website ourselves. Snag SQLBuddy or phpMyAdmin. Toss either one of these into your webroot subdirectory, such that it is accessible as http://
Fire that up in your browser and try to login with the details you setup and recorded from your webhost.
Aaaaaand now you should be presented with a fairly-empty looking view of your "test" database!
Looking around, it's not very intuitive what you should do with everything that's presented. This is the part where you pull out your database schema to work from.
1) Create Table - name "users"
2) Number of fields "7" (or you may add them one at a time, depends on the client you're using)
3) Fill out the fields to match the schema. Set the Collation as UTF-8. Hmm, perhaps we should have covered this. Basically, put everything as UTF-8 for now; again, depends on the job.
4) For "id", make sure you mark it as "PRIMARY", with option "auto_increment".
When complete, and hopefully no errors, you'll be presented with the structure of the table you just created, listing all the tables. MAGIC.
1) Depending on the client, click "INSERT".
2) Fill out everything with bogus values. This will be our "test" record:
name: Bob Bobkins
Submit it, and we're done! SO EASY. Get used to working with this environment, since it's a no-holds-barred way to work with your database data.