The MySQL Load Image command requires the FILE privilege. Since our servers are shared, MySQL users do not have this enabled. (If it was enabled, it would allow any MySQL user to read private files from your home directory.)
If you need to store image data within your MySQL database you can use a script to import (and export) the images. Creating and running scripts requires some programming knowledge and experience. We have included the outline for a basic PHP script which you could run from the command line on our servers by typing:
php script_name.php
Before running the script, you need a table for storing the image data in your database. The following SQL command can be used to create a suitable image table. You can run it in phpMyAdmin or on the MySQL command line:
CREATE TABLE images (image_id INTEGER NOT NULL,
image_data BLOB
)
TYPE=INNODB;
Then you can copy the code below into a PHP script. Before running this script you will need to set the correct script permissions and edit the code. In particular, you need to change the paths, database details, and image names. Also, if you don't have a table called images and intend to store it elsewhere, then you will need to edit the SQL statements as well.
<?php
# edit these values
$mysql_database = 'your MySQL database/username';
$mysql_username = $mysql_database; # always the same as the database name
$mysql_password = 'your MySQL password';
$image_to_import = '/home/ex/example.com/html/image.gif'
$new_exported_image = '/home/ex/example.com/html/new_image.gif';
# connect to the MySQL database
$link = mysql_connect('localhost', $mysql_username, $mysql_password)
or die('Could not connect: ' . mysql_error());
echo "Connected successfully\n";
mysql_select_db($mysql_database) or die("Could not select database $mysql_database\n");
# add the image to the MySQL database
$handle = fopen($image_to_import, 'r');
$file_content = fread($handle, filesize($image_to_import));
fclose($handle);
$encoded = chunk_split(base64_encode($file_content));
$query = "INSERT INTO images (image_id, image_data) VALUES('1','$encoded')";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
echo "Image has been inserted successfully\n";
# now read the image out of the database and save it to a new file
$query = 'SELECT image_data FROM images WHERE image_id= 1';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
# fetch the first row, (there is only one row since we selected on a single image_id)
$result_data_array = mysql_fetch_array($result,MYSQL_BOTH);
$file_data = $result_data_array[ 0 ];
$decoded_file_data = base64_decode($file_data);
# create a new (identical) image file
$new_handle = fopen($new_exported_image, 'w') or die("Can't create file");
fwrite($new_handle, $decoded_file_data);
echo "Image data has been read from the database and a new file created\n";
fclose($new_handle);
# close the database connection
mysql_close($link);
?>