How do I add a button/link that runs SQL Queries?

[Ocean]

Hi! I need some help with this...


I would like to add either a button or a link to the ShowThread Template.


However, when that button or link is clicked, I want it to run a number of SQL Queries and then refresh the page.


Can someone help me figure out how this needs to be structured? I assume that the link in the ShowThread template will have to call a PHP file, although I don't know if it needs to be a seperate file, or if the addition should just be made to ShowThread.php.

I'm also uncertain as to the formatting of code that needs to be done.


To give us something to work with, the first query it's going to need to run will be to empty a field within that Thread's record in the "thread" table. For example:

UPDATE thread SET field1='' WHERE threadid='12345'

I imagine that we have to somehow pass the Thread ID along to the script, and format the SQL Query to take that variable into account.


Can someone help enlighten me as to how this all needs to be done?

Thanks!

[Ocean]

Gentle bump?

[Ocean]

Alternately, if there is a simple hack that has this type of functionality, someone could point it out to me, so that I have an example to work with.

All I need is an example of a link or button in the vB templates that will call a section in one of the PHP files that runs a query or two, while passing along the Thread ID.


It would give me a place to start, if no one would rather give me the specifics themselves.


Thanks again!

[Colin F]

Just make a form, make a hidden input called threadid and give it the threadid value and then submit it with the button.
Make another hidden input field named "do" and give it a value like "bumpthread" (at least it sounds like you're making something like this )

In the showthread file, add do if ($_REQUEST['do'] == "bumpthread")
{

}
near the top and just write
$_REQUEST['do'] == "default";

make default the default thing...


[edit] showthread.php doesn't use $do.... oh well, I hope you understand what I mean

[Ocean]

Originally Posted by Colin F

Just make a form, make a hidden input called threadid and give it the threadid value and then submit it with the button.
Make another hidden input field named "do" and give it a value like "bumpthread" (at least it sounds like you're making something like this )

In the showthread file, add do if ($_REQUEST['do'] == "bumpthread")
{

}
near the top and just write
$_REQUEST['do'] == "default";

make default the default thing...


[edit] showthread.php doesn't use $do.... oh well, I hope you understand what I mean

I must admit, I'm a bit lost...

In the other thread, you helped me to duplicate the "whoviewed" field in the "thread" table. Well, with this question, I'm trying to figure out how I can put in a link/button in the ShowThread template, that when clicked, will run a SQL Query that will empty the "whoviewedcounter" field of the "thread" table for that particular thread. This way, in addition to knowing who's viewed a particular thread - ever, I can also have a seperate section with a resettable counter.

By doing that, I can reset the counter for a given thread any time I want, and I can see who's viewed it from that point on, in addition to who's viewed it at any point.


Now, there seem to be two parts to making this work:


1. Making a link/button in the ShowThread template, which should be relatively straightforward - I would imagine I could work with an example such as

"<a href="sendmessage.php?$session[sessionurl]do=sendtofriend&amp;t=$threadid">$vbphrase[email_this_page]</a>"

However, I need to know how exactly it should be formatted.


Would I format it like this:

"<a href="showthread.php?do=resetcounter;t=$threadid">$vbphrase[custom_phrase]</a>"


Is the "do=resetcounter" what you were referring to? And is the "t=$threadid" what I need in order to pass along the Thread ID to the script?


2. The portion that gets put into ShowThread.php.

What would I be placing in the ShowThread.php file?

Am I to understand that I need to put this in the beginning:


$_REQUEST['do'] == "default";



And this, somewhere below:


do if ($_REQUEST['do'] == "resetcounter")
{

// ***MySQL Queries go here?***
UPDATE thread SET whoviewedcounter='' WHERE threadid=????

}




I apologize and thank you, in advance. I'm competant, but I don't code in PHP/MySQL. As a result, I can do great things insofar as designing a program is concerned - but I need help with the actual syntax.

[Colin F]

Try something like this if you want to do it with a link:

<a href="misc.php?$session[sessionurl]do=resetviewcounter&amp;t=$threadid">Reset Whoviewed Counter</a>

Then, in your misc.php file, add this:

PHP Code:

if ($_REQUEST['do'] == "resetviewcounter")
{
if(!is_member_of($bbuserinfo, 6)) // only admins may reset counter (usergroup: 6)
{
print_no_permission();
}
$threadid = addslashes($_REQUEST['threadid']);
//***insert mysql queries
$DB_site->query("UPDATE " . TABLE_PREFIX . "thread SET whoviewedcounter='' WHERE threadid=$threadid");

//redirect back to thread
$url = "showthread.php?$session[sessionurl]t=$threadid";
eval(print_standard_redirect('redirect_resetviewcounter'));
} 


Add a phrase for redirect_resetviewcounter.

I think that should do it

[Ocean]

Originally Posted by Colin F

Try something like this if you want to do it with a link:

<snip>

Add a phrase for redirect_resetviewcounter.

I think that should do it

Colin, you are the best! That worked perfectly. I found that since I have redirects turned off on my board, the added phrase was irrelevant, and I used the default "redirecting" phrase.


Now, when you helped me with the first stage of my modification, I posted this question seperately because I didn't want to assume that you were interested in continuing to help me. But here we are, together again for step two (did I already thank you? ).

That being the case, I have one last step remaining - and I figure I might as well ask you since you've stuck with me this far... <grin> But if you're not interested, just say so, and I'll happily make it a seperate post.




The last thing I want to do, is to add another field to the "thread" table, which will store the date and time that the Reset Counter link we just added was last clicked. So, the chart of what I need is this:


1. I need to create a new field that will store the date and time for each thread that the counter was reset. When I created the first "whoviewedcounter" field, I did so with this query:

ALTER TABLE thread ADD whoviewedcounter TEXT NOT NULL;

However, I'm not sure what the query would be to add the proper field (let's call it "whoviewedreset") type we would need to store this information.


2. I need to be able to access the date/time so that I can insert it into the ShowThread template - I don't know if it would be in the format of "$vbphrase[whoviewedreset]", or if it's not that simple. Needless to say, I'm not sure what the best way to do this is.


3. If there is no date/time stored for a particular thread, I would imagine that the phrase should show as "Never".


4. When I click on the "Reset Counter" link we just worked on, the script in Misc.php should also update the date/time in the "whoviewedreset" field of that thread.



Whew! That seems like a lot more than it really is, I would imagine. But in any case, if you feel like helping me work this last part out, it would be greatly appreciated! If not, that's okay too. You've been more than generous so far, and I am extremely grateful for the assistance you have already given me.

[Colin F]

Originally Posted by Ocean

Colin, you are the best! That worked perfectly. I found that since I have redirects turned off on my board, the added phrase was irrelevant, and I used the default "redirecting" phrase.


Now, when you helped me with the first stage of my modification, I posted this question seperately because I didn't want to assume that you were interested in continuing to help me. But here we are, together again for step two (did I already thank you? ).

That being the case, I have one last step remaining - and I figure I might as well ask you since you've stuck with me this far... <grin> But if you're not interested, just say so, and I'll happily make it a seperate post.




The last thing I want to do, is to add another field to the "thread" table, which will store the date and time that the Reset Counter link we just added was last clicked. So, the chart of what I need is this:


1. I need to create a new field that will store the date and time for each thread that the counter was reset. When I created the first "whoviewedcounter" field, I did so with this query:

ALTER TABLE thread ADD whoviewedcounter TEXT NOT NULL;

However, I'm not sure what the query would be to add the proper field (let's call it "whoviewedreset") type we would need to store this information.


2. I need to be able to access the date/time so that I can insert it into the ShowThread template - I don't know if it would be in the format of "$vbphrase[whoviewedreset]", or if it's not that simple. Needless to say, I'm not sure what the best way to do this is.


3. If there is no date/time stored for a particular thread, I would imagine that the phrase should show as "Never".


4. When I click on the "Reset Counter" link we just worked on, the script in Misc.php should also update the date/time in the "whoviewedreset" field of that thread.



Whew! That seems like a lot more than it really is, I would imagine. But in any case, if you feel like helping me work this last part out, it would be greatly appreciated! If not, that's okay too. You've been more than generous so far, and I am extremely grateful for the assistance you have already given me.

Not that hard....


1. ALTER TABLE `testvb_thread` ADD `whoviewedreset` INT( 10 ) UNSIGNED NOT NULL ;

2. The data in there should be in $thread[whoviewedreset] or $threadinfo[whoviewedreset], I'm not sure
You would have to insert something like this in the showthread.php file and call the date with the var $whoviewedreset

if ($thread['whoviewedreset'] != '')
{
$whoviewedreset = vbdate('$vboptions[dateformat]', '$thread[whoviewedreset]');
}
else
{
$whoviewedreset = "Never";
}


3. solved in 2.

4. $DB_site->query("UPDATE " . TABLE_PREFIX . "thread SET whoviewedreset = " . TIMENOW . " WHERE threadid = $threadid");


Next time just ask me to code the script for you
No, I'm glad to be of help. I'm sure you'll get the hang of it sooner or later



(I have the feeling I forgot something, not sure what though...)

[Ocean]

Originally Posted by Colin F

Not that hard....

<snip>

Next time just ask me to code the script for you

You are doing no small portion of the code writing, it's true... <sniff>

Again, I apologize for that - and I can only repeat my thanks, for your patience and generosity.

Originally Posted by Colin F

No, I'm glad to be of help. I'm sure you'll get the hang of it sooner or later


(I have the feeling I forgot something, not sure what though...)

Well, I ran across two things:


1. I needed to change:


if ($thread['whoviewedreset'] != ' ')


To this:


if ($thread['whoviewedreset'] != '0')


Otherwise, the conditional wouldn't work properly.




2. Even with that change, the $whoviewedreset variable is returning this:


$vbop3100o1200[31pm31efoWed, 31 Dec 1969 19:00:00 -050012pm31]


I tried changing "$thread" to "$threadinfo", but that didn't do it.

(Note: for the above example, the actual database value of the "whoviewedreset" field was "1092694706".



Was vbdate called incorrectly, or is there a problem with the SQL Query that sets it?

[Colin F]

oh of course

change this:
$whoviewedreset = vbdate('$vboptions[dateformat]', '$thread[whoviewedreset]');

to this:
$whoviewedreset = vbdate($vboptions[dateformat], '$thread[whoviewedreset]');


That should do it.


Good night