Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6

Thread: Mysql

  1. #1
    New to the CF scene
    Join Date
    Jan 2011
    Location
    Dartford, Kent
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mysql

    Hi,
    I want to add data into a field that already has data, but I want to check to see if the data already exists in the field and if so dont add it.

    Basically its a approval field. So when an admin person clicks on to the account it puts the name of the admin in the field, and if another admin also approves it as them too.

    Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    You mean you want BOTH names to end up in the same field???

    Bad database design!!! Create a separate table just for approvals. One approval per record.

    Go read up on NORMALIZATION.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Jan 2011
    Location
    Reston, VA
    Posts
    37
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Old Pedant is right on this. You need a one-to-many relationship.

    Example:

    Approvals table:

    ID
    Document_ID
    Admin_ID or Name

    Then to show approvals you can query:

    SELECT * FROM approvals WHERE Document_ID=1

    You'll need to check for existing entries before inserting new ones.

    This sounds like more work but in the end it will save you a great deal of effort.
    Sr. Software Engineer | Paisley Systems Inc. | www.paisleysystems.com/php

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    Quote Originally Posted by gooney0 View Post
    Approvals table:

    ID
    Document_ID
    Admin_ID or Name

    You'll need to check for existing entries before inserting new ones.
    *Mild* disagreement.

    (1) What's the purpose of the ID field? If it's an auto_increment field, it's useless in a table like this. You'll never use any query that needs it. If you need something for a primary key, use a *composite* primary key: The Document_ID and Admin_ID together should clearly be unique in the table and so they make a perfect primary key.

    (2) You don't *really* need to check for existing entries ahead of time. You can simply use INSERT IGNORE:
    Code:
    INSERT IGNORE INTO Approvals (Document_ID,Admin_ID) VALUES( 33, 171 );
    or equivalent. If the INSERT would cause a duplicate (you *did* create the composite primary key I recommended, right?), then it is simply IGNOREd. A nice feature of MySQL.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Jan 2011
    Location
    Reston, VA
    Posts
    37
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Old Pedant,

    Very good suggestions. I'll have to read up on those a bit more and see how I can work them into the mix.

    That would seem a much better way of adding data, but processing "selected" data would seem less straightforward. (i would think?)

    With ID field: I would store the results into an array or object and use the ID field to uniquely point to that data. Something like:

    $people[1]["First_Name"]="John";

    Now it is easy to update the table using WHERE `ID` = "1"

    Thanks again. That INSERT IGNORE feature is a nice tip.
    Sr. Software Engineer | Paisley Systems Inc. | www.paisleysystems.com/php

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    You've lost me. How would you ensure that you'd get the same ID from one UPDATE to the next???

    And if ID is an auto_increment field, it is likely to be something like 1718 for a given "John" anyway.

    ******

    Re processing SELECTed data. I don't see the issue.
    Code:
    SELECT Project.projectname, Users.username
    FROM Project, Approvals, Users
    WHERE Project.projectid = Approvals.projectid
    AND User.userid = Approvals.userid
    Or even
    Code:
    SELECT Project.projectname, GROUP_CONCAT( Users.username, ', ' ) AS allApprovers
    FROM Project, Approvals, Users
    WHERE Project.projectid = Approvals.projectid
    AND User.userid = Approvals.userid
    GROUP BY Project.projectname
    And so on. I truly can't find any value at all in an ID field for the Approvals table. There almost never is any reason to have one in a many-to-many table, because the many-to-many table is never used in any "stand-alone" fashion. It pretty much has to be used in linking the two tables it is related to.

    What am I misunderstanding??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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