[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 🙂

$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.