• Choose a category
• All
• Classic-Fiction

Deletes row 3 from table

Please note that if exe_sql() is fetching data from the database, it will always return an array of data. If the query returns multiple rows of data, you'll get a multidimensional array. Otherwise, a single-dimensional array is returned.

Storing Images in a Database

It is usually better to store images in a file structure and then refer to the paths of the images in the database, but occasionally you may find the need to store images as blobs, or large unstructured pieces of data, directly in a database. These occasions may arise when you don't have the requisite system permissions to create a file. For example, many web administrators do not allow their webservers to create files, as a security measure. To store an image in a database, set the typecasting or variable type for the image to blob or large blob and insert the data, as shown in Listing 6-4.

$data_array['IMAGE_ID'] = 6;

$data_array['IMAGE'] = base64_encode(file_get_contents($file_path));

insert(DATABASE, $table, $data_array);

Listing 6-4: Storing an image directly in a database record

When you store a binary file, like an image, in a database, you should base64-encode the data first. Since the database assumes text or numeric data, this precaution ensures that no bit combinations will cause internal errors in the database. If you don't do this, you take the risk that some odd bit combination in the image will be interpreted as an unintended database command or special character.

Since images are—or should be—base64 encoded, you need to decode the images before you can reuse them. The script in Listing 6-5 shows how to display an image stored in a database record.

Listing 6-5: HTML that displays an image stored in a database

Listing 6-6 shows the code to extract, decode, and present the image.

# Get needed database library

include("LIB_mysql.php");

# Convert the variable on the URL to a new variable

$image_id=$_GET['img_id'];

# Get the base64-encoded image from the database

$sql = "select IMAGE from table where IMAGE_ID='".$image_id."'";

list($img) = exe_sql (DATABASE, $sql);

# Decode the image and send it as a file to the requester

header("Content-type: image/jpeg");

echo base64_decode($img);

exit;

?>

Listing 6-6: Script to query, decode, and create an image from an image record in a database

When an image tag is used in this fashion, the image src attribute is actually a function that pulls the image from the database before is sends it to the waiting web agent. This function knows which image to send because it is referenced in the query of the src attribute. In this case, that record is img_id, which corresponds with the table column IMAGE_ID. The program show_image.php actually creates a new image file each time it is executed.

Database or File?

Your decision to store information in a database or as files in a directory structure is largely dependent on your application, but because of the advantages that SQL brings to data storage, I often use databases. The one common exception to this rule is images files, which (as previously mentioned) are usually more efficiently stored as files in a directory. Nevertheless, when files are stored in local directories, it is often convenient to identify the physical address of the file you saved in a database.

* * *

[20] Projects always expand in scope.

[21] More information about MySQL is available at http://www.mysql.com and http://www.php.net.

Making Data Smaller

Now that you know how to store data, you'll want to efficiently store the data in ways that reduce the amount of disk spaced required, while facilitating easy retrieval and manipulation of that data. The following section explores methods for reducing the size of the data your webbots collect in these ways:

Storing references to data

Compressing data

Removing unneeded formatting

Thumbnailing or creating smaller representations