UNIQUE column type - assign identically-named entries to existing ID
I am creating a database of theatre productions and currently have a very basic setup with the below tables:-
prod_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
prod_name VARCHAR(255) NOT NULL
theatre_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
theatre_name VARCHAR(255) NOT NULL UNIQUE
prodid INT NOT NULL,
theatreid INT NOT NULL,
PRIMARY KEY (prodid, theatreid)
I have created 'add / edit production' forms into which you can enter the 'prod_name' and 'theatre_name'.
Currently if you enter a 'theatre_name' that already exists in the column it does not allow the entry to be added (given the field is 'UNIQUE') and simply displays the error message. What I would like to happen is for any such entries to be permitted and assigned the same 'theatre_id' as any pre-existing entry. (Not using the 'UNIQUE' command simply creates a new entry with a separate 'theatre_id' but with an identical name; I need all theatres with the same name assigned to the same id).
And I don't want to have to first create theatres in a separate 'add / edit' form.
Avoid INSERT IGNORE / AUTO_INCREMENT / SPORADIC ID problem
I've been trying to tackle the problem of the INSERT IGNORE command increasing the AUTO_INCREMENT counter (and creating sporadic IDs), and have come up with the below solution which checks the existence of the entry before an INSERT is even made. If it does exist then I pass the existing ID on, but if not I enter it into the database and then use the newly created ID.
The ID field is a PRIMARY KEY and the value field is UNIQUE so there is no chance of more than one result ever being returned.
Does this seem like an elegant solution to you? I know there is lots of debate about this problem (and one you have discussed previously: link here), but this to me seems quite logical.
$sql = "SELECT id FROM table WHERE col='$value'";
$result = mysqli_query($link, $sql);
$row = mysqli_fetch_array($result);
$id = $row['id'];
$sql = "INSERT INTO table SET
$categoryid = mysqli_insert_id($link);
$categoryid = $row['id'];