#mysql
02 October 2007
Total 26 pages. You are browsing page 4/26.
First :: Prev :: [...] [2] [3] [4] [5] [6] [...] :: Next :: Last
01:55 <****> still am..to a degree
01:55 <****> jbu311: wait, your school is using that?
01:55 <****> my school has like a bunch of mysql servers
01:55 <****> Xgc : I ve begun with that but there is an error as date_from is unnown :\ http://pastebin.ca/722422
01:55 <****> i can just change servers
01:56 <****> jbu311: aye
01:56 <****> jbu311: try SELECT 1 UNION ALL SELECT 1;
01:56 <****> jbu311: if that fails union def. won't work
01:56 <****> didnt work...i think we determined that it was my version
01:56 <****> I need some jack daniels ugh
01:56 <****> 3.23.54-log
01:57 <****> well, 3.x is ancient.
01:57 <****> 8[
01:57 <****> like ancient enough that a 'basic' keyword like union wouldn't work?
01:57 <****> I think we proved that statement already.
01:57 <****> i guess
01:57 <****> progzy: You can't use a column alias in the same select list.
01:57 <****> thanks guys, i gotta change computers, bye
01:58 <****> !man union
01:58 <****> see http://dev.mysql.com/doc/refman/5.0/en/union.html
01:58 <****> progzy: You can only use columns obtained in your FROM list.
01:58 <****> Xgc : I suppose I must write explicity the date along the algorithm ?
01:58 <****> 4.0.0
01:58 <****> heh
01:58 <****> If I m on the good way ...
01:58 <****> progzy: The start and end dates can be incorporated several ways.
01:58 <****> *explicitly
01:59 <****> progzy: SELECT ... FROM (SELECT ... AS start_date, ... AS end_date) as init JOIN ...
01:59 <****> chadmaynard: I thought it was 4.1 heh
01:59 <****> progzy: You can then use init.start_date in the outer select list.
02:00 <****> Xgc : well I m sinking :)
02:00 <****> guess ya learn something new everyday
02:01 <****> I have mysql compiled on solaris, but for some reason sysbench when compiled with mysql support tried to link against libmysqlclient_r. however no such library was created when mysql was compiled/installed.
02:01 <****> Xgc : I don't understand the use of join here :\
02:03 <****> neither the first select in fact
02:04 <****> what can I select with it ..
02:04 <****> progzy: I didn't mean anything special with that JOIN, only to suggest the SQL continues as you see fit.
02:04 <****> ok
02:04 <****> What about SELECT fROM with dates after ?
02:05 <****> progzy: The outer select can refer to that derived table init.*
02:05 <****> progzy: That solves the problem in your posted SQL.
02:05 <****> Xgc : oops .. I need more notions .. Got to read the doc on derived table ...
02:06 <****> Error :Every derived table must have its own alias
02:07 <****> progzy: The SQL I showed you had an alias.
02:07 <****> progzy: AS init
02:08 <****> yes .. works better now :)
02:13 <****> ENUM is stored as INTEGER, right?
02:14 <****> !man enum
02:14 <****> see http://dev.mysql.com/doc/refman/5.0/en/enum.html
02:15 <****> Messtastic manual.
02:15 <****> I have a simple MySQL search using php like SELECT user_name, user_real_name FROM `mediawiki_user` WHERE user_real_name LIKE '%{$searchString}%' - how do I make it case insensitive?
02:16 <****> AphelionZ: using the default collation it is case insensitive
02:16 <****> but typing my first name Mark and mark returns different results :-/
02:17 <****> Xgc : the SQL algo seems quite complex to me .. I must be on the bad way as you said it was simple ...
02:17 <****> AphelionZ: then the collation must be case sensitive.
02:17 <****> progzy: Post the SQL to a pastebin for comments.
02:17 <****> k...
02:18 <****> AphelionZ: if you standardize the way you store it you will know how to search for it.
02:18 <****> AphelionZ: else you'll need to change the collation
02:18 <****> AphelionZ, user_name is binary by default. For some reason. I don't know why. Tim changed it for 1.11 or so, I think.
02:19 <****> ok then collation won't matter
02:19 <****> oh, im running 1.10
02:19 <****> and im searching user_real_name
02:19 <****> Well, then it was for 1.10, I guess.
02:19 <****> Or it was always like that and I'm forgetting.
02:20 <****> AphelionZ: you'll have to string them to upper or lower.
02:20 <****> AphelionZ: You can change the collation within the SQL, if need be.
02:20 <****> Xgc: of a binary column?
02:20 <****> collation won't change anything
02:20 <****> chadmaynard: Probably. It doesn't touch the column.
02:21 <****> let me see..
02:21 <****> you think it will turn it into a string?
02:21 <****> Possibly: col_name COLLATE latin1_general_cs LIKE 'a%'
02:21 <****> s/cs/ci
02:22 <****> yep, collation = latin1_bin
02:22 <****> WHERE LOWER( user_real_name ) LIKE ... will work, as chadmaynard suggests.
02:22 <****> If LOWER() is the right function name.
02:22 <****> oh the collation is binary. I thought the datatype was binary
02:22 <****> Although of course that's a table scan right there, practically anything is.
02:22 <****> AphelionZ: So try latin1_general_ci, as in the SQL above.
02:22 <****> chadmaynard, it's declared varchar(255) binary.
02:22 <****> At least in trunk.
02:23 <****> Simetrical: yeah thats what i have as well
02:23 <****> Simetrical: with the purpose of making it case sensitive?
02:25 <****> ok so... SELECT user_name, user_real_name FROM `mediawiki_user` WHERE user_real_name COLLATE latin1_general_ci LIKE '%{$searchString}%'
02:25 <****> chadmaynard, I suppose so, although I don't know why. We don't have any unique keys or anything on user_real_name, and we don't generally sort on it or use it for anything except a result column, so I'm not sure it makes a big difference.
02:25 <****> AphelionZ: Right.
02:25 <****> Oh, right, I'm reminded that this may be because we're actually using it for binary UTF-8 data.
02:25 <****> So collations other than binary would be a bad idea.
02:26 <****> silly wiki folks
02:26 <****> The utf8-using schema probably uses a non-binary collation.
02:26 <****> chadmaynard, as opposed to three bytes per character reserved in indexes and no characters outside the BMP?
02:27 <****> it worked, gang!
02:27 <****> thanks :)
02:27 <****> AphelionZ: You're welcome.
02:27 <****> Xgc: Arrf I ll try again tomorrow .. too late too tired .. Thanks for your help
02:27 <****> progzy: No problem.
02:27 <****> ByE
02:27 <****> see ya
02:27 <****> See, the_wench is case-insensitive.
Total 26 pages. You are browsing page 4/26.
First :: Prev :: [...] [2] [3] [4] [5] [6] [...] :: Next :: Last