Coding advise about adding to a db column?

[Mr Blunt]

Originally Posted by Dream

little suggestion, you could use a "whodownloaded_" prefix in each field, so youll know no one else will use those fields

No suggestion is little to me.
Please, by all means speak your mind guys.
I'm still learning and WANT to learn.

First off, it's "just one" field.
Called "downloads" under the "user" table.

It's not a vbulletin field.
It's a custom field added to vbulletin's "user" table.
Downside is TWTCommish used a very common name for his field.

I was trying to avoid changing to a new field as I was hoping to keep this compatible with the old whodownloaded hack which many have been using for years.

Originally Posted by MarcoH64

As i understand you want to put all this in a single field. I would suggest against it, and would advice you to create a new table for this with seperate fields.

Yes, and I had a feeling you would say this.
I just had a nice crash course in handling arrays so I guess I could pull this off if it's really neccessary.

Originally Posted by MarcoH64

If you want to keep it in a single field in the user table i suggest you change your data into an array and srialize it into the field.

I haven't learned about serializing yet. Is this neccessary because of things like special characters in the text I'm writing to the database or for some other reason?

Originally Posted by MarcoH64

It is always a good idea to prefix you fields (and tables) with something, so you know you won't get any conflicts.

Duely noted and I will for hacks I write in future.
I was just working with what was around before me.



Hey, would it help if I showed my php code?
Then you can at least see what I'm doing before and after the database.
The code and variable names are pretty easy to follow.
I'll gladly post if someone's maybe willing to help my quest.

Here's my "attachment_complete" hook that writes to the db

$colon = ':';
$ip = IPADDRESS;
$alt_ip = ALT_IP;
$dateline = TIMENOW;
$userid = $vbulletin->userinfo['userid'];
$attachmentid = $vbulletin->input->clean_gpc('r', 'attachmentid', TYPE_UINT);

if ($userid AND $attachmentid AND $dateline)
{
	$dl = $db->query_first("
		SELECT downloads
		FROM " . TABLE_PREFIX . "user
		WHERE userid = $userid
	");
	$comma = ($dl[downloads]) ? ',' : '';
	if ($vbulletin->options['logip'])
	{
		$db->query_write("
			UPDATE " . TABLE_PREFIX . "user
			SET downloads = '$dl[downloads]$comma$attachmentid$colon$dateline$colon$ip$colon$alt_ip'
			WHERE userid = $userid
		");
	}
	else
	{
		$db->query_write("
			UPDATE " . TABLE_PREFIX . "user
			SET downloads = '$dl[downloads]$comma$attachmentid$colon$dateline$colon$colon'
			WHERE userid = $userid
		");
	}
};

Here's my extension php file to handle the reading and array handling.

<?php

require_once("./global.php");
$attachmentid = $vbulletin->input->clean_gpc('r', 'attachmentid', TYPE_UINT);

$whodl = array();
$whodl[shownames] = false;
$whodl[showdates] = false;
$whodl[showips] = false;
$whodl[showaltips] = false;

if ($show['whodladmin'])
{
	$whodl[shownames] = true;
	$whodl[showdates] = true;
	$whodl[showips] = true;
	$whodl[showaltips] = true;
}
else
{
	if ($show['whodlnames'])
	{
		$whodl[shownames] = true;
	}
}

if ($whodl[shownames])
{
	$whodl[rows] = 0;
	$whodl[tracked] = 0;
	
	if ($attachmentid)
	{
		$poster = $db->query_first("
			SELECT userid, dateline, filename, visible, counter, postid
			FROM " . TABLE_PREFIX . "attachment
			WHERE attachmentid = $attachmentid
		");

		if ($poster[visible] = 1)
		{
			if (!$poster[userid] OR $poster[userid] == 0)
			{
				$poster[userid] = 0;
				$poster[username] = $vbphrase['unregistered'];
			}
			else
			{
				$postername = $db->query_first("
					SELECT username
					FROM " . TABLE_PREFIX . "user
					WHERE userid = $poster[userid]
				");
			}

			if ($show['whodldatestoposter'] AND $vbulletin->userinfo['userid'] == $poster[userid])
			{
				$whodl[showdates] = true;
			}

			$whodl[postername] = $postername[username];
			$whodl[posterid] = $poster[userid];
			$whodl[posterdate] = vbdate($vbulletin->options['dateformat'],$poster[dateline]);
			$whodl[filename] = $poster[filename];
			$whodl[counter] = $poster[counter];
			$whodl[postid] = $poster[postid];

			$whodlrows = $db->query_read("
				SELECT userid, username, downloads
				FROM " . TABLE_PREFIX . "user
				WHERE downloads LIKE \"$attachmentid:%\" OR downloads LIKE \"%,$attachmentid:%\" OR downloads LIKE \"$attachmentid,%\" OR downloads LIKE \"%,$attachmentid,%\" OR downloads LIKE \"%,$attachmentid\"
				ORDER BY username ASC
			");

			if ($db->num_rows($whodlrows))
			{
				$whodl[rows] = $db->num_rows($whodlrows);
				while ($whodlrow = $db->fetch_array($whodlrows))
				{
					$whodl[usersid] = $whodlrow[userid];
					$whodl[usersname] = $whodlrow[username];
					$whodluser = preg_replace('/:/', ',', $whodlrow[downloads], -1);
					$whodluser = preg_split('/,/', $whodluser, -1);
					$whodluser = array_count_values($whodluser);
					$whodl[userscount] = $whodluser[$attachmentid];
					$whodl[tracked] += $whodl[userscount];
					eval('$whodownloadsbit .= "' . fetch_template('whodownloadsbit') . '";');

					if ($whodl[showdates] OR $whodl[showips] OR $whodl[showaltips])
					{
						$whodlextras = preg_split('/,/', $whodlrow[downloads], -1);
						foreach ($whodlextras as $whodlextra)
						{
							$whodlextra = preg_split('/:/', $whodlextra, -1);
							if ($whodlextra[0] == $attachmentid)
							{
								$whodl[date] = $whodl[showdates] ? vbdate($vbulletin->options['dateformat'],$whodlextra[1]) : '';
								$whodl[ip] = $whodl[showips] ? $whodlextra[2] : '';
								$whodl[alt_ip] = $whodl[showaltips] ? $whodlextra[3] : '';
								eval('$whodownloadsbit .= "' . fetch_template('whodownloadsdate') . '";');
							}
						}
					}
				}
			}
			else
			{
				$whodl[error] = 'No Users Found';
				eval('$whodownloadsbit .= "' . fetch_template('whodownloadsbit') . '";');
			}
		}
		else
		{
			$whodl[error] = 'File Not Found';
			eval('$whodownloadsbit .= "' . fetch_template('whodownloadsbit') . '";');
		}
	}
	else
	{
		$whodl[error] = 'File Not Found';
		eval('$whodownloadsbit .= "' . fetch_template('whodownloadsbit') . '";');
	}
}
else
{
	$whodl[error] = 'Feature Disabled Or No Permission';
	eval('$whodownloadsbit .= "' . fetch_template('whodownloadsbit') . '";');
}
eval('print_output("' . fetch_template('whodownloads') . '");');

?>

and then I add two new $show variables with a global hook to handle permissions for the ability to view the button which opens my whodownloaded popup.

$show['whodldatestoposter'] = true;
$show['whodladmin'] = iif($vbulletin->userinfo['usergroupid'] == 6, true, false);
$show['whodlnames'] = iif($vbulletin->userinfo['usergroupid'] == 5 OR $vbulletin->userinfo['usergroupid'] == 7 OR $vbulletin->userinfo['usergroupid'] == 2, true, false);