how to update mysql for non existing images

[hobert]

I occasionally make changes to pictures via FTP, and wrote a little code snippet to make the delete check in the edit pics page autofill if the original pic is missing.  Then I just process each set of 100, starting from the last and move backwards through an album.  It takes about 10-15 secs per 100 (3-5 mins per 1000).

in editpics.php, after:

Code Select Expand

$isgalleryicon_selected = ($CURRENT_PIC['galleryicon']) ? 'checked="checked" ':'';
$isgalleryicon_disabled = (!isset($CURRENT_PIC['category']) || ($CURRENT_PIC['category'] < FIRST_USER_CAT)) ? ' style="display:none;" ':'';
I added a line:

Code Select Expand

$checked= (file_exists("albums/".$CURRENT_PIC['filepath'].$CURRENT_PIC['filename'])) ? '' : 'checked';//if file is missing, automatically check the delete box

I modified:

Code Select Expand

<input type="checkbox" name="delete{$CURRENT_PIC['pid']}" id="delete{$CURRENT_PIC['pid']}" value="1" class="checkbox" title="{$lang_editpics_php['del_pic']}" /><label for="delete{$CURRENT_PIC['pid']}" class="clickable_option">{$icon_array['delete']}</label>
to (added $checked)

Code Select Expand

<input type="checkbox" name="delete{$CURRENT_PIC['pid']}" id="delete{$CURRENT_PIC['pid']}" value="1" class="checkbox" title="{$lang_editpics_php['del_pic']}" $checked /><label for="delete{$CURRENT_PIC['pid']}" class="clickable_option">{$icon_array['delete']}</label>

[sharpo]

Not sure if to reply to this or start new topic - SORRY!

I'm trying to create a new gallery on a different host with 1 user from an existing gallery with several users. The gallery works but I cannot remove the "missing images" that appear. I obviously didn't include the userpics folders from the other users, as they were not needed, and that's what I can't delete. A couple of the users only had a few images and I was able to upload those userpic folders, then delete the users and files. But the remaining users have in one case over 1000 images, so rather a lot to upload, just to delete.

I've tried the check_files plugin and the editpics.php suggestion but neither seem to work. Any suggestions please.

[Αndré]

So you cloned a complete gallery and now want to remove all pictures but from one user? If so, we can easily remove all pictures from the database where that user is not the owner. Please post a link to your new gallery and tell us which is that one user.

[sharpo]

So you cloned a complete gallery and now want to remove all pictures but from one user? If so, we can easily remove all pictures from the database where that user is not the owner. Please post a link to your new gallery and tell us which is that one user.

Thanks Αndré,

http://www.sharpo.uk/andrew/index.php?cat=1

The link is above, that page shows which user I wish to retain, the other two are to be deleted. Had it been just a few files for each user, I would've uploaded them and deleted via "users", as I did with a couple of other users.

[Αndré]

What should happen with the pictures in the other categories? You need to say what exactly has to be deleted and what not.

[sharpo]

What should happen with the pictures in the other categories? You need to say what exactly has to be deleted and what not.

What I was attempting to do was just leave the 1 user. The categories could stay but be removed/deleted individually later if the remaining user had no images that linked to them.

My aim was to make Andrew Hadley administrator, then delete Richard Shaw and me (sharpo) which would leave 1 Admin and Andrew. I just deleted the other user (37 427 I think it was) as no images were assigned to him. Hope that explains my aims.

[Αndré]

So all other pictures, which doesn't reside in the albums of the following category should be deleted, right? http://www.sharpo.uk/andrew/index.php?cat=10007

This means all other user galleries and all pictures in all other categories and albums. Is this correct?

[sharpo]

So all other pictures, which doesn't reside in the albums of the following category should be deleted, right? http://www.sharpo.uk/andrew/index.php?cat=10007

This means all other user galleries and all pictures in all other categories and albums. Is this correct?

That's correct, yes.

[Αndré]

Run this query with a tool like phpMyAdmin:

Code Select Expand

DELETE FROM cpg15x_pictures WHERE owner_id != 7

Please create a backup before you run the query. Maybe you need to adjust the table prefix (cpg15x_).

[sharpo]

Run this query with a tool like phpMyAdmin:

Code Select Expand

DELETE FROM cpg15x_pictures WHERE owner_id != 7

Please create a backup before you run the query. Maybe you need to adjust the table prefix (cpg15x_).

Changed it to cpg14x and ran it, but nothing seems to have changed.

[Αndré]

Either the table prefix is still wrong (just have a look at the table names or at include/config.inc.php) or the MySQL user you used doesn't have sufficient permissions to delete from that database/table. What's the output when you run the query?

[sharpo]

Either the table prefix is still wrong (just have a look at the table names or at include/config.inc.php) or the MySQL user you used doesn't have sufficient permissions to delete from that database/table. What's the output when you run the query?

I submit the query using phpmyadmin on the cpg14x_pictures table, it seems to finish doing whatever after a few seconds, but no indication of what it did or didn't do.

[Αndré]

Create a new .php file in your gallery root and add this code to it:

Code Select Expand

<?php
define('IN_COPPERMINE', true);
require('include/init.inc.php');
pageheader();
cpg_db_query("DELETE FROM {$CONFIG['TABLE_PICTURES']} WHERE owner_id != 7");
echo "Done.";
pagefooter();
//EOF

Now, open that file with your browser.

[sharpo]

Create a new .php file in your gallery root and add this code to it:

Code Select Expand

<?php
define('IN_COPPERMINE', true);
require('include/init.inc.php');
pageheader();
cpg_db_query("DELETE FROM {$CONFIG['TABLE_PICTURES']} WHERE owner_id != 7");
echo "Done.";
pagefooter();
//EOF

Now, open that file with your browser.

Fantastic! Appears to have worked, now showing as 532 files in the gallery, which as far as I know is correct.

Thank you very much Αndré, I appreciate your help.