problem migrating pictures

[ukscotth]

Hi,

 

Im trying to transfer a folder full of pictures to a new database as im changing the software my site runs off and im having a bit of trouble with the coding, can anyone see anything wrong with this ? im getting this error 'Couldn't insert image'

 

<?php 
include("include/configvb.inc.php");

$pics = mysql_query("SELECT * FROM joovili_pictures");
						  

			while ($got_pics = mysql_fetch_array($pics)){


$fid = mysql_query("SELECT * FROM joovili_users 
WHERE username = '".$got_pics['picture_username']."' LIMIT 1");

  if (mysql_num_rows($fid) == 0) {} else {

$fid1 = mysql_fetch_array($fid); }				


$fh = fopen($got_pics['picture_path'], "r");
list($width, $height, $type, $attr) = getimagesize($got_pics['picture_path']);
$data = addslashes(fread($fh, filesize($got_pics['picture_path']))); 
fclose($fh); 

$id = $got_pics['picture_id'];
$uid = $fid1['id'];
  

$SQL = " 
INSERT INTO picture(pictureid, userid, extension, filedata, width, height, state, caption) 

VALUES('$id','$uid','jpg','$data','$width','$height','visible','oldpic')"; 
  
      

$RESULT = mysql_query($SQL) or die("Couldn't insert image");

}

?>

[kickstart]

Hi

 

Try a temp change to the query execute to:-

 

$RESULT = mysql_query($SQL) or die("Couldn't insert image ".mysql_error()." $SQL");

 

and see what is output.

 

All the best

 

Keith

[ukscotth]

Thanks Keith,

 

It came up with a duplicate entry error, looks like its working now ive emptied the table. Theres over 10,000 pictures, will it be able to cope do you think i will i have to run it in parts ?

[kickstart]

Hi

 

Might be worth upping the time limit for the script, but would think it should work.

 

You could probably also merge the 2 SELECT statements (ie, join the tables) to save about 10000 SELECTs which might also help, but as a one off job quite probably not worthwhile at this stage.

 

All the best

 

Keith

[ukscotth]

Ok thats great, thanks very much for your help. Its well appreciated

[aschk]

Don't use PHP to do this.

Use the mysqldump tools to dump the data from the table, and then use mysql to import the data.

[kickstart]

Don't use PHP to do this.

Use the mysqldump tools to dump the data from the table, and then use mysql to import the data.

 

Suspect the issue with keeping it in MySQL is where he is taking the file name, opening it and extracting the details for the insert to the 2nd table.

 

All the best

 

Keith

[aschk]

Ah yes indeed. It would appear I misinterpreted the question.

So, you might wish to consider doing this in batches instead.

Take 10 records, collate all the data for them, and do an insert containing 10 items.

Doing lots of inserts will be time consuming.

[ukscotth]

thanks aschk.

 

Job already done. Took about half hour but as its just a one off job its fine.

 

Thanks again for your quick responses.

 

Scott

[kickstart]

Hi

 

Quick knock together of what may be a faster way of doing it for future reference, including batching up the inserts (excuse the inevitable typos):-

 

<?php 
include("include/configvb.inc.php");

$pics = mysql_query("SELECT a.picture_path, a.picture_id, b.id FROM joovili_pictures a LEFT OUTER JOIN joovili_users a ON a.picture_username = b.username");
$recCnt = 0;
$SQL1 = "INSERT INTO picture(pictureid, userid, extension, filedata, width, height, state, caption) VALUES ";
$insertArray = array(); 
while ($got_pics = mysql_fetch_array($pics))
{
$recCnt++;
$fh = fopen($got_pics['picture_path'], "r");
list($width, $height, $type, $attr) = getimagesize($got_pics['picture_path']);
$data = addslashes(fread($fh, filesize($got_pics['picture_path']))); 
fclose($fh); 

$id = $got_pics['picture_id'];
$uid = $got_pics['id'];

$insertArray[] = "('$id','$uid','jpg','$data','$width','$height','visible','oldpic')"; 
if ($recCnt % 10 == 0)
{
	$RESULT = mysql_query($SQL1.implode(',',$insertArray)) or die("Couldn't insert image ".mysql_error()." $SQL");
	$insertArray = array(); 
}
}

if (!empty($insertArray))
{
$RESULT = mysql_query($SQL1.implode(',',$insertArray)) or die("Couldn't insert image ".mysql_error()." $SQL");
}

?>

 

All the best

 

Keith

[ukscotth]

thanks alot keith thats great