mysql OR dilema!

compsci · #1 (**permalink**) 09-06-08, 09:29

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]

r937 · #2 (**permalink**) 09-06-08, 09:58

the part that you forgot to mention is whether you actually want the threads with 0 to be returned or not

compsci · #3 (**permalink**) 09-06-08, 10:00

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!

shammat · #4 (**permalink**) 09-06-08, 10:17

I don't understand how

Quote:

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

and

Quote:

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"

compsci · #5 (**permalink**) 09-06-08, 10:23

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!

r937 · #6 (**permalink**) 09-06-08, 10:26

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?

compsci · #7 (**permalink**) 09-06-08, 10:43

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:

gvee · #8 (**permalink**) 09-07-08, 07:25

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

Internet Services

Web Development

Digital Marketing

Consumer Tech