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

[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?