Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / New
Stats: 3,194,215 members, 7,953,795 topics. Date: Friday, 20 September 2024 at 06:41 AM

Please Help With This Sql Problem - Webmasters - Nairaland

Nairaland Forum / Science/Technology / Webmasters / Please Help With This Sql Problem (1222 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)

(1) (Reply) (Go Down)

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 contribution
How'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 (
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
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:
Yeepa! see query o! Unsubscribes and runs away from the thread very fast.


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.

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!
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:
Yeepa! see query o! Unsubscribes and runs away from the thread very fast.


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:


what I now did is select * from msg where((senderid=1 or recipientid=1)and(senderid=2 or recipientid=2))

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 wink
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"wink;


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 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"wink;


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";

}

?>
nyc, 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 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"wink;


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";

}

?>
Tried 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

<?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"wink;


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";

}

?>


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
<?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"wink;


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 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 tongue
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 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 tongue
LOBATAN!

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)

New Settings For MTN 0.0 Unlimited Free Browsing / MTN Gives Free Data On Beta Talk And Truetalk After Recharging / Adsense Deal

(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. 62
Disclaimer: Every Nairaland member is solely responsible for anything that he/she posts or uploads on Nairaland.