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

$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

I use image resizer software ImRe 2.1 from Vicky Software.

Yes, as you see.  This software use simple gui and it’s freeware too

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