Posted 05.25.2011

Building a Basic Blog in Code Igniter (Part 3)

A while back, I started a series on CodeIgniter.

Even though it’s been 4 months since I last posted in this series (oops!), these posts have quickly turned into my post popular posts. I figure, give the people what they want. Right?

Setting up the database

We’ll need to set up the database.

NOTE: I’ve written a separate post on getting MAMP set up on Mac.

First things first, let’s talk a little bit about database strategy.

The most important rule about creating databases is making sure they run efficiently. That plays out in 2 ways: (1) making sure there’s no duplicate information and (2) being able to access all the information in your database. What good is it to put information in, if you can’t get it out?!

So, how does this play out?

I have 1 database per site, but, a database can have multiple tables. A table can contain any kind of information, from blog posts to comments. Here’s an example of a table (think in terms of excel grids):

The important thing to note: in this table each row has a unique id. It’s a perfect way to get the information out that we need. I just ask the database:

“What’s the title of the blog post with the ID of 2?”
“About”
“Perfect!”

Have you ever been to a WordPress site and noticed the URL? http://www.mygreatsite.com/?=p=50. That p=50 on the end of the URL is one of the ways you can ask for the ID. If you change the number in the URL, it should serve a different blog post with the corresponding ID.

Onward and upward.

There are several different types of relationships that happen within a database.

You may have a table for blog posts, a table for comments, and a table for categories. You get the idea. Well, how do they all relate to each other? There are a couple different options:

One to One Relationship

This relationship works the same way the name suggests. You have one entry in one table that relates to one entry in another table. Let’s pretend we have a list of users in one table and a list of jobs (admin, editor, writer, or user) in another table. Each person in the database can only have 1 job.

 

We could add an extra column in the users table to specify what their role is, but that’s not very efficient. What is I accidentally misspelled something? or what about Admin (capital A) verses admin (lowercase a)? Or what if in the future, I wanted to add additional information about those roles to my database? A description of that job? Pages they have access to? Well, then, adding an additional column is definitely not efficient then.

Instead, I add a column that references the ID in the other table.

 

Ahh, much better!

Many to Many Relationship

Again, the name tells you all you need to know. Many things related to many things. (so profound)

An example, please. We have a table of blog posts and they’re all related to tags. A post can have 1 tag or it can have 50.

 

So, how does work? We can’t add a column to the blog posts table. We don’t know how many tags each post has. Putting a comma deliminated list in a table cell doesn’t seem to be very efficient. Trying to read the list would require quite a bit of extra code. Same thing with the tags table. We don’t know how many posts will be associated with a given tag. What’s a developer to do?

Well, you can add a relationship table. The sole purpose of that table is to you how 2 tables relate to each other. In this case, the table will tell you the ID of the blog post and the ID of the related tag.

 

Not too shabby.

The Tables for Our site

When I’m planning out a database for a site, I’ll just list out all the information that I need to keep track of and then I’ll list all the elements and figure out how they relate.

Here is all the different types of information I’ll need. These will eventually turn into my database tables:

  • Blog Posts
  • Comments
  • Authors
  • Categories

OK, now all the elements that relate to this type of information:

 

Pretty easy. Now, how do they all relate? Well, we need to know which blog post the comment is related to. So, I’ll add a “Blog Post ID” to the list. I need to know which author wrote a given post, so I’ll add “Author ID” to the blog post table. I also need to know what category the blog post is listed under (each post can only have one category), so I’ll add “Category ID” to the blog post table as well. Here’s my final structure:

 

A few things to know about naming

You can’t have any spaces and with the exception of hyphens and underscores, I try and leave symbols out of the picture.

When I’m naming my databases, I try and include a suffix that tells me what kind of database it is. In this case, we’re running Code Igniter, so I’ll name my database awesome_ci. But, if it was a WordPress site, I’d call it awesome_wp, Expression Engine awesome_ee, you get the idea. Why do I do this? Well, I mess with enough platforms, that (1) it’s helpful to tell at a glance and (2) sometimes I’ll actually experiment with multiple platforms for a given site. So I could easily have an awesome_wp and an awesome_ci. This helps me keep everything straight, but again, this is personal preference.

When I’m naming my tables, I try and take cues from CakePHP.

  • They use plural names for all table names and singular names for all column names. Instead of calling a table person, it becomes people.
  • Relationship tables list the two tables that are being related in alphabetical order. Going back to our tags example from earlier: posts_tags. At a glance, I know which tables contain information and which tables are relationship tables. Again, this goes back to personal preference, but it has also help me develop personal standards.
  • With the exception of underscores and hyphens, table names and rows don’t contain symbols

Going back to our list, here’s how I’m going to name everything:

 

Enough Talking, Let’s Do!

My personal preference is Navicat. But, phpMyAdmin and SequelPro all do the same thing.

In Navicat, right click on the connection and select New Database.

Name your database.

With you database selected and open (in Navicat the icon will turn green next to the database name), you can start adding tables.

It’s pretty straightforward. But, there are a few things that might cause some hiccups along the way.

Every table column must have a type. There are several different types to choose from. For the most part, I stick to 4 different types:

  • int – this stands for integer. It can only be a whole number.
  • varchar – this is essentially a string. It can contain letters, numbers, and symbols. It’s going to be a lot shorter than a blob.
  • blob – this is very similar to a varchar, except it can be much longer. This is great for when you have a blog post or comment.
  • timestamp – this is for holding the date. I’ve also used date or time as alternatives, it just depends on what information you need to store. In this instance, I want both date and time, which makes timestamp the perfect solution.

Remember talking about the ID? Well, you need to specify that that’s the key for the table. In Navicat, just select the row, and click on the “Primary Key” heading in the toolbar. You’ll also need to check auto increment (with the ID row still selected). This means that it will automatically increment the value for ID. For example, if the last ID entered was a 6, it knows that the next row is going to be 7.

I checked allow null for the title and body fields. I’m telling the database, it’s OK, if these are left blank, they’ll equal NULL.

Status is kind of weird. You’ll notice we didn’t create a table to hold all the different kinds of statuses we might have. I figured, a post is either on or off. Technically, this isn’t an integer, it’s a boolean. A boolean, can have two values: true or false. In computer language it translates into 0 and 1 (which is where the integer part comes into play). 0 is off/false, 1 is on on/true.

As I side note, have you ever wondered about the symbol on the power switch? —Hey, it’s a 1 and 0!

I try and remember it like this: if it’s off or false, you’ve got nothing, 0.

OK, so just go through our list that we made. Adding tables and rows for the entire database we listed out.

Hopefully this all makes sense. Don’t hesitate to ask questions or add your personal experience in the comments below.

 



Posted 05.24.2011

Turning your Mac into a server

SO…MAMP.

Most of you are probably thinking, “What in the world?”

MAMP stands for Mac, Apache, MySQL, PHP. (Windows can do something similar with WAMP)

NOTE: This post assumes that you at least know what MySQL and PHP are. If not, I wrote a post called Technology Alphabet Soup where I tried to explain a lot of the jargon.

So, what does MAMP do? Well, it let’s you set up MySQL and PHP on your Mac so you can develop sites locally, on your Mac, instead of having to work off the server. This is a more efficient workflow because (1) you don’t have to have an Internet connection to work and (2) you don’t have to wait for files to upload in order to test your work.

Go to mamp.info to download MAMP.

You can download MAMP Pro ($59), which has some great features, but the free version will also get the job done.

Install the program just like you’d install any other Mac software.

Launch the MAMP.app

You can go ahead and launch the demo version of MAMP Pro regardless.

OK, the security warning.

The demo should last for 30 days.

Once you click through and get this screen, everything should be up and running.

Now, if you click on the MySQL tab, you can click on “Launch phpMyAdmin.” — MAMP installs phpMyAdmin by default! Nice!

Notice, it will automatically launch a web browser where you can now create, edit, and delete databases.

Notice the web address: http://localhost:8888/phpMyAdmin/?lang=en-iso-8859-1&language=English Any sites that you create are going to use http://localhost:8888/

So, if you go to your Applications folder > MAMP > htdocs > all your site files will go there.

If you’re following along with the CodeIgniter tutorials, you can create a folder called awesome for your site files.

Now, you can add all your site files that I gave you and you can view them at http://localhost:8888/awesome/

Congrats! You’ve got MAMP up and running. Your Mac works like a server.

…one more thing… when you start working with databases, you’ll need a MySQL password. You can set this up by clicking on the Change MySQL root password button.

From there, it should be pretty straightforward.

 



Posted 05.19.2011

SEO Tips Nobody Ever Told me

I’m trying to be better about SEO (search engine optimization). But, it’s hard work! It takes effort. It’s a little more than copying and pasting the first couple sentences of a post and using it as the page description.

Did you know that 20-25% of your ranking on the major search engines come from what users actually see? Good gravy!

So far, these have been my favorite resources:

Most of the stuff that I read online, all say the same thing:

  • Pay attention to your page titles. You want the most important keywords in the page title and each page needs a unique title.
  • Have a unique META description and META keywords on each page META descriptions are used by Google when they display your page results on their site. META keywords are terms that you think people will use when searching for your site.
  • Build links When someone links to your site that’s the equivalent of a vote: someone saying “I believe this site is important.” You want to build up as many links to your site as possible. That’s why people talk about the importance of commenting on other people’s site and including a lik back to your own site. Not only will people read your comment (and hopefully see value), but you’ll also have another “vote” for your site.
  • Check your H1s (or other H tags for that matter) Heading tags prioritize your information for Google. An H1 should contain the most important thing on any given page. (Typically, that’s going to be the name of your company). The second most important thing (H2) might be the page name or blog post title.
  • Be sure to include sitemap.xml A sitemap.xml is exactly what the file name suggests. It’s a sitemap, written in XML. One of the first posts I wrote for this blog was on SEO an writing a sitemap.xml. In fact, by simply adding a sitemap.xml I was able to jump to #1 on Google (when you searched “Amy Haywood”. Up until that point, Amy Haywood the chemistry teacher had been beating me!) There’s some well written documentation on Google’s site for creating your own.

But, is that all there really is to it?

PageRank

PageRank is the system (0-10) Google uses to determine the quality of your site. How do you figure out where you rank? Well, download the (GoogleToolbar)[http://toolbar.google.com] for your browser. When you visit a site, you’ll see a meter displaying that site’s rank.

If you go to my favorite site, AMYHAYWOOD.com, then you’ll see I rank (unfortunately) a 1/10.

PageRank for AMYHAYWOOD.com

CNN, on the other hand, ranks a 10/10. But, then again, if I’m paying someone millions of dollars to handle my SEO, then I sure hope I’m getting a 10/10.

PageRank for CNN




SO…what are other factors that nobody ever talks about?

Have trophy words

SEO for 2010 talks about having a certain keywords that are do or die. For me, “Amy Haywood” is one of those. When someone Googles my name, I need them to find me instead of Amy Haywood the chemistry teacher, lawyer, or music teacher.

KEYWORD IN YOUR DOMAIN NAME

Bonus points if you have one of your trophy words in your domain name. Even though my blog is about programming and design, I don’t have either of those words in my domain. That’s fine. It just means I won’t rank as high on those terms. As I mentioned earlier, “Amy Haywood” is my trophy word. It’s in my domain and therefore, it should rank higher than even my own LinkedIn page.

KEYWORD IN THE PAGE NAME URL

Search engines like it when you have a keyword in the URL. —That’s an incentive, if you have a WordPress Blog to change your permalink structure from the default (/?p=182) to something related to your post name.

SEO School even talks about rearranging your URL so that keywords appear earlier. For example, if I wrote a post about The Litle Mermaid and my URL was /the-little-mermaid. I might consider /mermaid-little-the. Obviously, “the” is going to be the least important word and “mermaid” the most important.

DOMAIN AGE

How long you’ve owned your domain name. I’ve had AMYHAYWOOD.com since I was 16. After 10 years (that’s F-O-R-E-V-E-R in Internet years), Google assumes that my site has (some) crediblity. It’s been around the block a few times.

HOW LONG YOUR DOMAIN IS REGISTERED FOR

Most of the domain names I own, I’ve only registered for a year with automatic renewal turned on. Personally, I would rather hang on to my money as long as possible. However, Google (and GoDaddy for that matter) would like it better if I was registered for 3 years. There’s some stability there.

ALT AND TITLE ATTRIBUTES

Make sure that you links are utilizing the title attribute and images the alt attribute. Google can read text just fine, images are another story. But, if you have a alt attribute filled out explaining what the image is? brownie points! Same with links and alt attribute.

KEYWORD IN THE FIRST 100 WORDS IN HTML ON THE PAGE

Search engines figure if you have a keyword within the first 100 words in the HTML page, it must be important. The earlier, the better.

BUILD UP LONG TAIL KEYWORDS

A keyword is more like a key phrase than a word. These are words that people may search long term to find exactly what they are looking for. I recently discovered that my most valuable posts have been Code Igniter and jQuery posts where explain how to do very specific tasks. I’m sure that’s direclty related to the long tail keywords I’ve entered.

SUBMITTING YOUR SITE TO SEARCH ENGINES

Google is definitely the most popular search engine. Afterall, it’s turned into a verb! Bing has been trying to gain ground (after being late to the party, they’ve done a decent job). But, there are


Practical Application

Using Google AdWords

There are several services that you can subscribe to that will help you find keywords and whatnot. BUT, the thing I love about Google is they will handle all that for free. Enter Google AdWords’ Keyword Tool. Simply type in the word or phrase you want to use. It will give you similar words and phrases, as well as how much competiton is out there, how many global monthly searches, and how many local montly searches. If competition is high or nobody is searching for that term, you may want to let it rest.

I started looking at keywords I could use for this post. I simply typed in SEO.

The first few don’t apply to me: seo tools, seo software… but, seo tips and seo basics definitely work! SEO tips has medium competition, with 74,000 monthly searches. Sounds like a good target, if you ask me! Now, if I’m smart (which I am), I’ll change my original blog post title from “Things people never told me about SEO” to “SEO tips nobody ever told me.” Now, not only does my keyword appear in my URL, but it will also be in my page title. Sweet!

Plugins

If you’re running on WordPress, download and install All in One SEO Pack. It will help you generate a sitemap, META descriptions, META keywords, and all that jazz.

If you’re running on Expression Engine, like I am, you can download/buy Better Meta Data. It will do the exact same thing. (I’ll be honest, I’d rather hang on to my $40 and do it myself. Check out here and here.)