From 1cb945e201704062e9940f327464f4b31a861f20 Mon Sep 17 00:00:00 2001 From: Starla Insigna Date: Mon, 5 Jan 2009 21:48:01 -0500 Subject: Changed comments' user index Previously, the comments table referenced users by username. However, if user ever changed their username, everything would become messed up. To fix this, the index has been changed to the user ID. This change obviously requires maintenance: This SQL must be run: "ALTER TABLE `comments` ADD `user_id` INT( 11 ) NOT NULL AFTER `page_id`;" This PHP will generate some SQL that must be run: "); } $getusers = "SELECT * FROM anon_commenters"; $getusers2 = mysql_query($getusers); while ($getusers3 = mysql_fetch_array($getusers2)) { echo("UPDATE comments SET user_id = " . $getusers3['id'] . " WHERE username = \"" . $getusers3['username'] . "\" AND is_anon = 1;
"); } ?> This SQL must be run: "ALTER TABLE `comments` DROP `username`;" --- includes/comments.php | 6 +++--- includes/footer.php | 12 ++++++------ includes/session.php | 18 ++++++++++++++++++ pages/admin.php | 2 +- pages/post.php | 4 ++-- 5 files changed, 30 insertions(+), 12 deletions(-) diff --git a/includes/comments.php b/includes/comments.php index f1b8228..3a64321 100755 --- a/includes/comments.php +++ b/includes/comments.php @@ -45,7 +45,7 @@ while ($getcomments3[$i] = mysql_fetch_array($getcomments2)) { if ($getcomments3[$i]['is_anon'] == 0) { - $getuser = "SELECT * FROM phpbb_users WHERE username = \"" . $getcomments3[$i]['username'] . "\""; + $getuser = "SELECT * FROM phpbb_users WHERE user_id = " . $getcomments3[$i]['user_id']; $getuser2 = mysql_query($getuser); $getuser3 = mysql_fetch_array($getuser2); @@ -54,11 +54,11 @@ while ($getcomments3[$i] = mysql_fetch_array($getcomments2)) $website = $getuser3['user_website']; } else if ($getcomments3[$i]['is_anon'] == 1) { - $getanon = "SELECT * FROM anon_commenters WHERE username = \"" . $getcomments3[$i]['username'] . "\""; + $getanon = "SELECT * FROM anon_commenters WHERE id = " . $getcomments3[$i]['user_id']; $getanon2 = mysql_query($getanon); $getanon3 = mysql_fetch_array($getanon2); - if ($getanon3['username'] == $getcomments3[$i]['username']) + if ($getanon3['id'] == $getcomments3[$i]['user_id']) { $username = $getanon3['username'] . ' (Guest)'; $email = $getanon3['email']; diff --git a/includes/footer.php b/includes/footer.php index 77d161b..a37b438 100755 --- a/includes/footer.php +++ b/includes/footer.php @@ -95,7 +95,7 @@ if (!isset($noRightbar)) { if ($getcomments3[$i]['is_anon'] == 0) { - $getuser = "SELECT * FROM phpbb_users WHERE username = \"" . $getcomments3[$i]['username'] . "\""; + $getuser = "SELECT * FROM phpbb_users WHERE user_id = " . $getcomments3[$i]['user_id']; $getuser2 = mysql_query($getuser); $getuser3 = mysql_fetch_array($getuser2); @@ -103,11 +103,11 @@ if (!isset($noRightbar)) $website = $getuser3['user_website']; } else if ($getcomments3[$i]['is_anon'] == 1) { - $getanon = "SELECT * FROM anon_commenters WHERE username = \"" . $getcomments3[$i]['username'] . "\""; + $getanon = "SELECT * FROM anon_commenters WHERE id = " . $getcomments3[$i]['user_id']; $getanon2 = mysql_query($getanon); $getanon3 = mysql_fetch_array($getanon2); - if ($getanon3['username'] == $getcomments3[$i]['username']) + if ($getanon3['id'] == $getcomments3[$i]['user_id']) { $username = $getanon3['username'] . ' (Guest)'; $website = $getanon3['website']; @@ -142,16 +142,16 @@ if (!isset($noRightbar)) } $users = array(); - $getusers = "SELECT DISTINCT username FROM comments WHERE is_anon = 0"; + $getusers = "SELECT DISTINCT user_id FROM comments WHERE is_anon = 0"; $getusers2 = mysql_query($getusers); $i=0; while ($getusers3[$i] = mysql_fetch_array($getusers2)) { - $getcount = "SELECT COUNT(*) FROM comments WHERE username = \"" . $getusers3[$i]['username'] . "\""; + $getcount = "SELECT COUNT(*) FROM comments WHERE user_id = " . $getusers3[$i]['user_id']; $getcount2 = mysql_query($getcount); $getcount3 = mysql_fetch_array($getcount2); - $getuser = "SELECT * FROM phpbb_users WHERE username = \"" . $getusers3[$i]['username'] . "\""; + $getuser = "SELECT * FROM phpbb_users WHERE user_id = " . $getusers3[$i]['user_id']; $getuser2 = mysql_query($getuser); $getuser3 = mysql_fetch_array($getuser2); diff --git a/includes/session.php b/includes/session.php index 19ad24e..94c919a 100755 --- a/includes/session.php +++ b/includes/session.php @@ -38,6 +38,24 @@ function getSessionID() return false; } +function getSessionUserID() +{ + $getconfig = "SELECT * FROM phpbb_config WHERE config_name LIKE \"cookie_name\""; + $getconfig2 = mysql_query($getconfig); + $getconfig3 = mysql_fetch_array($getconfig2); + + if (isset($_COOKIE[$getconfig3['config_value'] . '_sid'])) + { + $getsession = "SELECT * FROM phpbb_sessions WHERE session_id LIKE \"" . mysql_real_escape_string($_COOKIE[$getconfig3['config_value'] . '_sid']) . "\""; + $getsession2 = mysql_query($getsession) or die($getsession); + $getsession3 = mysql_fetch_array($getsession2); + + return $getsession3['session_user_id']; + } + + return false; +} + function getSessionUsername() { $getconfig = "SELECT * FROM phpbb_config WHERE config_name LIKE \"cookie_name\""; diff --git a/pages/admin.php b/pages/admin.php index fda03c3..12a2d01 100755 --- a/pages/admin.php +++ b/pages/admin.php @@ -563,7 +563,7 @@ if (isLoggedIn()) $insanon = "INSERT INTO anon_commenters (username,email,website) VALUES (\"" . $getcomment3['author'] . "\",\"" . $getcomment3['email'] . "\",\"" . $getcomment3['website'] . "\")"; $insanon2 = mysql_query($insanon); - $inscomment = "INSERT INTO comments (page_id,username,comment,is_anon) VALUES (\"" . $getcomment3['page_id'] . "\",\"" . $getcomment3['author'] . "\",\"" . $getcomment3['comment'] . "\",1)"; + $inscomment = "INSERT INTO comments (page_id,user_id,comment,is_anon) VALUES (\"" . $getcomment3['page_id'] . "\"," . mysql_insert_id() . ",\"" . $getcomment3['comment'] . "\",1)"; $inscomment2 = mysql_query($inscomment); $delcomment = "DELETE FROM moderation WHERE id = " . $getcomment3['id']; diff --git a/pages/post.php b/pages/post.php index 7d6dd08..87413e8 100755 --- a/pages/post.php +++ b/pages/post.php @@ -56,7 +56,7 @@ if (!isset($_GET['id'])) { if ($getanon3['email'] == $_POST['email']) { - $setcomment = "INSERT INTO comments SET page_id = \"" . $_GET['id'] . "\", username = \"" . $_POST['username'] . "\", comment = \"" . $_POST['comment'] . "\", is_anon = 1"; + $setcomment = "INSERT INTO comments SET page_id = \"" . $_GET['id'] . "\", user_id = " . $getanon3['id'] . ", comment = \"" . $_POST['comment'] . "\", is_anon = 1"; $setcomment2 = mysql_query($setcomment); $page_id = $_GET['id']; @@ -84,7 +84,7 @@ if (!isset($_GET['id'])) } } } else { - $setcomment = "INSERT INTO comments SET page_id = \"" . $_GET['id'] . "\", username = \"" . getSessionUsername() . "\", comment = \"" . $_POST['comment'] . "\", is_anon = 0"; + $setcomment = "INSERT INTO comments SET page_id = \"" . $_GET['id'] . "\", user_id = " . getSessionUserID() . ", comment = \"" . $_POST['comment'] . "\", is_anon = 0"; $setcomment2 = mysql_query($setcomment); mail('hatkirby@fourisland.com', 'New comment on Four Island!', getSessionUsername() . ' has posted a comment on Four Island under the "page id" ' . $_GET['id']); -- cgit 1.4.1