PHP Private Message Tutorial - Delete function not working

[petrus123]

Hello, I am new to this forum.

I have been following the brilliant PHP private message tutorial, but have run into a problem. I have tried to fix it but so far have not had any luck.

The problem is my delete function. Whilst there are no error messages, it does nothing. It does not change the value in the database. Additionally, if, in my database, I change the conversation_deleted value for any conversation, nothing happens on anyone's account.

Please can you help me!

Thank you in advance.

Below is my private_message.inc.php page

If you need any other code, please let me know.

[syntax=php] <?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)
FROM `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 `conversations_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 (or marks as deleted) a given conversation.
function delete_conversation($conversation_id){
$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);

}
}
?>[/syntax]

[petrus123]

Improvement: Now I have got the function to get the values in the database, but there are three main problems that I can clearly see:

1) It will not do anything unless I delete the top message
2) It deletes all messages if I delete the top message
3) If anyone sends a user a message, that user will see all of their previous messages, even when they are completely deleted from the database

I am getting very confused.

Please help!

Thank you in advance.

[Temor]

Hello, and welcome to the Forums!

I'll do my best to walk you through this troubleshooting.

Could show me the structure of your database? I haven't looked at it in quite a while so I could use a refresh on that. It also might contain a typo or an invalid datatype.

Even though it does not show you any errors, there might be hidden ones. Try echoing mysql_error();
[syntax=php]echo mysql_error();[/syntax] to see if anything shows.



If you post all of your code here I'll take a look at it with you

[petrus123]

Thank you very much.

My database has four tables:

conversations - conversation_id and conversation_subject

conversations_members - conversation_id, user_id, conversation_last_view, conversation_deleted

conversations_messages - message_id, conversation_id, user_id, message_date, message_text

users - user_id, user_name, user_password

Attached is all my code as a .zip file.

As for the error debugging, where would I put the line of code you suggested?

Sorry about the dumb question and the almost-certainly-dumb mistake.

Also, I want to publish this on Friday, so the earlier your reply, the better.

Thank you in advance.

[Temor]

Could you export your database to an sql file and zip that up too? I need it to play around with the code.
At first glance I can't really see what's wrong. But I'll take a good look at it later tonight or tomorrow.

[petrus123]

Thank you for replying so promptly.

I am totally new to MySQL, but I've just exported the whole database (hopefully I've done it correctly).

[syntax=sql]

-- phpMyAdmin SQL Dump
-- version 3.5.2.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 11, 2014 at 01:05 PM
-- Server version: 5.1.61
-- PHP Version: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `u406538221_chat`
--

-- --------------------------------------------------------

--
-- Table structure for table `conversations`
--

CREATE TABLE IF NOT EXISTS `conversations` (
`conversation_id` int(100) NOT NULL AUTO_INCREMENT,
`conversation_subject` varchar(128) COLLATE latin1_general_ci NOT NULL,
PRIMARY KEY (`conversation_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=45 ;

--
-- Dumping data for table `conversations`
--

INSERT INTO `conversations` (`conversation_id`, `conversation_subject`) VALUES
(1, ''),
(2, 'test'),
(3, 'different'),
(4, ''),
(5, ''),
(6, ''),
(7, ''),
(8, 'test'),
(9, 'testing'),
(10, 'test'),
(11, 'test'),
(12, 'another test'),
(13, 'test'),
(14, 'Is this in bold?'),
(15, 'Is THIS in bold?'),
(16, 'IS THIS IN BOLD?'),
(17, 'have I fixed it?'),
(18, 'Bold?'),
(19, 'test'),
(20, 'test'),
(23, 'can this be deleted?'),
(24, 'can this be deleted?'),
(25, '1'),
(26, '2'),
(27, '1'),
(28, '2'),
(29, '3'),
(30, 'hi'),
(31, 'deletion'),
(32, 'test'),
(33, 'peter'),
(34, 'failure'),
(35, 'test'),
(36, 'another test'),
(37, 'greetings'),
(38, 'greetings'),
(39, 'testing deletion'),
(40, 'greetings'),
(41, 'another test'),
(42, 'afuiasdhfioljh'),
(43, 'final test (for now)'),
(44, 'peter');

-- --------------------------------------------------------

--
-- Table structure for table `conversations_members`
--

CREATE TABLE IF NOT EXISTS `conversations_members` (
`conversation_id` int(100) NOT NULL,
`user_id` int(100) NOT NULL,
`conversation_last_view` int(10) NOT NULL,
`conversation_deleted` int(1) NOT NULL,
UNIQUE KEY `unique` (`conversation_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `conversations_members`
--

INSERT INTO `conversations_members` (`conversation_id`, `user_id`, `conversation_last_view`, `conversation_deleted`) VALUES
(42, 1, 1392128078, 1),
(41, 2, 0, 0),
(41, 1, 1392127897, 1),
(32, 2, 0, 0),
(32, 1, 1392101536, 1),
(40, 2, 0, 0),
(40, 1, 1392127788, 1),
(39, 4, 0, 0),
(39, 2, 0, 0),
(39, 1, 1392126336, 1),
(38, 2, 0, 0),
(38, 1, 1392126178, 1),
(37, 2, 0, 0),
(37, 1, 1392117939, 1),
(36, 2, 0, 0),
(36, 1, 1392117918, 1),
(35, 2, 0, 0),
(35, 1, 1392117880, 1),
(34, 2, 0, 0),
(34, 1, 1392101963, 1),
(33, 2, 0, 0),
(33, 1, 1392101640, 1),
(43, 1, 1392128603, 1),
(42, 2, 0, 0),
(44, 1, 1392140035, 1),
(43, 2, 0, 0),
(44, 2, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `conversations_messages`
--

CREATE TABLE IF NOT EXISTS `conversations_messages` (
`message_id` int(200) NOT NULL AUTO_INCREMENT,
`conversation_id` int(100) NOT NULL,
`user_id` int(100) NOT NULL,
`message_date` int(10) NOT NULL,
`message_text` text COLLATE latin1_general_ci NOT NULL,
PRIMARY KEY (`message_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=62 ;

--
-- Dumping data for table `conversations_messages`
--

INSERT INTO `conversations_messages` (`message_id`, `conversation_id`, `user_id`, `message_date`, `message_text`) VALUES
(54, 37, 1, 1392117939, 'greetings, me!'),
(53, 36, 1, 1392117918, 'another test'),
(52, 35, 1, 1392117880, 'test'),
(51, 34, 1, 1392101963, 'fail'),
(50, 33, 1, 1392101640, 'peter'),
(49, 32, 1, 1392101536, 'test'),
(48, 31, 1, 1392101022, 'deletion'),
(47, 30, 1, 1392100706, 'hi'),
(45, 28, 1, 1392100641, '2'),
(46, 29, 1, 1392100658, '3'),
(44, 27, 1, 1392100637, '1'),
(43, 26, 1, 1392100542, '2'),
(42, 25, 1, 1392100536, '1'),
(41, 24, 1, 1392100436, 'delete'),
(40, 23, 1, 1392100399, 'Can I delete this message with my current delete function? Is it working?'),
(55, 38, 1, 1392126178, 'greetings'),
(56, 39, 1, 1392126336, 'testing'),
(57, 40, 1, 1392127788, 'greetings'),
(58, 41, 1, 1392127897, 'testing again'),
(59, 42, 1, 1392128078, 'ahdfioashdfuiashduifasghdiof'),
(60, 43, 1, 1392128603, 'this is the final test of the inbox.page.inc.php and private_message.inc.php pages'),
(61, 44, 1, 1392140035, 'peter');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
`user_id` int(100) NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) COLLATE latin1_general_ci NOT NULL,
`user_password` varchar(40) COLLATE latin1_general_ci NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `user_name`, `user_password`) VALUES
(2, 'Peter', 'a94a8fe5ccb19ba61c4c0873d391e987982fbbd3'),
(3, 'Haden', 'a94a8fe5ccb19ba61c4c0873d391e987982fbbd3'),
(4, 'Alfie', 'a94a8fe5ccb19ba61c4c0873d391e987982fbbd3');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[/syntax]

Thank you very much.

Let me know how you're doing with the checking.

p.s. in case you're wondering, all the passwords being the same is just for the export, it's not actually like that

[petrus123]

Sorry - I should have zipped the .sql up like you said.

Attached is the zipped version.

Thank you again for helping me.

Oh, and by the way, I changed the password of the database for the purposes of security.

[Temor]

Thanks. I won't have time to look at it in depth today but I'll have an answer for you by tomorrow.

[petrus123]

Great - Thank you very much!

[ScTech]

I won't be able to get on for the next few days and have to go in a few minutes. Sorry I don't have time to help fix it.

Temor, if you don't find a solution, perhaps take a look at the create_ conversation() funtion specifically line 93. I've never watched the tutorial but this array looks weird as it's just one thing inside.
[syntax=php] $values = array("({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), 0)");[/syntax]

Also, the add_conversation_message() function query, there's a semicolon after $text.

[petrus123]

Good news - I followed the advice of ScTech and changed that line of code

Good news - It deletes messages

Good news - They do not come back when another message is sent

Bad news - They are deleted for everyone - not just the user who deleted them

Bad news - I have another problem:

The view_conversation page is only giving me the option to add to the conversation, but I cannot actually view the original message.

Attached is my new code zip. The database remains the same.

Please reply soon - as I said, I really want to get this out on Friday.

[ScTech]

Which file belongs to the view_conversation page? The view_conversation.page.inc.php file is blank for me. My guess since it's deleting everything is that your session is not set so the query deletes everything. I can tell you after I see the page.