#mysql
31 December 2007
Total 13 pages. You are browsing page 1/13.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Mon Dec 31 00:00:01 2007
00:00 <****> javaJake: good
00:00 <****> Been struggling with this problem for a whole day now, nice to have it over with... :P
00:00 <****> javaJake: to help you celebrate I'm going to open a bottle of read and eat my dinner. ciao!
00:01 <****> ttyl :P
00:16 <****> hi all, I have a query that i am struggling with a bit.
00:17 <****> SELECT users.* FROM users LEFT JOIN points ON users.id = points.user_id WHERE (points.user_id IS NULL OR points.pointer_id != 1)
00:18 <****> what i am trying to do is select all users who have not been associated with a point by an arbitrary user. I am trying to get the users without any points and users with points from others but not from a particular user.
00:19 <****> This works when i leave it as NULL, but as soon as I add the points.pointer_id to the mix things break because it finds the other instances of points made by other users and doesn't properly exclude those users, have points from the user in question.
00:19 <****> is there a way to scope this call so that points.pointer_id != 1 is true for all records within a particular user?
00:19 <****> not sure if any of this makes sense ;-)
00:41 <****> This query turned out to be quite the monster. :P
00:42 <****> Thankfully this project is a small one, or I'd be doing some worrying right about now.
00:45 <****> Hi folks. I have a select ordered by fields "token, sdate". I want all rows with a non null token to come first but ordered on their sdate field, and then all the rest with no token field ordered by their sdate. How do I do that?
00:47 <****> shamrock, basically, you want it ordered by the sdate field? :)
00:47 <****> yes, but all records with a token field first in a group.
00:47 <****> shamrock, as far as I can tell, you are asking "I want all rows with some token to come first and be ordered by sdate, and then all the rest to be ordered by sdate"
00:47 <****> shamrock, the general solution is to use CASE in the order by clause
00:48 <****> or FIELD()
00:48 <****> CASE is potable
00:48 <****> portable
00:48 <****> javaJake: yes. thats what I said i think.
00:48 <****> javaJake: sorry. no. Not some token. Any token.
00:49 <****> Right that's what I meant too. :P
00:49 <****> :-;
00:49 <****> hmm
00:50 <****> shamrock, but a solution to your special case is ORDER BY token IS NULL , sdate
00:51 <****> it will be incredibly slow no matter how you do it
00:51 <****> only a few records
00:53 <****> gnari: Im not sure how your suggestion would work.
00:54 <****> shamrock, are you doubting that it works, or do you want an explanation?
00:55 <****> I just tried it and it didn't work. I think.
00:56 <****> hang on. I'll double check.
00:56 <****> shamrock, then you did not explain the problem properly, because it works as advertised
00:56 <****> gnari: yes, well, hang on :-;
00:57 <****> gnari: ot exactly as advertised...
00:57 <****> not &&
00:57 <****> he asked for field IS NOT NULL
00:57 <****> he wants NULLs first
00:58 <****> he said non-nulls first
00:58 <****> quote:"I want all rows with a non null token to come first"
00:58 <****> oh. then I misread
00:59 <****> yes - tokens first (ordered by date), then all null tokens ordered by date.
00:59 <****> "I want all rows with any token to come first and be ordered by sdate, and then all the rest to be ordered by sdate"
00:59 <****> select null as token, 1 as sdate union select 1,2 union select null,0 union select 3,1 order by token IS NULL,sdate;
01:00 <****> *blink*
01:13 <****> if a string that contains single quotes (') has mysql_real_escape_string performed on it and then is inserted into a database and if its entry doesn't contain any backslashes, does that mean that mysql-real-escape-string failed to work?
01:14 <****> can always do '' instead of \'
01:14 <****> ?
01:15 <****> why, haha, great nick. :D
01:15 <****> and the data inside the table will not have the escape char, only the string in the insert statement require it
01:15 <****> rightyo
01:15 <****> thanks
01:15 <****> hah, thanks, javaJake
01:19 <****> If I issue a slow query, and then while it's running another thread issues the identical query, will the query cache pick up on what's going on, and tell the second thread to wait instead of running the two identical queries in parallel?
01:19 <****> Or would it run them both, since they started at different times they could hypothetically have different result sets
01:19 <****> if they're EXACTLY identical, it may
01:20 <****> but as you say, if runing the qwuery does not lock the table, it has no guarantee that the result set is the same
01:20 <****> so you could force the lock, which would be an even bigger hit, probably
01:20 <****> if it is possible to query cached it, then result should be the same
01:20 <****> whats the maximum lenght of TEXT?
01:21 <****> unless there is another pending write query ib between the 2 selects
01:21 <****> more than likely with a long query
01:22 <****> if there is a pending write, the 2nd select will need to wait for lock, so cannot execute till the write complete, the write would have invalidate teh query cache
01:23 <****> gotcha, that makes sense
01:23 <****> hm, but query cache steps does not even check for locks
01:23 <****> so more than likely, every instance of my query is running completely. there are far more than just 2
01:23 <****> since the query is not even parsed
01:46 <****> Hi - wasn't quite sure which channel to post in - one of my perl scripts used "use Mysql;" for the MySQL module (not DBD:: or anything), but now in v5.8.8 it just can't find the module. Is the module I was using defunct or is there a new equivalent?
01:46 <****> yeah... that'd be a #perl question :)
01:47 <****> fair enough. thanks :)
02:39 <****> i really wanna know why
02:39 <****> this is giving me just the first lin!
02:40 <****> http://pastecode.com/?show=d74dd7447
02:40 <****> :/
02:42 <****> angr4r0x: limit 1;
02:42 <****> heh
02:43 <****> threnody: will try
02:43 <****> ;p
02:43 <****> angr4r0x: will try what? your sql says 'limit 1'. you get one.
02:44 <****> nothing :/
02:45 <****> angr4r0x: remove the 'limit 1'
02:47 <****> threnody: i did it
02:47 <****> the same thing
02:47 <****> just line first name of first field - -'
02:47 <****> i thik th problem is on $id variable
02:49 <****> angr4r0x: there is no way for anyone to tell if any of your php variables contain the values you think they do. Echo them out and start looking.
02:49 <****> threnody: this is what im doing right now
02:50 <****> angr4r0x: good luck! ;^)
02:50 <****> and it seems $id has no values but its not possible because it returns me something
02:50 <****> your $id = line is commented out...
02:51 <****> heh
02:51 * ToeBee wonders if someone needs some sleep
02:52 <****> ToeBee: lol i got it
02:53 <****> but i dont want to do it by line i'll try another stuff hehe :)
03:06 <****> anyone care to lend a hand setting up a MySQL server?
03:07 <****> KaoticEvil do you have a more specific question?
03:07 <****> ebergen: well, i installed ubuntu-server, with lamp
03:07 <****> and i cant change/set the root password
03:08 <****> reset root
Total 13 pages. You are browsing page 1/13.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last