#mysql
27 October 2007
Total 16 pages. You are browsing page 2/16.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
00:35 <****> wickedANDwise, that is the result of 7 rows
00:35 <****> My goal is to match those id numbers with they're referencing names
00:36 <****> for instance user_id 568 = John Doe
00:36 <****> the players on his team are the set of numbers, which need to match up with names from a different table as well
00:37 <****> the numbers after that are the ids of those players positions, which again match up to a table with names
00:38 <****> DanMan010, what is the purpose of those group_concats?
00:38 <****> I was told that for what im doing, I should use that function
00:39 <****> Basically I want the users names, Their players (7) and those players positions
00:39 <****> anyone know?
00:39 <****> in readable format
00:40 <****> DanMan010, ok. show me the sql that made that output.
00:40 <****> select user_id, GROUP_CONCAT(player_id), GROUP_CONCAT(position_id) FROM condors_picks GROUP BY user_id;
00:41 <****> users_id would need to get its info from the users table, player_id from the players table, and so on
00:42 <****> DanMan010, select user_id,GROUP_CONCAT(concat(player_id,'=',position_id)) FROM condors_picks GROUP BY user_id;
00:44 <****> DanMan010, select user_id,GROUP_CONCAT(concat(players.playername,'=',position_id)) FROM condors_picks JOIN players ON players.id=players_id GROUP BY user_id
00:46 <****> lol
00:46 <****> thats awesome
00:46 <****> DanMan010, do a similar join for the user name
00:46 <****> I appreciate it
00:47 <****> Is there anyone out there that can help with setting up a trigger? This is a first attempt & it logically looks correct, but fails with an error. Here is the code, what I'm trying to accomplish, and the error: http://pastebin.com/d5e361461
00:47 <****> DanMan010, I am leaving the channel. can you deal with it from here?
00:47 <****> I think so, thank you
00:50 <****> bpiper: delimeter issue perhaps?
00:51 <****> nm
00:51 <****> I don't believe so, the error said something about "not allowed to return result set from a trigger"
00:52 <****> mikegriffin: any other suggestions?
00:52 <****> http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html
00:53 <****> you might select into a temp table
00:54 <****> seems no one know ow to help me
00:54 <****> how
00:54 <****> mikegriffin: thanks, I'm reading the doc now...
00:56 <****> how do you turn a string into an integer?
00:56 <****> cast()
00:56 <****> http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
00:57 <****> hi guys, does someone know how to avoid this error while dumping a database : mysqldump: Got error: 1016: Can't open file: './xnxx_to_ant/cat_104_180.frm' (errno: 24) when using LOCK TABLES plz
00:57 <****> does mysql support alter table to change the type of a text field to an int?
00:57 <****> seed__: you are probably looking for a directory that doesn't exist, or mysql doesn't have permission to view it
00:58 <****> bpiper: no I don't think so, it's radom error
00:59 <****> seed__: have you tried using the full directory name instead of ./
01:00 <****> seed__: ./ will be looking in your mysqldata directory...
01:01 <****> bpiper : i am using mysqldump -u user -p database nothing else mysql find directories by itself
01:01 <****> seed__: sorry, brain fart I was thinking in terms of infile :-P
01:06 <****> Hello
01:07 <****> hi
01:10 <****> How do I call the ID that I just added to the database table, the code is here: http://pastebin.com/d3d2ca625
01:10 <****> I have a VARCHAR field with some "numeric" values like 1, 2, 10... If I ask mysql to ORDER it ASC the result is: 1, 10, 2 - with 10 in front of 2, that's something I don't want. To achieve the desired result I need to equalize the "digits" like 01, 02, 10... this way ORDER BY ... ASC returns 01, 02, 10. Is there any instruction to make mysql return 1, 2, 10 instead of 1, 10, 2?
01:18 <****> fromvega, do you have a maximum length of the numbers? if yes, append zeros in front - else, it is a bit more difficult i think
01:18 <****> fromvega it'll be very kludgy
01:19 <****> order by col+0? wouldnt that cast to an int?
01:19 <****> spq, no, there is no maximum length... I'm trying to let the job for mysql instead of sorting the results
01:19 <****> Trengo, dunno
01:19 <****> I'll try
01:20 <****> Trengo, if thers no maximum length, some numbers cant be casted to int
01:20 <****> spq really big numbers
01:20 <****> i mean *really* big
01:20 <****> Trengo, it worked! hehehe
01:21 <****> fromvega, you will have problems if your values become too big
01:21 <****> spq, I'll not have biiiig numbers... hehehe
01:21 <****> order by col-reallybignumber?
01:21 <****> fromvega, then in fact you have a maximum length of your numbers ^^
01:21 <****> fromvega great
01:22 <****> better not have numbers at all - sounds like a dodgy design decision somewhere
01:22 <****> spq, hehehe maybe
01:22 <****> adaptr, in fact it's not numbers, it's like 1.1.39.098, 1.3.443.444
01:23 <****> so + 0 will not work :P
01:23 <****> o_O
01:23 <****> fromvega then castig it to a number will do bad things to your data
01:23 <****> you want to have 10.0 after 9.99 ?
01:23 <****> fromvega if they are in a column with alphanumeric data then you have two options: either forget about numerical sorting, or redesign your data structures
01:23 <****> yeah,
01:24 <****> do you have exactly x fields? (like ip addresses have 4 fields?
01:24 <****> if these fields they are made up of are structured, split them up
01:26 <****> if the "numbers" may not have zeroes prepended in the middle (as you would need to sort the above), then numerical sorting is impossible - the data doesn't allow it
01:26 <****> a Troll!
01:26 <****> run away
01:26 <****> run away
01:26 <****> lob a holy handgrenade
01:26 <****> adaptr, you could add these numbers, if the numbers have a maximum length
01:27 <****> adaptr, the problem is just the presentation, I will need to format the "numbers" for better visualization... If I have then formated in the database I can order and present then directly, If I can't have the results ordered I'll need to sort arrays and format them before displaying... I'm wondering if it's better to do like that or to reformat all records (to pad with 0) everytime I get new digits
01:27 <****> spq, the fields can grow or shrink
01:28 <****> fromvega, what do you mean with the fields?
01:28 <****> fromvega if the numbers cannot be stored numeric-sortable in the database, then the database can't do the sorting for you; this falls under the heading of "self-evident"
01:29 <****> sort and present in your application
01:29 <****> how can I Select only rows from 200-499
01:29 <****> unless you're prepared to store them twice
01:29 <****> SELECT * FROM tbl LIMIT ?
01:29 <****> !m fandm LIMIT
01:29 <****> fandm: (Information Functions) : http://dev.mysql.com/doc/mysql/en/information-functions.html
01:30 <****> spq, it's like accouting numbers
01:30 <****> what are accounting numbers?
01:30 <****> accounting
01:31 <****> hum, I don't know the word in english but they are numbers used to identify some accounting accounts? hehehe
01:31 <****> you have 1.1.1 and 0.9 and 111111111111111111111111111111111111111111111111111111111.2.9 ?
01:32 <****> spq, no, all will have the same number of fields, but this number can change... so I can have 1.1.1, 1.9.0, 11111111111111111111111.2.9
01:32 <****> or 1.1.1.1, 1.9.1.0, 222222222222222222222.2.2.2
01:32 <****> ok
01:33 <****> but I was just trying to avoid a boring solution hehehe
01:34 <****> so you have 4 fields, sort them one by one - cast to int (meaning you have a limited precision) or pad zeros
01:35 <****> which depends again on maximum length
01:35 <****> better by far to simply *store* them as bigint(20)
Total 16 pages. You are browsing page 2/16.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last