#mysql
13 November 2007
Total 28 pages. You are browsing page 1/28.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Tue Nov 13 00:00:14 2007
--- Day changed Tue Nov 13 2007
00:00 <****> azz0r, this should work on recent versions
00:00 <****> Server version: 4.1.22-standard
00:00 <****> can I get a mysql script to connect to a remote mysqld?
00:00 <****> not recent
00:00 <****> I need to move a database from one location to another
00:00 <****> and i need to do it in a script.
00:00 <****> ah crapola
00:01 <****> azz0r, don't remember the details of 4.1's deficiencies, but try: SELECT id from (`thread_id` as `id` ,max(`created`) as m FROM `Post` GROUP BY `thread_id`) as foo ORDER BY m DESC LIMIT 0,15
00:02 <****> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max(`created`) as m FROM `Post` GROUP BY `thread_id`) as foo ORDER BY m DESC LIM' at line 1
00:03 <****> wionder how long itll take my freaking host to update mysql :S
00:03 <****> azz0r, sorry. try: SELECT id from (SELECT `thread_id` as `id` ,max(`created`) as m FROM `Post` GROUP BY `thread_id`) as foo ORDER BY m DESC LIMIT 0,15
00:05 <****> Can I connect to remote databases via a mysql script? i.e. use database `dbname@host.com`
00:06 <****> thanks! seems to work ,got another error blocking my results but alas print_r points to the right results coming through! :D
00:07 <****> azz0r: You might have been able to just add max(field) AS m to the select list and then order by m desc without the derived table.
00:08 <****> Select * from tableblah WHERE name != 'Flash Site,Dynamic Swish,Flash intro header';
00:08 <****> Of course, that would change the contents of the result set.
00:09 <****> Is that not correct?
00:09 <****> Doesnt seem to work
00:09 <****> MACscr: It's perfect.
00:09 <****> MACscr, define " Doesnt seem to work"
00:09 <****> Doesnt seem to filter out those names
00:09 <****> From the results
00:10 <****> MACscr: Use NOT IN (...)
00:10 <****> MAC those?
00:10 <****> MACscr: You've provided one name. You need a different construct to provide that data as N names.
00:11 <****> Xgc : good point. Possible with mysql?
00:11 <****> Or should i separate them out with php
00:11 <****> MACscr: WHERE name NOT IN ('Flash Site','Dynamic Swish','Flash intro header');
00:12 <****> Xgc : ok, i guess i should have known that. Its just stored as blah, bhah2, blah3 in a db field, so i was hoping to be able to filter using straight mysql
00:13 <****> MACscr: That's one reason not to store data like that.
00:13 <****> Believe me, i personally wouldnt
00:13 <****> But i didnt create this db
00:13 <****> MACscr: If you change the data slightly, you can use LIKE to do it.
00:17 <****> MACscr: Very bad idea, but given your data: WHERE CONCAT(',',ugly_list_in_field,',') NOT LIKE CONCAT('%,',name,',%')
00:24 <****> Xgc : thanks for that idea, but i cant seem to figure out how to implent that into my qury and have it work
00:24 <****> http://phpfi.com/275752
00:25 <****> MACscr: What does setting contain? Show me just one value.
00:26 <****> Its only one value being returned anyway:
00:26 <****> Flash Site,Dynamic Swish,Flash intro header,Flash 8,Logosets,Icon Sets,Swish animated,Flash animated,Dynamic flash photo galleries
00:26 <****> MACscr: Your initial question doesn't seem to match that SQL.
00:26 <****> MACscr: Ok. So setting contains a coma separated list and only one value will be returned from that subselect?
00:27 <****> s/coma/comma/
00:27 <****> MACscr: You have at least two options.
00:27 <****> Really? I thought it did. Im trying to return a list of categories, minus those listed. The ones i listed are stored in a single field/row and comma separated
00:28 <****> MACscr: One is to use a JOIN and the other is to use a subquery within the CONCAT.
00:29 <****> Xgc, if needed, i can query the first field (cats we want to exclude), then just insert it into the second query as string using php
00:29 <****> Hmm, i need to learn my JOINs. Im a bit basic when it comes to mysql
00:32 <****> MACscr: Change ... WHERE name NOT IN (SELECT ...) ... to ... WHERE (SELECT CONCAT(',',setting,',') FROM ...) NOT LIKE CONCAT('%,',name,',%') ...
00:33 <****> I actually had just figured it out
00:33 <****> SELECT * from tm_template_categories WHERE CONCAT(',',(SELECT setting FROM `tm_config` WHERE VariableName='remove_cats'),',') NOT LIKE CONCAT('%,',name,',%')
00:33 <****> Thats what i ended up doing, seems to work fine
00:33 <****> That's fine too.
00:33 <****> Ugly, but fine.
00:34 <****> Xgc : thanks for the help, i really appreciate it. I would never have thought of the CONCAT part
00:34 <****> MACscr: You're welcome.
00:35 <****> MACscr: I want to see that one pass your next code walkthrough.
00:35 <****> hello i am new to sql i have a six field table that has 19000 records what i want to do is take that and turn it into 650 record that i get from select distinct f1, f2, f3 from db;
00:35 <****> Code walkthrough?
00:36 <****> MACscr: We don't do that anymore?
00:36 <****> You mean having someone look over your code?
00:36 <****> pzt2: What do you mean by "turn it into"?
00:37 <****> MACscr: Something like that.
00:37 <****> Xgc: eh, its just me using the code and im self employed, so I guess im safe. =P
00:37 <****> Xgc remove 18350 records while retaining the 650 with all six fields
00:38 <****> pzt2: Are you saying you want to change the table so that it contains only those 650 distinct records?
00:38 <****> Xgc yes while retaining the six fields instead of the three distinct
00:38 <****> pzt2: You are only testnig 3 fields for uniqueness, correct?
00:38 <****> yes
00:38 <****> pzt2: What values do you want from those other 3 fields?
00:39 <****> the first instance of the value
00:39 <****> pzt2: SELECT f1,f2,f3,max(f4),max(f5),max(f6) FROM tbl GROUP BY f1,f2,f3;
00:39 <****> pzt2: There's no such thing.
00:40 <****> pzt2: Tables have no implicit order.
00:40 <****> is it possible to define two trigger_events for a trigger? I have a INSERT INTO ... ON DUPLICATE KEY UPDATE query. This table holds user ratings. What I want to do is use a trigger to update an AvgRating column elsewhere. As the MySQL manual points out, a BEFORE INSERT trigger will always run, but my problem is that I need to compute the avg based on the SELECT from the holding table- and it doesnt have the user's new rating yet
00:41 <****> kpdvx: In theory, you can have as many triggers as you want.
00:41 <****> Xgc, can they cascade?
00:41 <****> kpdvx: I don't know if MySQL limits that. I suspect not, practically.
00:41 <****> I create the trigger once, and it runs for both
00:41 <****> kpdvx: you can only have 1 each per table, so max of 6
00:42 <****> Xgc well i was thinking the order of creation of the record
00:42 <****> insert on duplicate key upate, will always execute before insert trigger
00:42 <****> pzt2: How do you know that order? is there a field that tells you this?
00:42 <****> if it insert, after insert trigger is executed
00:42 <****> if it updates, the before and after update triggers are executed
00:42 <****> Xgc, I need the trigger to run AFTER INSERT and AFTER UPDATE, because the query may do an INSERT, or it may do an UPDATE (if there's a key clash). Can I define the query to run on both in the same declaration?
00:43 <****> Xgc f1 is date
00:43 <****> kpdvx: need to create 2 trigger
00:43 <****> kpdvx: Listen to kimseong. He's more familiar with MySQL triggers.
00:43 <****> kimseong, I figured. Too bad the two triggers will look almost exactly the same :(
00:43 <****> kpdvx: maybe can try before insert
00:44 <****> kpdvx: or create a stored proc, let the 2 triggers call the stored proc
00:44 <****> pzt2: Yes. You can do that. Is the date guaranteed to be distinct per f1,f2,f3 tuple?
00:44 <****> kimseong, I thought about that, but I'd be computing the average before the user's input
00:44 <****> not a bad idea with the stored proc
00:44 <****> Xgc yes
00:45 <****> pzt2: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
00:45 <****> pzt2: That'll show you how to obtain the distinct rows associated with minimum dates.
00:47 <****> Xgc i get that from select distinct f1, f2, f3 from db; what i want to know how to do is either delete the other 18350 records to redirect the 660 to a temp table and retain the six fields
Total 28 pages. You are browsing page 1/28.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last