Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    117

    Unanswered: mysql OR dilema!

    Hello all,

    Frustration is the word of the day. I have this statement:

    SELECT * from message WHERE threadID='none' OR msgID='152';

    This is the table structure:
    | msgID | threadID | location | subject |
    ---------------------------------------
    | 152 | 0 | ly | s1 |
    | 153 | 152 | ty | s2 |
    | 154 | 0 | fy | s3 |
    | 155 | 0 | xy | s4 |

    msgID and threadID are both of type integer. msgID is unique.

    The prolem is no matter what text i put in the select statement in threadID - it always returns all rows that have 0 in them!! Do i need to test for type too!

    WHY!

    Thanks all

    [Getting desperate so I posted this on another forum too]
    Last edited by compsci; 09-06-08 at 10:53.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the part that you forgot to mention is whether you actually want the threads with 0 to be returned or not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by r937
    the part that you forgot to mention is whether you actually want the threads with 0 to be returned or not
    Sorry too confused. No I dont want the rows with 0 as threadID to be returned. I am suprised why they are even being returned!

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I don't understand how
    SELECT * from message WHERE threadID='none' OR msgID='152';
    and
    msgID and threadID are both of type integer
    go together. 'none' is definitely not a valid integer value, so what are you trying to achieve?

    '152' is not an integer as well - although MySQL will silently accept that.

    I wonder if 'none' might actually be "converted" to 0 by MySQL, so that's probably the reason why you get the strange result (other DMBS would simply throw an error with the expression threadID = 'none')

    You should specify a valid integer value, say WHERE threadID <> 0 or something similar to filter out the rows you don't want, instead of relying on some obscure character to number "conversion"

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by shammat
    I wonder if 'none' might actually be "converted" to 0 by MySQL, so that's probably the reason why you get the strange result (other DMBS would simply throw an error with the expression threadID = 'none')
    If i put any sort of text in there it will always return all rows that have 0 in them.
    Quote Originally Posted by shammat
    You should specify a valid integer value, say WHERE threadID <> 0 or something similar to filter out the rows you don't want, instead of relying on some obscure character to number "conversion"
    Thats not a good idea since I want only specific threadIDs to be returned, for example threadID 50. I can't do return threadID >49 and threadID<51. A lot of effort to just get the 50th one!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you don't want them returned, change your query to this --
    Code:
    SELECT please
         , list
         , your
         , columns 
      FROM message 
     WHERE msgID = 152
    note no quotes around numbers, plz

    the reason you were getting threads with 0 returned is because 'none' is a string, not a number, and this would've given you a syntax error in any other database

    however, mysql, in its infinite wisdom, will go ahead and execute it anyway, after "silently" (without telling you) converting the invalid number ('none') to 0

    say, how come you have messages with thread 0 anyway?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    THANK YOU! Works - I replied and repped on on the other forum!

    Quote Originally Posted by r937
    say, how come you have messages with thread 0 anyway?
    Thanks for the reply again r937.

    ThreadID is 0 to indicate the start of a thread. All thread replies will have the ID (msgID) of the first thread that has threadID as 0. So this is one thread:

    msgID, threadID, message
    ------------------------
    152 0 message
    153 152 message2
    154 152 message3

    Omfg, I just realised after wrting the above and reading the above. This should do it:

    PHP Code:
        if($row['threadID']!=0){
            
    $threadID $row['threadID'];
        }
        else{
            
    $threadID $calledRecord;//basicaly don't show other start threads that have a threadID of 0!
        

    Does exactly what you suggested, just sets threadID same as msgID.

    Thank you for yor help r937!!!!!!!!!! :trockon:

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Instead of returning unwanted data (in this case threads with an ID of 0) to the server and suppressing them; exclude them from your query.
    Code:
    WHERE  msg_id = 152
    AND    thread_id <> 0
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •