Re: [PHP] Re: Incremental Date Based ID
I think I have it all figured out now, so as promised, here is the code. Thanks everyone for your suggestions. $id_query = "select id from WHERE id LIKE '$post_date%' ORDER BY DESC LIMIT 1 "; $id_results = mysql_query($id_query,$conn); $id_row = mysql_fetch_array($id_results); $id_num_rows = mysql_num_rows($id_results); if ($id_num_rows != "0") { $id_last = $id_row["id"]; $id_letter = ord(ltrim($id_last,"\0..9,-")); $new_id = $post_date; $new_id .= chr($id_letter+1); } else { $new_id = $post_date; $new_id .= "a"; } So, it calls the database, gets the last rows with this date as part of the id (2006-05-08d), strips out the date part, adds one to the letter and outputs back the next ID (2006-05-08e). On Mar 8, 2006, at 8:39 AM, Kevin Murphy wrote: Thanks for the info, but I think you are misunderstanding what I am trying to do. I am simply trying to create a sequential ID based on a Date and a Letter (or number, if I have to) field that will be used as part of the link. After they are created, they will not change. And Its not about how they appear on the page in the HTML (I know how to make those be sequential in that way), I am more concerned with the ID making them unique. And yes, people do care about the way the link works. People, say for example, like my bosses, whom I am trying to accommodate. :-) And some SEO people will tell you that the link does indeed matter when doing that kind of stuff. I think I have figured it out using a combination of MAX and Limit to check to see what the last one is and then write the next one. Thanks all for your help. If I figure it out, I will post the code for future reference. On Mar 8, 2006, at 12:00 AM, Paul Novitski wrote: At 05:05 PM 3/7/2006, Kevin Murphy wrote: Well, part of the issue is that I want to be able to use this as part of the link: /news.php?article=2006-03-05a /news.php?article=2006-03-05b With respect, I too think you should re-examine your reasons for wanting to number (or letter) the links consecutively in the database. To whom does the spelling of an URL really matter? I believe that website visitors rarely care; they care about how things are labelled in the foreground and they want to get to the correct page, but I don't think many people really study the details of the address bar and even fewer make judgements about the quality or completeness of website content on that basis. There are in fact solid reasons for NOT changing URLs once they're established -- for example, the persistence of bookmarks, links from other websites, and search engine memory. Once you establish an URL pointing to a particular page it's going to be recorded, stored, and repeated by many other entities on the internet. If you're whimsically changing URLs at random intervals, old URLs will no longer point to the same content. You'll be doing your own material a disservice by frustrating one of the most powerful assets the internet can lend you: its persistence of vision. I wonder if your desire for contiguously lettered URLs can be satisfied by simply assigning consecutive letters to the display text that links to the articles: HTML: Title of article Title of article Title of article CSS: ol { list-style-type: lower-alpha; } RESULT: a. Title of article b. Title of article c. Title of article As others have pointed out, the sequence in which the URLs spill out from the database can be controlled by a timestamp in each record. The lettering of the articles a-z can be controlled by the ordered list for the benefit of the visitor. When an article is inserted into or deleted from the list, the list retains its contiguity IN THE PRESENTATION and you aren't put in the inelegant position of trying to renumber records in a database. Regards, Paul -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Incremental Date Based ID
Thanks for the info, but I think you are misunderstanding what I am trying to do. I am simply trying to create a sequential ID based on a Date and a Letter (or number, if I have to) field that will be used as part of the link. After they are created, they will not change. And Its not about how they appear on the page in the HTML (I know how to make those be sequential in that way), I am more concerned with the ID making them unique. And yes, people do care about the way the link works. People, say for example, like my bosses, whom I am trying to accommodate. :-) And some SEO people will tell you that the link does indeed matter when doing that kind of stuff. I think I have figured it out using a combination of MAX and Limit to check to see what the last one is and then write the next one. Thanks all for your help. If I figure it out, I will post the code for future reference. On Mar 8, 2006, at 12:00 AM, Paul Novitski wrote: At 05:05 PM 3/7/2006, Kevin Murphy wrote: Well, part of the issue is that I want to be able to use this as part of the link: /news.php?article=2006-03-05a /news.php?article=2006-03-05b With respect, I too think you should re-examine your reasons for wanting to number (or letter) the links consecutively in the database. To whom does the spelling of an URL really matter? I believe that website visitors rarely care; they care about how things are labelled in the foreground and they want to get to the correct page, but I don't think many people really study the details of the address bar and even fewer make judgements about the quality or completeness of website content on that basis. There are in fact solid reasons for NOT changing URLs once they're established -- for example, the persistence of bookmarks, links from other websites, and search engine memory. Once you establish an URL pointing to a particular page it's going to be recorded, stored, and repeated by many other entities on the internet. If you're whimsically changing URLs at random intervals, old URLs will no longer point to the same content. You'll be doing your own material a disservice by frustrating one of the most powerful assets the internet can lend you: its persistence of vision. I wonder if your desire for contiguously lettered URLs can be satisfied by simply assigning consecutive letters to the display text that links to the articles: HTML: Title of article Title of article Title of article CSS: ol { list-style-type: lower-alpha; } RESULT: a. Title of article b. Title of article c. Title of article As others have pointed out, the sequence in which the URLs spill out from the database can be controlled by a timestamp in each record. The lettering of the articles a-z can be controlled by the ordered list for the benefit of the visitor. When an article is inserted into or deleted from the list, the list retains its contiguity IN THE PRESENTATION and you aren't put in the inelegant position of trying to renumber records in a database. Regards, Paul -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Incremental Date Based ID
hello there, i had to deal with such an issue for labeling job-tickets -– (yymmdd.nn) so maybe try this... // today‘s date : "yymmdd" $use_akt_Datum = date("ymd"); // how many jobs (artikels) are there today ? $HeutigeAuftraegeSQL = "SELECT * FROM auftrag WHERE Auftragsnummer LIKE '".$use_akt_Datum."%' ORDER BY Auftragsnummer ASC;"; $mySQL_Link->query($HeutigeAuftraegeSQL); for ($ai = 0; $ai < $mySQL_Link->rows; $ai++) { $ANs[$ai] = $mySQL_Link->fetch($ai); } $at = explode(".", $ANs[$ai-1]["Auftragsnummer"],2); // this gets the last sub-id $aktuellerAuftrag = $at[1] + 1; // incr last sub-id // build new job(article)-id $aktuelleAuftragsNR = $use_akt_Datum.".".sprintf("%02d", $aktuellerAuftrag); // check if this new id might allready exists... .. .. hope that helps... bb >At 05:05 PM 3/7/2006, Kevin Murphy wrote: >>Well, part of the issue is that I want to be able to use this as part >>of the link: >> >>/news.php?article=2006-03-05a >>/news.php?article=2006-03-05b > > >With respect, I too think you should re-examine your reasons for >wanting to number (or letter) the links consecutively in the database. > >To whom does the spelling of an URL really matter? I believe that >website visitors rarely care; they care about how things are labelled >in the foreground and they want to get to the correct page, but I >don't think many people really study the details of the address bar >and even fewer make judgements about the quality or completeness of >website content on that basis. > >There are in fact solid reasons for NOT changing URLs once they're >established -- for example, the persistence of bookmarks, links from >other websites, and search engine memory. Once you establish an URL >pointing to a particular page it's going to be recorded, stored, and >repeated by many other entities on the internet. If you're >whimsically changing URLs at random intervals, old URLs will no >longer point to the same content. You'll be doing your own material >a disservice by frustrating one of the most powerful assets the >internet can lend you: its persistence of vision. > >I wonder if your desire for contiguously lettered URLs can be >satisfied by simply assigning consecutive letters to the display text >that links to the articles: > >HTML: > >Title of article >Title of article >Title of article > >CSS: >ol >{ >list-style-type: lower-alpha; >} > >RESULT: >a. Title of article >b. Title of article >c. Title of article > >As others have pointed out, the sequence in which the URLs spill out >from the database can be controlled by a timestamp in each >record. The lettering of the articles a-z can be controlled by the >ordered list for the benefit of the visitor. When an article is >inserted into or deleted from the list, the list retains its >contiguity IN THE PRESENTATION and you aren't put in the inelegant >position of trying to renumber records in a database. > >Regards, >Paul > >-- >PHP General Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php > > Björn Bartels -Development/IT-Services- -- dbusiness.de gmbh digital business & printing gmbh Greifswalder Str. 152 D-10409 Berlin Fon: [0.30] 4.21.19.95 Fax: [0.30] 4.21.19.74 www.dbusiness.de [EMAIL PROTECTED] ftp://dbusiness.dyndns.org Björn Bartels -Development/IT-Services- -- dbusiness.de gmbh digital business & printing gmbh Greifswalder Str. 152 D-10409 Berlin Fon: [0.30] 4.21.19.95 Fax: [0.30] 4.21.19.74 www.dbusiness.de [EMAIL PROTECTED] ftp://dbusiness.dyndns.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Incremental Date Based ID
At 05:05 PM 3/7/2006, Kevin Murphy wrote: Well, part of the issue is that I want to be able to use this as part of the link: /news.php?article=2006-03-05a /news.php?article=2006-03-05b With respect, I too think you should re-examine your reasons for wanting to number (or letter) the links consecutively in the database. To whom does the spelling of an URL really matter? I believe that website visitors rarely care; they care about how things are labelled in the foreground and they want to get to the correct page, but I don't think many people really study the details of the address bar and even fewer make judgements about the quality or completeness of website content on that basis. There are in fact solid reasons for NOT changing URLs once they're established -- for example, the persistence of bookmarks, links from other websites, and search engine memory. Once you establish an URL pointing to a particular page it's going to be recorded, stored, and repeated by many other entities on the internet. If you're whimsically changing URLs at random intervals, old URLs will no longer point to the same content. You'll be doing your own material a disservice by frustrating one of the most powerful assets the internet can lend you: its persistence of vision. I wonder if your desire for contiguously lettered URLs can be satisfied by simply assigning consecutive letters to the display text that links to the articles: HTML: Title of article Title of article Title of article CSS: ol { list-style-type: lower-alpha; } RESULT: a. Title of article b. Title of article c. Title of article As others have pointed out, the sequence in which the URLs spill out from the database can be controlled by a timestamp in each record. The lettering of the articles a-z can be controlled by the ordered list for the benefit of the visitor. When an article is inserted into or deleted from the list, the list retains its contiguity IN THE PRESENTATION and you aren't put in the inelegant position of trying to renumber records in a database. Regards, Paul -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Incremental Date Based ID
At 09:05 PM 3/7/2006, Kevin Murphy wrote: Well, part of the issue is that I want to be able to use this as part of the link: /news.php?article=2006-03-05a /news.php?article=2006-03-05b which i will eventually do a htacess rewrite to make it look like /news/2006-03-05a.php /news/2006-03-05a.php I don't think I can do that with just the Unix timestamp. Why not - treat the number as a string and assign that to the file. I was under the impression you were storing the articles in a database. Alternately, create a directory for each year, one for each month within the year, one for each day within the month, then sequence alphabetically within the day. Does this resemble the structure of the MS knowledgebase? Yep! The first year Allnovascotia.com was published we did something like that, although the articles just had regular titles. In any event - have fun. Cheers - Miles PS Convention in this group is to bottom post. /mt -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Incremental Date Based ID
Should have read the whole thread first, I guess... :) Gerry http://groups.yahoo.com/group/php_and_mysql/ On 3/7/06, Shaunak Kashyap <[EMAIL PROTECTED]> wrote: > A possible solution: > > Make a composite primary key where the first field is the date and the > second field would be of type enumeration (A..Z, in ascending order). > Then use MAX() WHERE = to > figure out the next primary key. > > Shaunak Kashyap -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Incremental Date Based ID
Kevin, You can do it with a Unix timestamp, but that would just confuse the issue. Have you considered using two fields instead of one? An article ID and and article sub-ID would be retrieved as article ID = date, and all sub-ID's descending. The first record in the list would contain the highest sub-ID, irregardless of gaps. Gerry http://groups.yahoo.com/group/php_and_mysql/ On 3/7/06, Kevin Murphy <[EMAIL PROTECTED]> wrote: > Well, part of the issue is that I want to be able to use this as part > of the link: > > /news.php?article=2006-03-05a > /news.php?article=2006-03-05b > > which i will eventually do a htacess rewrite to make it look like > > /news/2006-03-05a.php > /news/2006-03-05a.php > > I don't think I can do that with just the Unix timestamp. > > On Mar 7, 2006, at 4:56 PM, Al wrote: > > > Kevin Murphy wrote: > >> I'm trying to set up an ID field that works like this for news > >> articles that are posted to a website. > >> 2006-03-05a > >> 2006-03-05b > >> I know how to generate the date, and I am pretty sure I can > >> generate the letter code based on counting the number of rows and > >> then assigning the next letter (we will never have more than 26 in > >> a day... usually its closer to 1 or 2 per day). > >> The problem is if there has been something deleted. > >> 2006-03-05a > >> 2006-03-05c > >> If I then Count the rows with this date I get an answer of 2, and > >> so the next letter should be "c" but there already is a "c" > >> because "b" got deleted. > >> So, is there any way of generating this style ID number > >> automatically? > >> --Kevin Murphy > >> Webmaster - Information and Marketing Services > >> Western Nevada Community College > >> www.wncc.edu > >> (775) 445-3326 > > > > > > Why not simply use the Unix time stamp. time() If more than one can > > arrive within the same second, append a letter. > > > > If users need to see the key, use date() to decode it for them > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Gerry http://portal.danen.org/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Re: Incremental Date Based ID
A possible solution: Make a composite primary key where the first field is the date and the second field would be of type enumeration (A..Z, in ascending order). Then use MAX() WHERE = to figure out the next primary key. Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. > -Original Message- > From: Kevin Murphy [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 07, 2006 5:06 PM > To: php-general@lists.php.net > Subject: Re: [PHP] Re: Incremental Date Based ID > > Well, part of the issue is that I want to be able to use this as part > of the link: > > /news.php?article=2006-03-05a > /news.php?article=2006-03-05b > > which i will eventually do a htacess rewrite to make it look like > > /news/2006-03-05a.php > /news/2006-03-05a.php > > I don't think I can do that with just the Unix timestamp. > > On Mar 7, 2006, at 4:56 PM, Al wrote: > > > Kevin Murphy wrote: > >> I'm trying to set up an ID field that works like this for news > >> articles that are posted to a website. > >> 2006-03-05a > >> 2006-03-05b > >> I know how to generate the date, and I am pretty sure I can > >> generate the letter code based on counting the number of rows and > >> then assigning the next letter (we will never have more than 26 in > >> a day... usually its closer to 1 or 2 per day). > >> The problem is if there has been something deleted. > >> 2006-03-05a > >> 2006-03-05c > >> If I then Count the rows with this date I get an answer of 2, and > >> so the next letter should be "c" but there already is a "c" > >> because "b" got deleted. > >> So, is there any way of generating this style ID number > >> automatically? > >> --Kevin Murphy > >> Webmaster - Information and Marketing Services > >> Western Nevada Community College > >> www.wncc.edu > >> (775) 445-3326 > > > > > > Why not simply use the Unix time stamp. time() If more than one can > > arrive within the same second, append a letter. > > > > If users need to see the key, use date() to decode it for them > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Incremental Date Based ID
Kevin Murphy wrote: Well, part of the issue is that I want to be able to use this as part of the link: /news.php?article=2006-03-05a /news.php?article=2006-03-05b which i will eventually do a htacess rewrite to make it look like /news/2006-03-05a.php /news/2006-03-05a.php I don't think I can do that with just the Unix timestamp. That doesn't answer the question about why you care about gaps or why you need the number of posts per day. On Mar 7, 2006, at 4:56 PM, Al wrote: Kevin Murphy wrote: I'm trying to set up an ID field that works like this for news articles that are posted to a website. 2006-03-05a 2006-03-05b I know how to generate the date, and I am pretty sure I can generate the letter code based on counting the number of rows and then assigning the next letter (we will never have more than 26 in a day... usually its closer to 1 or 2 per day). The problem is if there has been something deleted. 2006-03-05a 2006-03-05c If I then Count the rows with this date I get an answer of 2, and so the next letter should be "c" but there already is a "c" because "b" got deleted. So, is there any way of generating this style ID number automatically? --Kevin Murphy Webmaster - Information and Marketing Services Western Nevada Community College www.wncc.edu (775) 445-3326 Why not simply use the Unix time stamp. time() If more than one can arrive within the same second, append a letter. If users need to see the key, use date() to decode it for them -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Incremental Date Based ID
Well, part of the issue is that I want to be able to use this as part of the link: /news.php?article=2006-03-05a /news.php?article=2006-03-05b which i will eventually do a htacess rewrite to make it look like /news/2006-03-05a.php /news/2006-03-05a.php I don't think I can do that with just the Unix timestamp. On Mar 7, 2006, at 4:56 PM, Al wrote: Kevin Murphy wrote: I'm trying to set up an ID field that works like this for news articles that are posted to a website. 2006-03-05a 2006-03-05b I know how to generate the date, and I am pretty sure I can generate the letter code based on counting the number of rows and then assigning the next letter (we will never have more than 26 in a day... usually its closer to 1 or 2 per day). The problem is if there has been something deleted. 2006-03-05a 2006-03-05c If I then Count the rows with this date I get an answer of 2, and so the next letter should be "c" but there already is a "c" because "b" got deleted. So, is there any way of generating this style ID number automatically? --Kevin Murphy Webmaster - Information and Marketing Services Western Nevada Community College www.wncc.edu (775) 445-3326 Why not simply use the Unix time stamp. time() If more than one can arrive within the same second, append a letter. If users need to see the key, use date() to decode it for them -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Incremental Date Based ID
Kevin Murphy wrote: I'm trying to set up an ID field that works like this for news articles that are posted to a website. 2006-03-05a 2006-03-05b I know how to generate the date, and I am pretty sure I can generate the letter code based on counting the number of rows and then assigning the next letter (we will never have more than 26 in a day... usually its closer to 1 or 2 per day). The problem is if there has been something deleted. 2006-03-05a 2006-03-05c If I then Count the rows with this date I get an answer of 2, and so the next letter should be "c" but there already is a "c" because "b" got deleted. So, is there any way of generating this style ID number automatically? --Kevin Murphy Webmaster - Information and Marketing Services Western Nevada Community College www.wncc.edu (775) 445-3326 Why not simply use the Unix time stamp. time() If more than one can arrive within the same second, append a letter. If users need to see the key, use date() to decode it for them -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php