Struggling to use DB stuff in 2.0

[Largo_npc]

I'm trying to create a mod that would be useful to me, but I'm having some problems with the DB query.

Here's what I'm using.

test.php in Sources.

Code Select Expand


<?php

require_once("../SSI.php");
require_once("Subs.php");

function approveApp($passID)
{
/*
Change Topic Title to prefix with [TRIAL]
Write message saying Welcome ect.
Move Member Group to Trialee
*/
global $smcFunc, $db_prefix;

$result = $smcFunc['db_query']('','
SELECT subject
FROM {db_prefix}messages
WHERE id_msg = {int:topic}
LIMIT 1',
array(
'topic' => $passID,
)
);

echo '-', $result, '-', $smcFunc['db_query'], '-', $db_prefix, '-';
}

function approveTrial($topicID, $memberID, $approverID)
{
/*
Change Topic Title to prefix with [PASSED]
Write message saying Congrats ect.
Move Member Group to Member
*/
}

function denyApp($topicID, $memberID, $approverID)
{
/*
Change Topic Title to prefix with [DECLINE]
Write message saying Sorry but NO!
Lock thread.
*/
}

approveApp(5);

?>


$result returns nothing though.
What am I doing wrong? Is this the right way to go about it?

[Largo_npc]

Ok, I found where I went wrong and corrected it in the post above and in my code, not now it returns

Resource id #30

That is definitely not the subject!

[Arantor]

$result is a reference to a DB construct, and $smcFunc['db_query'] is a function.

After your $result = call, you need something like:

Code Select Expand

if($smcFunc['db_num_rows']($result)) { // actually were rows
  $row = $smcFunc['db_fetch_assoc']($result);
  $smcFunc['db_free_result']($result);
  // now $row['subject'] contains what you want.
}

[Largo_npc]

ahhh right.

Cheers dude. I'll give it a shot.

[Largo_npc]

Ok, stuck again xD

Not sure how to correctly use insert now.
This is what I've got.

Code Select Expand

function approveApp($passID)
{
/*
Change Topic Title to prefix with [TRIAL]
Write message saying Welcome ect.
Move Member Group to Trialee
*/
global $smcFunc, $db_prefix;

$result = $smcFunc['db_query']('','
SELECT subject
FROM {db_prefix}messages
WHERE id_topic = {int:topic}
LIMIT 1',
array(
'topic' => $passID,
)
);

if($smcFunc['db_num_rows']($result)) { // actually were rows
$row = $smcFunc['db_fetch_assoc']($result);
$smcFunc['db_free_result']($result);
// now $row['subject'] contains what you want.
}

$tmpSub = $row['subject'];
if(!strstr( $tmpSub, "[TRIAL]" ))
$tmpSub = "[TRIAL] " . $tmpSub;
echo '-', $tmpSub, '-';


$smcFunc['db_insert']('replace',
'{db_prefix}messages',
array(
'subject' => 'string',
),
array(
$tmpSub,
),
array('subject')
);
}

it doesn't update the topic's Subject though. Is there something else I need to do like with the query?
I'm trying to read the Database topic stickied in this section but it's not clear to me at all.

[Arantor]

It might be easier if I understood what you're trying to do.

If you're updating an existing topic's subject, you need to consider the subject in {db_prefix}topics, but you still need to have reference to the topic's id otherwise you hit every topic.

[Largo_npc]

Yeah I'm trying to update the topic's Subject.

Code Select Expand

/*
Change Topic Title to prefix with [TRIAL]
Write message saying Welcome ect.
Move Member Group to Trialee
*/

So "Jim" would become "[TRIAL] JIM".

A new message would then be posted in the subject from the Moderator who accepts the trial if possible, if not then a Bot will post so the ID can be hard coded inside.

Then creator of the topic is then moved to a different Prime Member Group

[Largo_npc]

I've changed the query now, but it's giving me a syntax error and I'm not sure why.

Code Select Expand

$result = $smcFunc['db_query']('','
SELECT
t.id_member_started AS member_id,
m.subject AS topic_name,
m.poster_name AS topic_author_name,
FROM {db_prefix}topics t
LEFT JOIN {db_prefix}messages m ON m.id_msg = t.id_first_msg
WHERE t.id_topic = {int:topic}
LIMIT 1',
array(
'topic' => $passID,
)
);

[Arantor]

Well, the operation you'll need on the modify messages front will be something like:

Code Select Expand


$smcFunc['db_query']('', '
UPDATE {db_prefix}topics
SET subject = {string:subject}
WHERE id_topic = {int:id_topic}'
array(
'subject' => $tmpSub,
'id_topic' => $passID
)
);


The replace operation only works if you're inserting the whole row - because it is effectively mapped to REPLACE INTO table (column, column) VALUES (value, value), which is a new insert unless the primary key is matched.

That takes care of the topic (as seen in the main list of threads; it doesn't change the topic's first post's subject, mind)

As for updating the member, you might want to look into the updateMemberData function as in the function DB (see the Support link)


EDIT: Just seen your new code. What type of syntax error?

[Largo_npc]

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 'FROM ***.smf_topics t
LEFT JOIN ***.smf_messages m ON m.id_msg = t' at line 5
File: /var/www/vhosts/nonplayercharacters.co.uk/httpdocs/Sources/test.php
Line: 27

[Arantor]

Take out the comma before the FROM clause.

[Largo_npc]

That got it. Cheers.

This is my first time playing with SQL xD

[Largo_npc]

Everything is working perfectly now with a few adjustments. Thanks very much matey.

Just a quick question.
Is it possible to gather input from a pop-up box using php?

The reason I ask this is because I have my 2 functions to approve or deny, and in the case of the deny, I want to give a reason.

Also, I've still not decided the best way to implement this into my forums for moderators to use. I currently have to edit the test.php file with the function I want to use and the topic ID.

I'll drop you my entire code just incase.

Code Select Expand

<?php

require_once("../SSI.php");
require_once("Subs.php");
require_once("Subs-Post.php");

function approveApp($passID)
{
/*
Change Topic Title to prefix with [TRIAL]
Write message saying Welcome ect.
Move Member Group to Trialee
*/
global $smcFunc, $db_prefix;

$result = $smcFunc['db_query']('','
SELECT
t.id_member_started AS member_id,
m.subject AS topic_name,
m.id_board AS board_id,
m.poster_name AS topic_author_name,
m.id_msg AS message
FROM {db_prefix}topics t
LEFT JOIN {db_prefix}messages m ON m.id_msg = t.id_first_msg
WHERE t.id_topic = {int:topic}
LIMIT 1',
array(
'topic' => $passID,
)
);

if($smcFunc['db_num_rows']($result)) { // actually were rows
$row = $smcFunc['db_fetch_assoc']($result);
$smcFunc['db_free_result']($result);
// now $row['subject'] contains what you want.
}

$tmpSub = $row['topic_name'];
if(!strstr( $tmpSub, "[TRIAL]" ))
{
$trial = false;
$tmpSub = "[TRIAL] " . $tmpSub;
$lock = 0;
}
else
{
$find = '[TRIAL] ';
$pos1 = stripos($tmpSub, $find);
$tmpSub2 = explode($find, $tmpSub);
foreach($tmpSub as $test)
echo $test;
$tmpSub = $tmpSub2[1];
$tmpSub = "[PASSED] " . $tmpSub;
$trial = true;
$lock = 1;
}

$memberID = $row['member_id'];
$messageID = $row['message'];

$smcFunc['db_query']('', '
UPDATE {db_prefix}messages
SET subject = {string:subject}, icon = {string:new_icon}
WHERE id_msg = {int:message}',
array(
'subject' => $tmpSub,
'message' => $messageID,
'new_icon' => "thumbup",
)
);
if($trial)
{
$newGroup = array(
'id_group' => 9,
);
$msgBody = $row['topic_author_name'] . ",\n\nThe Legion has decided that you will fit in perfectly around here and we are pleased to offer you full membership!\n\nCongrats and see you on the battlefield from all at .//npc";

}
else
{
$newGroup = array(
'id_group' => 10,
);
$msgBody = $row['topic_author_name'] . ",\n\nWe'd like to accept you for a trail in order for us to get to know you and you get to know us as a Legion.\n\nThere is no time period for this trial, it will just run until we make a discussion.\n\nAgain, Welcome from all at .//npc";

}
updateMemberData($memberID, $newGroup);

$postMessage = array(
'body' => $msgBody,
'subject' => "RE: " . $tmpSub,
);
$postOptions = array(
'board' => $row['board_id'],
'id' => $passID,
'lock_mode' => $lock,
);
$postPoster = array(
'id' => 17,
);
createPost($postMessage, $postOptions, $postPoster);
}

function denyApp($passID, $reason)
{
/*
Change Topic Title to prefix with [DECLINE]
Write message saying Sorry but NO!
Lock thread.
*/
global $smcFunc, $db_prefix;

$result = $smcFunc['db_query']('','
SELECT
t.id_member_started AS member_id,
m.subject AS topic_name,
m.id_board AS board_id,
m.poster_name AS topic_author_name,
m.id_msg AS message
FROM {db_prefix}topics t
LEFT JOIN {db_prefix}messages m ON m.id_msg = t.id_first_msg
WHERE t.id_topic = {int:topic}
LIMIT 1',
array(
'topic' => $passID,
)
);

if($smcFunc['db_num_rows']($result)) { // actually were rows
$row = $smcFunc['db_fetch_assoc']($result);
$smcFunc['db_free_result']($result);
// now $row['subject'] contains what you want.
}

$tmpSub = $row['topic_name'];
if(!strstr( $tmpSub, "[TRIAL]" ))
{
$trial = false;
$tmpSub = "[DECLINED] " . $tmpSub;
}
else
{
$find = '[TRIAL] ';
$pos1 = stripos($tmpSub, $find);
$tmpSub2 = explode($find, $tmpSub);
foreach($tmpSub as $test)
echo $test;
$tmpSub = $tmpSub2[1];
$tmpSub = "[DECLINED] " . $tmpSub;
$trial = true;
}

$memberID = $row['member_id'];
$messageID = $row['message'];

$smcFunc['db_query']('', '
UPDATE {db_prefix}messages
SET subject = {string:subject}, icon = {string:new_icon}
WHERE id_msg = {int:message}',
array(
'subject' => $tmpSub,
'message' => $messageID,
'new_icon' => "thumbup",
)
);
if($trial)
{
$newGroup = array(
'id_group' => 0,
);
$msgBody = $row['topic_author_name'] . ",\n\nUnfortunately, you have not passed your trial because: " . $reason . "\n\nThank you for your interest in .//npc and we'd like to wish you all the best.";
updateMemberData($memberID, $newGroup);
}
else
{
$msgBody = $row['topic_author_name'] . ",\n\nUnfortunately, you were not selected for a trial this time because: " . $reason . "\n\nThank you for your interest in .//npc and we'd like to wish you all the best.";

}


$postMessage = array(
'body' => $msgBody,
'subject' => "RE: " . $tmpSub,
);
$postOptions = array(
'board' => $row['board_id'],
'id' => $passID,
'lock_mode' => 1,
);
$postPoster = array(
'id' => 17,
);
createPost($postMessage, $postOptions, $postPoster);
}
//approveApp(114);
denyApp(114, "Testing purposes");

?>

[Arantor]

If the popup box adds something inside the HTML <form> you're submitting, sure you can. But I'd need to know more about the front end you're looking at for that.

[Largo_npc]

I'm really not sure how I'd implement this into a front end.

I know that I want it to only be available on a certain board and for moderators.

I thought about making a button that would call the relevant function, but I don't think that would work as pressing a button would need to take you to a new page which would then call the function I think? and I'm not sure how to do that.

I also thought about using the Quick Moderation drop down list as I may be able to just call a function from there, but I'm not sure I can.

I really just can't get what I want out into words to explain it better

[Largo_npc]

I thought about copying the Copy Topic mod

Copy Topic

Would that work?

[Arantor]

First glance seems to suggest it might be relevant, if the mod exposes a UI that is satisfactory, see how it does it. (Doesn't seem to hook into quick moderation at first glance, but that's not a huge matter)

[Largo_npc]

Ok, I've done away with Quick Moderation as I don't want that.

This is what I've got.



The problem is I don't have the actions it points to define or anything, I'm not sure how to do that. Also, the decline action would need to gather a string from the moderator for the Reason. What would be the best way to go about doing that?

[Arantor]

Simplest solution would be to have the decline button not point to the decline action (which should be declared in index.php and refer to what function it would be calling) but instead have it generate a form in JS from the onclick event, where the forum directs to index.php?action=decline, passing topic as a <input type="hidden"> and passing the text content in that form.

I'm a little surprised no-one has done this before yet, actually for 2.0's post moderation.

[Largo_npc]

That's way above my head tbh, so is this whole adding the actions thing.

I've added the action in index.php, but I just get a white screen so I'm missing something.

I'll give a run down of what I want to happen with this.

There's a Recruitment section on my forums where people post applications (Topics are started via Custom Form Mod so rogue Subjects are not a problem).

Then, a moderator can approve the Application or Decline it (The Approve and Decline buttons only show for the correct Recruitment board [working])

Say the Mod clicks Approve, it calls the approveApp function somehow and passes it the topic ID.
If the mod Declines it, it should some how gather a Reason for declining and then call the denyApp function passing it the topic id and the reason string.

I'm stumped tbh.