Export picture from mysql database to file

[amazonify]032152599X:left[/amazonify]
As our data getting mobile and copying picture is annoying for me I decide to save the picture in the database (mysql).

Another challenge came when other division need the data exported in a directory with same dimension (135 x 180 pixel).

Structure for table ‘student_pic’  :

CREATE TABLE `student_pic` (
  `image_id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(50) NOT NULL default ”,
  `image_type` varchar(50) NOT NULL default ”,
  `image` longblob NOT NULL,
  `image_size` bigint(20) NOT NULL default ‘0’,
  `image_name` varchar(255) NOT NULL default ”,
  `image_date` datetime NOT NULL default ‘0000-00-00 00:00:00′,
  UNIQUE KEY `image_id` (`image_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3385 ;

Yes, I use mysql 5 ( 5.0.45)

After googling for while and read php manual I use this script to export data from database :

 

$db = mysql_connect(”localhost”, “root”,”alam”) or die(mysql_error());
mysql_select_db(”education”,$db) or die(mysql_error());

$username_student=’wahyu’;

$sql    = “SELECT * FROM student_pic WHERE username=’$username_student’”;
$result = mysql_query ($sql);
if (mysql_num_rows ($result)>0) {
$row = mysql_fetch_array ($result);
$image_type = $row[”image_type”];
$image = $row[”image”];

Header (”Content-type: $image_type”);
$file = fopen(”c:/appserv/www/$username_student.jpg”,”w”);
fwrite($file, $image);

}

explanation of this script :

$db = mysql_connect(”localhost”, “root”,”alam”) or die(mysql_error());

connect to database, its host = localhost, user = root and password = alam

mysql_select_db(”education”,$db) or die(mysql_error());

select database to connect, in this case connect to ‘education’

$username_student=’wahyu’;

pick sample user before looping for 3385 entry icon smile Export picture from mysql database to file

$sql    = “SELECT * FROM student_pic WHERE username=’$username_student’”;
$result = mysql_query ($sql);

take data from table student_pic  for user ( in this case I use wahyu)

if (mysql_num_rows ($result)>0) {

action …..

}

if query return a result then script executed.

$row = mysql_fetch_array ($result);

fetch data with mysql_fetch_array

 

$image_type = $row[”image_type”];

put variable $image_type for Header purpose

$image = $row[”image”];

 

put variable $image for gettting blob data in table

Header (”Content-type: $image_type”);

Header, tell browser about data type.

$file = fopen(”c:/appserv/www/$username_student.jpg”,”w”);

open file handler, since I use appserv I use c:/appserv/www as absolute path

fwrite($file, $image);

save the file.

Thic snippet will export picture from database to a file in directory www. Not resizing the dimension.

I already try to resize it with php but I still not satisfied with the result. Thats why I use software to do that job icon smile Export picture from mysql database to file

I use image resizer software ImRe 2.1 from Vicky Software.

  Export picture from mysql database to file

Yes, as you see.  This software use simple gui and it’s freeware too icon smile Export picture from mysql database to file

Hope this help someone that looking for way to export data from mysql database to a file and resize the pictures.

5 Comments

  1. I am looking for full script to store pics in mySQL database with other 5-10 information fields and to display it back. Photo gallery scripts do not give me
    additional fields

  2. I want to fetch data(longblob) from database and save in file with image with related id, so can you please provide any help or suggestion.
    I am developer and previous developer save images in data base and I want all images with related id from database, so please help me.

Comments are closed.