MartynLearnsPHP Posted November 15, 2014 Share Posted November 15, 2014 (edited) 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:idusernamemember_first_namemember_last_nameThe relevant fields from my conversation database are:idid2 (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 Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/ Share on other sites More sharing options...
Barand Posted November 15, 2014 Share Posted 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 ..... Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496648 Share on other sites More sharing options...
MartynLearnsPHP Posted November 15, 2014 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496649 Share on other sites More sharing options...
MartynLearnsPHP Posted November 15, 2014 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496662 Share on other sites More sharing options...
Barand Posted November 15, 2014 Share Posted 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"? Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496663 Share on other sites More sharing options...
MartynLearnsPHP Posted November 15, 2014 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496665 Share on other sites More sharing options...
Barand Posted November 15, 2014 Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496667 Share on other sites More sharing options...
MartynLearnsPHP Posted November 15, 2014 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496676 Share on other sites More sharing options...
Solution Barand Posted November 15, 2014 Solution Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496693 Share on other sites More sharing options...
MartynLearnsPHP Posted November 16, 2014 Author Share Posted November 16, 2014 (edited) 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 " "; 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">   </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">   </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 Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496700 Share on other sites More sharing options...
Barand Posted November 16, 2014 Share Posted November 16, 2014 Invalid, or zero, dates show as 1970-01-01 (day 0 in unix time) Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496709 Share on other sites More sharing options...
MartynLearnsPHP Posted November 16, 2014 Author Share Posted November 16, 2014 Isn't that to be expected? If the date is a timestamp, there shouldn't be any zero or invalid dates. Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496712 Share on other sites More sharing options...
MartynLearnsPHP Posted November 16, 2014 Author Share Posted November 16, 2014 Sorry - I just realised that you were replying to my "45 years ago" comment from last night. Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496716 Share on other sites More sharing options...
MartynLearnsPHP Posted November 16, 2014 Author Share Posted November 16, 2014 I've cracked it! 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"); Quote Link to comment https://forums.phpfreaks.com/topic/292477-select-from-database-query-not-filtering/#findComment-1496718 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.