Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Aug 2007
    Posts
    16

    Unanswered: finding highest entries - select and insert statements - efficient?

    Dear Forum

    I need some guidance with this php/mySQL statement.
    The basic issue is that I need to first retrieve 2 max values in the database before I can insert the new statement. I can do it with 3 separate mySQL statemtents. I was wondering if there's a more efficient way of doing this?

    Here is my statement:
    $query = "SET @max_orderP = ( SELECT max( OrderNumP ) FROM CATEGORIES ); SET @max_orderC = ( SELECT max( OrderNumC ) FROM CATEGORIES ); INSERT INTO CATEGORIES(Name, OrderNumP, OrderNumC, Status, SessionID) VALUES ('THIS IS A TEST', @max_orderP+1, @max_orderC+1,'1','XXXXX')";

    Any insight appreciated.

    Thank you in advance

    SK

  2. #2
    Join Date
    Aug 2007
    Posts
    16

    another option?

    Here is another option to show what I have in mind... This doesn't work but maybe it's a syntax problem?

    INSERT INTO CATEGORIES(Name, OrderNumP, OrderNumC, Status, SessionID) VALUES ('THIS IS A TEST', max(OrderNumP)+1,max(OrderNumC)+1,'1','XXXXX')

  3. #3
    Join Date
    Aug 2007
    Posts
    16
    I've considered doing it in 2 steps. Getting both OrderNumP and OrderNumC in one step, but that doesn't seem to work either in php, even though it does work in the phpmysql SQL window...

    --------------
    $query = "SELECT max( OrderNumC ), max(OrderNumP) FROM CATEGORIES";
    $result = mysql_query($query);

    if (!$result) {
    //errors
    $_REQUEST['error_id'] = DB_ERROR;
    require("getError.php");
    die('<!--Invalid query: '.mysql_error().'//-->');
    }else{
    $row = mysql_fetch_assoc($result);
    $lastOrderNumP=$row['max( OrderNumP )'];
    $lastOrderNumC=$row['max( OrderNumC )'];

    print $lastOrderNumP;// this does not get printed!
    print $lastOrderNumC;// this does get printed! ???
    }

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Hello there Stephank, and firstly welcome to dbforums!

    What you can do is the following :
    Code:
    $sql = "INSERT INTO CATEGORIES ( Name, OrderNumP, OrderNumC, Status, SessionID) 
    SELECT '{$Name}', MAX(OrderNumP), MAX(OrderNumC), '{$Status}', '{$SessionID}' 
    FROM CATEGORIES";
    
    $result = mysql_query($sql);
    I'm assuming of course in the above that you have a variable called $Name, $Status, and $SessionID, which i'm guessing you can populate from somewhere.
    Last edited by aschk; 08-20-07 at 08:26.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just curious, what are OrderNumP and OrderNumC? what do they mean?

    and why to both of them automatically increment for new inserts?

    and that has gots to be the weirdest CATEGORIES table i ever seen
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2007
    Posts
    16

    That worked!

    Thanks aschk! that worked. Here's the code I've used:

    ------------------------------------------------------------
    "INSERT INTO CATEGORIES ( Name, OrderNumP, OrderNumC, Status, SessionID)
    SELECT 'NEW TEST CATEGORY', MAX(OrderNumP)+1, MAX(OrderNumC)+1, '3', '14241424'
    FROM CATEGORIES"
    -----------------------------------------------------------

    If there are other approaches feel free to post them, I'm new to this game and happy to learn.

    To answer r937's question: The OrderNumC and OrderNumP are necessary because the same categories appear in 2 different sections of the website (a client list section and a project list section). They want to be able to display the categories in different order depending on which section...

    Best,

    stephank

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would like to suggest another approach

    create a table for sections (it will have only two rows at this point)

    change your table for categories and remove the OrderNumP and OrderNumC columns

    create a table called SectionCategories with { sectionid,categoryid } as a composite primary key, together with OrderNum as a data column

    now when inserting, you can still do the MAX() trick, but it will need a WHERE condition so that you increment the correct section's order number

    trust me, this will make things go a lot easier for you later
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2007
    Posts
    16
    ah yes. I see. I used this approach with other parts of this site but not with the categories because this request came in at the end after I've already started building things. Thanks for highlighting this issue. I appreciate it.

    Cheers,

    stephank

  9. #9
    Join Date
    Aug 2007
    Posts
    16
    R937 I have a question. Might sound basic... What is a composite primary key?

    I so far have created two separate fields, in this case: sectionID and categoryID... but you're saying that those two fields can be combined into one field?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not combined into one field, but combined for the sake of uniqueness

    create table SectionCategories
    ( sectionID integer not null
    , categoryID integer not null
    , foreign key ( sectionID ) references Sections ( id )
    , foreign key ( categoryID ) references Categories ( id )
    , primary key ( sectionID, categoryID )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2007
    Posts
    16
    r937,

    Interesting! I appreciate the insight.

    I have been aware of the concept but I've never used the foreign key ( sectionID)... lines. Do those actually connect to the Sections.id fields? meaning is there some error checking if let's say Sections.id does not contain an id that is being inserted in SectionCategories? Or does this need to be checked manually?

    stephank

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes! that's it exactly! you do not need to check it manually! the database does it for you!



    it's called relational integrity and it guarantees, for example, that you can never have a row in the referencing table with a foreign key value that is not found in the primary key of the referenced table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Aug 2007
    Posts
    16
    Wow. Excellent! That is very helpful. I think this will change my life HA HA

    Another question if you dont mind me asking.

    One problem I have already data in the database that I dont' want to refill. I am using phpmyadmin, but I can't figure out how to set the foreign keys and the references in there after the database has already been created...

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use ALTER TABLE statements to add the foreign keys

    make sure there's an index defined for each one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    In phpMyAdmin under the structure tab for the table there is a link which says "Relation view". If you hit that it will take you to a page where you can create foreign key indexes. Bear in mind in order to set these the column in the table needs to be indexed first, else it won't let you add a foreign key constraint

Posting Permissions

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