Programming Help

Web Development



What's Here?

  • Members: 118,867
  • Replies: 435,624
  • Topics: 67,342
  • Snippets: 2,415
  • Tutorials: 641
  • Total Online: 1,757
  • Members: 60
  • Guests: 1,697

Who's Online?

Welcome to Dream.In.Code
Click Here
Getting PHP Help is Easy!

Join 118,867 PHP Programmers for FREE! Ask your question and get quick answers from experts. There are 1,757 online right now! We've got more than 500 tutorials and 2,000 snippets. Join and find out why Dream.In.Code is the #1 programming help community on the internet! Registration is fast and FREE... Join Now!



Query help

 
 Reply to this topicStart new topic

Query help
JBrace1990
post 23 Jul, 2008 - 11:54 AM
Post #1


D.I.C Regular

Group Icon
Joined: 9 Mar, 2008
Posts: 461



Thanked 20 times

Dream Kudos: 350
My Contributions
ok, in the chat i'm making (Flex), i'm using this query (and yes it IS php, php's the backend of the chat), to pull private messages out of the system... it should be returning 4, however i only get it to return 4... i've tried modifying the query, but with no luck...

So what it does is pulls information out of the database according to the data from another query... when they log in, chat_logged_in becomes update with the current time()... now since all of the pms i've been adding manually have a time of 2147483647, it should be pulling all 5...

here's the 5 in the database:
CODE
id     poster     message     roomName     bold     italics     fontColor     time     IP     toUser
1526      Jacob Evans      ish JB      Main Room      yes      no      #000000      2147483647             JBrace1990      
1528     JBrace1990     testing     Main Room     no     no     #000000     2147483647           Jacob Evans
1529     JBrace1990     hello Fred     Main Room     no     no     #000000     2147483647           Fred
1532     JBrace1990     test     Main Room     no     no     #000000     2147483647           Joe
1533     Charlie     test2     Main Room     no     no     #000000     2147483647           JBrace1990


So if poster or toUser is equal to JBrace1990, i want to pull it... problem is, i can't tell why it doesn't pull the last one =/ it pulls the ones before.... so can someone please help? it's not working and i've been trying to fix it for an hour now =/

php
$sql3 = mysql_query("SELECT COUNT(toUser) AS total,fontColor,bold,italics,poster,
message,toUser,id FROM flex_chat_messages WHERE time >= '$row2[chat_logged_in]' 
AND toUser='$username' OR time >= '$row2[chat_logged_in]' AND poster='$username' 
AND toUser != '' GROUP BY toUser ORDER BY id,poster,toUser")or die(mysql_error());


Also, i need a query that would be able to pick out a convo, IE: JBrace1990 and Jacob Evans, regardless of who is the poster, and who is the toUser... i can't think of it....

basically the first one is going to get all of the conversations from the DB, and the second query is going to get the amount of different conversations, 1 for each pair of usernames....

This post has been edited by JBrace1990: 23 Jul, 2008 - 07:16 PM
User is offlineProfile CardPM

Go to the top of the page


Martyr2
post 24 Jul, 2008 - 09:25 PM
Post #2


Programming Theoretician

Group Icon
Joined: 18 Apr, 2007
Posts: 4,669



Thanked 125 times

Expert In: C/C++, Java, VB, VB.NET, C#, PHP, Web Development, HTML & CSS, Javascript

My Contributions
Always always ALWAYS use parenthesis in queries like this to make sure you are controlling which clauses are getting executed and which are "and"ed together and which ones are "or"ed together.

Your query has a few problems in it. You test time > chat_logged_in twice... wouldn't it make sense to only check it once?

CODE

"SELECT COUNT(toUser) AS total,fontColor,bold,italics,poster,
message,toUser,id FROM flex_chat_messages WHERE time >= '$row2[chat_logged_in]'
AND toUser='$username' OR time >= '$row2[chat_logged_in]' AND poster='$username'
AND toUser != '' GROUP BY toUser ORDER BY id,poster,toUser"


Could be reworded like so....

CODE

"SELECT COUNT(toUser) AS total,fontColor,bold,italics,poster,
message,toUser,id FROM flex_chat_messages WHERE time >= '$row2[chat_logged_in]'
AND (toUser='$username' OR poster='$username')
GROUP BY toUser ORDER BY id,poster,toUser"


Here we are saying if the time is greater than or equal to the chat logged in time and the toUser is $username or poster is $username. I am not sure why you had the toUser != '' in there because you should always have a toUser specified and if toUser equals $username of course it won't be an empty string.

So see if that query will work for you. It should pull all records where toUser or poster is that of the user you specified.

Now as far as the conversation, it is going to be a bit tougher because not only is a conversation between two people, it is also for a given period of time. So essentially you and I could have multiple conversations in one or multiple days. One way we could attempt to do this would be to mark the conversation with an ID and then pull all messages based on that conversation ID or we could simply pull records which share the same two names back and forth in the toUser and poster fields for a given time period.

The choice is up to you how you want to do this and which would lead to a better design choice for your application.

Hope this helps! smile.gif
User is offlineProfile CardPM

Go to the top of the page

JBrace1990
post 24 Jul, 2008 - 10:08 PM
Post #3


D.I.C Regular

Group Icon
Joined: 9 Mar, 2008
Posts: 461



Thanked 20 times

Dream Kudos: 350
My Contributions
thank you martyr...

This PM system is the thing that's messing me up... since it's a chat system, PMs need to be real time...

the "toUser !='' " section is because i don't want it to pull records where there is no toUser (hence, it's a message to the entire chat, not just a specific user)

As for the conversations, they are all stored and kept in the database with the messages to everyone (where toUser = ''), or to a specific user (where toUser != '')... the $row2['chat_logged_in'] limits which messages are pulled, meaning conversations will be pulled from the database, and all ones which are from this current chat session are pulled...

Think of a FlashChat (if you've ever used it... it's from tufat), or an IM conversation... now i'm fairly sure that AIM, MSN, or w/e would store all of the messages that have been made (to some degree)... they pull only certain ones, meaning conversations made before you logged in are not factored into your IM screen, this is where the query has to differentiate...

But as for this query, i'll add AND toUser != '' and see how it works...

EDIT: lol... i think i found the problem >.>;; i grouped by toUser, meaning that since there were 2 messages to me, only one was taken because the other was grouped with it...

SO, now I just need one to mark the amount of conversations... any suggestions? or maybe i can edit this one somehow?

php
mysql_query("SELECT COUNT(toUser) AS total,fontColor,bold,italics,poster,
message,toUser,id FROM flex_chat_messages WHERE time >= '$row2[chat_logged_in]'
AND (toUser='$username' OR poster='$username') AND toUser != ''
GROUP BY id ORDER BY id,poster,toUser")or die(mysql_error());


EDIT2: in a fit of genius earlier, i started thinking that if I put a while in a for, I can get the messages and output them correctly... the only problem with it would be the amount of queries... it would be 1 query for each IM, plus another query to get the list of IMs... now it seems a little excessive, but the second query (the one for each messages), would be like SELECT * FROM flex_chat_messages WHERE poster = '$username' AND toUser = '$toUser' OR toUser = '$username' AND poster = '$toUser'... it seems just somewhat off to me, like there should be an easier way....

This post has been edited by JBrace1990: 25 Jul, 2008 - 10:23 PM
User is offlineProfile CardPM

Go to the top of the page

Fast ReplyReply to this topicStart new topic
Time is now: 10/13/08 02:02AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live PHP Help!

PHP Tutorials

Reference Sheets

PHP Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month