php with mysql question

[mh8759]

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

[freddydoesphp]

mysql_query("select * from tablename order by ID desc");

[mh8759]

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

[Hierophant]

mysql_query("select * from tablename order by ID desc limit 1");

[mh8759]

thank you very much for this

[Kevin Yank]

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]

[freddydoesphp]

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");

[Hierophant]

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.

[TheOriginalH]

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...

[TWTCommish]

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.

[Karl]

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).

[TWTCommish]

Yeah, I know there's a better way to do it than my way...just don't know how else.

[freddydoesphp]

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.

[mh8759]

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

[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.

[adam_shep]

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.

[TWTCommish]

Not if you don't specify that field in the UPDATE command it doesn't!

[Hierophant]

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.

[Kevin Yank]

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.

[Hierophant]

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.

[Kevin Yank]

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.

[Hierophant]

I do agree with you there.