List loop problems.
I've created an library that stores image names by ID and an image gallery table that stores the values of up to 15 images by image ID. The data looks like this:
ImageTable (id | name)
1 | one.jpg
2 | two.jpg
3 | three.jpg
4 | four.jpg
5 | five.jpg
6 | six.jpg
Gallery Table (id | slide1 | slide2 imageID ... and so on to slide15)
1 | 1 | 3 | 4 | null | null .... and so on to 15
1 | 2 | 5 | 6 | null | null .... and so on to 15
In this example the slide show 1(id1) would show one.jpg, three.jpg, and four.jpg
Slide show 2 would show two.jpg, five.jpg, and six.jpg.
If I pass a recordID (gallery.php?recordID=1) to the page I'm getting a perfect gallery recordset. Just one column comes in. I can't seem to figure out how to match that table row to the image table.
I think the problem is in the Query. I've tried dozens of loop and join queries but I can't seem to get anything to work I want to build a list of image names like this (html omitted):
Avoid writing multiple queries especially if you need to loop them.
Are these images only ever available in one gallery? Given null entries here, that indicates that there is something not correct with the design which will cause problems later. A simpler approach would be to create a table with a galleryid and imageid and create a composite of these. A third table with the gallery information is used, but only necessary if specific information is applied. This lets you create the many to many should an image reside in more than one gallery and provides an easy to join column. Mostly software to require enforcement of sizes though since you can't enforce it to contain <= 15 from the storage side.
There may be a couple of thousand photos eventually. The maximum length of a slide show is 15 slides. This is to limit loading times. When an image is uploaded it is given an ID and a description. One table contains all of the images.
A user can then go into the slide show creation page and assign up to 15 images to a slideshow. They appear in the order selected. When the slideshow script runs into a null it loops to the first slide. You could even have only one slide in a slide show.
I am trying to avoid having image names in two tables. I think the easiest thing to do is to simply create a table for each slide show that contains links to the slide id's.
If I were to redo the slide show creation form to insert image names instead of image id's then getting the slide show to populate would be easier but I'd loose the descriptions and other info from the images entered when the images are uploaded.
This should be simple. Take a look at a list of values, the image id's and call up all of the information for that ID. I do this kind of stuff with lots of other tables, this one just has me stumped. It should be really simple to look at a the slide show table $row_gallery[slide1] and call up the image name and all of the other data from the imageID stored in the slide1 column.
Can slides be reused in more than one slideshow? Looks like it can only exist once per slideshow, but you don't indicate if it can be reused in other slideshows. Sounds like the 15 is more of a preference (you can also use AJAX to seamlessly grab the next 15 for example), so enforcing that through the software would also make sense to me instead of enforcing through the structure.
Just need to know if you can reuse an image in multiple slideshows.
The slides can be reused. I've designed a gallery page that displays 5 rows of 3 thumbnails. You can load up to 15 slides or you can load 1. I call that page from a menu on another page that uses master detail page techniques to create a link like this: gallery.php?recordID=2
I have an image library that is created when a user uploads an image and enters a lot of data about that image. Meta Data is included. The primary key for the image table is the image ID.
When I was first developing this system I setup the form that is used to enter gallery images to insert the image name (example.jpg) instead of the ID. Now a simple WHEN statement in the query for the page will bring up all of the 15 possible images. The page works perfectly.
Now I replaced the image names in the Gallery table with the image id's. My problem is easily linking those ID's back to the right record.
Because of a deadline, I went ahead and wrote 15 separate queries, one for each column in the gallery page recordset. It was a bunch of typing, but now I can display any of the data I want for each image in the gallery.
That query looks like this: (html and other fields omitted)
Yeah it sounds like your structure is incorrect. The 15 maximum should be a soft limitation that's enforced by the software itself (otherwise you do have nulls). If you enforce it in storage, than it should be no more and no less than 15.
With a many to many relationship, you need to create a flattening table and it *usually* uses 2x supporting tables (if one is more of a lookup type, it may be easier to use an enum instead; I'll assume that both images and gallery would contain information relevant to a single record so that would require three tables).
An example of such:
To query it, you would join for information.
This lets you ignore the hard limitation of the 15 (and only 15 since nulls are not desirable if you can get around them) in order to expand or contract the amount allowed, but does require that logic be implemented in the language to enforce a particular maximum as well as a specific ordering be applied numerically.
I still am not explaining myself very well. I'm creating a gallery page with a hard set of design limits. 15 images maximum. Let's compare it to a contact database with first name, last name, address, e-mail.
To echo the contact info in order you have to have a separate data field for each column. It's no different for this gallery. I can see how generating a gallery from a table that pairs images and galleries and then running a query to join the two records together would be fairly straight forward but I'd have to redesign my gallery building form completely.
I'm not going to re-work my project at this point because I've built a beautiful and easy form that uses a searchable, selectable interactive image list to populate the gallery slide fields.
I probably should have also mentioned that when images are uploaded a whole bunch of info is gathered. Metadata, the user that uploaded the images, the description, the geotag info... I need to bring that data in for each image in the gallery because it's displayed to the users and it's important to the QA and QC process for the client.
I simplified the query and the form entry from above so there were fewer lines of code and it's working just fine for now. I'm not sure why a gallery image table like the one that you suggested would be more efficient than a table with a slot for each slide. It looks like there's twice as many fields to search through because you've got 2 fields to look at plus an order field to generate the image set where my single table just has to look once for a match to an image ID.
Thanks again for your help.
If you refuse to alter your structure to represent a proper many to many relation, then your options are to either:
Either way, you're putting weight on the language to enforce something. You may as well write the proper many to many normalization in order to control the storage properly. Improper normalization is NOT easy to query properly.
I think I'm ok. This is no different that a table with contact info, name, address, e-mail etc. Some fields are blank, some get updated with new information, and a lot of data can be updated with one update action. The slides are as unique as a first name, last name, email. You wouldn't necessarily want to store every field in a contact form in a separate table. You may want to store address information, phone numbers, or business information in separate tables that are joined together, but this project's design specifications made a single table for all galleries look like the best idea.
The design limits of 15 images are forced by legal requirements. Slide 1 in Gallery 1, 5, 7, and 23 may be image 1, a photo of Plant 1's production line. Image 5 may be of Data Plate A from Machine Room G taken last march and image 240 may be of Data Plate A from Machine Room G taken yesterday. Gallery 10 is for Machine Room G and Slide 4 is for Data Plate A. When the gallery is updated you replace the image for Slide 4 in Gallery 10 with image 240 from a UI that shows all images in their appropriate slots and a catalogue that is searchable for all images from Machine Room G. I have to make this very easy for a user with an iPad or a smart phone to take images and update the gallery for that particular project. I've built an app that uploads the images and field data to the database.
90% of all galleries contain all 15 images. Updates are made, in some cases, daily. New record sets of specific images for each piece of equipment on the line are created every time a change is made. This is why I wanted one table with slots for each piece of data. It also allows you to create an entire gallery with one insert record action.
A mod log is set up for every action on this Intranet website that records every action in the database so that any archival records from any configuration of the gallery, or any other table, can be instantly called up by simply entering a date. It seems to me that the table you suggested would be far more complex to query for audit history. It also seems like it would take far more effort (mouse clicks) for a user to insert images to a gallery.
I don't know of a way to add multiple rows to a table with one insert or one update action. If one exists that would really help the next project I'm developing.
Thanks again for looking at my problem and making suggestions. You have given me a lot to think about.
This is completely different than a table with contact information. Each property is fully dependent on the record in order to create it in its entirety. If I have a first and last name column, two people can have the name Jane Doe, but these are distinct entities. If Jane Doe #1 marries and changes their name to Jane Smith, this doesn't alter the record for Jane Doe #2.
In this situation if you alter primary record image to change the filepath, every record changes as the image data is related via primary key. There is no comparison between the two. If you wanted to follow suit with the concept of the user table, than each image associated in slide would be an image of its own, and file1 would not be the same as file1.
If you see null entries, then it will likely violate normal form (can't recall if its 3rd or Boyce-Codd it will violate; I'm thinking 3rd, but null alone doesn't dictate this). Does that mean you cannot violate them? No, it doesn't, but it may not be wise. One of the very few exceptions I have done in the past is allowing a home and work number in a single contact table. If no home number is provided, it will be left null. Done proper I would only have a record in a ContactPhoneNumber for the associated type which I would make an enum.
You should avoid violating normalization when it comes to many to many relationships. Failing to do so creates anomalies and forces complex querying.
To do as you have and query everything at once, you would require a join on every field. That also means that if you want to cut down on the querytime you will need to index every one of them as well. See my query above for the first three columns, you simply need to expand that to cover every one. So with the unnormalized data you will have to choose to either query and end up with 15 joins, or you have to use 16 queries.
Multiple inserts are a simple matter of adding a comma in the values section:
The actual gallery page just comes from a single query of a single table. I used my multiple queries on the creation form to populate lists and check boxes to avoid data entry errors. Your's may be a better solution but this project is done and delivered.
I'll do some research on multiple inserts from check boxes. That sounds interesting. With this client we want to avoid as much typing as possible so most data entry forms are lists, radio buttons and check boxes. This avoids typos and other problems.
|All times are GMT +1. The time now is 01:34 PM.|
Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.