andygout — 2012-08-25T12:02:24-04:00 — #1
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.
r937 — 2012-08-25T15:51:11-04:00 — #2
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 — 2012-08-27T11:53:16-04:00 — #3
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
$theatreid = mysqli_insert_id($link); //OBTAINS NEWLY ACQUIRED theatre_id
$sql = "INSERT INTO prodtheatre SET
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)!
r937 — 2012-08-27T12:06:57-04:00 — #4
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 — 2012-08-27T12:39:11-04:00 — #5
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 — 2012-08-27T13:03:17-04:00 — #6
okay, try this --
, theatreid )
WHERE theatre_name = '$theatre_name'
andygout — 2012-08-27T13:27:18-04:00 — #7
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!
$sql = "INSERT INTO prodtheatre(prodid, theatreid) SELECT $prodid, theatre_id FROM theatre WHERE theatre_name = '$theatre_name'";
andygout — 2012-10-28T19:11:57-04:00 — #8
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'];
r937 — 2012-10-28T19:46:15-04:00 — #9
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 — 2012-10-29T03:48:02-04:00 — #10
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!).