Topic: isolating users' email addresses in their own mysql field

[bobscan]

some situations may call for easier management of users' emails. Currently, mibew just tosses the users' entered survey email address as a tmessage string for the operator, but we needed it to help us create profiles for them in our logs.


1.#######################################################
First, add a column field for their emails in the chatthread control table:
mysql snippet:

Code: [Select]

ALTER TABLE `webim`.`chatthread` ADD COLUMN `useremail` VARCHAR(45) NULL  AFTER `userName`;
Chatthread table records the messageid for a user, so we can associate users' emails with their chat dialogue.


2.#######################################################
Now, we'll add the php to enable saving the users' emails to this new field. 

client.php line 115 replace with:

Code: [Select]

$thread = create_thread($groupid,$visitor['name'],$email,$remoteHost, $referrer,$current_locale,$visitor['id'], $userbrowser,$state_loading,$link);


3.#######################################################
leavemessage.php line 138 replace with:

Code: [Select]

$thread = create_thread($groupid,$name,$email,$remoteHost,$referrer,$current_locale,$visitor['id'], $userbrowser,$state_left,$link);

we're just adding  the $email string to the functions here.
4.#######################################################
Now to actually make the function parse this added string:

/libs/chat.php , replace the create_thread() function  with this:

Code: [Select]

function create_thread($groupid, $username, $email, $remoteHost, $referer, $lang, $userid, $userbrowser, $initialState, $link)
{
global $mysqlprefix;
$query = sprintf(
"insert into ${mysqlprefix}chatthread (userName,useremail,userid,ltoken,remote,referer,lrevision,locale,userAgent,dtmcreated,dtmmodified,istate" . ($groupid ? ",groupid" : "") . ") values " .
"('%s','%s','%s',%s,'%s','%s',%s,'%s','%s',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,$initialState" . ($groupid ? ",$groupid" : "") . ")",
mysql_real_escape_string($username, $link),
mysql_real_escape_string($email, $link), \\NOTE:  Adding a new line here to define the input string, with small adjustments above to actually insert it. the rest is distro-default.
mysql_real_escape_string($userid, $link),
next_token(),
mysql_real_escape_string($remoteHost, $link),
mysql_real_escape_string($referer, $link),
next_revision($link),
mysql_real_escape_string($lang, $link),
mysql_real_escape_string($userbrowser, $link));

perform_query($query, $link);
$id = mysql_insert_id($link);

$newthread = thread_by_id_($id, $link);
return $newthread;
}


If anyone figures out a quick and clean way  to add the users' email addresses on each row of webim.chatmessage, that would be awesome. I cant seem to get function post_message_ to read the $email string, probably because it doesn't exist anymore by the time the function is called.

Referring to webim.chatthreads for each message is less than desirable but at least it works.