Adding an extra field to Post, and make that available in Display.

[darrenbeige]

I'm wanting to add a dropdown field to Post.template.php. I can generate the HTML and add it to Post.template, that's all fine.

What I am unsure about is how to then retrieve that data, when anyone is viewing the topic.

How can I do this? If possible, I'd love for the field to be editable afterwards, and via JS quick edit.

NOTE: I want it per-topic, not per-post, so I assume the topics table is something to do with this.

[Arantor]

You have to pick up the data in the Post.php file, where the rest of the form is grabbed.

Yes, it'll go into the topics table, meaning you'll have to expand on that table too somewhere.

Then you'll also have to modify Display.php to capture it when displaying the thread, plus Display.template.php to display whatever it is.

More details would probably useful.

[darrenbeige]

Sorry for not being clearer, but I'm trying to do this myself. I'll probably fail (and will then have the SMF community to fall back on) but for the time being, thanks Arantor.

[darrenbeige]

Well, I haven't got very far, and now I'm stuck. I've managed to hook into my db, and run my query, which returns an array of data. I use this array to create a select box which is now present and only displayed when starting a new topic (by checking if $topic is empty). The select is named 'related_article'.

That's how far I've got. I don't really know how to continue. Guidance is appreciated. From what I can see, I think I need to add another element to $topicOptions, and edit createPost() to accept the new option. Update the smf_topics to include an extra column (related_article). Then, I need to update some query in Display.php to make the added data available to Display.template.php.

Thanks,

[Arantor]

That's basically what you would do. Unfortunately I don't have access to the source to experiment as I'm not at home until next week

[darrenbeige]

No problem. I appreciate help at all. Should I be adding the new column to smf_topics or smf_messages?

[Arantor]

Depends on what the field represents?

If it's applicable to the entire topic, which it sounds like it is (given it's going in $topicOptions), it goes in smf_topics.

[darrenbeige]

Yeah its a per topic thing. Whilst other users are able to 'edit' the data (outside of Post.template.php), the data applies to the entire topic, not per message. In a way, its kinda like a very customized tagging system. (I know there is a mod for tagging posts, but I looked at it - modifying it to do what I wanna do would probably take longer than doing it this way, and this way I learn too)

What do you think is the best way of storing a list of data (eg array("item1", "item2", "item3")) in a mysql field? Serialization?

[Arantor]

Depends on the type of data it is, exactly. Is it a predefined list of tags?

[darrenbeige]

Sort of. The user can only pick from a list of different 'tags', but the list will grow in size as the content database increases in size. Equally, for manipulation purposes, I think leaving it as a serialized array is the easiest idea - I can then access the original array pretty easily.

It might help in the future if I explain what I am actually building. In a nutshell, its a system where a user can assign forum topics to content articles that exist on my main site. Therefore, in a section at the top of a topic, users can click through to the article (or articles) the topic is discussing (if any). By doing it like this, it also means I can perform a DB query from within my main CMS (handwritten), and display a list of related forum topics in the articles info.

In essence, what is being stored in the DB is basically an array of ids which correspond to the relevant articles, which will be parsed through the DB to retrieve titles etc.

[Arantor]

If you're doing that, don't even bother serializing it. Just create a table of tags, that contains the topic number and the tag - one row per tag.

Then when you query, you just query that table where the topic id matches.

[darrenbeige]

Its impractical. It's much easier to do it the way I'm intending. I don't know if I explained it correctly to you, but to me this seems pointless, when I can just add it to another column in the smf_topics table.

[Arantor]

Adding a text field to one of the busiest tables in SMF is widely impractical in performance terms. A straight join on primary key is much faster.

[darrenbeige]

I never really thought of performance. Thanks for pointing that out. Whats a good way of cloning a select and inserting it directly below the previous one? (Javascript obviously)

[Arantor]

Again, depends what you're trying to do.

If you mean copy a select (and give it a new name in the process, at least if you hope to store it), your best option is actually to create a new one, iterate through the contents of the existing one, then attach those to the new one, if that makes sense. Should be enough examples of doing that visible from Google (I don't have any examples I can drag out right now)

[darrenbeige]

For interest only, why would adding it to smf_topics make it so much slower? Adding one text field (varchar 60) surely can't it slow it down THAT much? Remember, I will be using this field regularly in the same places as where smf_topics are loaded anyway. Plus, whilst the fields may be modified occassionally its not going to be a majorly updated field.

For the JS clone of a select, I have a working function in Firefox, but haven't tested in any other browsers yet.

[Arantor]

Actually, yes it would, since it's still more data that has to be moved through whenever the topics table is being involved, which is in loads of places (offhand, board index, message index, thread view, recent posts, stats, who's online, print view)

Being updated regularly or not isn't so much of an issue, but the more you have in a table the slower that table by definition becomes; if it's a fixed width field (like a numeric column) it's not quite so bad, but if it's variable width it's another field MySQL has to step through to calculate length to the next row.

Where are you planning to use this? If it's just in message index and display view (and not print view), I'd suggest you keep it in its own table, especially if you build it as a true many-many relationship (i.e. tags table, topics table, topic_tags table, where only the first actually holds text in this case)

[darrenbeige]

Currently, I'm probably using this field:
        - Display.php
        - RecentPosts.php
        - MessageIndex.php (if I can make it fit nicely)
        - Print view (topics only)
        - BoardIndex.php (planning a section below RecentMessages)
        - Elsewhere in my main site : article view, category view

I've made a decision. I'm gonna stick it in smf_topics for now, and see if its THAT much slower (anymore than 1/4 of a second is an issue), then go from there.

Regarding checking if Post.template.php is a new topic or not, checking if (empty($topic)) does not work if I'm replying to a topic that has no replies already. How can I prevent this from happening?

[Arantor]

Check to see how in Post.php and Post.template.php how it gets the subject; if there's a subject on starting the template, it's a new topic invariably.

[darrenbeige]

Check to see how in Post.php and Post.template.php how it gets the subject; if there's a subject on starting the template, it's a new topic invariably.

Thats what I did. In Post.php, the value is set here:

Code Select Expand

elseif (empty($topic)) {, however running that in my code (Post.template.php) returns true if the topic has no replies. I have counteracted this by setting a variable in the elseif and checking for that variable in Post.template.php

[darrenbeige]

I have some sample code running, however it's causing an SQL error whenever I try and insert data. Echoing the query shows

Code Select Expand

INSERT INTO smf_topics (ID_BOARD, ID_MEMBER_STARTED, ID_MEMBER_UPDATED, ID_FIRST_MSG, ID_LAST_MSG, locked, isSticky, numViews, ID_POLL, related_articles) VALUES (5, 1, 1, 69, 69, 0, 0, 0, 0, 'eclipse-illusion')

and the error is

Code Select Expand


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 4
File: eclipse-illusion
Line: '")

Help is appreciated, as I can't see anything wrong with the code currently.

[Arantor]

Please can I see the entire SMF code you're using, not just the query itself?

[darrenbeige]

Code Select Expand


<?php
db_query("
INSERT INTO {$db_prefix}topics
(ID_BOARD, ID_MEMBER_STARTED, ID_MEMBER_UPDATED, ID_FIRST_MSG, ID_LAST_MSG, locked, isSticky, numViews, ID_POLL, related_articles)
VALUES ($topicOptions[board], $posterOptions[id], $posterOptions[id], $msgOptions[id], $msgOptions[id],
" . ($topicOptions['lock_mode'] === null ? '0' : $topicOptions['lock_mode']) . ', ' .
($topicOptions['sticky_mode'] === null ? '0' : $topicOptions['sticky_mode']) . ", 0, " . ($topicOptions['poll'] === null ? '0' : $topicOptions['poll']) . ', \'', !empty($topicOptions['related_article']) && count($topicOptions['related_article']) > 0 ? implode(",", array_keys($topicOptions['related_article']))) : '0', '\'")', __FILE__, __LINE__);
?>


Thats the entire db_query(), all I did was adapt the original query that was there with an additional table and value. Note that $topicOptions['related_article'] is sanitized beforehand.

What other code do you want to see?
      

[Arantor]

Well, it's kicking up because there's a mismatch of ' and " pairs somewhere in there.

Looks like:

Code Select Expand

. ', \'', !empty($topicOptions['related_article'])

Should have been

Code Select Expand

. ', \'' . !empty($topicOptions['related_article'])

[darrenbeige]

No luck, editing the code to a concanated string causes this error:

Code Select Expand

"Parse error: syntax error, unexpected ')' in /www/110mb.com/d/a/r/r/e/n/b/e/darrenbeige/htdocs/forum/Sources/Subs-Post.php on line 1627"

[Arantor]

Hmm, then there's more errors possible one too many ) given the other things there.

The original problem was that your string was being pushed into the second and third parameters.

[darrenbeige]

The original problem was that your string was being pushed into the second and third parameters.

Yeah, I realized that when you said - I noticed my syntax highlighter was reporting too many parameters.

Just found the extra ).

Code Select Expand


implode(",", array_keys($topicOptions['related_article'])))


should be

Code Select Expand


implode(",", array_keys($topicOptions['related_article']))


[darrenbeige]

New issue:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'woman-shoot-solution,eclipse-illusion,sudoku' at line 1
File: /www/110mb.com/d/a/r/r/e/n/b/e/darrenbeige/htdocs/forum/Sources/Subs-Post.php
Line: 1633

Query:

Code Select Expand

INSERT INTO smf_topics (ID_BOARD, ID_MEMBER_STARTED, ID_MEMBER_UPDATED, ID_FIRST_MSG, ID_LAST_MSG, locked, isSticky, numViews, ID_POLL, related_articles) VALUES (5, 1, 1, 71, 71, 0, 0, 0, 0, 'woman-shoot-solution,eclipse-illusion,sudoku')

Code:

Code Select Expand

<?php  
db_query("
INSERT INTO {$db_prefix}topics
(ID_BOARD, ID_MEMBER_STARTED, ID_MEMBER_UPDATED, ID_FIRST_MSG, ID_LAST_MSG, locked, isSticky, numViews, ID_POLL, related_articles)
VALUES ($topicOptions[board], $posterOptions[id], $posterOptions[id], $msgOptions[id], $msgOptions[id],
" . ($topicOptions['lock_mode'] === null ? '0' : $topicOptions['lock_mode']) . ', ' .
($topicOptions['sticky_mode'] === null ? '0' : $topicOptions['sticky_mode']) . ", 0, " . ($topicOptions['poll'] === null ? '0' : $topicOptions['poll']) . ', \''. !empty($topicOptions['related_article']) && count($topicOptions['related_article']) > 0 ? implode(",", array_keys($topicOptions['related_article'])) : '0'. '\'")', __FILE__, __LINE__);
?>

Sorry to keep bugging you Arantor, and please know that I am very grateful for your help.

[Arantor]

Weird. That error would usually be derived from not quoting a string in the query.

What you might find useful to do is sanitise the value in $topicOptions['related_article'] prior to the query, i.e. resetting it to whatever the outcome of those nested checks would be, prior to the query, then simply inserting it into the query.

[darrenbeige]

By nested checks do you mean ternary statements?

[Arantor]

Yes - but you have multiple conditions that form the ternary statement.

And I have a feeling that . might interfere in terms of precedence, meaning the closing " may not be applied properly.

[darrenbeige]

Fixed and working. BTW, it wasn't the ternary statement that was causing the issue. It was at the end of the code here:

Code Select Expand

'\'")', __FILE__, __LINE__);

which had an extra ", so the working code was:
'\')', __FILE__, __LINE__);[/code]

Thanks for your help anyway though Arantor. Next question, is how do I get this field to work in the modify, and JS modify views?

[Arantor]

True enough, but often simplifying and moving the ternary statement to outside the query statement would make it clearer.

Assuming you've modified createPost, you replicate the changes in modifyPost in Post.php. Both the main modify and inline modify should use that function.

[darrenbeige]

Thanks Arantor.



When conversing with others, I always find them taken aback that I openly recommend SMF. They exclaim that its the underdog in several respects, but for me this is the reason SMF is so brilliant: you can hook in, optimize, and customize the code so easily.

[Arantor]

I get the same reaction as you, but I think people are realising what you can do with SMF with a little effort.

It definitely has been the underdog, but most of the features in 2.0 are making the ground up, and the modding community here is growing!

[darrenbeige]

Just a little update about this. I have successfully integrated the related_article field now with new post and modify for updating the db, and have successfully brought this data for display into Display.php and MessageIndex.php. Now I am in the process of adding JSModify capability, and finishing off the integration with each of the remaining template pages. To do part of this, I am looking to create a 'Hot Articles' section. This would be an algorithmically calculated list of the top 3 'hot articles'. I have worked out a way of processing this, and it requires a rather complex DB query, and I was wondering if one of you guys could help. Basically, I need a query that will SELECT all recently active topics within the past week that do not have '0' as their value in related_articles, ORDER BY numReplies, and another query that SELECT all recently active topics within the past day that do not have '0' as their value in related_articles, ORDER BY numReplies. 

I can do most of the query its just the posted within a week/day bit which is above my head.

NOTE: 'recently active' includes new topics, and replies to old topics.

Thanks for any help anyone may be able to give,

[Arantor]

Simply, it should be a case of:

Code Select Expand

WHERE related_articles <> '0'

Or:

Code Select Expand

WHERE otherstuff AND related_articles <> '0'

[darrenbeige]

Yeah I know that, but how can I get the 'recently active' posts from the db ONLY?

[darrenbeige]

Well, I have a query for the week:

Code Select Expand


SELECT m.ID_TOPIC, m.posterTime, m.modifiedTime, m.subject, t.ID_TOPIC, t.ID_FIRST_MSG, t.related_articles FROM smf_messages as m, smf_topics as t WHERE m.ID_TOPIC = t.ID_TOPIC AND t.related_articles <> '0' AND ((CURRENT_TIMESTAMP() - 604800) > m.posterTime OR (CURRENT_TIMESTAMP() - 604800) > m.modifiedTime) ORDER BY m.ID_TOPIC DESC


and it seems to work. In reality, once plugged into my PHP application I WOULD replace the CURRENT_TIMESTAMP() calls to the server variable $_SERVER['REQUEST_TIME'] and leave the calculation to PHP, not mysql. For a day, I'm just changing 604800 to 86400 (number of seconds in a day). Does this look good to you?

[Arantor]

Ah, yes, I missed the time requirement you mentioned; that's one problem with large blocks of text, I have the occasional glaze-over and miss the more subtle points.

I don't see an issue there, though in reality I'd use time() on the PHP side and possibly account for user timezone?

[darrenbeige]

$_SERVER['REQUEST_TIME'] is tied to the user's timezone isn't it? It's generated by SMF isn't it?

[Arantor]

No, it's generated by the webserver from the HTTP request. It 'should' be right but I would never suggest relying on it. Then again this isn't a modification you're distributing, so you can afford not to worry quite so much about it if it works on your forum.

[darrenbeige]

Is it? It never seems to be generated elsewhere on my site.

[Arantor]

Anything in $_SERVER should be generated by the server as per the PHP manual for $_SERVER, though I can well imagine SMF populating it on versions < 5.1.

But generally, time() will have exactly the same value in anyway.

[darrenbeige]

If it's already stored in $_SERVER['REQUEST_TIME'] then why do I need to generate it again in time()?

I'll probably stick to $_SERVER['REQUEST_TIME']. Like you say, it doesn't need to be to-the-second accurate.

[Arantor]

Because if you change hosting it may not be available if the host is not prompt in updating it.

time() is one of those things that will always be available, while $_SERVER variables are not. But, if it works for you stick with it.

[darrenbeige]

I'm not releasing the code, so the $_SERVER variable is fine for me. SMF Error Console will inform me if the variable goes astray.

I'm putting the 'Hot Articles' on hold though, 'cos I've just found an error in my Modify code. Can't fix it now though, I'll have to wait until later.