How to store an array with unknown length in mySQL database

Question

This will be a long 'question' but I have 0 ideas on how to do this so it would also be very hard to formulate that question:

What I'm trying to do: I want to make the admin page from which I can upload a product. For that, I have a file input for the slideshow. That image will be sent to my node.js backend and I'm saving that file in my public/uploads dir. The path file path is then sent back to the frontend and added to an array together with other links for other slides and when I press a submit button, that array gets sent to the backend. I want to save that array in mySQL.

The array that I send to the backend to be uploaded is

[
  'uploads/slideshows/nbvnbvdnvvbnSlideshow.jpg',
  'uploads/slideshows/nbvnbvdnvvbnSlideshow2.jpg',
  'uploads/slideshows/nbvnbvdnvvbnSlideshow3.jpg',
  'uploads/slideshows/nbvnbvdnvvbnSlideshow4.jpg',
  'uploads/slideshows/nbvnbvdnvvbnSlideshow5.jpg'
]

and is called slidehowPaths.

The length of the array will differ from slideshow to slideshow so I cant use:

db.query(
        'INSERT INTO slideshowarrays VALUES (?,?,?,?,?)',  
        slideshowPaths
)

I tried using something like:

db.query( 
        `INSERT INTO slideshowarrays VALUES (${'?'+',?'.repeat(slideshowPaths.length - 1)})`, 
        slideshowPaths
)

But that also throws an error ER_WRONG_VALUE_COUNT_ON_ROW. I honestly dont even have a clue of what to do with this.

My Table: Only link1 has NN, and 7 is the maximum number of links I will ever use.

I'm very sorry If I formulated this question terribly, please do ask questions in the comments below if you need clarification. I'd be grateful if there you tell me any solution, no matter how dirty.

I think it should work if you just add null values to your array so that is always has 7 entries: for(let i = slideshowPaths.length; i < 7; i++) { slideshowPaths.push(null) } — cuzi, Jan 03 '21 at 22:33
This is a duplicate of several questions, you should be able to find what you are looking for here: https://stackoverflow.com/questions/17371639/how-to-store-arrays-in-mysql — Alex Collette, Jan 03 '21 at 22:37

score 1 · Accepted Answer · answered Jan 03 '21 at 23:04

I would not design this to insert the five images into five columns of the same row. What if you need a product with six or eight or ten images? You would need to expand your table to have more columns. You'd always wonder if tomorrow you will need to support a product with one more image than the number of columns.

Instead, store each image in its own row. Then you only need one column for the image name. You loop over your inputs and execute INSERT once for each image, inserting a new row for each. Then you can support any number of images per product, without adding columns to your table.

This is typical of a one-to-many relationship: you need a second table. You store product details in your products table, and then you store multiple rows in the product_images table.

Create the table like:```create table product_images (id int,imageid int,link varchar(256))``` — ElapsedSoul, Jan 04 '21 at 00:52

How to store an array with unknown length in mySQL database

1 Answers1