PHP - Convert Image URL to SQL BLOB

Question

I am programming with PHP and using XAMPP for Apache and MySQL.

I have searched and tried for two days a way to convert an image URL to a binary variable. After this process I try to insert it as a BLOB in a SQL database table.

I have tried several approaches (not included below) such as base64(), file_get_contents(), save the image file or even cURL...

Here's a picture of the SQL database table structure:

and here's the "mind blowing psychedelic" PHP code:

<?php 
    // APPROACH #1
    $blob = file_get_contents('http://www.domainname.com/random/page/IMAGE.png');

    // APPROACH #2
    $bin = base64_encode($blob);

    // INSERT INTO QUERY
    $stmt = $db->prepare("INSERT INTO imagezzz (id_link, imagez) VALUES (?,?)");

    // APPROACH #1
    $stmt->bind_param("ib", $number, $blob);

    // APPROACH #2
    $stmt->bind_param("ib", $number, $bin);

    $stmt->execute();
?>

When the query is executed it throws no error. In fact the INSERT query is performed and a new table line is added. The first and second field (id[int] and id_link[int]) haves data but the the third field (imagez [BLOB]) is empty.

I have performed a SQL INSERT QUERY inside phpMyAdmin (by uploading a local image) and it works fine...

Do not use addslashes arbitrarily like this. It's a nearly useless function that's almost always inappropriate for the job at hand, especially SQL. — tadman
– tadman, Commented Apr 7, 2020 at 23:02

LSerni · Accepted Answer · 2020-04-07 22:37:25Z

2

If you can get the file with file_get_contents, this ought to work:

$blob = file_get_contents('http://www.domainname.com/random/page/IMAGE.png');
print "Image is " . strlen($blob) . " bytes long\n";
$bin  = base64_encode($blob);
$estimate = round(strlen($blob)/0.75);
print "Blob is " . strlen($bin) . " bytes long (expecting about {$estimate}\n";
$stmt = $db->prepare("INSERT INTO imagezzz (id_link, imagez) VALUES (?,?)");
$stmt->execute([ $number, $bin ]);

answered Apr 7, 2020 at 22:37

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Grinnex. Over a year ago

Thank you very much for your help and time! I really mean it! Unfortunately the blob insert still doesn't work. It creates a row with data except on the blob(imagez) field. Maybe the problem is from XAMPP, PHPMyAdmin or MySQL... I will search for some clue... Thanks anyways! 🙏

J Harvey Over a year ago

@Grinnex. You may want to consider changing your Blob Field to a LongBlob if you're not receiving any INSERT Errors.

Collectives™ on Stack Overflow

PHP - Convert Image URL to SQL BLOB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related