Getting the name of an empty column with mysql

Question

I have a set of image paths stored in a table like this:

    +---------------------------------------------------------+
    |ID           |pimg0        |pimg1         |pimg2         |
    +-------------+-------------+--------------+--------------+
    |1            |path/to/img0 |path/to/img1  |              |
    +---------------------------+--------------+--------------+
    |2            |path/to/img0 |path/to/img1  |              |
    +---------------------------+--------------+--------------+

and I want to get the table name of the empty field so I can put a new entry into it.

I have tried nesting IFNULL() commands in the mysql query, which didn't work:

    IFNULL(pimg0, IFNULL(pimg1, IFNULL(pimg2, IFNULL(pimg3, IFNULL(pimg4, IFNULL(pimg5))))))

I have also tried some case blocks which didn't work. Ideally I would like my query to return "pimg2" in the above scenario, but if it was to return "pimg1" I could easily increment it.

EDIT: Edited the table above, to clarify.

Answer 1

You're experiencing one of the pain points of repeating groups across columns, which is a violation of First Normal Form.

The pain goes away if you normalize, by creating a child table with one column for image path, and adding multiple rows when you have multiple images.

CREATE TABLE Images (
  image_id  INT AUTO_INCREMENT PRIMARY KEY,
  owner_id INT NOT NULL, -- references `id` in your original table
  pimg      VARCHAR(40)
);
INSERT INTO Images (owner_id, pimg) VALUES 
 (1, 'path/to/img0'), (1, 'path/to/img1'), 
 (2, 'path/to/img2'), (2, 'path/to/img3');

Structuring a database in this way makes it easier to do many tasks:

• Insert a new image by INSERT a new row; no need to find the blank column.
• Add a fourth or a fifth image to a given owner_id.
• Search a given image and report which owner_id owns it.
• Count images per owner_id.
• Avoid duplicates.

Answer 2

SELECT id, 'pimg0'
FROM table WHERE pimg0 is null
UNION
SELECT id, 'pimg1'
FROM table WHERE pimg1 is null
UNION
SELECT id, 'pimg2'
FROM table WHERE pimg2 is null