UNIQUE column type - assign identically-named entries to existing ID

[andygout]

Hi,

I am creating a database of theatre productions and currently have a very basic setup with the below tables:-

production
prod_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
prod_name VARCHAR(255) NOT NULL

theatre
theatre_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
theatre_name VARCHAR(255) NOT NULL UNIQUE

prodtheatre
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.

Thanks,

Andy

[r937]

I need all theatres with the same name assigned to the same id

think about what you're saying here

if it has the same name, then it has the same id

so attempting to add a new one with the same name ~should~ be rejected

UNIQUE is correct, and you need to adjust your error handling logic to reflect that

if the name already exists, you have to actually retrieve the id, in order to have values for inserting into the prodtheatre table

then again, if it doesn't exist, you also have to retrieve the id, which you can do in php with the mysql_insert_id() function

for simplicity of coding, i would use INSERT IGNORE, and then simply SELECT (rather than testing for an error and using mysql_insert_id() if there was no error)

[andygout]

Hi Rudy,

Thanks for the advice; I had not come across the INSERT IGNORE command so that's massively useful. Here is my code (minus error messages):-

$sql = "INSERT IGNORE INTO theatre SET
    theatre_name='$theatre_name',
    theatre_created=NOW(),
    theatre_updated=NOW()";

$theatreid = mysqli_insert_id($link); //OBTAINS NEWLY ACQUIRED theatre_id
    
$sql = "INSERT INTO prodtheatre SET
    prodid='$prodid',
    theatreid='$theatreid'"; 

Might you be able to explain how to use the SELECT command to retrieve and insert the 'theatre_id' data (from pre-existing or newly-created entries in the 'theatre' table) into the 'theatreid' field (of the 'prodtheatre' table)?

Thanks (I'm still a beginner)!

Andy

[r937]

Might you be able to explain how to use the SELECT command to retrieve ...

SELECT theatreid
  FROM theatre 
 WHERE theatre_name='$theatre_name'

returns a unique id because theatre_name is unique

note you wouldn't use mysql_insert_id() because if it already existed (triggering the IGNORE) then no new auto_increment is assigned

[andygout]

Sorry, I get all that, but what I can't quite figure out is the code required to insert that retrieved data into the 'prodtheatre' table along with the 'prodid' data (pulled from an earlier mysql_insert_id() command), bearing in mind that 'theatre_id' comes from the 'theatre' table and goes into the 'prodtheatre' table under the a different header, 'theatreid'.

[r937]

okay, try this --

INSERT 
  INTO prodtheatre 
     ( prodid
     , theatreid )
SELECT $prodid
     , theatreid
  FROM theatre 
 WHERE theatre_name = '$theatre_name'

[andygout]

Amazing! Thanks so much, and sorry for being a bit thick about it.

The only change I needed to make was that the data I was selecting was 'theatre_id', not 'theatreid' - working fine now!

Final excerpt:-

$sql = "INSERT INTO prodtheatre(prodid, theatreid) SELECT $prodid, theatre_id FROM theatre WHERE theatre_name = '$theatre_name'"; 

[andygout]

Hi Rudy,

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.

Cheers!

Andy

$sql = "SELECT id FROM table WHERE col='$value'";
$result = mysqli_query($link, $sql);    
$row = mysqli_fetch_array($result);

$id = $row['id'];

if (!$id)
{
    $sql = "INSERT INTO table SET
        value='$value'";
        $categoryid = mysqli_insert_id($link);
}
else
{
    $categoryid = $row['id'];
} 

[r937]

...the problem of the INSERT IGNORE command increasing the AUTO_INCREMENT counter (and creating sporadic IDs)

in my opinion, this is ~not~ a problem

the purpose of an auto_increment primary key is to be unique, not consecutive

gaps in the numbers don't mean a thing

secondly, if your `value` columns (poor name, by the way) is UNIQUE, why do you even need an auto_increment?

[andygout]

Good points. It's probably because I'm currently using the ID for the URL and so wanted to keep things tidy, but I want to change that to use the name in future, so I expect you are right (btw - I'm just using 'value' to illustrate; wouldn't use that in practise!).

Thanks!

Andy