Select from Database Query not Filtering

MartynLearnsPHP · November 15, 2014

Last Christmas my wife bought me a book on html and css because I said that I have always wanted to write a website. Over the last 10 months I have really enjoyed learning web design and have progressed to try and learn php and using myPHPadmin databases.

I am now trying to write a private message function.

I've got a members database and a conversation database. I am trying to filter out whether a private message is 'read' or 'unread', but whatever I try it is still listing all messages under both options. I've spent 48 hours staring at this bit of code. Can anyone see what I have done wrong?

The relevant (and self-explanatory) fields from my members database are:
id
username
member_first_name
member_last_name

The relevant fields from my conversation database are:
id
id2 (always set as '1' to count messages in a conversation)
member1 (the initial sender of the first message)
member2 (the initial recipient of the first message)
read1 (member1 has read this message default to yes when message sent and to no when message received)
read2 (member2 has read this message default to yes when message sent and to no when message received)
removed1 (member1 has deleted this message from their record)
removed2 (member2 has deleted this message from their record)
time (timestamp)

<?php
$query1 = DB::getInstance()->query("SELECT c1.id, c1.title, c1.time, c1.removed1, c1.removed2, count(c2.id) AS reps, 
                    m.id as memberid, m.username m.member_first_name, m.member_last_name
                    FROM conversation as c1, conversation as c2, members as m
                    WHERE ((c1.member1='{$logid}' AND c1.read1='No' AND c1.removed1='No'
                    AND m.id=c1.member2)
                    OR (c1.member2='{$logid}' AND c1.read2='No' AND c1.removed2='No'
                    AND m.id=c1.member1))
                    AND c1.id2='1' AND c2.id=c1.id
                    GROUP BY c1.id
                    ORDER BY c1.id DESC");
$query2 = DB::getInstance()->query("SELECT c1.id, c1.title, c1.time, c1.removed1, c1.removed2, count(c2.id) as reps, 
                    m.id as memberid, m.username, m.member_first_name, m.member_last_name
                    FROM conversation as c1, conversation as c2, members as m
                    WHERE ((c1.member1='{$logid}' AND c1.read1='Yes' AND c1.removed1='No'
                    AND m.id=c1.member2)
                    OR (c1.member2='{$logid}' AND c1.read2='Yes' AND c1.removed2='No' AND
                    m.id=c1.member1))
                    AND c1.id2='1' AND c2.id=c1.id
                    GROUP BY c1.id
                    ORDER BY c1.id DESC");
?>

UNREAD MESSAGES (<?php echo intval($query1->count()); ?>)
<br>
READ MESSAGES (<?php echo intval($query2->count()); ?>)

I've tried running through things in a logical path:

UNREAD MESSAGES

member1 = logged in user
read1 = No
removed1 = No
members.id = member2

OR

member2 = logged in user
read2 = No
removed2 = No
members.id = member1

READ MESSAGES

member1 = logged in user
read1 = Yes
removed1 = No
members.id = member2

OR

member2 = logged in user
read2 = Yes
removed2 = No
members.id = member1

I can't understand how if 'read1 = yes and the logged in user is member1, why they are being counted in the unread list.

Can anyone see what I have done wrong?

Edited November 15, 2014 by MartynLearnsPHP

Barand · November 15, 2014

Very confusing SQL queries.

When you have a table like your "conversation" table which references two member ids it is usual to connect the member table twice with different aliases and not the conversation table.

It is much better to to use explicit join syntax rather then "...FROM A,B,C WHERE...".

it is more efficient
it separates the structure of the query from the record selection criteria

eg

SELECT m1.username as user1, m2.username as user2, ....
FROM conversation c
    JOIN member m1 ON c.member1 = m1.id
    JOIN member m2 ON c.member2 = m2.id
WHERE .....

MartynLearnsPHP · November 15, 2014

Thanks, Barand.

I tried adapting this tutorial to suit my requirements: http://www.webestools.com/scripts_tutorials-code-source-15-personal-message-system-in-php-mysql-pm-system-private-message-discussion.html

But I will try and rewrite it using your suggestions.

MartynLearnsPHP · November 15, 2014

Well, I've tried using JOINS as per the suggestion. This is what I came up with:

<?php
$query1 = DB::getInstance()->query("SELECT c.id, c.title, c.time, c.removed1, c.removed2, count(c.id) as reps, 
					m1.username as user1, m1.member_first_name as firstname1, m1.member_last_name as lastname1, 
					m2.username as user2, m2.member_first_name as firstname2, m2.member_last_name as lastname2, 
					FROM conversation as c
						JOIN members as m1 ON c.member1 = m1.id
						JOIN members as m2 ON c.member2 = m2.id
					where ((c.member1='{$memberid}' AND c.read1='No' AND c.removed1='No' AND m1.id=c.member2) 
					OR (c.member2='{$memberid}' AND c.read2='No' AND removed2='No' AND m2.id=c.member1)) 
					AND c.id2='1' AND (c.id=m1.id or c.id=m2.id)
					GROUP BY c.id ORDER BY c.id DESC");
$query2 = DB::getInstance()->query("SELECT c.id, c.title, c.time, c.removed1, c.removed2, count(c.id) as reps, 
					m1.username as user1, m1.member_first_name as firstname1, m1.member_last_name as lastname1, 
					m2.username as user2, m2.member_first_name as firstname2, m2.member_last_name as lastname2, 
					FROM conversation as c
						JOIN members as m1 ON c.member1 = m1.id
						JOIN members as m2 ON c.member2 = m2.id
					where ((c.member1='{$memberid}' AND c.read1='Yes' AND c.removed1='No' AND m1.id=c.member2) 
					OR (c.member2='{$memberid}' AND c.read2='Yes' AND removed2='No' AND m2.id=c.member1)) 
					AND c.id2='1' AND (c.id=m1.id OR c.id=m2.id)
					GROUP BY c.id ORDER BY c.id DESC");
?>

					
UNREAD MESSAGES (<?php echo intval($query1->count()); ?>)
<br>
UNREAD MESSAGES (<?php echo intval($query2->count()); ?>)

But all it is doing is counting every member in my database. My original code (using the tutorial linked to above) did the job of sifting out only the messages, the only problem was that it didn't separate read from unread messages.

Barand · November 15, 2014

AND (c.id=m1.id or c.id=m2.id)

Why?

You use GROUP BY c.id. If id is unique, and it probably is, then you you get a total count of 1 per id.

What are you trying to count in "reps"?

MartynLearnsPHP · November 15, 2014

The reps is to count the number of replies within a string of messages, so that I can provide a message summary to the user.

Replies to Message: <?php echo $result1->reps-1; ?>

The AND... input was a legacy of endless attempts to try and get this working. It serves no purpose, but it doesn't affect anything if I do take it out.

Barand · November 15, 2014

Your message table does not contain a link to the message it is replying to, so how do expect to count the replies?

MartynLearnsPHP · November 15, 2014

Sorry, the message table has a column called title (which I forgot to include) so the idea is that it counts replies against messages with that title.

Barand · November 15, 2014



SELECT c.id, c.title, c.time, c.removed1, c.removed2, ct.reps, 

m1.username as user1, m1.member_first_name as firstname1, m1.member_last_name as lastname1, 

m2.username as user2, m2.member_first_name as firstname2, m2.member_last_name as lastname2, 

FROM conversation as c

    JOIN members as m1 ON c.member1 = m1.id

    JOIN members as m2 ON c.member2 = m2.id

    JOIN (

        SELECT c.title, COUNT(*) as reps

        FROM conversation

        GROUP BY title

    ) as ct ON c.title = ct.title

WHERE ((c.member1='{$memberid}' AND c.read1='No' AND c.removed1='No' ) 

    OR (c.member2='{$memberid}' AND c.read2='No' AND removed2='No' )) 

    AND c.id2='1' 

ORDER BY c.id DESC

MartynLearnsPHP · November 16, 2014

Barand. I really, really appreciate all your help.

I've done a quick cut and paste of your coding into my coding, but it is still just showing a list of everyone in the member table (who share the same project) and the title is showing as blank and last posts are showing as 45 years ago. This is probably something that I have done wrong.

I will have a good night's sleep now and then try and work out how/if I can implement your coding.

I will let you know if I can get it to work and what I needed to tweak to make it work.

But, in summary, this is what I currently have for this page (in it's entirety):

<?php
require 'core/memberinit.php';
include 'timeago.php';
$member = new Member();
if(!$member->isLoggedIn()) {
	echo "<script>window.close();</script>";
}
$memberid = $member->data() ->id;
$admin = $member->data() ->admin_username;

if (!empty($_POST['compose'])) {
	if(Input::exists()) {
		if(Token::check(Input::get('token'))) {
		$validate = new Validate();
		$validation = $validate->check($_POST, array(
			'title' => array(
				'alias' => 'title',
				'required' => true,
				'min' => 1
			),
			'message' => array(
				'alias' => 'Message',
				'required' => true,
				'min' => 1
			),
			'member2' => array(
				'alias' => 'Recipient',
				'required' => true
			)
		));
		if($validation->passed()) {
			try { 
				$conversation = DB::getInstance()->insert(conversation, array(
					'id2' => "1",
					'title' => Input::get('title'),
					'member1' => $memberid,
					'member2' =>  Input::get('member2'),
					'message' => Input::get('message'),
					'read1' => "Yes",
					'read2' => "No",
					'time' => date('Y-m-d H:i:s')
				));
			
				} catch(Exception $e) {
				die($e->getMessage());
			}
					
			Redirect::to('privatemessages.php');
			} else {
			$errors = $validate->errors();
			}
		} 
	}
}
?>
<!DOCTYPE html>
<html>

<head>
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <title>Private Messages - Your Bunker</title>
  <link rel="stylesheet" href="//code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
  <script src="//code.jquery.com/jquery-1.10.2.js"></script>
  <script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
  <link href="style.css" rel="stylesheet" type="text/css" />
  <script type= "text/javascript" src = "countries.js"></script>
  <script>
  $(function() {
  $( "#datepicker" ).datepicker();
  });
  </script>
</head>

<body>

<div class="style3"></div><div class="style_2"><span class="style3" ><strong>Your Bunker</strong></span></div>

<div id="wrap">

<div id="topbar">
	<h1 id="sitename"><a href="index.html" target="_blank"><?php echo escape($member->data() ->username); ?>'s Private Messages</a></h1>
	<div id="menus">
	<ul id="topmenu">

	   <li><a href="memberbunker.php">The Bunker</a>
	   </li>
	   <li><a href="memberchangepassword.php">Change Password</a>
	   </li>
	   <li  class="active"><a href="memberprojects.php">Projects</a>
	   </li>
	   <li><a href="forum/index.php">The Bunker Forum</a>
	   </li>
	   <li><a href="logout.php">Log out</a>
	   </li>
	   <li><a href="membercontactus.php">Contact Us</a>
	   </li>
	   </ul>
	   </div>
	   </div>



<div id="content">
<div id="mainpage">
<div id="mainpage2">
<h2>PRIVATE MESSAGES:</h2>

<br><br>


<h2>Compose a New Message:</h2><br><br>

<form method="post" action="">
<table>
	<tr><col width="200"><col width="650">
		<td valign="top">
			<b>Select a Member from your Bunker:</b><br><br>
			<select name='member2'>
					<option value=''>Please Select</option>
			<?php
				$friends = DB::getInstance()->query("SELECT * FROM members WHERE admin_username='{$admin}' 
							ORDER BY member_last_name, member_first_name");
				foreach ($friends->results() as $friendresult) {
					echo "<option value='";
					echo $friendresult->id;
					echo "'>";
					echo $friendresult->member_first_name;
					echo "&nbsp";
					echo $friendresult->member_last_name;
					echo "</option>";
				}
			?>
		</td>
		<td valign="top" align="center">
			<b><label>Subject: </label><b>
			<input type="text" id="title" name="title" >
			<br><br>
			<b><label>Compose your private message:</label></b><br>
			<textarea name ="message" cols="60" rows="5"><?php echo htmlentities($omessage, ENT_QUOTES, 'UTF-8'); ?></textarea><br>
			<input type="hidden" name="member1" value="<?php echo $logid; ?>" >
			<input type="hidden" name"id2" value="1"> 
			<input type="hidden" name="token" value="<?php echo Token::generate(); ?>" >
			<input type="submit" class="adminbutton" value="Send Message" name="compose">
		</td>
	</tr>
</table>
</form>

<br><br>


	
<div id="bunkeradmin">

<h2>Messages</h2>

<?php
$query1 = DB::getInstance()->query("SELECT c.id, c.title, c.time, c.removed1, c.removed2, ct.reps, 
					m1.username as user1, m1.member_first_name as firstname1, m1.member_last_name as lastname1, 
					m2.username as user2, m2.member_first_name as firstname2, m2.member_last_name as lastname2, 
				FROM conversation as c
					JOIN members as m1 ON c.member1 = m1.id
					JOIN members as m2 ON c.member2 = m2.id
					JOIN (
						SELECT c.title, COUNT(*) as reps
						FROM conversation
						GROUP BY title
						) as ct ON c.title = ct.title
				WHERE ((c.member1='{$memberid}' AND c.read1='No' AND c.removed1='No' ) 
					OR (c.member2='{$memberid}' AND c.read2='No' AND c.removed2='No' )) 
					AND c.id2='1' 
				ORDER BY c.id DESC");
$query2 = DB::getInstance()->query("SELECT c.id, c.title, c.time, c.removed1, c.removed2, ct.reps, 
					m1.username as user1, m1.member_first_name as firstname1, m1.member_last_name as lastname1, 
					m2.username as user2, m2.member_first_name as firstname2, m2.member_last_name as lastname2, 
				FROM conversation as c
					JOIN members as m1 ON c.member1 = m1.id
					JOIN members as m2 ON c.member2 = m2.id
					JOIN (
						SELECT c.title, COUNT(*) as reps
						FROM conversation
						GROUP BY title
						) as ct ON c.title = ct.title
				WHERE ((c.member1='{$memberid}' AND c.read1='yes' AND c.removed1='No' ) 
					OR (c.member2='{$memberid}' AND c.read2='yes' AND c.removed2='No' )) 
					AND c.id2='1' 
				ORDER BY c.id DESCC");
?>

					
<br><br>

<h6>UNREAD MESSAGES (<?php echo intval($query1->count()); ?>)</h6>

<br>

<table><col width="200px"><col width="100px">

<?php
foreach ($query1->results() as $result1)
{
?>
	<tr>
		<td align="left">
			<?php echo htmlentities($result1->member_first_name); ?>
			<?php echo htmlentities($result1->member_last_name); ?>
			<?php echo "("; echo htmlentities($result1->username); echo ")"; ?>
		</td>
		<td align="right">
			<?php echo timeAgo(strtotime($result1->time)); ?>
		</td>
	</tr>
	<tr>
		<td colspan="2" align="left">
			<?php echo "Subject: "; ?><a href="read_pm.php?id=<?php echo $result1->id; ?>"><?php echo htmlentities($result1->title); ?>
		</td>
	</tr>
	<tr>
		<td colspan="2" align="center">
			&nbsp
		</td>
	</tr>
<?php
}
if(intval($query1->count()==0))
{
?>
	<tr>
		<td colspan="2" align="center">
			You have no unread messages.
		</td>
	</tr>

<?php
}
?>

</table>

<br><br>

<h6>READ MESSAGES (<?php echo intval($query2->count()); ?>)</h6>

<br>

<table><col width="200px"><col width="100px">

<?php
foreach ($query2->results() as $result2)
{
?>
	<tr>
		<td align="left">
			<?php echo htmlentities($result2->member_first_name); ?>
			<?php echo htmlentities($result2->member_last_name); ?>
			<?php echo "("; echo htmlentities($result2->username); echo ")"; ?>
		</td>
		<td align="right">
			<?php echo timeAgo(strtotime($result2->time)); ?>
		</td>
	</tr>
	<tr>
		<td colspan="2" align="left">
			<?php echo "Subject: "; ?><a href="read_pm.php?id=<?php echo $result2->id; ?>"><?php echo htmlentities($result2->title); ?>
		</td>
	</tr>
	<tr>
		<td colspan="2" align="center">
			&nbsp
		</td>
	</tr>
<?php
}
if(intval($query1->count()==0))
{
?>
	<tr>
		<td colspan="2" align="center">
			You have no unread messages.
		</td>
	</tr>

<?php
}
?>

</table>

<br><br>
</div>

<div id="bunkerprofile">
<h2>View Your Messages</h2>



</div></div>


</div>
<div class="clear"></div>
</div>
</div>

<div id="footer">
<p>Copyright © 2014 Your Bunker </p>
</div>
</body>
</html>

Edited November 16, 2014 by MartynLearnsPHP

Barand · November 16, 2014

Invalid, or zero, dates show as 1970-01-01 (day 0 in unix time)

MartynLearnsPHP · November 16, 2014

Isn't that to be expected? If the date is a timestamp, there shouldn't be any zero or invalid dates.

MartynLearnsPHP · November 16, 2014

Sorry - I just realised that you were replying to my "45 years ago" comment from last night.

MartynLearnsPHP · November 16, 2014

I've cracked it! :happy-04:

Thank you so much for your help, Barand. It seems like my problem was that I was so over complicating things.

I realised that I don't need the count option (people prefer a facebook style private message style now rather than a subject discussion).

The following code is working for me:

$query1 = DB::getInstance()->query("select c.id, c.title, c.time, 
					m.id as memberid, m.username, m.member_first_name, m.member_last_name 
					from conversation as c, members as m
					where ((c.member1='{$logid}' and c.read1='No' and c.removed1='No' and m.id=c.member2) 
					or (c.member2='{$logid}' and c.read2='No' and c.removed2='No' and m.id=c.member1)) 
					group by m.id order by m.id desc");
$query2 = DB::getInstance()->query("select c.id, c.title, c.time, 
					m.id as memberid, m.username, m.member_first_name, m.member_last_name 
					from conversation as c, members as m
					where ((c.member1='{$logid}' and c.read1='Yes' and c.removed1='No' and m.id=c.member2) 
					or (c.member2='{$logid}' and c.read2='Yes' and c.removed2='No' and m.id=c.member1)) 
					group by m.id order by m.id desc");

Sign In

Select from Database Query not Filtering

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information