BetterPHP

The non-existent community
https://betterphp.co.uk/board/

Private Message System Errors

https://betterphp.co.uk/board/viewtopic.php?t=1013

Page 1 of 1

Private Message System Errors

Posted: Sat Feb 11, 2012 7:46 pm
by AndrewD92
Hey, I have a error with the private message system that I cant solve. All the conversation conversations is confusing. I posted this thread before, but deleted it as I thought I fixed it but I made matters worse. I get the following error message:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\PrivateMessage\core\inc\private_message.inc.php on line 22

I thought I fixed it. But the stuff i removed was needed and when i re-watched Private Message System [part 10] they were there. My private_message.inc.php code is below. If you spot any other errors please let me know!
<?php

// Fetches a summary of the conversations.
function fetch_conversation_summary(){
	$sql = "SELECT
				`conversations`.`conversation_id`,
				`conversations`.`conversation_subject`,
				MAX(`conversations_messages`.`message_date`) AS `conversation_last_reply`,
				MAX(`conversations_messages`.`message_date`) > `conversations_members`.`conversation_last_view` AS `conversation_unread`
			FROM `conversations`
			LEFT JOIN `conversations_messages` ON  `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
			INNER JOIN `conversations_members` ON `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
			WHERE `conversations_members`.`user_id` = {$_SESSION['user_id']}
			AND `conversation_members`.`conversation_deleted` = 0
			GROUP BY `conversations`.`conversation_id`
			ORDER BY `conversation_last_reply` DESC";
			
	$result = mysql_query($sql);
	
	$conversations = array();
			
	while (($row = mysql_fetch_assoc($result)) !== false){
		$conversations[] = array(
			'id' 				=> $row['conversation_id'],
			'subject' 			=> $row['conversation_subject'],
			'last_reply' 		=> $row['conversation_last_reply'],
			'unread_messages'	=> ($row['conversation_unread'] == 1),
		);
		die(mysql_error());
	}

	return $conversations;
}

// Fetches all of the messages in the given converstion.
function fetch_conversation_messages($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT
				`conversations_messages`.`message_date`,
				`conversations_messages`.`message_date` > `conversations_members`.`conversation_last_view` AS `message_unread`,
				`conversations_message`.`message_text`,
				`users`.`user_name`
			FROM `conversations_messages`
			INNER JOIN `users` ON `conversations_messages`.`user_id` = `users`.`user_id`
			INNER JOIN `conversations_members` ON `conversations_messages`.`conversation_id` = `conversations_members`.`conversation_id`
			WHERE `conversations_messages`.`conversation_id` = {$conversation_id}
			AND `conversations_members`.`user_id` = {$_SESSION['user_id']}
			ORDER BY `conversations_messages`.`message_date` DESC";

	$result = mysql_query($sql);
	
	$messages = array();
	
	while (($row = mysql_fetch_assoc($result)) !== false){
		$messages[] = array(
			'date' 		=> $row['message_date'],
			'unread'	=> $row['message_unread'],
			'text' 		=> $row['message_text'],
			'user_name' => $row['user_name'],
		);
	}
	
	return $messages;
}

// Sets the last view time to the current time for the given conversation.
function update_conversation_last_view($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql ="UPDATE `conversations_members`
			SET `conversation_last_view` = UNIX_TIMESTAMP()
			WHERE `conversation_id` = {$conversation_id}
			AND `user_id` = {$_SESSION['user_id']}";
			
	mysql_query($sql);	
}

// Creates a new conversation, making the given users a member.
function create_conversation($user_ids, $subject, $body){
	$subject	= mysql_real_escape_string(htmlentities($subject));
	$body		= mysql_real_escape_string(htmlentities($body));
	
	mysql_query("INSERT INTO `conversations` (`conversation_subject`) VALUES ('{$subject}')");
	
	$conversation_id = mysql_insert_id();
	
	$sql = "INSERT INTO `conversations_messages` (`conversation_id`, `user_id`, `message_date`, `message_text`)
			VALUES ({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), '{$body}')";
			
	mysql_query($sql);
	
	$values = array("({$conversation_id}, {$_SESSION['user_id']}, {UNIX_TIMESTAMP()}, 0)");
	
	foreach ($user_id as $user_id){
		$user_id = (int) $user_id;
		
		$values [] = "({$conversation_id}, {$user_id}, 0, 0)";
	}
	
	$sql = "INSERT INTO `conversations_members` (`conversation_id`, `user_id`, `conversation_last_view`, `conversation_deleted`)
			VALUES " . implode(", ", $values);
			
			mysql_query($sql);
}

// Checks to see if the given user is a member of the given conversation.
function validate_conversation_id($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT COUNT(1)
			FORM `conversations_members`
			WHERE `conversation_id` = {$conversation_id}
			AND `user_id` = {$_SESSION['user_id']}
			AND `conversation_deleted` = 0";
			
	$result = mysql_query($sql);
	
	return (mysql_result($result, 0) == 1);
}

// Adds a message to the given conversation.
function add_conversation_message($conversation_id, $text){
	$conversation_id 	= (int)$conversation_id;
	$text				= mysql_real_escape_string(htmlentities($text));
	
	$sql = "INSERT INTO `conversation_messages` (`conversation_id`, `user_id`, `message_date`, `message_text`)
			VALUES ({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), '{$text};')";
	
	mysql_query($sql);
	
	mysql_query("UPDATE `conversation_members` SET `conversation_deleted` = 0 where `conversation_id = {$conversation_id}");
}

// Deletes of marks as deleted a given conversation.
function delete_conversation(){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT DISTINCT `conversation_deleted`
			FROM `conversations_members`
			WHERE `user_id` != {$_SESSION['user_id']}
			AND `conversation_id` = {$conversation_id}";
			
	$result = mysql_query($sql);
	
	if (mysql_num_rows($result) === 1 && mysql_result($result, 0) == 1){
		mysql_query("DELETE FROM `conversations` WHERE `conversation_id` = {conversation_id}");
		mysql_query("DELETE FROM `conversations_members` WHERE `conversation_id` = {conversation_id}");
		mysql_query("DELETE FROM `conversations_messages` WHERE `conversation_id` = {conversation_id}");
	}else{
		$sql = "UPDATE `conversations_members`
				SET `conversation_deleted` = 1
				WHERE `conversation_id` = {$conversation_id}
				AND `user_id` = {$_SESSION['user_id']}";
				
		mysql_query($sql);
}
}
?>

Re: Private Message System Errors

Posted: Sat Feb 11, 2012 8:24 pm
by Temor
If you echo mysql_error(); under the query on line 18 it should tell you what is causing the query to fail.

Re: Private Message System Errors

Posted: Sat Feb 11, 2012 8:38 pm
by AndrewD92
I fix this error now. It was simple as a missing S on conversation. I have another problem now tho, my inbox wont display any messages.
<?php

$errors = array();

if (isset($_GET['delete_conversation'])){
	if (validate_conversation_id($_GET['delete_conversation']) == false){
		$errors[] = 'Invalid Conversation ID.';
	}
	
	if (empty($errors)){
		delete_conversation($_GET['delete_conversation']);
	}
}

$conversations = fetch_conversation_summary();

if (empty($conversations)){
	$errors[] = 'You have no messages.';
}

if (empty($errors) === false){
	foreach ($errors as $error){
		echo $error;
	}
}

?>
<a href="index.php?page=new_conversation">New Conversation | </a>
<a href="index.php?page=logout">Logout</a>

<?php

	foreach ($conversations as $conversation){
		?>
		<div class="conversation <?php if ($conversation['unread_messages']) echo 'unread'; ?>">
			<h2>
				<a href="index.php?page=inbox&delete_conversation=<?php echo $conversation['id']; ?>">[X]</a>
				<a href="index.php?page=view_conversation&conversation_id=<?php echo $conversation['id']; ?>"><?php echo $conversation['subject']; ?>
			</h2>
			<p>Last Reply: <?php echo date('d/m/Y H:i:s', $conversation['last_reply']); ?></p>
		</div>
<?php	
	}

?>
Can you see where the error could be in my inbox code? Or maybe its my private_message back end file.
<?php

// Fetches a summary of the conversations.
function fetch_conversation_summary(){
	$sql = "SELECT
				`conversations`.`conversation_id`,
				`conversations`.`conversation_subject`,
				MAX(`conversations_messages`.`message_date`) AS `conversation_last_reply`,
				MAX(`conversations_messages`.`message_date`) > `conversations_members`.`conversation_last_view` AS `conversation_unread`
			FROM `conversations`
			LEFT JOIN `conversations_messages` ON  `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
			INNER JOIN `conversations_members` ON `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
			WHERE `conversations_members`.`user_id` = {$_SESSION['user_id']}
			AND `conversations_members`.`conversation_deleted` = 0
			GROUP BY `conversations`.`conversation_id`
			ORDER BY `conversation_last_reply` DESC";
			
	$result = mysql_query($sql);
	
	$conversations = array();
			
	while (($row = mysql_fetch_assoc($result)) !== false){
		$conversations[] = array(
			'id' 				=> $row['conversation_id'],
			'subject' 			=> $row['conversation_subject'],
			'last_reply' 		=> $row['conversation_last_reply'],
			'unread_messages'	=> ($row['conversation_unread'] == 1),
		);
		die(mysql_error());
	}

	return $conversations;
}

// Fetches all of the messages in the given converstion.
function fetch_conversation_messages($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT
				`conversations_messages`.`message_date`,
				`conversations_messages`.`message_date` > `conversations_members`.`conversation_last_view` AS `message_unread`,
				`conversations_message`.`message_text`,
				`users`.`user_name`
			FROM `conversations_messages`
			INNER JOIN `users` ON `conversations_messages`.`user_id` = `users`.`user_id`
			INNER JOIN `conversations_members` ON `conversations_messages`.`conversation_id` = `conversations_members`.`conversation_id`
			WHERE `conversations_messages`.`conversation_id` = {$conversation_id}
			AND `conversations_members`.`user_id` = {$_SESSION['user_id']}
			ORDER BY `conversations_messages`.`message_date` DESC";

	$result = mysql_query($sql);
	
	$messages = array();
	
	while (($row = mysql_fetch_assoc($result)) !== false){
		$messages[] = array(
			'date' 		=> $row['message_date'],
			'unread'	=> $row['message_unread'],
			'text' 		=> $row['message_text'],
			'user_name' => $row['user_name'],
		);
	}
	
	return $messages;
}

// Sets the last view time to the current time for the given conversation.
function update_conversation_last_view($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql ="UPDATE `conversations_members`
			SET `conversation_last_view` = UNIX_TIMESTAMP()
			WHERE `conversation_id` = {$conversation_id}
			AND `user_id` = {$_SESSION['user_id']}";
			
	mysql_query($sql);	
}

// Creates a new conversation, making the given users a member.
function create_conversation($user_ids, $subject, $body){
	$subject	= mysql_real_escape_string(htmlentities($subject));
	$body		= mysql_real_escape_string(htmlentities($body));
	
	mysql_query("INSERT INTO `conversations` (`conversation_subject`) VALUES ('{$subject}')");
	
	$conversation_id = mysql_insert_id();
	
	$sql = "INSERT INTO `conversations_messages` (`conversation_id`, `user_id`, `message_date`, `message_text`)
			VALUES ({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), '{$body}')";
			
	mysql_query($sql);
	
	$values = array("({$conversation_id}, {$_SESSION['user_id']}, {UNIX_TIMESTAMP()}, 0)");
	
	foreach ($user_ids as $user_id){
		$user_id = (int)$user_id;
		
		$values[] = "({$conversation_id}, {$user_id}, 0, 0)";
	}
	
	$sql = "INSERT INTO `conversations_members` (`conversation_id`, `user_id`, `conversation_last_view`, `conversation_deleted`)
			VALUES " . implode(", ", $values);
			
			mysql_query($sql);
}

// Checks to see if the given user is a member of the given conversation.
function validate_conversation_id($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT COUNT(1)
			FORM `conversations_members`
			WHERE `conversation_id` = {$conversation_id}
			AND `user_id` = {$_SESSION['user_id']}
			AND `conversation_deleted` = 0";
			
	$result = mysql_query($sql);
	
	return (mysql_result($result, 0) == 1);
}

// Adds a message to the given conversation.
function add_conversation_message($conversation_id, $text){
	$conversation_id 	= (int)$conversation_id;
	$text				= mysql_real_escape_string(htmlentities($text));
	
	$sql = "INSERT INTO `conversation_messages` (`conversation_id`, `user_id`, `message_date`, `message_text`)
			VALUES ({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), '{$text};')";
	
	mysql_query($sql);
	
	mysql_query("UPDATE `conversation_members` SET `conversation_deleted` = 0 where `conversation_id = {$conversation_id}");
}

// Deletes of marks as deleted a given conversation.
function delete_conversation(){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT DISTINCT `conversation_deleted`
			FROM `conversations_members`
			WHERE `user_id` != {$_SESSION['user_id']}
			AND `conversation_id` = {$conversation_id}";
			
	$result = mysql_query($sql);
	
	if (mysql_num_rows($result) === 1 && mysql_result($result, 0) == 1){
		mysql_query("DELETE FROM `conversations` WHERE `conversation_id` = {conversation_id}");
		mysql_query("DELETE FROM `conversations_members` WHERE `conversation_id` = {conversation_id}");
		mysql_query("DELETE FROM `conversations_messages` WHERE `conversation_id` = {conversation_id}");
	}else{
		$sql = "UPDATE `conversations_members`
				SET `conversation_deleted` = 1
				WHERE `conversation_id` = {$conversation_id}
				AND `user_id` = {$_SESSION['user_id']}";
				
		mysql_query($sql);
}
}
?>

Re: Private Message System Errors

Posted: Sat Feb 11, 2012 11:45 pm
by Temor
line 29 in your backend file: 
   die(mysql_error());
die() kills the script, preventing it from running any more code. This means it never gets to the return line and therefore cannot return anything.
Change die() to echo

Re: Private Message System Errors

Posted: Sun Feb 12, 2012 2:47 pm
by jacek
Or just remove it if the query is working now :D

Re: Private Message System Errors

Posted: Sun Feb 12, 2012 3:35 pm
by AndrewD92
No no, sorry. Thats a copy of the code when I was having my first problem. I just reused it on here. In my main version the die error is not there.

The problem I'm having is when I send a new message to a user it don;t add to the conversations_members database and therefore nothing appears in the inbox or view conversation files. The messages do appear in the conversations_messages database tho. Just wondering if you could see any problems in my code. Here is the proper one:
<?php

// Fetches a summary of the conversations.
function fetch_conversation_summary(){
	$sql = "SELECT
				`conversations`.`conversation_id`,
				`conversations`.`conversation_subject`,
				MAX(`conversations_messages`.`message_date`) AS `conversation_last_reply`,
				MAX(`conversations_messages`.`message_date`) > `conversations_members`.`conversation_last_view` AS `conversation_unread`
			FROM `conversations`
			LEFT JOIN `conversations_messages` ON  `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
			INNER JOIN `conversations_members` ON `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
			WHERE `conversations_members`.`user_id` = {$_SESSION['user_id']}
			AND `conversations_members`.`conversation_deleted` = 0
			GROUP BY `conversations`.`conversation_id`
			ORDER BY `conversation_last_reply` DESC";
			
	$result = mysql_query($sql);
	
	$conversations = array();
			
	while (($row = mysql_fetch_assoc($result)) !== false){
		$conversations[] = array(
			'id' 				=> $row['conversation_id'],
			'subject' 			=> $row['conversation_subject'],
			'last_reply' 		=> $row['conversation_last_reply'],
			'unread_messages'	=> ($row['conversation_unread'] == 1),
		);
	}

	return $conversations;
}

// Fetches all of the messages in the given converstion.
function fetch_conversation_messages($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT
				`conversations_messages`.`message_date`,
				`conversations_messages`.`message_date` > `conversations_members`.`conversation_last_view` AS `message_unread`,
				`conversations_message`.`message_text`,
				`users`.`user_name`
			FROM `conversations_messages`
			INNER JOIN `users` ON `conversations_messages`.`user_id` = `users`.`user_id`
			INNER JOIN `conversations_members` ON `conversations_messages`.`conversation_id` = `
			s`.`conversation_id`
			WHERE `conversations_messages`.`conversation_id` = {$conversation_id}
			AND `conversations_members`.`user_id` = {$_SESSION['user_id']}
			ORDER BY `conversations_messages`.`message_date` DESC";

	$result = mysql_query($sql);
	
	$messages = array();
	
	while (($row = mysql_fetch_assoc($result)) !== false){
		$messages[] = array(
			'date' 		=> $row['message_date'],
			'unread'	=> $row['message_unread'],
			'text' 		=> $row['message_text'],
			'user_name' => $row['user_name'],
		);
	}
	
	return $messages;
}

// Sets the last view time to the current time for the given conversation.
function update_conversation_last_view($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql ="UPDATE `conversations_members`
			SET `conversation_last_view` = UNIX_TIMESTAMP()
			WHERE `conversation_id` = {$conversation_id}
			AND `user_id` = {$_SESSION['user_id']}";
			
	mysql_query($sql);	
}

// Creates a new conversation, making the given users a member.
function create_conversation($user_ids, $subject, $body){
	$subject	= mysql_real_escape_string(htmlentities($subject));
	$body		= mysql_real_escape_string(htmlentities($body));
	
	mysql_query("INSERT INTO `conversations` (`conversation_subject`) VALUES ('{$subject}')");
	
	$conversation_id = mysql_insert_id();
	
	$sql = "INSERT INTO `conversations_messages` (`conversation_id`, `user_id`, `message_date`, `message_text`)
			VALUES ({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), '{$body}')";
			
	mysql_query($sql);
	
	$values = array("({$conversation_id}, {$_SESSION['user_id']}, {UNIX_TIMESTAMP()}, 0)");
	
	foreach ($user_ids as $user_id){
		$user_id = (int)$user_id;
		
		$values[] = "({$conversation_id}, {$user_id}, 0, 0)";
	}
	
	$sql = "INSERT INTO `conversations_members` (`conversation_id`, `user_id`, `conversation_last_view`, `conversation_deleted`)
			VALUES " . implode(", ", $values);
			
			mysql_query($sql);
}

// Checks to see if the given user is a member of the given conversation.
function validate_conversation_id($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT COUNT(1)
			FORM `conversations_members`
			WHERE `conversation_id` = {$conversation_id}
			AND `user_id` = {$_SESSION['user_id']}
			AND `conversation_deleted` = 0";
			
	$result = mysql_query($sql);
	
	return (mysql_result($result, 0) == 1);
}

// Adds a message to the given conversation.
function add_conversation_message($conversation_id, $text){
	$conversation_id 	= (int)$conversation_id;
	$text				= mysql_real_escape_string(htmlentities($text));
	
	$sql = "INSERT INTO `conversation_messages` (`conversation_id`, `user_id`, `message_date`, `message_text`)
			VALUES ({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), '{$text};')";
	
	mysql_query($sql);
	
	mysql_query("UPDATE `conversations_members` SET `conversation_deleted` = 0 where `conversation_id = {$conversation_id}");
}

// Deletes of marks as deleted a given conversation.
function delete_conversation(){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT DISTINCT `conversation_deleted`
			FROM `conversations_members`
			WHERE `user_id` != {$_SESSION['user_id']}
			AND `conversation_id` = {$conversation_id}";
			
	$result = mysql_query($sql);
	
	if (mysql_num_rows($result) === 1 && mysql_result($result, 0) == 1){
		mysql_query("DELETE FROM `conversations` WHERE `conversation_id` = {conversation_id}");
		mysql_query("DELETE FROM `conversations_members` WHERE `conversation_id` = {conversation_id}");
		mysql_query("DELETE FROM `conversations_messages` WHERE `conversation_id` = {conversation_id}");
	}else{
		$sql = "UPDATE `conversations_members`
				SET `conversation_deleted` = 1
				WHERE `conversation_id` = {$conversation_id}
				AND `user_id` = {$_SESSION['user_id']}";
				
		mysql_query($sql);
}
}
?>

Re: Private Message System Errors

Posted: Mon Feb 13, 2012 12:49 am
by jacek
Okay, does adding the
echo mysql_error();
after the mysql_query() line show anything useful ?

Re: Private Message System Errors

Posted: Mon Feb 13, 2012 11:24 am
by AndrewD92
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 ')}, 0), (19, 14, 0, 0)' at line 2

then I refreshed and it slightly changed.

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 ')}, 0), (20, 14, 0, 0)' at line 2

This means nothing to me? I'm confused.

Re: Private Message System Errors

Posted: Mon Feb 13, 2012 2:42 pm
by jacek
Ah okay, on this line
$values = array("({$conversation_id}, {$_SESSION['user_id']}, {UNIX_TIMESTAMP()}, 0)");
You should not have the { and } around unix_timestamp()

Re: Private Message System Errors

Posted: Mon Feb 13, 2012 9:27 pm
by AndrewD92
Well, as the tree said to the lumberjack, I'm stumped.
I done what you said but the same happens. Nothing adds to the conversations_members database. I have no idea what the problem is.

Re: Private Message System Errors

Posted: Tue Feb 14, 2012 1:13 pm
by jacek
Post your code with the changes you made (just that one function will do) and also the result of mysql_error() which should have changed.

Re: Private Message System Errors

Posted: Wed Feb 15, 2012 5:01 pm
by AndrewD92
Here is my code for private_message.inc.php.
Adding the die error shows nothing now.
<?php

// Fetches a summary of the conversations.
function fetch_conversation_summary(){
	$sql = "SELECT
				`conversations`.`conversation_id`,
				`conversations`.`conversation_subject`,
				MAX(`conversations_messages`.`message_date`) AS `conversation_last_reply`,
				MAX(`conversations_messages`.`message_date`) > `conversations_members`.`conversation_last_view` AS `conversation_unread`
			FROM `conversations`
			LEFT JOIN `conversations_messages` ON  `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
			INNER JOIN `conversations_members` ON `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
			WHERE `conversations_members`.`user_id` = {$_SESSION['user_id']}
			AND `conversations_members`.`conversation_deleted` = 0
			GROUP BY `conversations`.`conversation_id`
			ORDER BY `conversation_last_reply` DESC";
			
	$result = mysql_query($sql);
	
	$conversations = array();
			
	while (($row = mysql_fetch_assoc($result)) !== false){
		$conversations[] = array(
			'id' 				=> $row['conversation_id'],
			'subject' 			=> $row['conversation_subject'],
			'last_reply' 		=> $row['conversation_last_reply'],
			'unread_messages'	=> ($row['conversation_unread'] == 1),
		);
	}

	return $conversations;
}

// Fetches all of the messages in the given converstion.
function fetch_conversation_messages($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT
				`conversations_messages`.`message_date`,
				`conversations_messages`.`message_date` > `conversations_members`.`conversation_last_view` AS `message_unread`,
				`conversations_message`.`message_text`,
				`users`.`user_name`
			FROM `conversations_messages`
			INNER JOIN `users` ON `conversations_messages`.`user_id` = `users`.`user_id`
			INNER JOIN `conversations_members` ON `conversations_messages`.`conversation_id` = `
			s`.`conversation_id`
			WHERE `conversations_messages`.`conversation_id` = {$conversation_id}
			AND `conversations_members`.`user_id` = {$_SESSION['user_id']}
			ORDER BY `conversations_messages`.`message_date` DESC";

	$result = mysql_query($sql);
	
	$messages = array();
	
	while (($row = mysql_fetch_assoc($result)) !== false){
		$messages[] = array(
			'date' 		=> $row['message_date'],
			'unread'	=> $row['message_unread'],
			'text' 		=> $row['message_text'],
			'user_name' => $row['user_name'],
		);
	}
	
	return $messages;
}

// Sets the last view time to the current time for the given conversation.
function update_conversation_last_view($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql ="UPDATE `conversations_members`
			SET `conversation_last_view` = UNIX_TIMESTAMP()
			WHERE `conversation_id` = {$conversation_id}
			AND `user_id` = {$_SESSION['user_id']}";
			
	mysql_query($sql);	
}

// Creates a new conversation, making the given users a member.
function create_conversation($user_ids, $subject, $body){
	$subject	= mysql_real_escape_string(htmlentities($subject));
	$body		= mysql_real_escape_string(htmlentities($body));
	
	mysql_query("INSERT INTO `conversations` (`conversation_subject`) VALUES ('{$subject}')");
	
	$conversation_id = mysql_insert_id();
	
	$sql = "INSERT INTO `conversations_messages` (`conversation_id`, `user_id`, `message_date`, `message_text`)
			VALUES ({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), '{$body}')";
			
	mysql_query($sql);
	
	$values = array("({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), 0)");
	
	foreach ($user_ids as $user_id){
		$user_id = (int)$user_id;
		
		$values[] = "({$conversation_id}, {$user_id}, 0, 0)";
	}
	
	$sql = "INSERT INTO `conversations_members` (`conversation_id`, `user_id`, `conversation_last_view`, `conversation_deleted`)
			VALUES " . implode(", ", $values);
			
			mysql_query($sql);
}

// Checks to see if the given user is a member of the given conversation.
function validate_conversation_id($conversation_id){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT COUNT(1)
			FORM `conversations_members`
			WHERE `conversation_id` = {$conversation_id}
			AND `user_id` = {$_SESSION['user_id']}
			AND `conversation_deleted` = 0";
			
	$result = mysql_query($sql);
	
	return (mysql_result($result, 0) == 1);

}

// Adds a message to the given conversation.
function add_conversation_message($conversation_id, $text){
	$conversation_id 	= (int)$conversation_id;
	$text				= mysql_real_escape_string(htmlentities($text));
	
	$sql = "INSERT INTO `conversation_messages` (`conversation_id`, `user_id`, `message_date`, `message_text`)
			VALUES ({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), '{$text};')";
	
	mysql_query($sql);
	
	mysql_query("UPDATE `conversations_members` SET `conversation_deleted` = 0 where `conversation_id = {$conversation_id}");
}

// Deletes of marks as deleted a given conversation.
function delete_conversation(){
	$conversation_id = (int)$conversation_id;
	
	$sql = "SELECT DISTINCT `conversation_deleted`
			FROM `conversations_members`
			WHERE `user_id` != {$_SESSION['user_id']}
			AND `conversation_id` = {$conversation_id}";
			
	$result = mysql_query($sql);
	
	if (mysql_num_rows($result) === 1 && mysql_result($result, 0) == 1){
		mysql_query("DELETE FROM `conversations` WHERE `conversation_id` = {conversation_id}");
		mysql_query("DELETE FROM `conversations_members` WHERE `conversation_id` = {conversation_id}");
		mysql_query("DELETE FROM `conversations_messages` WHERE `conversation_id` = {conversation_id}");
	}else{
		$sql = "UPDATE `conversations_members`
				SET `conversation_deleted` = 1
				WHERE `conversation_id` = {$conversation_id}
				AND `user_id` = {$_SESSION['user_id']}";
				
		mysql_query($sql);
}
}
?>

Re: Private Message System Errors

Posted: Fri Feb 17, 2012 5:07 pm
by jacek
Could you try outputting the built SQL query from line 101. Just an an echo $sql after it :)

It might be that $user_ids has no users in it.

Re: Private Message System Errors

Posted: Fri Feb 17, 2012 8:56 pm
by AndrewD92
After adding echo ($sql); on line 101 I get this message:

INSERT INTO `conversations_members` (`conversation_id`, `user_id`, `conversation_last_view`, `conversation_deleted`) VALUES (30, 14, UNIX_TIMESTAMP(), 0), (30, 15, 0, 0)

and now its working? Im so confused. I didst do anything apart from add echo ($sql) and not i have 1 unread message. Is echo ($sql) a required part of the code? But the date and time is wrong. Last Reply: 11/02/2012 21:15:32

And now there are a whole bunch of new errors I have to try and fix,

Warning: mysql_result() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\PrivateMessage\core\inc\private_message.inc.php on line 121

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 '`conversations_members` WHERE `conversation_id` = 1 AND `user_id` = 14 ' at line 2

Thanks for the help!

Re: Private Message System Errors

Posted: Fri Feb 17, 2012 9:58 pm
by Temor
There is a typo on line 112.

Re: Private Message System Errors

Posted: Fri Feb 17, 2012 10:04 pm
by AndrewD92
Thanks! This one is so buggy. I have worked out most of the errors now with help from you guys ! :D Im gona have to rewatch the videos. I think I have missed something in view_conversation.page.inc.php and the delete conversation function.

Re: Private Message System Errors

Posted: Fri Feb 17, 2012 10:28 pm
by AndrewD92
To save time, does anyone know off the top of their head where the messages variable was declared. Was it in private_messages.inc.php?

Re: Private Message System Errors

Posted: Sun Feb 19, 2012 1:23 am
by jacek
AndrewD92 wrote:To save time, does anyone know off the top of their head where the messages variable was declared. Was it in private_messages.inc.php?
Which variable ? It should be declared int he file you use it in. $messages was used a few times ;)

Re: Private Message System Errors

Posted: Sun Feb 19, 2012 2:03 pm
by AndrewD92
Im rewatching the project videos from the beginning as I have a whole bundle of errors, although I think I could skip a few if this problem was solved so as I could view the messages

Notice: Undefined variable: messages in C:\xampp\htdocs\PrivateMessage\core\pages\view_conversation.page.inc.php on line 46

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\PrivateMessage\core\pages\view_conversation.page.inc.php on line 46
<?php

$errors = array();

$valid_conversation = (isset($_GET['conversation_id']) && validate_conversation_id($_GET['conversation_id']));

if ($valid_conversation === false){
	$errors[] = 'Invalid Conversation ID.';
}

if (isset($_POST['message'])){
	if (empty($_POST['message'])){
		$errors[] = 'You must enter a message.';
	}
	
	if (empty($errors)){
		add_conversation_message($_GET['conversation_id'], $_POST['message']);
	}
}

if (empty($errors) === false){
	foreach ($errors as $error){
	}
}

if ($valid_conversation){
	if (isset($_POST['message'])){
		update_conversation_last_view($_GET['conversation_id']);
		$messages = fetch_conversation_messages($_GET['conversation_id']);
	}else{
		$messages = fetch_conversation_messages($_GET['conversation_id']);
		update_conversation_last_view($_GET['conversation_id']);
	}
}
?>
<a href="index.php?page=inbox">Inbox</a>
<a href="index.php?page=logout">Logout</a>

	<form action="" method="post">
		<p><textarea name="message" rows="10" cols="110"></textarea></p>
		<p><input type="submit" value="Add Message" /></p>
	</form>
	
	<?php
	
	foreach ($messages as $message){
		?>
		<div class="message <?php if ($message['unread']) echo 'unread'; ?>"</div>
			<p>Username: <?php echo $message['user_name']; ?></p>
			<p>Date: <?php echo date('d/m/Y H:i:s', $message['date']); ?></p>
			<p>Message: <?php echo $message['text']; ?></p>

<?php

}
?>
Messages is declared on line 46, right?

Re: Private Message System Errors

Posted: Sun Feb 19, 2012 8:36 pm
by jacek
No, it's declared on line 29 or 31 and used on line 46

One of the validation checks must be failing, try completing the loop on line 22 and see if that shows the problem :?
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited