bx_photos_main in database

Ok, need a little help here.

I am uploading a crapload of puictures for a few members of my racing group. Changing the album ownership is easy but I need a simple sql script that will change the owner of each photo in the album as listed in bx_photos_main by using the query function

I can't figure out how to do more than 1 at a time and I have several hundred to change.

i can give you one but it will make you the owner of every photo....

on second thought you should have just logged in as that user and uploaded the photos ......  but to late now i am guessing.

let me know either way.

well let me see, how would i do this? 

a few things could be done:

though where not totally headache free, certainly a method to use that could possibly provide what you are looking for. 

first thing i would try, would be to use deano's tools, and login as member-x, and see if you are able to upload photos while logged in as that member? 

if not, then there are other work-arounds

backup your profiles table (export sql)

on any given userID, edit the profile, and change the email address to some dev email address, though valid, as you will need to be able to receive mail. 

logout of admin, request forgotten password on userID you have changed the email address on, this should send you a newly generated password. 

now you can login as that user, and upload to that account

when you are done, then find on the database profiles table, the account you were logged into, then look in your sql backup and find the values for the UserID you were working on, then run an UPDATE statement on sql, updating that UserID with the values from the backup on that UserID

yeah a bit of a pita, but its a method, and retains ownership to user-x, rather than to admin. 

ok thats two procedures i would try if i need to accomplish this task. 

@sky

just went and tested myself the first suggestion, and logging on as a member, allows you to upload photos and create albums as that user. 

so i think your hunt is over~~~~~

LOLOL! You guys crack me up!

I could have done ALL that already. It is a simple matter to change the ownership of an album and photo in the database itself.

Albums are easy, there's only a few of them. Photos are harder because there are so many!

I am just looking for a slick way to change the ownership of a bunch of photos all at once in the bx_photos_main table from within phpmyadmin using the query feature. I can do it one at a time and it works perfectly, it just takes me almost 5 seconds per entry and gets BORING FAST!

I think what dolphin_jay was trying to tell you is any query we give you will change ALL of the photos in that table to the same ownership. Not just the ones for a specific album.

The difference when you do it by hand is you know which one your changing. The query will not because there is no reference to the album in that table.

Actually, I think there is. All albums are given an ID just like the photos. I can actually change it so an album is owned by one person and the photo is owned by another.

If that's the case, why can't you just write a sql (statement/query?)  something like this (and i don't know how to do this, it's why I'm asking)

Where bx_photos_main photo album ID is XX, change photo ownership ID to xx (in this case, the album ID is 26 and I want to change the photo ownership ID to 24 instead of 1)

nobody?

 working on it sky, its doable, just not in a position to make it work, since i dont have a database with the same issue you are trying to resolve. 

 that was why i asked that you zip up the database, and i can load it, and run queries on it. 

i am close, but need to be able to test it

i am thinking we can achieve this with LEFT JOIN

and UPDATE the Owner field on both bx_photos_main and sys_albums

just need a database that has more than 1 user LOL, because i sure as crap dont know where to get umpteen images loaded on admin, to even be able to have to try to move them around from within the database.

this logic is semi-applied on the albums already, since you as the album owner, can move images around from album to album, so i am looking at that logic, coupled with my own twist on the query, to be able to produce something. 

however, i am of the belief that this is again, a function that should have been written into the admin panel for management. 

It can be done but will require a complex query with a left join because the table bx_photos_main DOES NOT contain the ID of the album the photo belongs to.

So your statement Where bx_photos_main photo album ID is XX is not possible with a standard query because bx_photos_main does not contain the photo album ID

But yea, DosDawg is on the right track. It's just these left join querys are very complex. I will at some point in time be purchasing some software to help me design such complex querys.

 deano, 

i am thinking that on sys_albums the ID, if you assign an update to update the owner of the photos, then they would be set in the default album, then management of the images would take place from user-level. 

yes it is a complex multi-level query and without a database to work with, its being based on presumption. i need a database that has the requirement of this query in order to get it tested. 

using the LEFT JOIN is the process in which i will try to utilize that will allow updating two tables, just the complexity grows when you are updating an OwnerID, AlbumID and ImageID, and with any missing parameter as is my case in trying to structure this query, i am not getting a result that i am looking for. 

im stabbing at it though. just like you say, its not a run-of-the-mill query, and needs to be tested, and ascertain if all requirements have been filled. 

 My bad, you are right. For some reason, I had it in my brain that it did.

Ok, so you need to work between 2 tables. Go to the first one (sys_albums), identify the images by ID from within it, then go to the second one (bx_photos_main) and make the ownership changes to those specific ID's.

Simple right? (lol)

Oh yeah, DosDawg, I don;t see any reason to be updating the image ID. We're only looking to change ownership of the album itself and the ownership of the images within it. The image ID would remain the same

It just seems to me that if i can go into the database and manually make these changes easily one by one, there ought to be a way to automate the task.

Maybe we are looking at it wrong. Maybe we need to make an assumption here.Maybe we need to assume that the photos put into an album will be all the photos within a certain ID range. Like photos with ID 54 through 126.

I am adding massive amounts of photos for other users all at once. That means, everything will be linear and I can actually set a range of ID's I want to change. Not a perfect solution but would work for me.

So, it would be where photos in bx_photos_main have an Id from 10 to 100, change ownership to xx.

I could even apply the same sql query to change the albums as well

Ok, I am reading around and I still think this is an easy thing to do.

It took me a few minutes to figure this out, but the syntax for UPDATING ONE TABLE ONLY using a relationship between two tables in MySQL 4.0 is actually quite simple:
update t1, t2 set t1.field = t2.value where t1.this = t2.that;

You know, cruising the web, you can learn many things. For those of you that want a basic understanding of how a mysql database works, this is a GREAT link
http://www.atlasindia.com/sql.htm

there is still a problem with this, because you have yet to instantiate which imageID is to get updated to change the owner. 

yes, if you were changing the owner on all images where Owner=1 on bx_photos_main, and where Owner=1 on sys_albums, then this is the simplest form. however, since you have said you have hundreds of images, or maybe even thousands, then this is not going to work for you. 

so you have to set which imageID  you want to reassign, so the UPDATE would need to include the imageID's or a BETWEEN or an = or something, otherwise, you are based on this UPDATE statement you share here, would set Owner='newowner" on bx_photos_main, and Owner="newowner" FROM bx_photos_main

leaving out which images you actually wish to reasign to any given album owner. so if you think you found the solution, god bless you, but that is not what i would perceive as being the solution. 

another good place to learn is http://mysqlcourse.com where you are provide a live sql environment and tutorials to show how running queries works. 

i think you are still missing parameters here based on what you stated initially

IF all images on bx_photos_main belong to Owner=1, and albums on sys_albums has Owner='x', and you update on bx_photos_main, where Owner=1, then you are essentially assigning all images with Owner ID 1, to new owner parameter. 

i would suggest you quit uploading the images as admin, and use deanos tools to upload these images? then at least that way, the images are assigned to the desired owner from the beginning? otherwise, i can only foresee this creating additional work for you. 

I can certainly do it the easy way but where's the fun in that?

You stated it yourself, I do want to change the ownership of image 54 through 200 in bx_photos_main. that's all I need the sql for. 

I don't even need to involve the sys_album table to do this.

The quote I added was someone who professes to be a sql expert saying it would be easy to view table 1 to determine which images to change in table 2.

update t1, t2 set t1.field = t2.value where t1.this = t2.that;

table 2 would be the bx_photos_main ownership column and table 1 would be the sys_albums_objects ownership column. In essence, changing the ownership in the photos owner column to match the owner column the album table possesses.

And yes, the sys_albums_objects does have the photo ID in it

So, if you tell it to look in 'sys_albums_object' for any 'id_object' number with corresponding 'id_album=26', then find that corresponding 'id_object' number represented as 'id' in 'bx_photos_main' and change the 'owner' to match the owner of the album id of '24', you have accomplished your goal.

RIGHT!?

Of course, if you really wanted to do it right, you would first run a script that tells it to change the album ID in 'sys_albums' 'id' '26' from owner '1' to '24' first.

NOW, do i have to learn how to write the script too? (lol)

 one other field you are missing here is object_count

 That would only matter if you were changing the amount of photos within the album. We're not. We're only changing ownership.

 well then that was not expressed, you said you uploaded the photos, and wanted to change ownership of the photos by assigning photo x to ownerID x

in doing so, this would change the count, since the owner you are changing the ownership of the photos to, would not have the correct count. 

if you are in fact changing the album owner, which is not what was stated, then you are correct, the count on the album would not be affected, if in fact the ownership of each of those files that were created in a given album, are in fact traversed over with the ownership change.

i read the inquiry to be changing the ownerID of the image, not ownerID of the album, would be a different query indeed. 

 ahem...

UPDATE `bx_photos_main` SET `Owner` = '24' WHERE `Title` like 'Hoffpauir%';

129 rows affected. ( Query took 0.0666 sec )

YOU WIN THE BIG PRIZE!