Jump to content


Photo

MAKE A DONATION to maintain this site - See the 'Home' page for details


  • Please log in to reply
64 replies to this topic

#41 d3c0y

d3c0y

    The 2000+ club

  • Members
  • PipPipPipPipPipPipPip
  • 2,847 posts
  • Website:http://garagesanmaru.com
  • Location:Brisbane
  • Tagline:Forum Jerk

Posted 14 July 2015 - 09:44 AM

Forum LAN party!

#42 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 15 July 2015 - 12:21 AM

Need a little SQL help here...

It should be simple but I keep getting errors...Rather than post the SQL statements I've tried and adding confusion I'll include a couple of photos of the database tables...

ips_gallery_images - This table is for all the images in the photo gallery. The problem is the column image_member_id is 0 for all images. (Dangling reference?).

But it does contain an image_album_id which determines the album they belong to. (foreign key).

See attachment ips_gallery_images.png.

Now if we look at the ips_gallery_albums table, we can see columns album_owner_id and album_id. See attachment ips_gallery_albums.png

So what I need to do is:

Set all the images within an album to have the same owner id as the parent album.

How would you structure this SQL statement? (Provided a few may already be correct having manually set them, so a where image_member_id=0 might be a good idea here).




Attached Thumbnails

  • 47562_48015fd442c137aa7d78d90b95898fb145974759.png
  • 47560_02c9598c992f74a4b2109c9eaa4c4d541c403e40.png


#43 andyk_79

andyk_79

    Senior Member

  • Members
  • PipPipPipPipPip
  • 612 posts
  • Website:http://www.viczcar.com/forum/topic/12055-andrews-1972-240/
  • Location:Sydney West
  • Tagline:Useless Pest asking clueless questions

Posted 15 July 2015 - 07:42 AM

UPDATE ips_gallery_images SET image_member_id = (SELECT album_owner_id FROM ips_gallery_albums WHERE ips_gallery_albums.album_id = ips_gallery_images.image_album_id) WHERE image_member_id = 0

#44 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 15 July 2015 - 09:25 AM

Brilliant that appears to have worked.
I had originally tried something like this.

update ips_gallery_images
set ips_gallery_images.image_member_id = ips_gallery_albums.album_owner_id
where ips_gallery_image.image_album_id = ips_gallery_albums.album_id;

My problem is I'm not always sure how to structure a statement and when I need to use a select statement or not?

#45 George

George

    The 1000+ club

  • Members
  • PipPipPipPipPipPip
  • 1,846 posts
  • Location:Sydney

Posted 15 July 2015 - 09:28 AM

UPDATE ips_gallery_images SET image_member_id = (SELECT album_owner_id FROM ips_gallery_albums WHERE ips_gallery_albums.album_id = ips_gallery_images.image_album_id) WHERE image_member_id = 0


I concur...  ::)

#46 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 15 July 2015 - 11:44 AM

Thanks George for the sanity check. :)

#47 andyk_79

andyk_79

    Senior Member

  • Members
  • PipPipPipPipPip
  • 612 posts
  • Website:http://www.viczcar.com/forum/topic/12055-andrews-1972-240/
  • Location:Sydney West
  • Tagline:Useless Pest asking clueless questions

Posted 15 July 2015 - 01:38 PM

My problem is I'm not always sure how to structure a statement and when I need to use a select statement or not?


Pretty much any time you are updating to a value that comes from a different table (or another row in the same table) you need to use a select statement.  The issue is that if you simply reference the other table in your update statement you are creating a join.

#48 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 15 July 2015 - 02:28 PM

Thanks Andy,

Just so you can see the progress. You can see most album's now have an associated owner. Also you can see that the images inside these albums are now associated with the correct owner of the album.

You can see that "new album" or "240z" or albums of a very generic nature are missing references, this is because there was no other unique key (or foreign key) I could use to match the owner with an album, at least not without a lot of work. I'll manually update these later so they have the correct owners etc..

Now I have to figure out why the comments are not displaying below images. I can see the entries in the table, but there is something else preventing the comments being displayed. I think it's a count in the images table...

Attached Thumbnails

  • 47566_2042c45b906ba137359108b1907d9f39be1e344b.png
  • 47568_e3c28d6b8364f157285a458c981ce280ede43b6d.png


#49 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 18 July 2015 - 01:38 PM

So I figured out why comments are not showing... Turns out that during the migration process the comment, it's date stamp, it's author and author id were all correct, but the image id referenced somehow got modified and the image id is out of whack. I've tried to see if there was any pattern to this modification but so far the numbers are all over the place. I can't work out why. I was hoping a pattern would allow me to apply 1 single statement to update them all to the correct image_id but at the moment I'm doing it manually (all 500 of them). Which is a pain in the arse no doubt but it works.

Here is an attached example showing the comment appearing under the image correctly.

An arduous task but I'll just do it in batches of 50 over the next few days (to save my sanity).

Attached Thumbnails

  • 47586_40aa3e1ef958d23bda469e99ab3717bae0bfbb3a.jpg


#50 GongZ

GongZ

    Senior Member

  • Members
  • PipPipPipPipPip
  • 810 posts
  • Location:Wollongong NSW
  • Tagline:Watanabe wannabe

Posted 18 July 2015 - 03:54 PM

Its looking good Gavin.

I am really looking forward to seeing it go live.

#51 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 18 July 2015 - 04:02 PM

You and me both. It will be good to have the site driven by 1 piece of software instead of multiple and have it all share the same database. Which will be a huge win in my opinion, it will also make adding new features a lot easier. Hopefully will also speed the site up a bit also with some optimisation stuff I'm going to try, but probably most importantly improve the mobile experience since quite a few people use tablets and mobile phones to access content on here now.

#52 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 12 August 2015 - 12:14 AM

Minor update, so not sure if I mentioned this anywhere previously but after manually updating a lot of the gallery tables for individual albums, images and comments I then imported the latest snapshot of the current database and everything went pear shaped.

What happened is that during the import of the latest snapshot all the member/user IDs would be reset again. This meant all the references in the gallery tables were no longer relevant.

FYI: By snapshot I mean a snapshot of the database at a given point of time. Since the site is still running and live the data I import becomes irrelevant after a couple of hours when people post content to the site.

The solution was to create a common key between the tables that wouldn't change each time I imported the latest snapshot and did the database conversion. Since every user has a unique email address this was the common key.

Now all I've got to do is figure out a query that will look at the new member id compare with the old and update to the new based on a users email address. I'll have to do this for the albums and comments tables. Then every time I import a snapshot I just run the queries and everything is back to normal.

Basically it's a round-about way of doing things but it will work...

Just wanted to give an update that this work is still happening, just slowly.

#53 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 12 August 2015 - 03:50 PM

Just an FYI, I am not sure how some users are doing it because for me the Gallery is broken in terms of adding new content. But it appears some are still able to add photos.

Please do not add photos to the gallery because these won't be migrated across (unless I manually do it after) but the current migration means that I've missed the last 6 images.

These were uploaded by imackz and zed24t

Once migrated across no problems, but between now and then please resist uploading more images to the album since I won't have the latest database snapshot to work from.

#54 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 12 August 2015 - 06:16 PM

Good news, I've managed to write some scripts that fix the gallery tables after migration.

So I should be able to upload a test version of the new site shortly.

#55 PB260Z

PB260Z

    Peter (PB)

  • Moderators
  • PipPipPipPipPipPipPip
  • 3,237 posts
  • Location:Northern Sydney
  • Tagline:Must stop buying new toys for the workshop.

Posted 12 August 2015 - 06:29 PM

Good news, I've managed to write some scripts that fix the gallery tables after migration.

So I should be able to upload a test version of the new site shortly.


So are we looking to a UAT type exercise for a week or two ?

#56 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 12 August 2015 - 06:43 PM

So are we looking to a UAT type exercise for a week or two ?


For those not familiar with the lingo UAT = User Acceptance Testing.

Yes, so rather than just dump the new site on everyone and expect everything to go smoothly, I'll upload it to a test domain ask people to have a play around with it and give me their feedback. If everything works fine. Great, but I suspect there will be some minor tweaks needed before everything is happy with it.

I've also got to add some scripts to the template and maybe change a couple of things with regard to the navigation buttons etc..

Also interested to see how mobile/tablet users react to the new layout for mobile.

#57 PB260Z

PB260Z

    Peter (PB)

  • Moderators
  • PipPipPipPipPipPipPip
  • 3,237 posts
  • Location:Northern Sydney
  • Tagline:Must stop buying new toys for the workshop.

Posted 12 August 2015 - 06:47 PM

Hi Gav

Nearly all of my forum time is on the iPad so happy to contribute to the testing on that platform.
At work we describe UAT as getting people to try and break the software.

"Can we break it - Yes we can" is often the cry from our UAT team.

Cheers

#58 gav240z

gav240z

    Administrator

  • Administrators
  • 12,389 posts
  • Website:http://www.viczcar.com
  • Location:Sydney NSW
  • Tagline:Jack of all trades, master of none.

Posted 18 August 2015 - 10:48 AM

For details on the new software and where it can be tested refer to this thread.
http://www.viczcar.c...ic,16409.0.html

#59 dat240z

dat240z

    The 1000+ club

  • Members
  • PipPipPipPipPipPip
  • 1,110 posts
  • Location:Canberra
  • Tagline:RB25det powered 240z - willwoods - coils - engineered

Posted 23 August 2015 - 11:41 PM

Gav, can you confirm if my donation has gone though - 23/8 @ 23..36? It came up with this...image.jpg

#60 PB260Z

PB260Z

    Peter (PB)

  • Moderators
  • PipPipPipPipPipPipPip
  • 3,237 posts
  • Location:Northern Sydney
  • Tagline:Must stop buying new toys for the workshop.

Posted 23 August 2015 - 11:47 PM

Hi Mate

Whilst I can't access the accoynt to see if the $$ actually came in, I would be confident that it did though for the following reason

- the error you got is the same I get, but my monies have always gone trough.

When he gets time, I am sure Gav will confirm.

Cheers

PB

Edited by PB260Z, 23 August 2015 - 11:49 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users