The Bare Minimums

This is the first part of series of entries where I document the process that I’ve taken to make this blog from scratch.

I’ve decided to start blogging, and I needed a platform to do so. I could use a number of pre-made platforms, but I’ve decided to go it on my own and do the whole thing by hand.

The Database

I started out with a very simple table in my blog_database:

# Name Type Extra
1 id int(11) auto_increment
2 date datetime
3 title char(255)
3 content text

I have id as my primary key for the table which auto increments when I add a new entry, date as the timestamp for when a post is published, title as the title of for the post, and content to store the contents of the post (in raw HTML for the time being). I also created two users for the database: a blog_view user that only has permission run SELECT queries against the database, and a blog_adm user that has permission to run ALTER, DELETE, INSERT, etc. against the database.

Connecting to the Database

With my minimal database in place, I started putting in the necessary PHP to connect to and query my SQL database:

// variables for SQL connection
$host = 'localhost';
$dbname = 'blog\_db';
$user = 'blog\_view';
$pass = 'P@ssw0rd!';  // not the real password :)
$db;

// try to connect to the database
try
{
  $db = new PDO('mysql:host='.$host.';dbname='.$dbname, $user, $pass);
}
// catch if we can't connect
catch (PDOException $e)
{
  // get the error message
  $errorMessage = $e->getMessage();

  // echo out the error message
  echo $errorMessage;

  // die (not user friendly for now)
  die();
}

echo 'Connection worked!';

I uploaded to my test page and when I saw “Connection worked!” on my page, I knew that my connection to the database was good to go.

Querying the Database

Once I was able to ensure that I could connect to the database using my PDO, I moved on to pulling rows from the database:

// variable for storing returned results from SQL query
$results;

// try to query the database and get results
try
{
  $query = "SELECT \*
            FROM content
            ORDER BY id DESC";

  $statement = $db->prepare($query);
  $statement->execute();

  $results = $statement->fetchAll();

  $statement->closeCursor();
}
// catch if our query goes wrong
catch (PDOException $e)
{
  // get the error message
  $errorMessage = $e->getMessage();

  // echo out the error message
  echo $errorMessage;

  // die (not user friendly for now)
  die();
}

// dump the results to see if it worked
echo 'Results from query:<br>';
var\_dump($results);

Looping Through the Posts

Now that I knew that I could query the database without any issues, I looped through my returned results and echoed out each post’s date, title, and content:

<?php
// for each row returned
foreach ($results as $row)
{
?>
  <!-- output the divs for the blog content -->
  <div class="blog-post">
    <div class="blog-date-col">

      <!-- echo out the date field for the row -->
      <h4 class="blog-date">
        <?php echo date("d M Y", strtotime($row\["date"\])); ?>
      </h4>
    </div>

    <div class="blog-content-col">

      <!-- echo out the title field of the row -->
      <h2 class="blog-title">
        <?php echo $row\["title"\]; ?>
      </h2>

      <!-- echo out the content field of the row -->
        <?php echo $row\["content"\]; ?>
      </div>
    </div>
  </div>
<?php
}
?>

Up and Running

All said and done, I had my initial and simplistic blog up and running. I just had to remove some of my test code (i.e. echo 'Connection worked!';), etc. There’s still a lot of work to do, but it’s a good start.

Here’s a basic outline of what it looks like now:

<?php
// variables for header.php
$html\_title = 'Jim McKenna: Blog';
$menu\_selected = 'blog';
$title = 'Blog';

// variables for SQL connection
$host = 'localhost';
$dbname = 'blog\_db';
$user = 'blog\_view';
$pass = 'P@ssw0rd!';  // not the real password :)
$db;

// variable for storing returned results from SQL query
$results;

// try to connect to the database
try
{
  $db = new PDO('mysql:host='.$host.';dbname='.$dbname, $user, $pass);
}
// catch if we can't connect
catch (PDOException $e)
{
  // get the error message
  $errorMessage = $e->getMessage();

  // echo out the error message
  echo $errorMessage;

  // die (not user friendly for now)
  die();
}

// try to query the database and get results
try
{
  $query = "SELECT \*
            FROM content
            ORDER BY id DESC";

  $statement = $db->prepare($query);
  $statement->execute();

  $results = $statement->fetchAll();

  $statement->closeCursor();
}
// catch if our query goes wrong
catch (PDOException $e)
{
  // get the error message
  $errorMessage = $e->getMessage();

  // echo out the error message
  echo $errorMessage;

  // die (not user friendly for now)
  die();
}

// include the beginning html produced by header.php
include('inc/header.php');

// print the posts to the page

// for each row returned
foreach ($results as $row)
{
?>
  <!-- output the divs for the blog content -->
  <div class="blog-post">
    <div class="blog-date-col">

      <!-- echo out the date field for the row -->
      <h4 class="blog-date">
        <?php echo date("d M Y", strtotime($row\["date"\])); ?>
      </h4>
    </div>

    <div class="blog-content-col">

      <!-- echo out the title field of the row -->
      <h2 class="blog-title">
        <?php echo $row\["title"\]; ?>
      </h2>

      <!-- echo out the content field of the row -->
        <?php echo $row\["content"\]; ?>
      </div>
    </div>
  </div>
<?php
}

include('inc/footer.php');
?>