[RESOLVED] Importing Remote Images Into DB w/longblob

patsfans

I have a site where all the images are currently in a directory on another server (I did it at the time to conserve bandwidth), and am now looking to write a script that will import them from there into a database on a dedicated server as I'm getting rid of the other. I'm looking to write a script to load them from that server via the URL, and then insert it into my MYSQL database as a longblob file? I'm just trying to find an efficient way to make the move, and thought that this would be the best way to handle it.

Here's what I have:

<?php

$photo = "http://www.example.com/images/picture.jpg";
$image = file_get_contents($photo, FILE_BINARY);

$dbname = "database";

$connection = @mysql_connect("localhost", "user", "pass") or die("Couldn't Connect.");

$db = @mysql_select_db($dbname, $connection) or die("Couldn't Select Database.");

$table = "images";

$sqlcat = "
INSERT INTO $table
(image)
VALUES
(\"$image\")
";

$resultcat = mysql_query($sqlcat, $connection) or die ("Error in query: $sqlcat. " . mysql_error());	
?>

I can't get it to load the image, and then insert it into the LONGBLOB cell. I can't find another example of this online or anything similar, and the file_get_contents reference on php.net obviously doesn't explain this. So thank you for your help so far, and I'm sure this is probably easier than I'm making it out to be.

Also, the reason why there is a full URL to the image is because this script is one server, while the image is on another. I have a database that has the URL's to all the images in the table, so what my eventual goal will be is to run a loop that will import all the images into a longblob cell in the new DB and then I'll be deleting the images off the other server. The aforementioned code is to just provide a more simple example of what I'm trying to accomplish. Hopefully this makes sense 🙂

Thanks in advance for your assistance, and I appreciate your help! - Ian

bradgrafelman

Don't you need to store the image name as well? Otherwise you'll just have a bunch of rows containing binary data and you won't know where a certain image is.
You need to escape the binary data in the query string; use [man]mysql_real_escape_string/man to do this.

patsfans

bradgrafelman;10904533 wrote:
1. Don't you need to store the image name as well? Otherwise you'll just have a bunch of rows containing binary data and you won't know where a certain image is.

I will, I just streamlined the example to make it easier to read and troubleshoot.

2. You need to escape the binary data in the query string; use [man]mysql_real_escape_string/man to do this.

Thank you. Do you have an example? I just tried it and it's still not working correctly.

bradgrafelman

How did you try it? It's simply a matter of concatenating the return value of that function into your SQL query string.

patsfans

bradgrafelman;10904549 wrote:
How did you try it? It's simply a matter of concatenating the return value of that function into your SQL query string.

Here's the code:

<?php

$photo = "http://www.example.com/images/picture.jpg";
$image = file_get_contents($photo, FILE_BINARY);

$dbname = "database";

$connection = @mysql_connect("localhost", "user", "pass") or die("Couldn't Connect.");

$db = @mysql_select_db($dbname, $connection) or die("Couldn't Select Database.");

$table = "images";

$query = sprintf("INSERT INTO $table ('image') VALUES ('%s')",
mysql_real_escape_string($image, $connection));

mysql_query($query, $connection);	
?>

Does that look correct?

bradgrafelman

Seems fine, is display_errors turned on, error_reporting set to E_ALL, and did you check if mysql_query() returned false and, if so, output mysql_error() ?

patsfans

bradgrafelman;10904556 wrote:
Seems fine, is display_errors turned on, error_reporting set to E_ALL, and did you check if mysql_query() returned false and, if so, output mysql_error() ?

No, there's no error (I checked the error log) it's executing but it's just not loading the image so there is no row being inserted. 😕

bradgrafelman

Not sure what to say then, if you're sure that error_reporting is set to E_ALL. If PHP couldn't access remote files, it should throw an error saying why.

patsfans

bradgrafelman;10904585 wrote:
Not sure what to say then, if you're sure that error_reporting is set to E_ALL. If PHP couldn't access remote files, it should throw an error saying why.

I'll take that back. There is a row being inserted, but the blob has 0 Bytes. So is that the correct function to load the image before it's inserted, or is the problem that I'm using the wrong syntax for this function?

bradgrafelman

Try getting rid of the FILE_BINARY flag on the file_get_contents() call. Also, try echo'ing out $image before the query - does it output the binary data?

patsfans

bradgrafelman;10904612 wrote:
Try getting rid of the FILE_BINARY flag on the file_get_contents() call. Also, try echo'ing out $image before the query - does it output the binary data?

Well, it appears I've figured it out (at least the problem anyway). If the URL is not on the local server, it won't load the image (it loaded it via a URL that was to a file on the same domain). Is there a different command I need to use, or a function in .htaccess to tell the server to load remote files?

bradgrafelman

If you're sure you have the URL correct, you can try using [man]cURL[/man]. You might try contacting your host and explaining what you're doing (attempting to download a file from a remote website) and ask how they suggest you go about doing it.

patsfans

bradgrafelman;10904912 wrote:
If you're sure you have the URL correct, you can try using [man]cURL[/man]. You might try contacting your host and explaining what you're doing (attempting to download a file from a remote website) and ask how they suggest you go about doing it.

Have a friend at the server company and come to find out it was a firewall issue. The added the domain to the firewall white list and it worked perfectly.

Thank you to everyone for taking the time to try and help, I appreciate it. And I hope you all have a good weekend! - Ian