Welcome to Geeklog, Anonymous Saturday, March 02 2024 @ 02:23 am EST

Geeklog Forums

article numbering


zoombabwe

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

Quote by vinny:Alternatively you could just add a column to the gl_stories table that auto increments and assign values 0, 1, 2, 3, ... to your existing stories.


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!
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
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.
 Quote

zoombabwe

Anonymous
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!
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
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.
 Quote

zoombabwe

Anonymous
I had to create the field first as a key and w/o the autoincrement. then added autoincrement after and it worked.


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!
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
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.
 Quote

zoombabwe

Anonymous
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!
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
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.
 Quote

zoombabwe

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

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
in lib-common.php find this
Text Formatted Code
    $article = new Template( $_CONF['path_layout'] );
    $article->set_file( array(
            'article'          => $storytpl,
            'bodytext'         => 'storybodytext.thtml',
            'featuredarticle'  => 'featuredstorytext.thtml',
            'featuredbodytext' => 'featuredstorybodytext.thtml'
            ));

    $article->set_var( 'layout_url', $_CONF['layout_url'] );
    $article->set_var( 'site_url', $_CONF['site_url'] );
    $article->set_var( 'story_date', $A['day'] );
    $article->set_var( 'lang_views', $LANG01[106] );
    $article->set_var( 'story_hits', $A['hits'] );
    $article->set_var( 'story_id', $A['sid'] );

 
and add this variable
Text Formatted Code
    $article->set_var( 'story_num', $A['storynumfieldname'] );//use your custom field name in the array.

 
so it will look like this
Text Formatted Code
    $article = new Template( $_CONF['path_layout'] );
    $article->set_file( array(
            'article'          => $storytpl,
            'bodytext'         => 'storybodytext.thtml',
            'featuredarticle'  => 'featuredstorytext.thtml',
            'featuredbodytext' => 'featuredstorybodytext.thtml'
            ));

    $article->set_var( 'layout_url', $_CONF['layout_url'] );
    $article->set_var( 'site_url', $_CONF['site_url'] );
    $article->set_var( 'story_date', $A['day'] );
    $article->set_var( 'lang_views', $LANG01[106] );
    $article->set_var( 'story_num', $A['storynumfieldname'] );//use your custom field name in the array.
    $article->set_var( 'story_hits', $A['hits'] );
    $article->set_var( 'story_id', $A['sid'] );


 
if 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)
Text Formatted Code
        $result = DB_query ("SELECT sid,uid,tid,title,introtext,bodytext,hits,comments,featured,draft_flag,show_topic_icon,commentcode,postmode,owner_id,group_id,perm_owner,perm_group,perm_members,perm_anon,unix_timestamp(date) AS day FROM {$_TABLES['stories']} WHERE sid = '$story'");

 
change to
Text Formatted Code
        $result = DB_query ("SELECT *,unix_timestamp(date) AS day FROM {$_TABLES['stories']} WHERE sid = '$story'");

 

now your number should appear in your page wherever you put the variable in the template file.
or I just broke your site.
 Quote

zoombabwe

Anonymous
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!
 Quote

zoombabwe

Anonymous
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?
 Quote

Status: offline

miklos

Forum User
Newbie
Registered: 09/16/04
Posts: 9
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!
 Quote

mach

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

Status: offline

miklos

Forum User
Newbie
Registered: 09/16/04
Posts: 9
Ah yes! Quite right. Thanks.
 Quote

Status: offline

miklos

Forum User
Newbie
Registered: 09/16/04
Posts: 9
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!
 Quote

All times are EST. The time is now 02:23 am.

  • Normal Topic
  • Sticky Topic
  • Locked Topic
  • New Post
  • Sticky Topic W/ New Post
  • Locked Topic W/ New Post
  •  View Anonymous Posts
  •  Able to post
  •  Filtered HTML Allowed
  •  Censored Content