Welcome to Geeklog, Anonymous Saturday, September 07 2024 @ 08:09 pm EDT
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:
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 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!
12
11
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.
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.
![](http://www.geeklog.net/images/smiles/twocents.gif)
11
10
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!
thanks again!
10
13
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.
11
8
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!
8
10
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.
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.
9
5
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!
7
8
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.
or perhaps just create the block for you if you're not sure of the code.
10
10
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.
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.
6
9
Quote
Status: Banned
machinari
Forum User
Full Member
Registered: 03/22/04
Posts: 1512
in lib-common.php find this
$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
so it will look like this
$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)
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.
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'] );
Text Formatted Code
$article->set_var( 'story_num', $A['storynumfieldname'] );//use your custom field name in the array.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'] );
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'");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
![](http://www.geeklog.net/images/smiles/pray.gif)
or I just broke your site.
![](http://www.geeklog.net/images/smiles/doh.gif)
6
8
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!
thanks again!
8
10
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?
11
9
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...
9
7
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!
Something like, "Enter an article number here: <<input form>> ... <<Go Button>>"
Thanks a lot!
8
9
Quote
All times are EDT. The time is now 08:09 pm.
- 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