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 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Jul 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    php adjacent record insert to another table

    hi,
    i am a newbie in php and building a db for my executives' work performance. the db has 2 tables.

    point table defines work like pub, edn and point. there are many editions under one single publication and total of 4 publication.
    eg pub is A, edn is CE and point is 2
    and pub is T, edn is CE, point is 2.5
    these values are inserted into the database considering the merit of each work.


    an executive feeds in the values of pub, edition and i want a php to secure the assigned point according to the work to his name.

    thanks in advance.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,021
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    You described one table, what does the other table look like?
    Maybe it would help if you showed a few records from each table.
    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 to the CF scene
    Join Date
    Jul 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks for replying.
    the other table has emp name, pub, edn and point
    i want the executive to enter their name, pub and edn they have done and the point should update itself from the value from the other table i described before where i already inserted the points for different pub and edn.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,021
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Pardon me if I say I think this is an inadequate design.

    The trouble with this is that there is:
    (a) no record of what each Excutive entered. So you don't know if they added 10 points from one activity of 3 points from 3 activities and 1 point for another.
    (b) no way to correct entries, in case of errors.

    I truly think you need a *THIRD* table:
    Code:
    CREATE TABLE points (
        pointid INT AUTO_INCREMENT PRIMARY KEY,
        pub VARCHAR(10),
        edn VARCHAR(10),
        points INT
    ) ENGINE InnoDB;
    
    CREATE TABLE executives (
        execid INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR( 50 ),
        ... other fields ...
    ) ENGINE InnoDB;
    
    CREATE TABLE executive_accomplishments (
        execid INT NOT NULL,
        pointid INT NOT NULL,
        whenAccomplished DATETIME,
        CONSTRAINT FOREIGN KEY (execid) REFERENCES executives(execid),
        CONSTRAINT FOREIGN KEY (pointid) REFERENCES points(pointid)
    ) Engine InnoDB;
    Now, when the executive claims a given accomplishment, you just add a record to the executive_accomplishments table.

    And then, to find the total points for any given executive, you do a simple query:
    Code:
    SELECT SUM(P.points) AS totalpoints
    FROM points AS P, executive_accomplishmets AS EA, executives AS E
    WHERE P.pointid = EA.pointid
    AND EA.execid = E.execid
    AND E.name = 'john doe'
    Or similar.
    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.


  •  

    Tags for this Thread

    Posting Permissions

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