In my application , am storing images files as blob in database. I considered letting php to process multiple image upload.
The script seems to work great but I realize some images files could not be stored in database so I change the type of table image column from blob to longblob and this time some of those photos which couldn't be uploaded are now uploaded correctly.
again, I realized still some image files could still not be stored in database. I have spent time to read about blob data type and its just the length it varies.
after a long debugging what was wrong I got this message from server:
The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.
Please what could be the problem in my script or database?
below is my PHP code and Table structure:
--
-- Table structure for table `images`
--
CREATE TABLE `images` (
`image_id` int(11) NOT NULL,
`image` longblob NOT NULL,
`user_id` int(11) DEFAULT NULL,
`post_id` int(11) DEFAULT NULL,
`mime_type` varchar(32) DEFAULT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
<?php
if(isset($_FILES['file']['name'])){
//Handle file upload and status as files description
//lenght of files
$files = array();
//dataArray
$dataArray = array();
//loop through global FILES variable
foreach($_FILES['file']['name'] as $key => $value){
$files[] = $value;
$filename = $_FILES['file']['name'][$key];
$filesize = $_FILES['file']['size'][$key];
$filetemp = $_FILES['file']['tmp_name'][$key];
$fileerror = $_FILES['file']['error'][$key];
$file_ext = strtolower(end(explode('.',$filename)));
$valid_ext = array('jpg','png','jpeg','gif');
$maxsize = 4 *1024 *1024;
//get file MIME type
$valid_mime = array('image/jpeg','image/png','image/jpg','image/gif');
$mime = getimagesize($filetemp);
$mime = $mime['mime'];
if($filesize > $maxsize || !in_array($file_ext,$valid_ext) || !in_array($mime,$valid_mime) ){
//tell the user we can't upload files
$template = "<div class='overlay'></div>
<div class='er_cnt'>
<span class='er_cnt_span'>Can't Read Files</span>
<p> Your photo: <strong>$filename</strong> is not valid. Photos should be less than 4 MB and saved as JPG, PNG or GIF files. </p>
<br><hr><button class='close'> Close </button>
</div>";
echo $template;
exit();
}else{
//*********Everything is okay so continue*******//
//resize image...
//read entire file into a string
$data = file_get_contents($filetemp);
//append data to the dataArray variable
$dataArray[] = $data ;
}
} //end of first foreach
//store image in database after foreach loop
if(count($files) == count($dataArray) ){
//get the length of dataArray
$lenght = count($dataArray);
//define array to store image id
$fileID = array();
//grab the status coming from text area (use as file description)
//first insert post description into database
$status = trim($_POST['status']);
$status = htmlspecialchars($status);
//prepare and bind statement
$sql = $dbc_conn->prepare("INSERT INTO
$public_feed_table(user_id,post,timepost,file) VALUES(?,?,?,?)");
$sql->bind_param('issi',$IsLoggIn,$status,$time_post, $image=1);
//execute
$sql->execute();
//get the last insert id
$post_id = $sql->insert_id;
//loop through the dataArray
for($i=0; $i < $lenght; $i++){
//grab data from the array
$file_content = mysqli_real_escape_string ($dbc_conn,$dataArray[$i]);
$mime = getimagesizefromstring($dataArray[$i]);
$mime = $mime['mime'];
//save file into database (images table)
$sql = "INSERT INTO $images_table(image,user_id,post_id,mime_type)
VALUES('$file_content','$IsLoggIn','$post_id','$mime')";
$query = mysqli_query($dbc_conn,$sql);
//append file id
$fileID[] = mysqli_insert_id($dbc_conn);
}
}//end of array equallity
//store data for use of JSON
$username = getuser($IsLoggIn,'username');
$post = $status;
$datapost = $date_post;
$total_rating = '0';
$rating_msg = 'Be the first to rate your tagline';
$post_id = $post_id;
$post_owner = $IsLoggIn;
$name = ucfirst(getuser($IsLoggIn,'firstname'))." ".ucfirst(getuser($IsLoggIn,'lastname'));
$rate_button = true;
$voters_array = array();
$account_dir = getuser($IsLoggIn,'directory');
$avatar = getuser($IsLoggIn,'avatar');
$file = 1;
$pp_id = fetch_pp($IsLoggIn);
//send JSON as server response
$ajax_data = array(
"u" => $username,
"uid" => $IsLoggIn,
"date" => $date_post,
"pid" => ''.$post_id.'',
"n" => $name,
"f" => $file,
"ppid" => $pp_id,
"imageLength" => $lenght,
"fid" => implode(",",$fileID)
);
// retruring data as JSON encode then we use client side to process the data
echo json_encode($ajax_data);
}
