+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: uploading an image (BLOB) to mysql (/showthread.php?tid=78861)
uploading an image (BLOB) to mysql - richb201 - 03-19-2021
I have an image on my drive that a user has already uploaded. I need to copy that to a mysql rds table field that is a medium BLOB. How do I accomplish this in CI3? After doing this I need to delete the image from the hard drive.
RE: uploading an image (BLOB) to mysql - InsiteFX - 03-19-2021
MySQL BLOB using PHP
RE: uploading an image (BLOB) to mysql - richb201 - 03-20-2021
(03-19-2021, 11:26 AM)InsiteFX Wrote: MySQL BLOB using PHPThanks.
I am now able to convert the .png into a string. But when i try to insert it into the table I am getting:
ERROR - 2021-03-20 09:42:07 --> Query error: Invalid utf8 character string: '\x89PNG' - Invalid query: INSERT INTO word.image_BLOB VALUES(�PNG
The actual code is:
$sql = "INSERT INTO $_SESSION[table].image_BLOB VALUES($imgData)";
$iRc=$this->db->query($sql);
I also tried
$sql = "INSERT INTO $_SESSION[table].image_BLOB VALUES({$imgData})";
But with the same result. Any ideas of what to do?
It seems to insert but I really can't tell if it is getting placed correctly into the table since I am a neophyte with sqlWorkbench.
RE: uploading an image (BLOB) to mysql - InsiteFX - 03-20-2021
Because you are inserting it wrong.
Look at this code.
PHP Code:
$products[] = [
'product_name' => 'MySQL DATABASES',
'price' => 15,
'product_image' => file_get_contents("https://i.imgur.com/UYcHkKD.png" )
];
You assign the contents to the image not png.
RE: uploading an image (BLOB) to mysql - richb201 - 03-20-2021
(03-20-2021, 11:34 AM)InsiteFX Wrote: Because you are inserting it wrong.
Look at this code.
PHP Code:$products[] = [
'product_name' => 'MySQL DATABASES',
'price' => 15,
'product_image' => file_get_contents("https://i.imgur.com/UYcHkKD.png" )
];
You assign the contents to the image not png.
This is being called after the record has been inserted, and that is why it is update rather than insert.
public function upload_image3($post_array,$primary_key)
{
$szFile=str_replace("/files/","/thumbnails/",$post_array['thumbnail_url']);
$file_types = array( ".doc", ".ppt", ".pdf", ".xls", ".jpg");
$szFile=str_replace($file_types,".png",$szFile);
$szFile3 = file_get_contents($szFile);
$imgData = addslashes($szFile3);
$BLOB_insert = array(
"id" => $primary_key,
"image_BLOB" => $imgData
);
$iRc=$this->db->update($_SESSION[table],$BLOB_insert);
return;
}
$iRc is returning null so i don't know if this is succeeding. I then tried SELECT HEX(image_BLOB) FROM word where id=17 in mysqlWorkbench and the BLOB appears blank.
RE: uploading an image (BLOB) to mysql - InsiteFX - 03-20-2021
The insert should still be coded the same, Personally I would leave the images where they
are and then just save the image path to the database blobs will eat up you hard disk fast.
RE: uploading an image (BLOB) to mysql - richb201 - 03-20-2021
(03-20-2021, 01:51 PM)InsiteFX Wrote: The insert should still be coded the same, Personally I would leave the images where they>>Personally I would leave the images where they are and then just save the image path to the database blobs will eat up you hard disk fast.
are and then just save the image path to the database blobs will eat up you hard disk fast.
Yeah, me too! That has been working for a while. It used to be recommended not to keep images in the database for speed and cost. In my case, when running a report (which is the main reason to run the program), takes about 10 seconds on my laptop. Why? There is lots of database manipulation going on. To try to speed this up I split this 10 seconds into about 4 seconds of preprocessing and about 6 seconds of actually creating the charts, tables, etc. I am concerned that during this 6 seconds other users will be affected. For this reason I decided that I need to have a few servers sitting behind a load balancer. That should work to allieviate some of the issue. But that also means that a user who uploaded their images on server A will not be able to see them on server B and if they try to run their report from server B, the images will be missing.
That is why I moved the database to mysql RDS. The last part left for me to do is to move the uploaded images to a shared location. I have played with the idea of using AWS EFS, but in that case I need to either use AWS Direct Connect or VPC. Direct Connect is very expensive and VPC I am considering, but will take some effort. But keeping the small "thumbnails" in the mysql RDS would be easiest. Or so I thought.
I just can't seem to upload the BLOB.
I am not an expert in php or codeigniter or Docker
, obviously, but I am an expert in the taxation of research, which is what my application is for. I needed to say this because i constantly get criticized for my lack of technical skills and I really appreciate your non judgemental help. All I can say we all can't be EXPERT in everything! My design right now is pretty much to make each server independent. Each contains its own wordpress, fusionauth, apache, mysql in a docker container, but all of the data is kept centrally in RDS. In this way it doesn't matter which server is used since I am separating data and code, a basic IT precept.
RE: uploading an image (BLOB) to mysql - richb201 - 03-21-2021
In lookig at the log I see
Query error: Invalid utf8 character string: '\x89PNG' - Invalid query: UPDATE `substantiator`.`word` SET image_BLOB = �PNG
and then looking at the SHOW VARIABLES LIKE 'collation%'; in mysqlworkbench, I see that the database is set to 'collation_database', 'utf8mb4_0900_ai_ci'. So I think I need to modify the collation for the database to be utf8. But this is RDS so it is not very straightforward.
RE: uploading an image (BLOB) to mysql - richb201 - 03-24-2021
I updated the default collation and default char set but still have the issue. I think the problem is with the SQL 'update'. This is what I am using
$BLOB_insert = array(
"id" => $primary_key,
"img_BLOB" => $data
);
$this->db->update('word', $BLOB_insert);
I see this error in the log:
ERROR - 2021-03-24 18:13:03 --> Query error: Duplicate entry '47' for key 'word.PRIMARY' - Invalid query: UPDATE `word` SET `id` = 47, `img_BLOB` =
But I am trying to update id=47! It is true that the data in the blob is showing lots of '?' chars, but now I am thinking that is not the problem, the problem is the update command.
RE: uploading an image (BLOB) to mysql - richb201 - 03-25-2021
Well I got it up there. Turned out to be fact that the primary key was in the
$BLOB_insert = array(
"id" => $primary_key, <<<<here
"img_BLOB" => $data
);
What finally worked was:
$this->db->set('img_BLOB', $szFile3);
$this->db->where('id', $primary_key); <<using id in the where statement instead of the update
$this->db->update('word');