Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / NewStats: 3,200,016 members, 7,973,481 topics. Date: Saturday, 12 October 2024 at 05:21 PM |
Nairaland Forum / Science/Technology / Webmasters / Please Help With This Sql Problem (1225 Views)
Code On Developing A Web Search Engine Please : Php,ajax & Sql Programmers: / Quick Sql Injection Vulnerability Test / Creating Complex / Advanced Search Forms With Sql Queries In Dreamweaver (2) (3) (4)
Please Help With This Sql Problem by daddynasa: 11:24pm On Jul 13, 2014 |
I ve created two mysql tables namely 'users' and 'messages' the 'users' table contains userid and uname, 'message' table contains msgid, senderid, recipientid, msg, datesent . How can I retrieve all(conversation) messages btw to users, the sender and recipient uname? A good example is the facebook msg system at m.facebook.com |
Re: Please Help With This Sql Problem by daddynasa: 11:29pm On Jul 13, 2014 |
****between two users e.g userId =1 and userid=2**** |
Re: Please Help With This Sql Problem by dhtml(m): 5:44am On Jul 14, 2014 |
*dies* i canna understand your english. |
Re: Please Help With This Sql Problem by daddynasa: 6:58am On Jul 14, 2014 |
then u don't understand English |
Re: Please Help With This Sql Problem by daddynasa: 6:58am On Jul 14, 2014 |
Must u show Ur self? |
Re: Please Help With This Sql Problem by daddynasa: 7:02am On Jul 14, 2014 |
I believe u saw the asterisk beneath. and that serves as the correction. which one come be dies? . |
Re: Please Help With This Sql Problem by dhtml(m): 7:12am On Jul 14, 2014 |
You will have to join tables, i still dont understand the request. But as i see two tables, so i guess you have to join tables |
Re: Please Help With This Sql Problem by daddynasa: 7:32am On Jul 14, 2014 |
I tried (SELECT * FROM messages WHERE recipientid=1 AND senderid=2) UNION ( SELECT * FROM messages WHERE recipientid=2 AND senderid=1) but only one of the users sees the messages between the two users and secondly getting their uname from users table with their IDs using INNER JOIN is futile |
Re: Please Help With This Sql Problem by daddynasa: 7:38am On Jul 14, 2014 |
When u visit m.Facebook.com and u go to messages u ll see all Ur messages from different users after when u click the person's name it takes u to the chat between two of u and a reply box under. this is exactly what I am trying to achieve. I don't know if am table design is right for this goal. thank u |
Re: Please Help With This Sql Problem by daddynasa: 10:45am On Jul 14, 2014 |
got it. treated each conversation between pair as a thread using thread Id. thanks a lot for Ur contribution 1 Like |
Re: Please Help With This Sql Problem by dhtml(m): 12:13pm On Jul 14, 2014 |
You are welcome. So once you do left join on the tables, that should solve the problem. |
Re: Please Help With This Sql Problem by taofeeq137(m): 5:16pm On Jul 14, 2014 |
daddynasa: got it. treated each conversation between pair as a thread using thread Id. thanks a lot for Ur contributionHow's dat, I don't get dat, it looks simpler, when I did mine, I saved d Messageid as d Id of the first message sender and then when d other user tries to send message, the database is checked to see if there was a previous Id in which the receivers name appears, I there is, use the same id, if not, create another Id for the two new chatters, please explain your way of joinin two ids to one id |
Re: Please Help With This Sql Problem by taofeeq137(m): 5:52pm On Jul 14, 2014 |
dhtml: You are welcome. So once you do left join on the tables, that should solve the problem.Why not full outer join? |
Re: Please Help With This Sql Problem by magnumx: 7:53pm On Jul 14, 2014 |
Don't run a select all because d tables may have similar column names. This wud prevent ambiguity errors |
Re: Please Help With This Sql Problem by dhtml(m): 8:21pm On Jul 14, 2014 |
daddynasa: I tried (SELECT * FROM messages WHERE recipientid=1 AND senderid=2) UNION (Yeepa! see query o! Unsubscribes and runs away from the thread very fast. |
Re: Please Help With This Sql Problem by yawatide(f): 1:08pm On Jul 15, 2014 |
So I am looking at the tables and I don't see a (primary) key in one that would serve as the "hook" for the (secondary) key in the other. Maybe it's there, though column names don't match, but I don't see it. You may want to start by making the correction. At any rate, off the top of my head without giving it much thought and based on how you have structured your tables, I would probably do a SELECT * on all msg where userid = senderid AND userid = recipientid Good luck! |
Re: Please Help With This Sql Problem by daddynasa: 4:03pm On Jul 15, 2014 |
dhtml: no mind me hate.. smtimes too much code no good |
Re: Please Help With This Sql Problem by daddynasa: 4:06pm On Jul 15, 2014 |
yawatide: So I am looking at the tables and I don't see a (primary) key in one that would serve as the "hook" for the (secondary) key in the other. Maybe it's there, though column names don't match, but I don't see it. You may want to start by making the correction.your query does not get the messages between two users it fetches all messages where user is both sender and recipient. |
Re: Please Help With This Sql Problem by daddynasa: 4:10pm On Jul 15, 2014 |
dhtml: what I now did is select * from msg where((senderid=1 or recipientid=1)and(senderid=2 or recipientid=2)) |
Re: Please Help With This Sql Problem by taofeeq137(m): 8:46pm On Jul 15, 2014 |
daddynasa: Or select * from msg where senderid=1 and recipientid=2 union all select * from msg where senderid=2 and recipientid =1 sortby [date] desc, that should do |
Re: Please Help With This Sql Problem by yawatide(f): 12:00pm On Jul 16, 2014 |
@daddynasa: That is why I said, "At any rate, off the top of my head without giving it much thought and based on how you have structured your tables, I would probably..." I always qualify my statements because I know someone like you will sooner or later come along |
Re: Please Help With This Sql Problem by WebRabbi: 10:20pm On Jul 16, 2014 |
@ daddynasa, you can use my own solution ie <?php $server = 'localhost'; $username = 'root'; $password = ''; $database = 'messagBox'; $conn = new PDO("mysql:host=$server; dbname=$database", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $conn->exec("SET CHARACTER SET utf8" function SoftUserName($conn, $userID){ $SoftQuery = "SELECT uname FROM users WHERE userid = :userid"; $soft_prep = $conn->prepare($SoftQuery); $soft_prep->bindValue(':userid', $userID); $soft_prep->execute(); $rows_count = $soft_prep->rowCount(); if($rows_count == 1) { while($row = $soft_prep->fetch(PDO::FETCH_ASSOC)) { $userName = $row['uname']; } }else{ $userName = 'Anonymous'; } return $userName; } $senderID = 1; $recepID = 2; $SenderName = SoftUserName($conn, $senderID); $RecepName = SoftUserName($conn, $recepID); $SoftQuery = "SELECT msgid, senderid, recipientid, msg, datesent FROM message WHERE senderid = :senderid OR senderid = :recipientid OR recipientid = :recipientid OR recipientid = :senderid ORDER BY datesent ASC"; $soft_prep = $conn->prepare($SoftQuery); $soft_prep->bindValue(":senderid", $senderID); $soft_prep->bindValue(":recipientid", $recepID); $soft_prep->execute(); $rows_count = $soft_prep->rowCount(); if($rows_count >= 1) { while($row = $soft_prep->fetch(PDO::FETCH_ASSOC)) { $msgid = $row["msgid"]; $msg = $row["msg"]; $datesent = $row["datesent"]; $senderid = $row["senderid"]; $recipientid = $row["recipientid"]; if(($senderid == $senderID) && ($senderid != $recipientid)){ echo "<div style='float:left; border: 1px solid #333; color:#03F; padding: 30px; margin: 10px;'> $SenderName : $msg, $datesent</div><br clear='all' />"; } if (($recipientid == $senderID) && ($senderid != $recipientid)){ echo "<div style='float:right; border: 1px solid #333; color:#03F; padding: 30px; margin: 10px;'> $RecepName : $msg, $datesent</div><br clear='all'/>"; } $senderid = ''; $recipientid = ''; $msg =''; } }else{ echo "Error Msg"; } ?>
|
Re: Please Help With This Sql Problem by taofeeq137(m): 10:39am On Jul 18, 2014 |
WebRabbi: @ daddynasa, you can use my own solution ienyc, i'll try this in c# |
Re: Please Help With This Sql Problem by taofeeq137(m): 1:29pm On Jul 18, 2014 |
WebRabbi: @ daddynasa, you can use my own solution ieTried this on asp.net, worked fine, nice one |
Re: Please Help With This Sql Problem by daddynasa: 5:58pm On Jul 18, 2014 |
WebRabbi: @ daddynasa, you can use my own solution ie good one. thank you |
Re: Please Help With This Sql Problem by taofeeq137(m): 9:36am On Jul 20, 2014 |
WebRabbi: @ daddynasa, you can use my own solution ie
|
Re: Please Help With This Sql Problem by WebRabbi: 6:58pm On Jul 21, 2014 |
You are highly welcome @ taofeeq137 and daddynasa. Fabulous one @taofeeq137, I can see your screen shot. Nice one |
Re: Please Help With This Sql Problem by cbrass(m): 12:36am On Jul 22, 2014 |
Which one be all this scary queries sef..abeg no blind my eyes now, and softly dey quote long comments na...some of us na 3310 we dey take browse o Anyway back to the topic, just get the id of the message or comments or whatever it maybe I.e $id=$_GET['Id'] then combine the tables together do a LEFT JOIN ON....then use the $id LOBATAN, then stop naming your tables user or comments or posts, you make its easier for me to hack you |
Re: Please Help With This Sql Problem by boomtube: 1:00am On Jul 22, 2014 |
cbrass: Which one be all this scary queries sef..abeg no blind my eyes now, and softly dey quote long comments na...some of us na 3310 we dey take browse oLOBATAN! dhtml: You are welcome. So once you do left join on the tables, that should solve the problem.That is what i have said much earlier until i had to run away out of sheer frustration. I am surprised this thread has not been solved. |
Re: Please Help With This Sql Problem by taofeeq137(m): 12:50pm On Jul 22, 2014 |
I am surprised this thread has not been solved.It should have cos the guy said he got it |
Re: Please Help With This Sql Problem by micodon(m): 11:39pm On Jul 24, 2014 |
You need 3 tables. Users table (id, username, fname...). Conversations Table (id, user_one, user_two). Messages Table (id, sender_id, message, date). To get a user's conversation list (say the user's id is stored in a variable $user_id), "SELECT m.id as mid, u.fname, u.lname FROM messages m, users u WHERE m.user_one = $user_id OR m.user_two = $user_id AND CASE m.user_one = $user_id THEN u.id = m.user_one CASE m.user_two =$user_id THEN u.id = m.user_two END". To get a particular convo between two people, you need the convo_id gotten from the query above. If the convo_id is stored in $convo_id, the query "SELECT * FROM conversations JOIN users ON users.id = conversations.sender_id WHERE conversations.id = $convo_id" Well there may be errors as that's from the top of my head. The best way of designing databases is to normalize where possible as this will remove redundancy. NORMALIZE WHERE POSSIBLE. ALWAYS |
Re: Please Help With This Sql Problem by dhtml2(m): 12:29am On Jul 25, 2014 |
Check out this chat script - http://nairachat..biz/ - combined group chat with one on one chat. |
(1) (Reply)
Web Design Guru's Advice Needed. / How To Create A Forum Like Niaraland.com / Start Your Own Zikoko Viral Blog Without Hiring A Developer
(Go Up)
Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health religion celebs tv-movies music-radio literature webmasters programming techmarket Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 71 |