Geeklog Forums

Hi, sorry about the duplicate post, but I thought the subject matter was more appropriate for the "General Help" forum:



How should this be done in Geeklog 1.3.9sr1? I tried the following statement:

ALTER TABLE gl_stories ADD COLUMN article_number SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT;

and I got the following error:

ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key

So how should I add this auto-incrementing column to the gl_stories table, starting at number 1 and going up?

Thanks for any advice!

I think, but i'm not sure, that only your primary key can auto increment and it is SID for that table.
that means that you can not add another auto increment field in that table.
but maybe you could add a unique key that doesn't auto increment, and then just supply the number yourself via a script that identifies the row number of the last story and updates the column with that number. or something like that.

hmm, thanks for your reply, machinari...I agree that the SID is the primary key, I think, but I don't believe it is auto-incrementing...I wonder how Vinny, who originally proposed the idea, thought it should be done.

thanks again!

no you're right it isn't auto incrementing, but the point was that only primary keys can auto increment. anyway, that's wrong, not only primary can auto increment, but the column still must be a defined as a key--looked it up. I tried it and it worked. but I had to create the field first as a key and w/o the autoincrement. then added autoincrement after and it worked. not sure why. not much help there i guess.

Oh, so you got it to work? Did you do this on the gl_stories table? Could you post what SQL command/commands you used?

Thanks a lot machinari for taking the time to check this out!

here is what I did:
1st--
ALTER TABLE `gl_stories` ADD `testing` TINYINT UNSIGNED NOT NULL ;
2nd (remember, autoincrement only works on keys)--
ALTER TABLE `gl_stories` ADD INDEX ( `testing` ) ;
3rd--
ALTER TABLE `gl_stories` CHANGE `testing` `testing` TINYINT( 3 ) UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT

now all your stories are numbered.

note: when making a field that already exists into a key, as above, you cant use UNIQUE, cuz you have all zero values--not unique--so you must use INDEX, or something else instead. After you add the autoincrement, you can change to unique if you wish depending on your needs.

hope that helps.

wow, thanks machinari, that worked beautifully. now i just have to figure out how to make a block that displays the number...anyway, thanks again!

tell us exactly what you are trying to accomplish and perhaps we, someone, can give you a code tip.
or perhaps just create the block for you if you're not sure of the code.

well, what i would really like is to have a variable to use in my "storytext.thtml" like {story_number} , which would be retrieved from that auto-increment column you made for me in gl_stories .

Either that or in a block - I've been trying that without much luck, as I'm just starting to get familiar with how blocks work. And making a new variable is, unfortunately, totally beyond my understanding at this point...

I'd love any tips on code to use for this DB query...thanks a lot for paying attention, machinari, you're awesome.

in lib-common.php find thisand add this variableso it will look like thisif your article is called from index.php this will work. just use the {story_num} in your template file. but if your article is called from article.php, you need to make another change. the difference is that in index.php the sql statement retrieves "all" the fields in the table "gl_stories" using *, but in article.php the fields are explicitly named in the sql statement. that means that you have to add your new table name to the statement or, easier, replace all the table names with * except where the table is using an AS clause. so instead of (at about line 92) change to
now your number should appear in your page wherever you put the variable in the template file.
or I just broke your site.

wow! that's all i can say. outstanding. it worked. my gratitude to you, machinari! i learned a lot about geeklog here too, so maybe next time i'll be the one helping someone else with Geeklog....or maybe in a few years....

thanks again!

i'm SOOO close...all i need now is for the number to stick. for instance, if a number gets assigned to a story, and then the story is subsequently edited, the number ticks up again. (if i edit story 1, and my most recently added story is story 10, the edited story 1 becomes story 11.) does anyone know of a simple SQL way to do this?

Any chance this tip could be update for 1.3.11sr1? The correct bit in lib-common.php doesn't seem to be there anymore, and I can't figure out how to create the story_num variable. Thanks!

i think all that stuff that needs to be edited in order for this to work is now found in lib-story.php...

Ah yes! Quite right. Thanks.

So, I've got my articles numbered as described in this great thread. Does anyone have any advice regarding how I would I go about making a PHP block which had an input box, into which one could type an article number, and get transported to the article?

Something like, "Enter an article number here: <<input form>> ... <<Go Button>>"

Thanks a lot!