SitePoint Sponsor

User Tag List

Results 1 to 22 of 22

Hybrid View

  1. #1
    SitePoint Addict mh8759's Avatar
    Join Date
    Jun 2000
    Location
    Slovenia, Europe
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a script which helps me to put new web articles in database. I have another script to connect to the database and collect out these articles. How do I write MySql query or PHP code to pull out the latest articles that was added? So I want to take out the last article(the one with the highest ID number), but not knowing how many articles are there in the database at that moment. Is there somekind of function that has been implemented in PHP or mysql that does just that?

    Thanks
    Mare

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql_query("select * from tablename order by ID desc");
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Addict mh8759's Avatar
    Join Date
    Jun 2000
    Location
    Slovenia, Europe
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes this will probably order IDs in desceding way, however will this select only the last addition to the database(the top one)? I think it will select all the articles, but in desceding order.

    Mare

  4. #4
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    mysql_query("select * from tablename order by ID desc limit 1");
    Wayne Luke
    ------------


  5. #5
    SitePoint Addict mh8759's Avatar
    Join Date
    Jun 2000
    Location
    Slovenia, Europe
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you very much for this

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Note that the automatically assigned ID is not a reliable way to identify the most recently inserted article. If you delete an older article, for example, the next new article to be added will (depending on the version of MySQL you are using) take over its ID, rather than being assigned a new, higher ID that all other entries in the table.

    If you want to be able to sort entries by their insertion date, you'll have to store that date in a column of the table.

    [Edited by kyank on 09-06-2000 at 10:29 PM]
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good Call! Or you could just make everything easier and use phplib and the db_sequence table to get the next available id number and you will never find it taking a deleted item's id number. phplib even has a built in function for retrieving it $db->nextid("sequencename");
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by kyank
    Note that the automatically assigned ID is not a reliable way to identify the most recently inserted article. If you delete an older article, for example, the next new article to be added will (depending on the version of MySQL you are using) take over its ID, rather than being assigned a new, higher ID that all other entries in the table.

    If you want to be able to sort entries by their insertion date, you'll have to store that date in a column of the table.

    [Edited by kyank on 09-06-2000 at 10:29 PM]
    That is interesting to know. I have never used MySQL and automatically assigned ID's do not work that way in any of the DBMS that I have used. Those include Oracle 8i, SQL Server and Sybase.
    Wayne Luke
    ------------


  9. #9
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,811
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    bugger...
    I am just getting to grips with my first mySQL database, modelling it from a fairly complex MSaccess one I designed a while back. The equivalent (autonumber) property never "goes back on itself" - how do you stop mySQL doing that or can you not?
    hmmm...redesign may be in order...
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  10. #10
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Kevin's right...you'll need a date field. I actually have two...one for "date" which I usually use the CURDATE function for...but I also use another. CURDATE comes in the format of 200-09-07, or something like that...I have another called "datetext" which uses the getdate() command to select it in a nicer format (like "September 7, 2000") automatically.

  11. #11
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I usually use a Unix Timestamp for the date for insertion IDs, PHP can then easily cope with converting it to what date format you want after you retrieve it.

    Note though that a Unix timestamp is not good for dates before 1970 (The Unix Epoch).
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  12. #12
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I know there's a better way to do it than my way...just don't know how else.

  13. #13
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does anyone use phplib? It makes life so much easier, and again you can get a guaranteed autoincrement not one that reverts back to one that has been deleted, and you won't have tpo go adding any fields to your table.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  14. #14
    SitePoint Addict mh8759's Avatar
    Join Date
    Jun 2000
    Location
    Slovenia, Europe
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what is this phplib anyway? sorry for asking, but i really don't have time to check it out and read it through in the PHP manual. If you would be so nice and explain it a little..what does this library(or whatever it is) offer?

    Mare

  15. #15
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it is a libaray that offers everything from session management to databse queries. You basically setup a db object and it does everything for you for instance to query a table and get some results you would just use for instance:

    $db->query("SELECT * from tablename");
    while ($db->next_record()) {
    print $db->f(id);
    print $db->f(name);
    }

    It is a great tool and like I was saying before there will always be that problem of auto-incrementing id fields and it reverting back to the old id numbers if one gets deleted, but the libraray has a db_sequence table that gets cretaed and you can have unlimited sequences in it, then you can get the next id in the sequence by using
    $db->nextid("sequencename");
    I use that for my auto incrementing id field so I will always get a new number that is one higher than the last. It is simple to use, you should really take the time to check it out.

    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  16. #16
    SitePoint Zealot
    Join Date
    May 2000
    Location
    Eugene, OR
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Karl
    I usually use a Unix Timestamp for the date for insertion IDs
    But if you modify a record, doesn't it get a new Timestamp? So even though it may be an old entry, it looks like its brand new.


  17. #17
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not if you don't specify that field in the UPDATE command it doesn't!

  18. #18
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    Well it is a libaray that offers everything from session management to databse queries. You basically setup a db object and it does everything for you for instance to query a table and get some results you would just use for instance:

    $db->query("SELECT * from tablename");
    while ($db->next_record()) {
    print $db->f(id);
    print $db->f(name);
    }

    It is a great tool and like I was saying before there will always be that problem of auto-incrementing id fields and it reverting back to the old id numbers if one gets deleted, but the libraray has a db_sequence table that gets cretaed and you can have unlimited sequences in it, then you can get the next id in the sequence by using
    $db->nextid("sequencename");
    I use that for my auto incrementing id field so I will always get a new number that is one higher than the last. It is simple to use, you should really take the time to check it out.

    So this PHPLIB makes mySQL work like Oracle in regards to sequences. That is a very good thing to know.
    Wayne Luke
    ------------


  19. #19
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Okay, I've done some more research into this and have found that MySQL does, in fact, assign autoincrement ID's by going one larger than the highest ID. So in most situations this should be a pretty safe way of putting database items in order of insertion, with the following caveats:

    • If you specify a value for the autoincrement column in an INSERT/UPDATE query, you can create out-of-sequence entries in the table. Why you would intentionally do this is beyond me, but be aware of the possibility if anyone has direct query access to the database.
    • In versions of MySQL up to 3.23, if you delete the table entry with the highest ID, then that ID will be reused the next time an autoincrement value is generated. In most cases this will not affect order-of-insertion logic based on the column value, but keep it in mind just in case! MySQL 3.23 and later does not reuse autoincrement values at all. If 200 is the highest ID in the table and you delete that entry, the next inserted entry will still get an ID of 201. The only exception is if you delete all records in the table using "DELETE FROM tblName", in which case the count is reset to 1.


    My recommendation of using a date column for sorting entries according to insertion date stands, though. PRIMARY KEY columns, in good database design, should not be used for anything other than providing a unique ID for every entry in the table, and relating those entries to other tables based on that value.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  20. #20
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by kyank
    PRIMARY KEY columns, in good database design, should not be used for anything other than providing a unique ID for every entry in the table, and relating those entries to other tables based on that value.
    That may be true but in real world applications it is not always possible sometimes the primary key is the only unique field or combination of fields in the database and the only way to pull information from the database.

    Our live system has 200,000 records from the last month. They are tracked by the Primary Key. Our warehouse system has over 2 million records including multiple records for each customer depending on how many times they renew their contracts. In the data warehouse the primary key consists of two fields (CustomerCD and DatasourceCD) both of which are needed singularly for other calculations and procedures as well as making sure police or fire departments are programmatically dispatched to the proper location when needed.

    The new system we are converting to has over 900 tables in it to maintain everything from building locations to employees to whether the customer has 2 dogs or 3 cats. At conversion it will contain 2 million records and grow from there.
    Wayne Luke
    ------------


  21. #21
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    You're right, Wayne. In some cases it just makes more sense to abandon the ideals of pure database design for practical reasons; however, it is not my role to recommend such compromises when they are not necessary.

    That may be true but in real world applications it is not always possible sometimes the primary key is the only unique field or combination of fields in the database and the only way to pull information from the database.
    Yes, but this is a 'problem' that could have been corrected at the database design stage. Nothing says you couldn't just add a new column to act as a unique identifier, make it the primary key, and just create unique indexes for the other columns that require uniqueness.

    I'm aware that I'm being somewhat anal about this, but there's something to be said for learning the 'textbook' way of doing things. With a certain amount of experience (from which both Wayne and I benefit), these rules become less hard-and-fast. Specific instances can be spotted, for example, where it's safe to use one or more data-carrying columns as a primary key to cut back on the number of columns in a table. But following the rule of thumb "always create an ID column and don't use it for anything outside of the database" will prevent you from getting into trouble if you're still learning.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  22. #22
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I do agree with you there.
    Wayne Luke
    ------------



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
  •