SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by andygout View Post
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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):-

    PHP Code:
    $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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by andygout View Post
    Might you be able to explain how to use the SELECT command to retrieve ...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, try this --
    Code:
    INSERT 
      INTO prodtheatre 
         ( prodid
         , theatreid )
    SELECT $prodid
         , theatreid
      FROM theatre 
     WHERE theatre_name = '$theatre_name'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:-

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

  8. #8
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Avoid INSERT IGNORE / AUTO_INCREMENT / SPORADIC ID problem

    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

    PHP Code:
    $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'];


  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by andygout View Post
    ...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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •