I'm having a PHP/MySQL problem. Help would be appreciated! :)

[Ocean]

Hi! Okay, here's the background... I'm trying to modify an existing hack - Who Viewed This Thread (https://vborg.vbsupport.ru/showthread.php?t=61222).

Now, this hack creates an additional field ("whoviewed") in the "thread" table, and checks/stores UserIDs of people who view any given thread.


The step I'm on requires that a duplicate set of information be made and maintained.

What I did was to create an additional field in the same table called "whoviewedcounter".


I also changed the original addition to the ShowThread.php file:




$showinvisible = 1;

// Get who has already viewed this thread
$currentthread = $DB_site->query_first("SELECT whoviewed FROM ".TABLE_PREFIX."thread WHERE threadid = $threadid");
$vieweduserids = explode(" ", $currentthread['whoviewed']);

$userinfo = fetch_userinfo($bbuserinfo['userid']);


if (!$userinfo['invisible'] OR $showinvisible)
{
if (!empty($currentthread['whoviewed']))
{
if (!in_array($bbuserinfo['userid'], $vieweduserids))
{
$DB_site->query("UPDATE ".TABLE_PREFIX."thread SET whoviewed = CONCAT(whoviewed, \" \", \"" .$bbuserinfo['userid'] . "\") WHERE threadid = $threadid");
}
}
else
{
$DB_site->query("UPDATE ".TABLE_PREFIX."thread SET whoviewed = \"" . $bbuserinfo['userid'] . "\" WHERE threadid = $threadid");
}
}
// Who has viewed this thread?
if (empty($currentthread['whoviewed']))
{
$thread['viewers'] = '('.strtolower($vbphrase['none']).')';
}
else
{
$result = $DB_site->query("SELECT userid, username FROM ".TABLE_PREFIX."user WHERE userid IN (" . implode(", ", $vieweduserids) . ")");
$viewers = array();
while ($user = $DB_site->fetch_array($result))
{
array_push($viewers, "<a href=\"member.php?".$session['sessionurl'] . "u=".$user['userid'] . "\">" . htmlspecialchars($user['username']) . "</a>");
}
$thread['viewers'] = implode(", ", $viewers);
}





And I modified it to this:





$showinvisible = 1;

// Get who has already viewed this thread
$currentthread = $DB_site->query_first("SELECT whoviewed FROM ".TABLE_PREFIX."thread WHERE threadid = $threadid");
$vieweduserids = explode(" ", $currentthread['whoviewed']);

$currentthreadcounter = $DB_site->query_first("SELECT whoviewedcounter FROM ".TABLE_PREFIX."thread WHERE threadid = $threadid");
$vieweduseridscounter = explode(" ", $currentthread['whoviewedcounter']);


$userinfo = fetch_userinfo($bbuserinfo['userid']);

if (!$userinfo['invisible'] OR $showinvisible)
{
if (!empty($currentthread['whoviewed']))
{
if (!in_array($bbuserinfo['userid'], $vieweduserids))
{
$DB_site->query("UPDATE ".TABLE_PREFIX."thread SET whoviewed = CONCAT(whoviewed, \" \", \"" .$bbuserinfo['userid'] . "\") WHERE threadid = $threadid");
}
}
else
{
$DB_site->query("UPDATE ".TABLE_PREFIX."thread SET whoviewed = \"" . $bbuserinfo['userid'] . "\" WHERE threadid = $threadid");
}

if (!empty($currentthreadcounter['whoviewedcounter']))
{
if (!in_array($bbuserinfo['userid'], $vieweduseridscounter))
{
$DB_site->query("UPDATE ".TABLE_PREFIX."thread SET whoviewedcounter = CONCAT(whoviewedcounter, \" \", \"" .$bbuserinfo['userid'] . "\") WHERE threadid = $threadid");
}
}
else
{
$DB_site->query("UPDATE ".TABLE_PREFIX."thread SET whoviewedcounter = \"" . $bbuserinfo['userid'] . "\" WHERE threadid = $threadid");
}
}
// Who has viewed this thread?
if (empty($currentthread['whoviewed']))
{
$thread['viewers'] = '('.strtolower($vbphrase['none']).')';
}
else
{
$result = $DB_site->query("SELECT userid, username FROM ".TABLE_PREFIX."user WHERE userid IN (" . implode(", ", $vieweduserids) . ")");
$viewers = array();
while ($user = $DB_site->fetch_array($result))
{
array_push($viewers, "<a href=\"member.php?".$session['sessionurl'] . "u=".$user['userid'] . "\">" . htmlspecialchars($user['username']) . "</a>");
}
$thread['viewers'] = implode(", ", $viewers);
}

if (empty($currentthreadcounter['whoviewedcounter']))
{
$thread['viewerscounter'] = '('.strtolower($vbphrase['none']).')';
}
else
{
$resultcounter = $DB_site->query("SELECT userid, username FROM ".TABLE_PREFIX."user WHERE userid IN (" . implode(", ", $vieweduseridscounter) . ")");
$viewerscounter = array();
while ($user = $DB_site->fetch_array($resultcounter))
{
array_push($viewerscounter, "<a href=\"member.php?".$session['sessionurl'] . "u=".$user['userid'] . "\">" . htmlspecialchars($user['username']) . "</a>");
}
$thread['viewerscounter'] = implode(", ", $viewerscounter);
}





Effectively, I duplicated everything here - but I renamed every reference to point to the new field, and I renamed every variable to distinguish it from the original set.

I also made the requisite SHOWTHREAD template changes to reference "$thread[viewerscounter]" in addition to "$thread[viewers]".



Here's the problem: When I view a thread for the first time, both the original section and my added section show "None" for the users who have viewed this thread.

However, the second time I view that page (or if I hit Refresh), I get an error:




Database error in vBulletin 3.0.3:
Invalid SQL: SELECT userid, username FROM user WHERE userid IN ()
mysql error: 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 1
mysql error number: 1064
Date: Thursday 12th of August 2004 09:06:01 AM
Script: http://www.mydomain.com/testforums/showthread.php?t=15
Referer: https://www.mydomain.com/testforums/forumdisplay.php?f=6
Username: Admin
IP Address: 192.168.1.1




In addition to the above error, I notice that the same UserID is being added to the new field (whoviewedcounter) multiple times (i.e. "1 1 1 1 1") - once for each view, I think.

With the changes I made, I wouldn't think I would have any problems - but obviously I missed something (or a few somethings).


If someone can point out what I did wrong, I would greatly appreciate it! :)

[Ocean]

I apologize for not putting more specific information in the Thread Title. By the time I realized that, I discovered that I wasn't allowed to edit that information. :)

[Colin F]

I would use the single tiny squigly lines-on-top (') instead of the double ones (") here:

implode(", ", $vieweduserids)

making it:

implode(', ', $vieweduserids)

also, you might want to save the ID's in the database using the format 1,2,3,4,5,6,7 as you woudn't have to explode() and implode() again.

Then again, if anything I said doesn't make sense, excuse me. I'm extremely tired...

[Ocean]

I would use the single tiny squigly lines-on-top (') instead of the double ones (") here:

implode(", ", $vieweduserids)

making it:

implode(', ', $vieweduserids)

also, you might want to save the ID's in the database using the format 1,2,3,4,5,6,7 as you woudn't have to explode() and implode() again.


Then again, if anything I said doesn't make sense, excuse me. I'm extremely tired...

I tried making the character change - it didn't work. I even tried using that character change for all instances of the "implode" function, and that didn't do anything either.

As for your second idea, if you think that is the better way to do it, I'm all for it. However, I'm not sure how to modify the code to make it work that way. :)


This is the original addition to the ShowThread.php file:





$showinvisible = 1;
// Get who has already viewed this thread
$currentthread = $DB_site->query_first("SELECT whoviewed FROM ".TABLE_PREFIX."thread WHERE threadid = $threadid");
$vieweduserids = explode(" ", $currentthread['whoviewed']);
$userinfo = fetch_userinfo($bbuserinfo['userid']);
if (!$userinfo['invisible'] OR $showinvisible)
{
if (!empty($currentthread['whoviewed']))
{
if (!in_array($bbuserinfo['userid'], $vieweduserids))
{
$DB_site->query("UPDATE ".TABLE_PREFIX."thread SET whoviewed = CONCAT(whoviewed, \" \", \"" .$bbuserinfo['userid'] . "\") WHERE threadid = $threadid");
}
}
else
{
$DB_site->query("UPDATE ".TABLE_PREFIX."thread SET whoviewed = \"" . $bbuserinfo['userid'] . "\" WHERE threadid = $threadid");
}
}
// Who has viewed this thread?
if (empty($currentthread['whoviewed']))
{
$thread['viewers'] = '('.strtolower($vbphrase['none']).')';
}
else
{
$result = $DB_site->query("SELECT userid, username FROM ".TABLE_PREFIX."user WHERE userid IN (" . implode(", ", $vieweduserids) . ")");
$viewers = array();
while ($user = $DB_site->fetch_array($result))
{
array_push($viewers, "<a href=\"member.php?".$session['sessionurl'] . "u=".$user['userid'] . "\">" . htmlspecialchars($user['username']) . "</a>");
}
$thread['viewers'] = implode(", ", $viewers);
}





If you could show me what I need to do in order to make the original code work with the comma delineated format - I can then try again to modify it by adding a second section and see if it works.

(p.s. Thank you for your patience, and your help!) :)

[Colin F]

$showinvisible = 1;
// Get who has already viewed this thread
$currentthread = $DB_site->query_first("SELECT whoviewed FROM ".TABLE_PREFIX."thread WHERE threadid = $threadid");
$userinfo = fetch_userinfo($bbuserinfo['userid']);
if (!$userinfo['invisible'] OR $showinvisible)
{
if (!empty($currentthread['whoviewed']))
{
$vieweduserids = explode(",", $currentthread['whoviewed']);
if (!in_array($bbuserinfo['userid'], $vieweduserids))
{
$adduserid = ",".$bbuserinfo[userid];
$DB_site->query("UPDATE " . TABLE_PREFIX . "thread SET whoviewed = CONCAT(whoviewed, '$adduserid') WHERE threadid = $threadid");
}
}
else
{
$DB_site->query("UPDATE " . TABLE_PREFIX . "thread SET whoviewed = $bbuserinfo[userid] WHERE threadid = $threadid");
}
}

// Who has viewed this thread?
if (empty($currentthread['whoviewed']))
{
$thread['viewers'] = '('.strtolower($vbphrase['none']).')';
}
else
{
$result = $DB_site->query("SELECT userid, username FROM " . TABLE_PREFIX . "user WHERE userid IN ($currentthread[whoviewed])");
$viewers = array();
while ($user = $DB_site->fetch_array($result))
{
array_push($viewers, "<a href=\"member.php?".$session['sessionurl'] . "u=".$user['userid'] . "\">" . htmlspecialchars($user['username']) . "</a>");
}
$thread['viewers'] = implode(", ", $viewers);
}




Try this maybe...

[Ocean]

Try this maybe...



When I did, I got this error:




Database error in vBulletin 3.0.3:
Invalid SQL: SELECT userid, username FROM user WHERE userid IN (
)
mysql error: 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 2
mysql error number: 1064
Date: Saturday 14th of August 2004 09:20:49 AM
Script: http://www.mydomain.com/testforums/showthread.php?t=14
Referer: https://www.mydomain.com/testforums/forumdisplay.php?f=6
Username: Admin
IP Address: 192.168.1.1




The database showed that ",1" was entered. I'm wondering whether we're better off trying to fix the original modification, or if you still feel that it would be the better choice to make this work with commas. :)

[Ocean]

Aha! False alarm, Colin! I found out part of the problem - one of the earlier revisions corrupted the format of the data that was in the table. As a result, any of our future revisions were going to fail, because the new Queries couldn't interpret the data that was currently in those fields.

Once I cleared them out, your revised code (with the commas) worked perfectly! I was able to modify it for a duplicate set of data without any problems.


My remaining question to you is, do you know if it's possible to convert the old format into your new format? Or do I have no choice but to wipe the old data and start fresh?

[Colin F]

You would have to make a small script, something like this:


$threads = $DB_site->query("SELECT threadid,whoviewed FROM " . TABLE_PREFIX . "thread WHERE whoviewed != ''");
while ($thread = $DB_site->fetch_array($threads))
{
$vieweduserids = explode(" ", $thread['whoviewed']);
$changeduserids = implode(",", $vieweduserids);
$DB_site->query("UPDATE thread SET whoviewed = '$changeduserids' WHERE threadid = $thread[threadid]");
}


You might have to change the explode part to fit whatever the data looks like now.

[Ocean]

You would have to make a small script, something like this:


You might have to change the explode part to fit whatever the data looks like now.



I'll give it a try as soon as the rest of the modification is complete.


Thank you for all of your help, Colin! :) :) :)

[Ocean]

You would have to make a small script, something like this:

<snip>

You might have to change the explode part to fit whatever the data looks like now.


So, if I have this script saved as a PHP file:



<?php

$threads = $DB_site->query("SELECT threadid,whoviewed FROM " . TABLE_PREFIX . "thread WHERE whoviewed != ''");
while ($thread = $DB_site->fetch_array($threads))
{
$vieweduserids = explode(" ", $thread['whoviewed']);
$changeduserids = implode(",", $vieweduserids);
$DB_site->query("UPDATE thread SET whoviewed = '$changeduserids' WHERE threadid = $thread[threadid]");
}

?>




... what is the recommended way of running this script?

Is it just a matter of calling it from a URL? (i.e. www.mydomain.com/forums/customscript.php (http://www.mydomain.com/forums/customscript.php))

If so, are there any arguments that need to be passed? I'd be concerned as to whether it processed the correct board or not. (as I also might have www.mydomain.com/forum2 (http://www.mydomain.com/forum2))


Thanks! :)

[Ocean]

Well, I tried running the above script as a PHP file - all I got was a blank screen and no effect on the database.

Obviously, I did something wrong - the question is what? :)