Logic’s Last Stand

May 25, 2008

Beginner’s Guide to a Real Web-site – Part 6 – Intro to SQL

Filed under: Computers — Tags: , , , , , , — Zurahn @ 2:46 pm

Database interaction is in most cases dealt with using a language called SQL (Structured Query Language). For our purposes, we only need to know the basics, which are:
-Inserting data
-Updating data
-Deleting data
-Retrieving data

These are conveniently done using the keywords INSERT, UPDATE, DELETE and SELECT. However, before we can get to that, we need to understand what we’re storing and how to store it. We know that we simply want to store blog posts; these will have titles, dates, and a body. We can do this with a single table. A table is what we call what we store data in, because we use “columns” of data.

Our table, which we’ll call “blogs” will have these columns:
-id
-title
-date
-body

We can do this either in SQL or in the handy PHP tool phpMyAdmin which is included with WAMP (click the WAMP icon on the taskbar, and choose phpMyAdmin). PHP web hosts will typically have phpMyAdmin installed for your use. In our code we need to use SQL to interact with the database, but setting up the structure we can, and for simplicity’s sake in this tutorial, do it in phpMyAdmin.

Firstly, we create a new database, which we can call “mysite” and click the Create button. This will create the database, which will store the table. Now you’ll see at the bottom “Create new table on database mysite”. Enter “blogs” for the name, and we need 4 columns (id, title, date, body).

On the next screen, “Field” is the name of the column (id, title, date, body). For the data type dropdowns, id will be INT, title will be VARCHAR, date will be a TIMESTAMP (not DATETIME), and body will be TEXT.

INT is short for Integer (a number with no decimal points and can be positive or negative). IDs should be Integer values. Set the length to 11, EXTRA to auto_increment (meaning it automatically assigns a unique value to it when you INSERT), and choose the first radio button under the page with the key to set it as the Primary Key (the value that uniquely identifies a record — in our case a blog post — in the table).

For title, VARCHAR is a text field with a set length. We’ll set the length to 50 (seems reasonable for a maximum title length).

For date, TIMESTAMP is the number of seconds having passed since December 31, 1969. Timestamps are easily converted to date formats in PHP. Change default to ON UPDATE CURRENT_TIMESTAMP; this just means to insert the current time by default.

And for body, TEXT is a text field with no specified maximum length and is only limited by the database software itself. This will allow for blogs without having to worry about one being too long.

Click Save to create the table.

If you wanted to do this in SQL, here is the code, though I won’t go through it to explain.

CREATE TABLE mysite.blogs (
id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(50) NOT NULL,
date TIMESTAMP NOT NULL,
body TEXT NOT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB 

For the sake of building our SQL skills before jumping into PHP usage, click the SQL tab in phpMyAdmin to get a box where we can enter SQL queries. This way we can test our queries and see how they work. Here’s a query to insert a record into our table

INSERT INTO blogs (title, body) VALUES('A Blog Post', 'This is where a blog post would be');

Selecting all records from a table:

SELECT * FROM blogs;

Select specific records and columns from a table:

SELECT title FROM blogs WHERE id = '1';

We can also update the record:

UPDATE blogs SET title = 'My Blog Post' WHERE id = '1';

Or, finally, delete the record:

DELETE FROM blogs WHERE id = '1';

This is basically all you’ll need for the development of our site. We can use SQL statements directly in PHP to modify the database, which we’ll do in Part 7.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: