From Array to MySQL

Question

Not the best with MySQL, but I'm scraping data from websites with python, and storing the results in a mysql database. All the data I've collected have been placed into custom objects that I created (i.e each object is a car with a respective make, model, year, etc.) Now where I'm having an issue is that each car has a number of images associated with it, and it can range from zero to ten images. The question I have is how can I store these images in a MySQL table and connect it to its respective car.

This is the table I've created for each car so far (python code):

    command = ("""CREATE TABLE `%s`(
            `auctionHouse` varchar(255) NOT NULL,
            `auction` varchar(255) NOT NULL,
            `lot` varchar(255) NOT NULL,
            `year` int,
            `make` varchar(255) NOT NULL,
            `model` varchar(255),
            `description` text,
            `fullName` varchar(255) NOT NULL,
            `salePrice` varchar(255),
            `highPrice` int NOT NULL,
            PRIMARY KEY (`auction`,`lot`));
            """
           )%(auction)

Do I have to create another table for holding images and then when I query the car, use a join?

Arturo Alvarado · Accepted Answer · 2015-06-30 02:14:26Z

1

You do not have to create another table, but I would STRONGLY suggest you do. You specified that you have a finite number of images per car, so you could have 10 columns in your car table. But what happens when you decide to add more?

If you plan to store your images as Blobs, then most definitely don't create 10 columns; go ahead and create another table.

If you choose to create another table, I would suggest using Innodb and specify the foreign key constraint.

I would also suggest that you use a transaction when when you create the car entry, and associate the images to that car.

You need the id of the car you created to use it as the value in your image table to relate the image to the corresponding car.

http://www.w3schools.com/php/php_mysql_insert_lastid.asp

Then you can use a Join to get all the images related to the car, or you can query the images table by itself if you only need the images and you have the car Id.

answered Jun 30, 2015 at 2:14

Arturo Alvarado

4981 gold badge5 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JerryCrowley Over a year ago

Ok, created another table. Thanks!

Collectives™ on Stack Overflow

From Array to MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related