| |
| |
| |
|
Page: 1 2 3 4
<lokus> why not select col1, (seelct max(col) from ) from
<arjenAU> flupps|UK: I mean as a subquery in from clause
<flupps|UK> arjenAU: yes
<arjenAU> and what lokus says
<l008com> here is my query and its not working: http://www.whatsmyip.org/1913
<flupps|UK> hmm
<arjenAU> !tell us about doesn't work
<SQL> arjenAU asked me to tell you this: Look buddy, doesn't work is a strong statement. Does it sit on the couch all day? Does it want more money? Is it on IRC all the time? Please be specific! Give us the FULL EXACT error message. Tell us what it does and/or does not do.
<flupps|UK> SELECT col, (SELECT MAX(blah) FROM (SELECT ...) ) FROM...
<flupps|UK> like that?
<l008com> i can't find any documentation containing example queries that use subquries in the select clause
<flupps|UK> I should have tried that, yeah
<karat> In mysql 4.x does mysql listen to ENUM type's not null/default values?
<arjenAU> karat: in 4.x bad values would default to '' in an ENUM. 4.1 can give you a warning for that.
<arjenAU> karat: the absolute most snazzy way to deal with constraints is in 5.0 with an updatable VIEW ... WITH CHECK OPTION. brilliant. very very flexible.
<arjenAU> l008com: so?
<karat> arjenAU: Nods, working with what I got though :)
<arjenAU> karat: 5.0 is production.
<l008com> so what?
<lokus> l008com: what is the error at least
<l008com> 'you have an error in your sql syntax'
<lokus> l008com: where ?
<karat> arjenAU: Yeah, but 5.0 isn't available to me. I'm a little disappointed that check statements are parsed/ignored in 4.x too :(
<l008com> near '(loginlog.`time`) FROM loginlog WHERE loginlog.`time` > (UNIX_TIMESTAMP() - 2592'
<lokus> l008com: try to alias the table, you are using loginlog in the outer query as well
<arjenAU> karat: ye it depends on what you're used to.
<flupps|UK> I don't like that it's silently ignored either
<karat> arjenAU: Not really used to anything, but its hard to set constraints like the int has to be > 100 or something without check, unless theres some workaround.
<arjenAU> flupps|UK: I talked with robin about that.
<arjenAU> karat: aye. of course an app has to check input data anyway, but it's nice to have the RDBMS also verify its input.
<flupps|UK> what'd he say?
<arjenAU> flupps|UK: he takes good note of it and uses such feedback as input to help set the roadmap. that's his job. product manager...
<l008com> you know i just realized even if this query worked the way it looks like it should, its still not doing what i need it to do
<l008com> i'd say its time i give up, the ***holes that use this site will never appreciate my time anyway
<l008com> how do you do that alias thing?
<lokus> l008com: from table as t
<karat> arjenAU: theres no support for boolean values either is there?
<l008com> can you alias columns without aliasing whole tables?
<lokus> l008com: yes, but you should alias the table in your case
<l008com> well im again trying to do this without the subquery
<lokus> l008com: if you want to count() within a group with extra conditions, can use the sum trick sum(cond,1,0)
<l008com> wow and i think all i need to do is add WHERE active < 30
<l008com> ok so i alias a field in my select, and i can order by it, but I can't where by it?
<lokus> yes
<l008com> wtf
<lokus> can't you use the original column name or the expression
<l008com> i don't think so? would a COUNT(items) > 3333 in the where clause do anything? isn't that like, not enough information to work with
<lokus> l008com: for aggregate function count() put the condition in the having clause
<l008com> having clause?!?!
<lokus> check the select syntax
<l008com> ok i'm really giving up this time
<l008com> screw this ****
<l008com> did i mention i'm not getting paid to make this site?
<l008com> thanks anyway
<arjenAU> karat: huh?
<arjenAU> lokus: you forgot the if()
<CPUnerd_> group by field having
<lokus> arjenAU: ops
<arjenAU> lokus: sorry was in the mode. just found a typo in Joe Celko's SQL for Smarties also ;-)
<lokus> arjenAU: 1 thing, do you think create table ... select .. can create an empty table ?
<arjenAU> lokus: I guess so. if the query is valid but returns 0 rows. did you test it? CREATE TABLE maybe SELECT 1 AS foo WHERE 1=0;
<arjenAU> should do the trick ;-)
<lokus> tested it, yes it will
<phpnuborn> what is the difference between collations utf8-general-ci and latin1-swedish-ci ? my webhost tech rep is telling me that it makes no difference, even when sorting..
<arjenAU> phpnuborn: for general english, no diff
<lokus> phpnuborn: the 2 collations for different char set
<arjenAU> phpnuborn: key difference is of course that one if utf8 and the other is not. you need to handle utf8 properly inyour app
<phpnuborn> when i show a structure it say latin1-swedish-ci
<lokus> there is a utf8-swedish-ci too
<phpnuborn> for tables
<Solara> Hmm cute my database ate 6 tables durring a crash
<lokus> it means the column char set is latin1 not utf8
<lokus> or the table default charset and collation
<phpnuborn> should i make my webhost change it... ?
<phpnuborn> how insistant should i get
<lokus> phpnuborn: for a table ?
<phpnuborn> any database or table i create defaults to latin1-swedish-ci
<lokus> phpnuborn: you can change it yourself during table creation , or alter table
<phpnuborn> i tried
<Solara> hmm my innodb tables all vanished i wonder wth caused that -.-
<phpnuborn> thru phpmyadmin and putty/ssh
<phpnuborn> gave an error
<lokus> phpnuborn: need mysql 4.1 onwards
<phpnuborn> mysql Ver 14.7 Distrib 4.1.13, for pc-linux-gnu (i686) using readline 4.3
<lokus> the server version, do select version();
<phpnuborn> Server version: 4.1.13-standard
<arjenAU> Solara: not bloody likely. you make have to repair some indexes. using CHECK and REPAIR helps
<lokus> phpnuborn: show collation; show a list of avialable collations
<phpnuborn> big list.. 120 rows
<lokus> phpnuborn: then you should have all the collations active, i got 120 rows too
<StR> Hi all!
<StR> I have a mysqldump
<phpnuborn> so swedish use the same alhpabet as america?
<arjenAU> phpnuborn: the american alphabet is a subset.
<phpnuborn> *alphabet
<arjenAU> phpnuborn: which is what I already said, which is what the manual says, which is .... how much confirmation do you want?
<StR> but it has some innoDB tables that have foreign keys to tables crated later because they are defined later... what can I do?
<phpnuborn> so swedes have more letters than 26?
<arjenAU> Str: the dump should disable keys at the start, provided you used the right options (see mysqldump --help)
<lokus> phpnuborn: most european languages has more than 26
<StR> arjenAU: should, but it didn't
<phpnuborn> well its not creating databases and tables right.. its unprofessional.. would 1on1 or godaddy let this slide
<arjenAU> phpnuborn: 'it' ??
<arjenAU> godaddy sounds like such a professional na,e
<arjenAU> name even
<phpnuborn> and as sure as the sun will come up tomorrow, it will come back to bite me in the ***
<phpnuborn> cpane makes the databases and phpmyadmin creates the tables.. and i end up with latin1-swedish-ci
<arjenAU> cpanel should get a grip on 4.1 and up of MySQL.
<tunachips> is there a regex or a function that I can use in a query that will remove single quotes?
<CPUnerd_> str_replace("'", "", $var);
<tunachips> well, thanks, that should work, but it doesn't
<tunachips> damn asp crap
<lokus> mysql also has a replace() function
<tunachips> but that would return a quote string
<tunachips> I have a search field that gets entered into a query and it has single quotes
<lokus> tunachips: then you need to escaped them before placing it into the sql statement
<tunachips> and mysql doesn't like quoted field names
<tunachips> and that I don't know how to do as this is an asp controlparameter
<tunachips> the controlparameter enters 'xxx' into the query
<tunachips> and not xxx
<arjenAU> tunachips: mysql_real_escape_string()
<arjenAU> there'll be an aquivalent Connector/Net call for it
<tunachips> I'm using myOdbc, as the .net connector doesn't work in the visual crap
<tunachips> atleast what I've seen
<tunachips> this is insane
<tunachips> thanks for the help, don't know what I'm going to do here. can't get anything to work
<rbnz> heyas. with mysql 4, what can i use to import a 4.2 gb text file full of queries to tables already present in a db that wont take all week to run ?
<rbnz> at the mo i've got mysql -B -u root -p wiki<text.txt -where text.txt is a file of millions of statements
<rbnz> its running, but i want it to run at a more practical speed
<lokus> rbnz: insert statements ?
<will> So...
<will> When did MySQL started barfing on default values of '' on an INT?
<iPrometheus> is there a limit to the amount of rows you can put in table?
<iPrometheus> columns*
<C_Dreamer> iPrometheus: yes, of course. Everything has limits.
<vjanicek> iPrometheus| yes
<C_Dreamer> http://dev.mysql.com/doc/refman/5.1/en/table-size.html
<iPrometheus> thanks, sorry for the noob question haha
<C_Dreamer> (Incidentally, I was wrong about everything having limits - but it *is* true that there is a limit to the number of columns you can have in a table.)
<iPrometheus> 3000+, dont think i'm going to have any problems
<iPrometheus> hah
<C_Dreamer> Um, I think you might have problems with that many.
<iPrometheus> no, im saying thats the limit
<C_Dreamer> Ah, I see.
<iPrometheus> i dont think i will have any problems staying in the limit
<C_Dreamer> Good.
<Catcher> on a machine with a few shell users, so I want to install client or server?
<C_Dreamer> Most number of columns I ever had in a db table was 254, on a database where the limit was 255. A huge sigh of relief was in order!
<floppyears> hi
<floppyears> how can I get phpmyadmin tell me what sql statement was used to create a table?
Return to mysql
or
Go to some related
logs:
sql
TOP69
|
|