SitePoint Sponsor |
|
User Tag List
Results 1 to 22 of 22
Thread: php with mysql question
-
Sep 6, 2000, 15:43 #1
- 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
-
Sep 6, 2000, 16:05 #2
- 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.
-
Sep 6, 2000, 16:09 #3
- 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
-
Sep 6, 2000, 16:17 #4
-
Sep 6, 2000, 16:58 #5
- Join Date
- Jun 2000
- Location
- Slovenia, Europe
- Posts
- 205
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
thank you very much for this
-
Sep 6, 2000, 17:35 #6
- 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
-
Sep 6, 2000, 21:27 #7
- 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.
-
Sep 6, 2000, 23:56 #8
- 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]
-
Sep 7, 2000, 15:04 #9
- Join Date
- Aug 2000
- Location
- Thailand
- Posts
- 4,810
- 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
-
Sep 7, 2000, 15:22 #10
- 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.
-
Sep 7, 2000, 16:13 #11
- 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.
-
Sep 7, 2000, 16:21 #12
- 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.
-
Sep 7, 2000, 16:30 #13
- 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.
-
Sep 7, 2000, 16:45 #14
- 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
-
Sep 7, 2000, 17:05 #15
- 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.
-
Sep 7, 2000, 17:21 #16
- 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
-
Sep 7, 2000, 17:34 #17
- 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!
-
Sep 8, 2000, 08:44 #18
- 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.
-
Sep 8, 2000, 09:45 #19
- 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
-
Sep 8, 2000, 10:34 #20
- 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.
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.
-
Sep 8, 2000, 10:55 #21
- 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.
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
-
Sep 8, 2000, 11:44 #22
Bookmarks