Re: Importing fields of constant length [solved]
My tounge spoke too early, sorry! Sebastian Haag said: > Hello, > > is it possible to import data into a MySQL-DB from a text-file which does > not have separating characters (like a comma, semicolon or tab)? > > I have a .csv-file in which each column has a different constant lenght > (so many characters or digits) that I need to import. > > For example: > > 00721617 20040625 1000 Z > 00721617 20040626 1000 Z > 00721617 20040625 1000 OP > ... > ^10 ^20^30 > > Now I would like to read this into my database into certain fields. First > four digits should go to Field1, the next six should go to Field2 and so > on. > > I checked out the LOAD DATA INFILE syntax but couldn't find anything. I > cannot use the FIELDS TERMINATED BY option. Not true: http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html /*little more than halfway down the page*/ It says: If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (non-delimited) format is used. [...] For example, if a column is declared as INT(7), values for the column are written using seven-character fields. On input, values for the column are obtained by reading seven characters. LINES TERMINATED BY is still used to separate lines. [...] > > Is there a straightforward way to do this or do I have to load it into one > large field and seperate the fields by using MySQL's string functions? > > Thanks in advance for any suggestions! > > Sebastian > > -- > > Once a problem is defined - it is half way solved. (Henry Ford) > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing fields of constant length
Hello, is it possible to import data into a MySQL-DB from a text-file which does not have separating characters (like a comma, semicolon or tab)? I have a .csv-file in which each column has a different constant lenght (so many characters or digits) that I need to import. For example: 00721617 20040625 1000 Z 00721617 20040626 1000 Z 00721617 20040625 1000 OP ... ^10 ^20^30 Now I would like to read this into my database into certain fields. First four digits should go to Field1, the next six should go to Field2 and so on. I checked out the LOAD DATA INFILE syntax but couldn't find anything. I cannot use the FIELDS TERMINATED BY option. Is there a straightforward way to do this or do I have to load it into one large field and seperate the fields by using MySQL's string functions? Thanks in advance for any suggestions! Sebastian -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get previous and next result
Maria, Maria Garcia Suarez sagte: > Hi there! > > I'm currently developing a web where we let users > create their own forums. All the messages (wherever > they are posted) are stored in the same mysql table. > > When users read a certain message I would like to > display the previous and next message in that forum. > > Since all the messages (of the different forums) are > stored in the very same table I don't find how to > guess what's the ID of the previous and next message. > > Is there any way to find those IDs? > > Thanks. > > Kisses, > Maria > What I do is that I assign each message its ID (of course) plus its parent_ID in case of a reply (zero in case of thread-starting message) plus a thread_ID which is equal to the ID of the message which started the thread. Then I select all messages with the same thread_ID. There I have the messages that belong to one thread. I store the unique ID's in an array which I use to link to the next / previous message. You might want to assign each message a forum_ID, too. Did I get the idea across? If you are interested I can look for some code later. (Don't have any here at work) Let me know. Cheers, Sebastian -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.h errors
Steve, Steve Davies sagte: > Sinisa Milivojevic wrote: > >>On Mon, 08 Sep 2003 19:57:25 +0100 >>Steve Davies <[EMAIL PROTECTED]> wrote: >> >> >> >>>Hi All >>> >>>I'm currently trying to learn C so that I can recode some php/mysql apps >>> >>>I've got but I've run into probs right at the start. >>> >>>I'm trying to compile the examples PDuBois MySQL (myclient.c the really >>>easy one ;-( ) and get the errors below. >> > Yeah tried that - same error with includes from the tarball. > > Maybe I'm missing something when I call it - I'm using > > gcc -c -I/usr/include/mysql myclient.c > I haven't followed this thread at all, so I don't have a clue what exactly the problem is. But I thought I should just post my gcc-command that I had used long time ago. I remember I had a lot of trouble compiling my first C-API. Here we go: gcc -L/usr/lib/ -I/usr/include -o prg_name src_name.c -lmysqlclient It might work for you, it might not. Check the paths first though. Cheers, Sebastian -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with #1111 - Invalid use of group function
Hi, Paul DuBois said: > At 11:24 -0500 9/3/03, Cory Hicks wrote: >>Hey folks, >> >>I am trying to run the following sql query in mysql: >> >>SELECT TD.project_id, P.project_manager >>FROM time_daily TD >>INNER JOIN projects P ON P.project_id = TD.time_project_id >>WHERE TD.time_user_id = 'xpt' AND ( >>SUM( TD.time_hours_worked ) <> '0.00' >>) I have another suggestion. How about: SELECT TD.project_id, P.project_manager, SUM(TD.time_hours_worked) AS hours FROM time_daily TD INNER JOIN projects P ON P.project_id = TD.time_project_id WHERE TD.time_user_id = 'xpt' AND hours <> 0 GROUP BY TD.project_id, P.project_manager; That would output the sum also. But you don't need to use it in your API. >> >>And I keep getting the # errno.- Invalid use of group function - >> >>I don't want to pull out any rows where the SUM of time_hours_worked is >>'0.00'... >> >>I would be most grateful if anyone has any suggestions > > You cannot use aggregate functions like SUM() in the WHERE clause. > That's self-contradictory, because WHERE determines which rows to > select, whereas SUM() can be computed only after the rows have > been selected. :-) > > You might want to select the SUM() values into a temporary table, > then use that in a join with your original table. > >> >>Many thanks! >> >>Cory >>-- >>Cory Hicks <[EMAIL PROTECTED]> >>TRI International > > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with #1111 - Invalid use of group function
Sorry, I think it should be GROUP BY TD.project_id, P.project_manager Cory Hicks said: > Hey folks, > > I am trying to run the following sql query in mysql: > > SELECT TD.project_id, P.project_manager > FROM time_daily TD > INNER JOIN projects P ON P.project_id = TD.time_project_id > WHERE TD.time_user_id = 'xpt' AND ( > SUM( TD.time_hours_worked ) <> '0.00' > ) > > And I keep getting the # errno.- Invalid use of group function - > > I don't want to pull out any rows where the SUM of time_hours_worked is > '0.00'... > > I would be most grateful if anyone has any suggestions > > Many thanks! > > Cory > -- > Cory Hicks <[EMAIL PROTECTED]> > TRI International > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with #1111 - Invalid use of group function
Cory, Cory Hicks said: > Hey folks, > > I am trying to run the following sql query in mysql: > > SELECT TD.project_id, P.project_manager > FROM time_daily TD > INNER JOIN projects P ON P.project_id = TD.time_project_id > WHERE TD.time_user_id = 'xpt' AND ( > SUM( TD.time_hours_worked ) <> '0.00' > ) > > And I keep getting the # errno.- Invalid use of group function - > You need a GROUP BY clause if you use the SUM()-function with fields that you don't sum up. Just adding GROUP BY TD.project_id might work. Regards, Sebastian > I don't want to pull out any rows where the SUM of time_hours_worked is > '0.00'... > > I would be most grateful if anyone has any suggestions > > Many thanks! > > Cory > -- > Cory Hicks <[EMAIL PROTECTED]> > TRI International > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Abt mysqldatabase
Uma, Uma Shankari T. said: > > > Hello , > > >I need to copy the database contents from one operating system to > another operating system..if i copy the entire database folder it is > coping only the table structure..contents is not getting copied..can > anyone please tell me how do i go do this ?? You might want to use mysqldump. It has worked for me between win2000 and debian linux without any trouble. I guess MySQL doesn't care. http://www.mysql.com/doc/en/mysqldump.html > > Regards, > Uma > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlcc 0.9.2 no connections...
John, John Dangler said: > I installed and linked 0.9.2 on RH9 as instructed, and when I start > mysqlcc, I get the server registration dialog. When I attempt to > connect, I get a message in the message dialog which says "connecting." > and that's it! It never actually connects. I have checked to make sure > that mysqld is, in fact, running (it is). What have I missed ? is the CC on the same machine as the Deamon? I had trouble connecting to a different machine. With both on the same computer I did not have any problems. I didn't get into it too much so I don't really have a solution myself. Sorry. -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database from win to lin
Dan, Dan J. Rychlik said: > Hey, > > I have a question about copying a database that was developed on a windows > os and moving it to a Linux server os. Will this work ? Has it been > done before ? > > Thanks, > > -Dan Yes, I have done it just recently. Just use the mysqldump-command. -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user types
Andrew, Andrew Simpson said: > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > > > can anyone tell me the difference between the above 3. These are for the > root user. > > i need to create a newuser and im not sure which of the above they are > supposed to have > > thanks > Your question is a bit confusing. I suggest you take a look at the user's manual. GRANT is what you want to do. http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#User_Account_Management -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]