Problem with inserting into MySQL

[Shane930]

Hi, working with CF 7.1 and MySQL 5. I am using the insert form function in DW
and have a problem with inserting multiple records into the same auto increment
record ID. I am using multiple pages to insert image locations into the DB and
attempting to use the same ID variable passed from page to page. My problem is
that the auto increment record ID is only allowing the first record to be
inserted then it creates another record ID for the second insertion and another
for the third insertion. I want all of the image locations for that record ID
to go into fields in that one record ID, not create separate records.

Any help would be great.

Thanks

Shane

[Dan Bracuk]

In almost all cases, having separate records for each image location is the best way to go. But I did say almost. What is your reason for trying to do it all in one record?

[Shane930]

Hi Dan, my reason for wanting them all under the same auto increment record ID
was because I thought it was more efficient. But your question make me wonder.
Is it really better to have lets say 10 record ID with each image location
uploaded into separate records? If so and if it doesn't really create an
inefficient table then another question for you? When I build a recordset to
display all 10 images by a form variable ad_id, it only displays the first
image. The recordset works fine in the test mode, all ten records are shown,
but when I pull the page up I only see the first image with the other nine
image place holders. I then built another table with all of the image locators
in the same record ID and the all displayed on the page. So one way the detail
page only shows one picture and the other way I get 10 records with unique
record IDs load into the table.

Thanks for your help

Shane

[Shane930]

My recordset SQL is:

<cfquery name="photos" datasource="saa">
SELECT primary_image_path, image_path_1, image_path_2, image_path_3,
image_path_4, image_path_5, image_path_6, image_path_7, image_path_8,
image_path_9 FROM saa.photos WHERE ad_id = #FORM.ad_id#
</cfquery>

Shane

[Dan Bracuk]

Having a separate record for each file location gives you a lot more
flexibility. It also makes it easier to retrieve the records, your present
difficulties notwithstanding.

I notice from your query that your table has an awful lot of fields. What are
your business rules? Can two ads use the same image path?

[Shane930]

Hi Dan, I am working on a auto site project as a way to learn DW, CF, MySQL and
Web design. The site would allow users to upload pictures of their cars with
up to 10 photos per user. The table for photos has an auto increment
field(PK), an ad_id which ties the images to the user table and 10 image
fields, 1-10. To answer your question "Can two ads use the same image path?"
the answer would be no. So when I upload 10 images now I get 10 separate
records each with one image path and all have the same ad_id. Is this the best
way to do the images or should they be all in one record 1-10? Either way is
fine with me, I was just looking to keep the table clean and efficient for
rapid retrieval of the images, especially if their were a thousand users. The
displaying of the images is also another problem that is perplexing. But that
is another post, lol.

Thanks again for your input Dan, I appreciate it.

Shane

[Dan Bracuk]

A separate record, each having the same ad_id and diffferent image paths is the
way that I would do it. Depending on the overall schema, I might also not have
the autoincrement field at all. I would use the ad_id and image path as the
primary key.

[Shane930]

Hi Dan, thanks for the input.

I figured out what I was doing wrong. I was using the insert record behavior
for all 10 upload pages when I should have used one insert record page and 9
update record pages. The update record behavior has a check box for the
primary key so you can add to the record in the table.

Now the correct fields are updated and the page displays all of the images not
just one.

Take care

Shane