Uploading multiple images to the same record in a database.

Report · Aug 25, 2011

Hello all,

I am new to PHP and MySQL and I am using Dreamweaver to help make the coding process a whole lot easier. I have a client who owns a car lot and I have 99% of his website complete. The only outstanding item is, he needs to be able to upload multiple images for one car. As of right now, his MySQL database is set up with one table that has carid, make, model, year, mile, etc., all the way down to image_path which is a varchar field with a value of 100. I have tried using the Dreamweaver Developer toolbox, but I am completely lost on how to make this happen. I know that in order for this to happen I need to be able to update the image_path field (which points to an image path on my server that contains the images) in the database and I am aware of how to make update records with Dreamweaver but for some reason this one just isn't working out for me. If someone could help me out I would be very grateful.

Report · Aug 25, 2011

>As of right now, his MySQL database is set up with one table

For starters, you will need another table that stores the image paths for each image related to each vehicle. This table will have a foreign key to the main vehicle table.

Report · Aug 25, 2011

I have also read this as a solution, but the problem is I am unsure how to make foreign keys with the type of MySQL server I have (MyISAM). If it were possible I would have two tables, 1 table for carinfo with carid as the primary key. The second table has a primary key of imgid and a foreign key off carid that links to the carid in the carinffo table, am I correct in saying this?

Report · Aug 25, 2011

You can achieve this with a third table that contains just the auto increment ids of the other two tables.So this third table is what would be updated when you add an image to a particular auto record

Report · Aug 25, 2011

Hmmm, maybe I'm confused but...if I create a 3rd table that includes just the auto incremented ID's from the other two tables, and I were to upload an image to the 3rd table, theres no field type to say where the image is going so how is it going to know that this particular image goes with the car that has the id of (carid)

Report · Aug 25, 2011

Rob, this is a one-to-many relationship so a link table is not required. Or did you mean something else?

Report · Aug 27, 2011

Guys, I am still racking my brain. I have taken some screenshots to show you how I have my database tables set up. I was able to get the tables over to the InnoDB type so I can make foreign keys. Here is how I currently have them setup, can someone check this out and see if it's correct?

Table 1 (carinfo)

Table 2 (images)

Thanks

Report · Aug 27, 2011

Please disregard the previous post, I found out why the record wasn't appending. Carid in the second table was not indexed and did not have the same type as it should be set up to int(11) and it was set up as varchar. Now that I know my database is setup correctly I am going to begin tackling this upload problem again

Report · Aug 27, 2011

You won't be able to setup a foreign key right now because of a data type mismatch. The carid in the carinfo table is int(11) and in the images table is varchar(100) ( http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html ). Plus the carinfo table requires a value whereas the images table allows NULL. Then I'm not sure what the "image_path" is in either table. Is there a difference? If so, maybe that should be highlighted because when you start pulling out queries you might be thinking the same thing.

Also a quick lesson on datatypes might be in order too. Can you name any reason why mileage would not be an integer? I would personally change that to a mediumint. Year is another one. I don't plan on living 10,000 years myself, but a smallint would do just fine. The smaller you can make the data the quicker and more efficient the database will become. Also be careful with allowing NULL values. Do you really want those fields empty in your database?

Report · Aug 27, 2011

I have fixed the data types, and fixed the null items. I had this set because I kept getting errors about fields not being able to be null, i changed it just for testing reasons. The image_path field in the database is a field that will hold the path on the server where the images will be uploaded. To give you an idea of what I am trying to acheive I would like for the administrator of the website to be able to login to the page and find the vehicle he wants to edit by typing in the vin of the vehicle. He will then be able to upload multiple images for each vehicle in his database. When he uploads the files i want it to make a directory in the images/uploads folder so if he is uploading images for the vehicle with the vin of 1 the image_path would be images/uploads/1 where 1 is the vin. On the server if you were to browse to the folder images/uploads/1 you would find all the files that he uploaded for that particular VIN.

I have tried using Dreamweaver developer toolbox to acheive this but I can not get it to work. It's been almost a week of trying to complete this but I am STUCK!. I have the entire administration part of the website complete except this. I am able to add new vehicles to the database, edit vehicle information, delete vehicles, but I can not upload images. This is really driving me crazy so can someone please help me out. I am 99% sure I have the database configured correctly now.

Report · Aug 27, 2011

The image_path field in the database is a field that will hold the path on the server where the images will be uploaded.

I figured that, but I mentioned because you have the same field name in both tables when one is the path (carinfo), and the other appears to just be the name (images). If you pull a query that consists of a JOIN then you end up with 2 fields with the same name you end up with 2 identical field names in the same array. For such clarification reasons I would recommend naming the one for the images as image name or something else just in case. Otherwise if you call the image_path from the array you will get the latter result (images table) or you would have to call the items in the array by their numeral index.

I have tried using Dreamweaver developer toolbox

I wouldn't recommend that too much. The ADDT has been discontinued since 09 and is no longer supported. So it's possible that it might not be able to do everything that you are looking to have it do.

Report · Aug 25, 2011

Sorry, I shouldn't have mentioned foreign key. You don't really need to have a foreign key constraint. You just need another table to hold the image info with a column that references your other table.

>I am unsure how to make foreign keys with

>the type of MySQL server I have (MyISAM).

I don't work with MySQL so I don't know either. Having a foreign key will help maintain referential integrity, but it's not strictly required.

>The second table has a primary key of imgid and a foreign key

>off carid that links to the carid in the carinffo table, am I correct in saying this?

Yes. You could also create a composite primary key using carrid + imgid. But if you are using the DBMS to create a uique ID for imgid, then just use that for the primary key.

Report · Aug 27, 2011

Ok, I have changed the name in the images table from image_path to imagename. Is there any possibility that I could pay you to create this code for me? I do not have enough knowledge about this to do it successfully and time is ticking before I reach the project deadline.