8.4. Storing and Retrieving Images from the Database
If you need to, you can store the image itself in the database as a BLOB column. However, it's much more efficient to save the path to the image in the database instead. This means you need to do three things to save an image:
Add an image column to the entries table
Modify update.inc.php to save the image path along with the rest of the entry
Modify retrieveEntries() to select the new image column
8.4.1. Modifying the entries Table
Your next step is to add the image column to your entries table. You do this the same way that you added all the other columns to your table.
Navigate to http://localhost/phpmyadmin, open the simple_blog database, select the entries table, and open the SQL tab. Insert the following command to add the image column:
ALTER TABLE entries ADD image VARCHAR(150) DEFAULT NULL AFTER title
This creates an image column after the title column, which stores a 150-character string and defaults to NULL if no value is supplied.
8.4.2. Modifying update.inc.php to Save Images
Now that your entries table can store the image path, it's time to modify update.inc.php to save the image path.
You've already done everything necessary to make the image path available. All you need to do is remove the sections of code that output the image and exit the script.
After the code no longer outputs image data, you need to modify your queries to include the image path. You do this for both new entries and updated entries.
You can save the image path in the database by modifying update.inc.php to reflect the changes shown in bold:
<?php
// Include the functions so you can create a URL
include_once 'functions.inc.php';
// Include the image handling class
include_once 'images.inc.php';
if($_SERVER['REQUEST_METHOD']=='POST'
&& $_POST['submit']=='Save Entry'
&& !empty($_POST['page'])
&& !empty($_POST['title'])
&& !empty($_POST['entry'])){
// Create a URL to save in the database
$url = makeUrl($_POST['title']);
if(isset($_FILES['image']['tmp_name']))
{
try
{
// Instantiate the class and set a save dir
$img = new ImageHandler("/simple_blog/images/");
// Process the uploaded image and save the returned path
$img_path = $img->processUploadedImage($_FILES['image']);
}
catch(Exception $e)
{
// If an error occurred, output your custom error message
die($e->getMessage());
}
}
else
{
// Avoids a notice if no image was uploaded
$img_path = NULL;
}
// Include database credentials and connect to the database
include_once 'db.inc.php';
$db = new PDO(DB_INFO, DB_USER, DB_PASS);
// Edit an existing entry
if(!empty($_POST['id']))
{
$sql = "UPDATE entries
SET title=?, image=?, entry=?, url=?
WHERE id=?
LIMIT 1";
$stmt = $db->prepare($sql);$stmt->execute(
array(
$_POST['title'],
$img_path,
$_POST['entry'],
$url,
$_POST['id']
)
);
$stmt->closeCursor();
}
// Create a new entry
else
{
// Save the entry into the database
$sql = "INSERT INTO entries (page, title, image, entry, url)
VALUES (?, ?, ?, ?, ?)";
$stmt = $db->prepare($sql);
$stmt->execute(
array(
$_POST['page'],
$_POST['title'],
$img_path,
$_POST['entry'],
$url
)
);
$stmt->closeCursor();
}
// Sanitize the page information for use in the success URL
$page = htmlentities(strip_tags($_POST['page']));
// Send the user to the new entry
header('Location: /simple_blog/'.$page.'/'.$url);
exit;
}else
{
header('Location: ../'),
exit;
}
?>At this point, you're ready to create a new entry with an image. Navigate to your admin form in a browser and create an entry with the following information:
Title: Entry with an image
Body: This entry is created with an accompanying image
Add an image, then click Save Entry. If you look in the database, you should see that an image path has been saved in the image column.
Now you need to retrieve the path from the database to display uploaded images with the entry.
8.4.3. Modifying retrieveEntries() to Retrieve Images
Your first step in displaying saved images is to add the image column to the array returned from retrieveEntries(). This is easy to do; it requires only that you add the column name to the SQL query.
Modify retrieveEntries() in functions.inc.php to reflect the changes shown in bold:
function retrieveEntries($db, $page, $url=NULL)
{
/*
* If an entry URL was supplied, load the associated entry
*/
if(isset($url))
{
$sql = "SELECT id, page, title, image, entry
FROM entries
WHERE url=?
LIMIT 1";
$stmt = $db->prepare($sql);
$stmt->execute(array($url));
// Save the returned entry array
$e = $stmt->fetch();
// Set the fulldisp flag for a single entry
$fulldisp = 1;
}/*
* If no entry ID was supplied, load all entry titles for the page
*/
else
{
$sql = "SELECT id, page, title, image, entry, url
FROM entries
WHERE page=?
ORDER BY created DESC";
$stmt = $db->prepare($sql);
$stmt->execute(array($page));
$e = NULL; // Declare the variable to avoid errors
// Loop through returned results and store as an array
while($row = $stmt->fetch()) {
if($page=='blog')
{
$e[] = $row;
$fulldisp = 0;
}
else
{
$e = $row;
$fulldisp = 1;
}
}
/*
* If no entries were returned, display a default
* message and set the fulldisp flag to display a
* single entry
*/
if(!is_array($e))
{
$fulldisp = 1;
$e = array(
'title' => 'No Entries Yet',
'entry' => 'This page does not have an entry yet!'
);
}
}// Add the $fulldisp flag to the end of the array
array_push($e, $fulldisp);
return $e;
}Now you can access the value stored in the image column, just as you access all the other information in an entry. Next, you need to use this information to display the image for the world to see.
8.4.4. Modifying index.php to Display Images
Unlike text entries, images require a little bit of special treatment when you retrieve them from the database. You can't simply check whether the value is set, then output it; instead, you need to create some extra HTML markup to display images properly.
For the sake of keeping your code clean, you need to write a function that checks whether an image exists and return the appropriate HTML markup if it does.
8.4.4.1. Adding a Function to Format Images for Output
You add this function to functions.inc.php. The function accepts two arguments: the path to the image and the title of the entry (you use this as an alt attribute).
Its functionality is simple: if an image path is supplied, return valid HTML markup; if not, return NULL.
Open functions.inc.php and add the new function after the existing functions:
function formatImage($img=NULL, $alt=NULL)
{
if(isset($img))
{
return '<img src="'.$img.'" alt="'.$alt.'" />';
}
else
{
return NULL;
}
}With your new function in place, you can add your image to the displayed entry. For the sake of organization, you display images only with a fully displayed entry.
Your image handling is taken care of in an external function, so you can add your image to the display with just two lines of code. In index.php, where the block of code that handles full display is located, modify the script to contain the lines in bold:
<?php
// If the full display flag is set, show the entry
if($fulldisp==1)
{
// Get the URL if one wasn't passed
$url = (isset($url)) ? $url : $e['url'];
// Build the admin links
$admin = adminLinks($page, $url);
// Format the image if one exists
$img = formatImage($e['image'], $e['title']);
?>
<h2> <?php echo $e['title'] ?> </h2>
<p> <?php echo $img, $e['entry'] ?> </p>
<p>
<?php echo $admin['edit'] ?>
<?php if($page=='blog') echo $admin['delete'] ?>
</p>
<?php if($page=='blog'): ?>
<p class="backlink">
<a href="./">Back to Latest Entries</a>
</p>
<?php endif; ?>If you navigate to http://localhost/simple_blog/ and select your new entry, "Entry with an Image"; you should see the image displayed with your text (see Figure 8-7).
Unfortunately, the layout doesn't look very good if a large image is displayed (as shown in Figure 8-7). You don't want to resize every image uploaded manually, so you need to add new functionality to your ImageHandler class that resizes large images automatically.
Figure 8.7. Your image displayed with the accompanying text
