Php Mysql blog tutorial, creating a simple and secured blog

On today's tutorial we will talk about creating a blog on php and mysql

Table of contest:
1. Connecting to mysql database
2. Creating mysql tables
3. Coding the blog

              Download

Connecting to mysql database is simple, in just few lines of code

Before we start i suggest you read Php Mysql posting to database for better understanding of mysql.

<?php
// connecting to host
mysql_connect("host", "user", "password"); // replace this with your data
mysql_select_db("your_database"); // database name here
?>

Creating database, tables and columns
Now let's build the mysql table.
First build a database if you haven't, and dont forget to edit the connection details at the top.
Click on databases

Set a name to it and click create.

Now we must build the tables:

Click 'go', a new table will popup, complete those fields like in the pocture below:

Scroll down and click save.

Last one, click on posts and insert tab at top, write something to title, and id live empty.

Now lets continue coding the blog

<?php
// connecting to host
mysql_connect("host", "user", "password"); // replace this with your data
mysql_select_db("your_database"); // database name here

// lets build a blog
if(isset($_GET['post'])){
$post = $_GET['post'];

// first of all we will check string length
if(strlen($post) > 11){ // if post id is bigger than 11 charachters
die('Blog post nof found.');
}
// now we will make sure that the post id is numeric and this is a nice security method
if(is_numeric($post)){ // is numeric allows numbers only
$post = (int)$post; // and the int function, which replace every
// string to its correspoing number

// for the tutorial im gonna add mysql_real_escape_string
// but is not really needed in this case
$post = mysql_real_escape_string($post); // final sqli defense

// final part
$query = mysql_query("SELECT title FROM posts WHERE id=$post LIMIT 1");
while($row = mysql_fetch_array($query)){
echo $row['title'];
}

}
else{ // if post is not numeric then
die('Blog post nof found.'); // post does not exist
}

}
else{
// if post is not submitted display them all
$query = mysql_query("SELECT title, id FROM posts");
while($row = mysql_fetch_array($query)){
echo $row['title'];
echo $row['id'];
echo '<a href="?post='.$id.'">'.$title.'</a><br>';
}
}
?>Few last tips!

I used LIMIT 1 on the first mysql query.
That's done for 2 main pruposes:
1. we are only displaying 1 result
2. it's a nice speed optinization for mysql
To display blog posts by newest ones replace


SELECT title, id FROM posts
with
SELECT title, id FROM posts ORDER BY id DESC
It's a simple blog who does the work.
If you want to learn more about sql injection we suggest Sql Injection prevention tutorial