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.
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)
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)?
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’.
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'];
}
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!).