A Newbie question about make and the term.c file
I am trying to install mysql 5.1.59 on my ppc running os x and I get this error message in the term.c file. cc1: warnings being treated as errors term.c: In function ‘term_set’: term.c:946: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:947: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:949: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:950: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:952: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:953: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:955: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type term.c:956: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type make[2]: *** [term.o] Error 1 make[1]: *** [all-recursive] Error 1 make: *** [all-recursive] Error 1 I can't figure out what it means or how to fix it. Help please. Peter
Re: A Newbie question about make and the term.c file
I can't help directly with the error message (the warning seems fairly harmless), but may I inquire why you are building MySQL instead of using one of the prepared binaries? Compiling under OS/X can be pretty harrowing. - michael dykman On Sat, Oct 8, 2011 at 2:49 AM, Peter Schrock peter.schr...@gmail.comwrote: I am trying to install mysql 5.1.59 on my ppc running os x and I get this error message in the term.c file. cc1: warnings being treated as errors term.c: In function ‘term_set’: term.c:946: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:947: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:949: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:950: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:952: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:953: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:955: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type term.c:956: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type make[2]: *** [term.o] Error 1 make[1]: *** [all-recursive] Error 1 make: *** [all-recursive] Error 1 I can't figure out what it means or how to fix it. Help please. Peter -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: A Newbie question about make and the term.c file
I can understand your point, however, as stated, I am using a ppc architecture and am not afforded the luxury of binaries in the most up to date versions. I know using 5.1.59 isn't the most up to date, but I was also having issues with the most current version. I might start with the most current binary for ppc and then try upgrading. About the error, I am sure it is harmless, but I can't get past it, which means I can't use mysql. Peter On Oct 8, 2011, at 7:54 AM, Michael Dykman mdyk...@gmail.com wrote: I can't help directly with the error message (the warning seems fairly harmless), but may I inquire why you are building MySQL instead of using one of the prepared binaries? Compiling under OS/X can be pretty harrowing. - michael dykman On Sat, Oct 8, 2011 at 2:49 AM, Peter Schrock peter.schr...@gmail.comwrote: I am trying to install mysql 5.1.59 on my ppc running os x and I get this error message in the term.c file. cc1: warnings being treated as errors term.c: In function ‘term_set’: term.c:946: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:947: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:949: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:950: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:952: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:953: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:955: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type term.c:956: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type make[2]: *** [term.o] Error 1 make[1]: *** [all-recursive] Error 1 make: *** [all-recursive] Error 1 I can't figure out what it means or how to fix it. Help please. Peter -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Newbie question: Association table and Foreign Key
Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren
Re: Newbie question: Association table and Foreign Key
Just curious as it is not mentioned. Can Category ID also have multiple event id ? -- Cheers Dhaval Jaiswal On 01/03/2011 5:53 PM, Wagyu Beef wrote: Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: Association table and Foreign Key
Oh yeah, forgot to mention that. Yes, one event will have multiple categories. And one category can be applicable to multiple events. On Tue, Mar 1, 2011 at 8:33 PM, Dhaval Jaiswal jaiswal.dha...@enzenglobal.com wrote: Just curious as it is not mentioned. Can Category ID also have multiple event id ? -- Cheers Dhaval Jaiswal On 01/03/2011 5:53 PM, Wagyu Beef wrote: Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font
Re: Newbie question: Association table and Foreign Key
2011/03/01 20:23 +0800, Wagyu Beef Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Well, if your problem is really like that in the example that you quote, then look up 'REFERENCES' under 'CREATE TABLE'. That shows you what to put in the common table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newbie question database tables
Thanks all... I got this to work! Much appreciated.. And thanks for patience with a newbie! -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
newbie question database tables
I'm working on a database that needs to do a few things and getting brain freeze on one part. Scenario: I want to compile a db of articles with these tables: Categories Topics Users Categories cat_ID | cat_name Topics top_ID | top_name | top_content | cat_ID Users user_ID | user_name | top_ID or user_ID | user_name | top_ID | top_IDb | top_IDc etc (output to web page using php) But I need to show which users are using which topics, and I can add top_ID to the user file, which is fine if they are only using one topic. I could add 5 different topic to each user, but then I couldn't expand later. Reverse is true if I add user_ID to the Topics. So, need an idea how to solve this so it doesn't matter how many new users I keep adding, I can still see who is using the topics. As I said, a newbie question. Thanks much. -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: newbie question database tables
Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: Friday, September 10, 2010 12:09 PM To: mysql@lists.mysql.com Subject: newbie question database tables I'm working on a database that needs to do a few things and getting brain freeze on one part. Scenario: I want to compile a db of articles with these tables: Categories Topics Users Categories cat_ID | cat_name Topics top_ID | top_name | top_content | cat_ID Users user_ID | user_name | top_ID or user_ID | user_name | top_ID | top_IDb | top_IDc etc (output to web page using php) But I need to show which users are using which topics, and I can add top_ID to the user file, which is fine if they are only using one topic. [JS] What you need is another table, users_topic: users_topics: user_ID | top_ID and get rid of the top_ID field from the users table. That's the general technique to use when you need a cross-reference. I could add 5 different topic to each user, but then I couldn't expand later. Reverse is true if I add user_ID to the Topics. So, need an idea how to solve this so it doesn't matter how many new users I keep adding, I can still see who is using the topics. As I said, a newbie question. Thanks much. -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newbie question database tables
On 9/10/10 12:31 PM, Jerry Schwartz wrote: Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com Thank you! -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: importing cvs settings - followup
Thanks again for assistance. FYI, I did track this thread down http://ask.metafilter.com/57007/Missing-commas-in-CSV-file (exerpt: Maybe there is a space or something in the 14th column of the first 15 rows. posted by. on February 14, 2007 It's a bug in Excel (not something you did wrong.) posted by . February 14, 2007 ) -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Newbie question: importing cvs settings
-Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: Sunday, September 27, 2009 10:19 AM Cc: 'mysql' Subject: Re: Newbie question: importing cvs settings Back again... I have 192 records to import, and tried my extra line at the end hoping for a work around, but nope, it failed at line 17 again. Invalid field count in CSV input on line 17. Anyone have an idea why this might be happening? [JS] This is just a shot in the dark, but Excel can be rather surprising when it puts out a CSV file. Depending upon the data, and exactly how you've specified the export, it can put double-quotes in unexpected places. If you leave out the 17th line of data what happens? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Patrice Olivier-Wilson wrote: Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: importing cvs settings
Jerry Schwartz wrote: [JS] This is just a shot in the dark, but Excel can be rather surprising when it puts out a CSV file. Depending upon the data, and exactly how you've specified the export, it can put double-quotes in unexpected places. If you leave out the 17th line of data what happens? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Thanks Jerry, Gavin and John: Sorry for not an immediate response to all of your suggestions. Other demands were pulling at me since I first asked for assistance. I opened the .csv file with Text Wrangler, and the commas are missing at about line 17 portfolio_ID,portfolio_sort_ID,portfolio_title,portfolio_bodycopy,portfolio_image,portfolio_before ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg ,1,Kitchens,,123.jpg ,1,Kitchens,,123.jpg So not sure why that is happening. I'm on a Mac, using Excel 2008. But at least you all have helped me find what it is doing, so now, I can at least pull into a txt file and make corrections manually. My thanks to all of you for your help and patience. (above represents an empty portfolio_ID, a filled in sort_ID, title, empty bodycopy, image, empty before) Thank you. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Newbie question: importing cvs settings
1. Try opening up the csv file in a text editor, viewing it in a spreadsheet looks like it's hiding some extra formatting or lines that may be causing problems. 2. Try importing through the mysql CLI. From the screenshot you posted, it looks like PMA is parsing the file and creating an insert statement for each line. It may be incorrectly handling some of that data. Using the CLI you'll get better feedback about what, if any, the error is. Regards, Gavin Towey -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: Saturday, September 26, 2009 11:02 AM To: 'mysql' Subject: Re: Newbie question: importing cvs settings Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you John wrote: I assume you mean csv not cvs! What is the error you get when the import fails? What version of MySQL are you using? Can you post the output of SHOW CREATE TABLE for the table you are trying to load the file in to and a sample of the csv which is failing to load? Do you get the same error if you try and load the files using MySQL client and the LOAD DATA INFILE command? (See this link for details on how to use LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html) Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: 26 September 2009 17:08 To: mysql Subject: Newbie question: importing cvs settings Greetings: I have a project for which need to import cvs files into db. I can do so up to a point. The import will only do 16 lines, consistently. Error is failing at line 17. Steps: create table fields in Excel document, where they all match database fields enter information in several of the columns, but not all as client will be filling it in online (leaving ID blank) save excel to .cvs log into phpMyAdmin import cvs I've experimented with several settings in the import, but consistently, it fails at line 17, even with different .cvs files. Any guidance, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: importing cvs settings
Back again... I have 192 records to import, and tried my extra line at the end hoping for a work around, but nope, it failed at line 17 again. Invalid field count in CSV input on line 17. Anyone have an idea why this might be happening? Patrice Olivier-Wilson wrote: Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Newbie question: importing cvs settings
Patrice, Can you post the output of SHOW CREATE TABLE for the table you are having difficulty inserting into? Without knowing the table structure its very difficult to work out why your data load is failing. Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: 27 September 2009 15:19 Cc: 'mysql' Subject: Re: Newbie question: importing cvs settings Back again... I have 192 records to import, and tried my extra line at the end hoping for a work around, but nope, it failed at line 17 again. Invalid field count in CSV input on line 17. Anyone have an idea why this might be happening? Patrice Olivier-Wilson wrote: Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.113/2396 - Release Date: 09/26/09 05:51:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie question: importing cvs settings
Greetings: I have a project for which need to import cvs files into db. I can do so up to a point. The import will only do 16 lines, consistently. Error is failing at line 17. Steps: create table fields in Excel document, where they all match database fields enter information in several of the columns, but not all as client will be filling it in online (leaving ID blank) save excel to .cvs log into phpMyAdmin import cvs I've experimented with several settings in the import, but consistently, it fails at line 17, even with different .cvs files. Any guidance, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Newbie question: importing cvs settings
I assume you mean csv not cvs! What is the error you get when the import fails? What version of MySQL are you using? Can you post the output of SHOW CREATE TABLE for the table you are trying to load the file in to and a sample of the csv which is failing to load? Do you get the same error if you try and load the files using MySQL client and the LOAD DATA INFILE command? (See this link for details on how to use LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html) Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: 26 September 2009 17:08 To: mysql Subject: Newbie question: importing cvs settings Greetings: I have a project for which need to import cvs files into db. I can do so up to a point. The import will only do 16 lines, consistently. Error is failing at line 17. Steps: create table fields in Excel document, where they all match database fields enter information in several of the columns, but not all as client will be filling it in online (leaving ID blank) save excel to .cvs log into phpMyAdmin import cvs I've experimented with several settings in the import, but consistently, it fails at line 17, even with different .cvs files. Any guidance, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.113/2396 - Release Date: 09/26/09 05:51:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: importing cvs settings
Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you John wrote: I assume you mean csv not cvs! What is the error you get when the import fails? What version of MySQL are you using? Can you post the output of SHOW CREATE TABLE for the table you are trying to load the file in to and a sample of the csv which is failing to load? Do you get the same error if you try and load the files using MySQL client and the LOAD DATA INFILE command? (See this link for details on how to use LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html) Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: 26 September 2009 17:08 To: mysql Subject: Newbie question: importing cvs settings Greetings: I have a project for which need to import cvs files into db. I can do so up to a point. The import will only do 16 lines, consistently. Error is failing at line 17. Steps: create table fields in Excel document, where they all match database fields enter information in several of the columns, but not all as client will be filling it in online (leaving ID blank) save excel to .cvs log into phpMyAdmin import cvs I've experimented with several settings in the import, but consistently, it fails at line 17, even with different .cvs files. Any guidance, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie Question - MySQL Administrator
The online help for mysql administrator is here: http://dev.mysql.com/doc/administrator/en/index.html OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Fri, Feb 20, 2009 at 7:17 AM, Jeff Murdock jeff_murd...@yahoo.com wrote: This is on a Mac OS X (v10.5.6) system in case that matters. 1. - MySQL Administrator Help button says: HELP Help isn't available for MySQL Administrator. Really, no help or did I screw-up the install somehow? 2. - I tried to create my first Table in MySQL Administrator but got this message: ERROR Error executing SQL commands to create table. 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 'DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064) Trying to Execute this: CREATE TABLE `test`.`AddressBook` ( `RecNo` INT NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NULL, `Street2` VARCHAR DEFAULT NULL, `City` VARCHAR DEFAULT NULL, `State` VARCHAR DEFAULT NULL, `Zip` VARCHAR DEFAULT NULL, `HomePhone` VARCHAR DEFAULT NULL, `CellPhone` VARCHAR DEFAULT NULL, PRIMARY KEY (`RecNo`) ) CHARACTER SET utf8 COMMENT = 'Sample'; Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie Question - MySQL Administrator
This is on a Mac OS X (v10.5.6) system in case that matters. 1. - MySQL Administrator Help button says: HELP Help isn’t available for MySQL Administrator. Really, no help or did I screw-up the install somehow? 2. - I tried to create my first Table in MySQL Administrator but got this message: ERROR Error executing SQL commands to create table. 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 'DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064) Trying to Execute this: CREATE TABLE `test`.`AddressBook` ( `RecNo` INT NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NULL, `Street2` VARCHAR DEFAULT NULL, `City` VARCHAR DEFAULT NULL, `State` VARCHAR DEFAULT NULL, `Zip` VARCHAR DEFAULT NULL, `HomePhone` VARCHAR DEFAULT NULL, `CellPhone` VARCHAR DEFAULT NULL, PRIMARY KEY (`RecNo`) ) CHARACTER SET utf8 COMMENT = 'Sample'; Jeff
Re: Newbie Question - MySQL Administrator
Jeff, For starters, it looks like you need a value for VARCHAR. Try the same statement but with VARCHAR(255) . On Fri, Feb 20, 2009 at 1:17 AM, Jeff Murdock jeff_murd...@yahoo.com wrote: This is on a Mac OS X (v10.5.6) system in case that matters. 1. - MySQL Administrator Help button says: HELP Help isn't available for MySQL Administrator. Really, no help or did I screw-up the install somehow? 2. - I tried to create my first Table in MySQL Administrator but got this message: ERROR Error executing SQL commands to create table. 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 'DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064) Trying to Execute this: CREATE TABLE `test`.`AddressBook` ( `RecNo` INT NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NULL, `Street2` VARCHAR DEFAULT NULL, `City` VARCHAR DEFAULT NULL, `State` VARCHAR DEFAULT NULL, `Zip` VARCHAR DEFAULT NULL, `HomePhone` VARCHAR DEFAULT NULL, `CellPhone` VARCHAR DEFAULT NULL, PRIMARY KEY (`RecNo`) ) CHARACTER SET utf8 COMMENT = 'Sample'; Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie question, how do I respond to post?
Hello, I feel stupid here... How do I respond to a specific post? I posted last night, I got a response... now I want to post a response to the response. Don't see a way to do that... Thanks, Lee ** Start the year off right. Easy ways to stay in shape. http://body.aol.com/fitness/winter-exercise?NCID=aolcmp0030002489
RE: Newbie question, how do I respond to post?
Either use reply to all, or manually enter the list email address in the to field. There are two general feelings about list operation: 1) all replies should go to the list 2) all replies should default to the sender This list is configured as #2 (I prefer #1 myself) Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23, 2008 10:19 AM To: mysql@lists.mysql.com Subject: Newbie question, how do I respond to post? Hello, I feel stupid here... How do I respond to a specific post? I posted last night, I got a response... now I want to post a response to the response. Don't see a way to do that... Thanks, Lee ** Start the year off right. Easy ways to stay in shape. http://body.aol.com/fitness/winter-exercise?NCID=aolcmp0030002489 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question connecting with windows named pipes (resolved)
Thanks Sebastian, that did the trick. The full command I use is: $mysqli = new mysqli(., $username,$password, $database,null,/tmp/mysql.sock); Regards, JC Sebastian Mendel wrote: John Comerford schrieb: Hi Folks, I have a database running on Window XP, that I want to disable network connections to and enable 'named pipes'. I am running MySQL 5.0.27 and my.ini looks like... [...] I can connect to the DB using the GUI tools if I set my pipe name to '/tmp/mysql.sock' using the login dialog box. [...] mysql_connect('.', ...); you have to a dot as host: '.'
Re: Newbie Question connecting with windows named pipes (resolved)
John Comerford schrieb: Thanks Sebastian, that did the trick. The full command I use is: $mysqli = new mysqli(., $username,$password, $database,null,/tmp/mysql.sock); there is no need for the socket, their are no sockets on windows $mysqli = new mysqli('.', $username, $password, $database); -- Sebastian Mendel www.sebastianmendel.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question connecting with windows named pipes (resolved)
Hi Sebastian, I did read somewhere that sockets was not valid for windows, and '/tmp/mysql.sock' doesn't resolve to anything on my machine. But I have tried the following commands: $mysqli = new mysqli(., $username,$password, $database); or $mysqli = new mysqli(., $username,$password, $database,null); and I get the following error: Can't open named pipe to host: . pipe: MySQL (2) but when I include the /tmp/mysql.sock parameter it works fine. I was thinking that maybe I have things configured in such a way that MySQL is using /tmp/mysql.sock as the pipe name. I am going to do some testing this evening to see if my suspicion is correct. Thanks again, John Sebastian Mendel wrote: John Comerford schrieb: Thanks Sebastian, that did the trick. The full command I use is: $mysqli = new mysqli(., $username,$password, $database,null,/tmp/mysql.sock); there is no need for the socket, their are no sockets on windows $mysqli = new mysqli('.', $username, $password, $database);
Re: Newbie Question connecting with windows named pipes (resolved)
John Comerford schrieb: Hi Sebastian, I did read somewhere that sockets was not valid for windows, and '/tmp/mysql.sock' doesn't resolve to anything on my machine. But I have tried the following commands: $mysqli = new mysqli(., $username,$password, $database); or $mysqli = new mysqli(., $username,$password, $database,null); and I get the following error: Can't open named pipe to host: . pipe: MySQL (2) yes, 'MySQL' is default name of the pipe, used by the libaray but when I include the /tmp/mysql.sock parameter it works fine. I was thinking that maybe I have things configured in such a way that MySQL is using /tmp/mysql.sock as the pipe name. I am going to do some testing this evening to see if my suspicion is correct. yes: Note: you can specify a pipe name on the advanced network page, if required. possible this is et with the soccket paramter in my.cnf [mysqld] #port= 3306 socket= /tmp/mysql.sock -- Sebastian Mendel www.sebastianmendel.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question connecting with windows named pipes (resolved)
Hi Sebastian, That was it alright. I commented out the socket lines from mysql.ini (restarted it) and changed my php command to: $mysqli = new mysqli(., $username,$password, $database); and all seems to be working fine now. Thanks again for your help, much appreciated. Regards, John Sebastian Mendel wrote: John Comerford schrieb: Hi Sebastian, I did read somewhere that sockets was not valid for windows, and '/tmp/mysql.sock' doesn't resolve to anything on my machine. But I have tried the following commands: $mysqli = new mysqli(., $username,$password, $database); or $mysqli = new mysqli(., $username,$password, $database,null); and I get the following error: Can't open named pipe to host: . pipe: MySQL (2) yes, 'MySQL' is default name of the pipe, used by the libaray but when I include the /tmp/mysql.sock parameter it works fine. I was thinking that maybe I have things configured in such a way that MySQL is using /tmp/mysql.sock as the pipe name. I am going to do some testing this evening to see if my suspicion is correct. yes: Note: you can specify a pipe name on the advanced network page, if required. possible this is et with the soccket paramter in my.cnf [mysqld] #port= 3306 socket= /tmp/mysql.sock -- 1^st Floor, 184 -186 Glenferrie Road, Malvern VIC 3144 PH:*(03) 9500 1466* FX :*(03) 9500 1469* Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Web: www.styleman.com.au http://www.styleman.com.au The information in this e-mail is confidential and is intended solely for the addressee. Any views or opinions presented are solely those of the author and do not necessarily represent those of Option Systems Pty Ltd. If you are not the intended recipient, please delete this message and contact the sender.
Newbie Question connecting with windows named pipes
Hi Folks, I have a database running on Window XP, that I want to disable network connections to and enable 'named pipes'. I am running MySQL 5.0.27 and my.ini looks like... [client] #password= your_password port= 3306 socket= /tmp/mysql.sock [mysqld] #port= 3306 socket= /tmp/mysql.sock #Allow connections via named pipes (Windows NT+ only). Note: you can specify a pipe name on the advanced network page, if required. enable-named-pipe #Don't allow connections via TCP/IP. skip-networking I can connect to the DB using the GUI tools if I set my pipe name to '/tmp/mysql.sock' using the login dialog box. However when I try and connect using PHP I get an error. I have tried several variants of the connect command and I get various errors but all are along the lines of: Unknown MySQL server host '/tmp/mysql.sock' (11004) or Can't connect to MySQL server on 'localhost' (10061) I have tried $mysqli = new mysqli(null, $username,$password, $database); $mysqli = new mysqli(localhost:/tmp/mysql.sock, $username,$password, $database); $mysqli = new mysqli(localhost, $username,$password, $database,3306,/tmp/mysql.sock); $mysqli = new mysqli(localhost, $username,$password, $database,/tmp/mysql.sock); $mysqli = new mysqli(/tmp/mysql.sock, $username,$password, $database); I have also tried the above commands using mysqli_connect ? I have done a few searches of the web but seem to always come up with something like the above? Anybody have any ideas why it won't connect in PHP ? I know this is not a PHP forum, but I am wondering if I have something wrong in my MySQL setup ? TIA, JC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question connecting with windows named pipes
John Comerford schrieb: Hi Folks, I have a database running on Window XP, that I want to disable network connections to and enable 'named pipes'. I am running MySQL 5.0.27 and my.ini looks like... [...] I can connect to the DB using the GUI tools if I set my pipe name to '/tmp/mysql.sock' using the login dialog box. [...] mysql_connect('.', ...); you have to a dot as host: '.' -- Sebastian Mendel www.sebastianmendel.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing CSV file into MySQL DB - Newbie Question
Hi Derek, You never gave us a SHOW CREATE TABLE simple1, which would have helped. To replicate your problem, I did the following, on a linux box (it looks like you're using Windows), using mysql 5.0.18-standard-log: CREATE DATABASE cars; use cars; CREATE TABLE `simple1` ( `one` char(10) default NULL, `two` char(10) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; and then made a text file like your simple1.csv and ran the following: mysqlimport --lines-terminated-by=\n --fields-terminated-by=, --local --user=root --password=rootpass cars simple1.csv and got: cars.simple1: Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 I'd guess I got a warning and you didn't because of the version of MySQL. when I checked out the table, indeed I found that I had only one row. So I tried again, figuring that the binary knew what the end of the line was: mysqlimport --fields-terminated-by=, --local --user=root --password=rootpass cars simple1.csv cars.simple1: Records: 3 Deleted: 0 Skipped: 0 Warnings: 1 aha! 3 records this time! select * from simple1; +--+-+ | one | two | +--+-+ | test1 | test2 | | test11 | test3 | | | NULL| +--+-+ 3 rows in set (0.00 sec) I can guess that I got a warning because there was no comma-separated list on the 3rd line, so it put the first value (blank) into the first field of the 3rd record, but had nothing to put in the 2nd value, so it put NULL. You don't need double quotes in the file. However, if you are importing someone else's file or a previous export, you can put a --fields-enclosed-by='' (that is, single-quote double-quote single-quote) tag to tell mysqlimport that it shouldn't look at the double quotes. hope this helps! Sheeri On 3/5/06, Derek Doerr [EMAIL PROTECTED] wrote: I have a CSV file that I want to import into a MySQL DB table. The file contains 15 fields. The able to import into will contain those same 15 fields, plus an auto-generated Primary Key fields. Since this is the first time I'm working with mysqlimport, I created a small test table to start with - simple1, containing two varchar fields - field1 and field2. I'm trying to import a small test file into simple1, to get the hang of using mysqlimport. The test file contains 2 records and 3 lines - the 3rd line is blank: test1,test2 test11,test3 I run the import as follows: C:\Program Files\xampp\mysql\binmysqlimport.exe --lines-terminated-by=\r --fields-terminated-by=, --local --user=root cars c:\dev\test\simple1.csv The import report shows: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 The data that ends up in the DB, however, only contains 1 record: \test1\,\test2\ \test11\ (1) how do I get mysqlimport to import both records, properly parsing the fields - two fields per record? (2) do I need to wrap the imported records in double-quotes? Why do the double-quotes show up in the MySQL DB table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing CSV file into MySQL DB - Newbie Question
I have a CSV file that I want to import into a MySQL DB table. The file contains 15 fields. The able to import into will contain those same 15 fields, plus an auto-generated Primary Key fields. Since this is the first time I'm working with mysqlimport, I created a small test table to start with - simple1, containing two varchar fields - field1 and field2. I'm trying to import a small test file into simple1, to get the hang of using mysqlimport. The test file contains 2 records and 3 lines - the 3rd line is blank: test1,test2 test11,test3 I run the import as follows: C:\Program Files\xampp\mysql\binmysqlimport.exe --lines-terminated-by=\r --fields-terminated-by=, --local --user=root cars c:\dev\test\simple1.csv The import report shows: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 The data that ends up in the DB, however, only contains 1 record: \test1\,\test2\ \test11\ (1) how do I get mysqlimport to import both records, properly parsing the fields - two fields per record? (2) do I need to wrap the imported records in double-quotes? Why do the double-quotes show up in the MySQL DB table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hi, newbie question on a select statement
Perhaps you were using CAST() incorrectly? What was your attempt? my test table: show create table ultimas_repuestas; +---+-+ | Table | Create Table | +---+-+ | ultimas_repuestas | CREATE TABLE `ultimas_repuestas` ( `valorSNMP` varchar(3) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (0.00 sec) then I put some values in it: mysql select * from ultimas_repuestas order by valorSNMP; +---+ | valorSNMP | +---+ | 1 | | 10| | 11| | 12| | 13| | 14| | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | +---+ 14 rows in set (0.02 sec) Then I tried a CAST() statement: mysql select * from ultimas_repuestas order by CAST(valorSNMP as SIGNED INTEGER); +---+ | valorSNMP | +---+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10| | 11| | 12| | 13| | 14| +---+ 14 rows in set (0.00 sec) works just fine in MySQL 5.0. not that you gave the version #.although this works fine, too: mysql select * from ultimas_repuestas order by valorSNMP+0; +---+ | valorSNMP | +---+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10| | 11| | 12| | 13| | 14| +---+ 14 rows in set (0.00 sec) Hope this helps! Next time, instead of saying this didn't work give the example, and what you got back (what does 'doesn't work' mean? did you get an error? Or it didn't return things in the right order?) -Sheeri On 2/16/06, Ariel Sánchez Mora [EMAIL PROTECTED] wrote: I searched the other lists and couldn't find one that was more appropiate for this question; if there is, please tell me so :) I am monitoring networking equipment and so far I'm saving all my data in a MySQL database (hence an email to this list). I'm using only VARCHARs because the SNMP agent returns only strings and I didn't find enough a reason for converting the different types of answers, since most string comparations are donde correctly; I received both text and numbers from the monitoring tasks and wanted to keep it simple. However when I execute this select statement I am getting this problem: select info_oficina,valorSNMP from ultimas_respuestas_snmp where columna_donde_guardar='USO_CPU_1min' order by valorSNMP desc limit 10; info_oficina valorSNMP CSF Desamparados error Periferica Palmares 4 CSF San Pedro4 Sucursal Guapiles4 Periferica Pentagono San Pablo 30 Periferica Tibas 3 Periferica Buenos Aires 3 Sucursal Turrialba 3 Ventanilla Florencia 3 CSF del Sur (Ciudad Neilly) 3 Where info_oficina, valorSNMP are columns, ultimas_respuestas_snmp is my table, etc. The summary question is: given a column with the numbers 0 to 99 of type varchar, how can i tell MySQL to order it as if they were integer values, so that instead of 99,98,97,96,95,94,93,92,91,90,9,89 I'd have 99,98,97,96,95,94,93,92,91,90,89,88 Apparently, CAST() does not have an effect (but feel free to prove me wrong) Thanks! Ariel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi, newbie question on a select statement
I searched the other lists and couldn't find one that was more appropiate for this question; if there is, please tell me so :) I am monitoring networking equipment and so far I'm saving all my data in a MySQL database (hence an email to this list). I'm using only VARCHARs because the SNMP agent returns only strings and I didn't find enough a reason for converting the different types of answers, since most string comparations are donde correctly; I received both text and numbers from the monitoring tasks and wanted to keep it simple. However when I execute this select statement I am getting this problem: select info_oficina,valorSNMP from ultimas_respuestas_snmp where columna_donde_guardar='USO_CPU_1min' order by valorSNMP desc limit 10; info_oficina valorSNMP CSF Desamparados error Periferica Palmares 4 CSF San Pedro4 Sucursal Guapiles4 Periferica Pentagono San Pablo 30 Periferica Tibas 3 Periferica Buenos Aires 3 Sucursal Turrialba 3 Ventanilla Florencia 3 CSF del Sur (Ciudad Neilly) 3 Where info_oficina, valorSNMP are columns, ultimas_respuestas_snmp is my table, etc. The summary question is: given a column with the numbers 0 to 99 of type varchar, how can i tell MySQL to order it as if they were integer values, so that instead of 99,98,97,96,95,94,93,92,91,90,9,89 I'd have 99,98,97,96,95,94,93,92,91,90,89,88 Apparently, CAST() does not have an effect (but feel free to prove me wrong) Thanks! Ariel
RE: Hi, newbie question on a select statement
Hi Ariel, It works fine for me localhost.testshow create table mytest\G *** 1. row *** Table: mytest Create Table: CREATE TABLE `mytest` ( `id` int(11) NOT NULL auto_increment, `test_col` varchar(10) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 5.0.18-max on port mysql.sock as [EMAIL PROTECTED] localhost.test 5.0.18-max on port mysql.sock as [EMAIL PROTECTED] localhost.testselect * from mytest order by test_col; ++--+ | id | test_col | ++--+ | 1 | 1| | 10 | 10 | | 12 | 11 | | 2 | 2| | 11 | 20 | | 3 | 3| | 4 | 4| | 5 | 5| | 6 | 6| | 7 | 7| | 8 | 8| | 9 | 9| ++--+ 12 rows in set (0.01 sec) 5.0.18-max on port mysql.sock as [EMAIL PROTECTED] localhost.testselect * from mytest order by CAST(test_col as unsigned); ++--+ | id | test_col | ++--+ | 1 | 1| | 2 | 2| | 3 | 3| | 4 | 4| | 5 | 5| | 6 | 6| | 7 | 7| | 8 | 8| | 9 | 9| | 10 | 10 | | 12 | 11 | | 11 | 20 | ++--+ 12 rows in set (0.00 sec) Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Ariel Sánchez Mora [mailto:[EMAIL PROTECTED] Sent: Friday, 17 February 2006 11:29 AM To: mysql@lists.mysql.com Subject: Hi, newbie question on a select statement I searched the other lists and couldn't find one that was more appropiate for this question; if there is, please tell me so :) I am monitoring networking equipment and so far I'm saving all my data in a MySQL database (hence an email to this list). I'm using only VARCHARs because the SNMP agent returns only strings and I didn't find enough a reason for converting the different types of answers, since most string comparations are donde correctly; I received both text and numbers from the monitoring tasks and wanted to keep it simple. However when I execute this select statement I am getting this problem: select info_oficina,valorSNMP from ultimas_respuestas_snmp where columna_donde_guardar='USO_CPU_1min' order by valorSNMP desc limit 10; info_oficina valorSNMP CSF Desamparados error Periferica Palmares 4 CSF San Pedro4 Sucursal Guapiles4 Periferica Pentagono San Pablo 30 Periferica Tibas 3 Periferica Buenos Aires 3 Sucursal Turrialba 3 Ventanilla Florencia 3 CSF del Sur (Ciudad Neilly) 3 Where info_oficina, valorSNMP are columns, ultimas_respuestas_snmp is my table, etc. The summary question is: given a column with the numbers 0 to 99 of type varchar, how can i tell MySQL to order it as if they were integer values, so that instead of 99,98,97,96,95,94,93,92,91,90,9,89 I'd have 99,98,97,96,95,94,93,92,91,90,89,88 Apparently, CAST() does not have an effect (but feel free to prove me wrong) Thanks! Ariel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Complexity Limit Question [Newbie Question]
Hi, I have several tables linked in various ways so that an inner join is possible. However, at the same time and in the same SQL query, I'd also like to query by some field values in one of the tables. Two quick questions: a)Will MySQL allow joins that involve more than two tables (in my case, perhaps as many as 5)? b)Can limits on a key field be included in the join in the same SQL statement as does the join, i.e. ... WHERE N3 AND N20 ... or something like that. Thanks, Dave. --- P.S.--It might seem that I'm a lazy bum and unwilling to just try it. My situation is that I'm working on design documentation for the database and working through in my head whether everything can be done in approximately O(N) time. The book I have doesn't mention joins on more than two tables. I will get the database set up and try it soon. Thanks for your patience. --- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Complexity Limit Question [Newbie Question]
David T. Ashley wrote: Hi, I have several tables linked in various ways so that an inner join is possible. However, at the same time and in the same SQL query, I'd also like to query by some field values in one of the tables. Two quick questions: a)Will MySQL allow joins that involve more than two tables (in my case, perhaps as many as 5)? Yes. The join limit is 128 tables but if you hit it you've just done it wrong. b)Can limits on a key field be included in the join in the same SQL statement as does the join, i.e. ... WHERE N3 AND N20 ... or something like that. Yes. Though not everyone considers it good practice. As an example: SELECT p.name, c.name FROM parent p INNER JOIN child c ON c.parent_id=p.id AND c.age 18 WHERE p.sex in ('male','unspecified'); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Complexity Limit Question [Newbie Question]
David T. Ashley [EMAIL PROTECTED] wrote on 07/02/2006 14:03:04: Hi, I have several tables linked in various ways so that an inner join is possible. However, at the same time and in the same SQL query, I'd also like to query by some field values in one of the tables. Two quick questions: a)Will MySQL allow joins that involve more than two tables (in my case, perhaps as many as 5)? b)Can limits on a key field be included in the join in the same SQL statement as does the join, i.e. ... WHERE N3 AND N20 ... or something like that. Yes, you can do multi-way joins, and people often do. My biggest is 3-way, but some people do at least 5-way. Beware that it is easy to specify operations which will heavily load the system if you are not careful. The constraints in the WHERE statement are *logically* and syntactically done on the huge table produced by the joins. However, the MySQL optimiser is not stupid and will perform the filter upstream of the JOIN where possible. Some experimentation and use of the EXPLAIN statement may be necessary to find the best ordering for queries. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Complexity Limit Question [Newbie Question]
On Tue, February 7, 2006 10:46 am, [EMAIL PROTECTED] wrote: David T. Ashley [EMAIL PROTECTED] wrote on 07/02/2006 14:03:04: a)Will MySQL allow joins that involve more than two tables (in my case, perhaps as many as 5)? b)Can limits on a key field be included in the join in the same SQL statement as does the join, i.e. ... WHERE N3 AND N20 ... or something like that. Yes, you can do multi-way joins, and people often do. My biggest is 3-way, but some people do at least 5-way. Beware that it is easy to specify operations which will heavily load the system if you are not careful. The constraints in the WHERE statement are *logically* and syntactically done on the huge table produced by the joins. However, the MySQL optimiser is not stupid and will perform the filter upstream of the JOIN where possible. Some experimentation and use of the EXPLAIN statement may be necessary to find the best ordering for queries. My thought process was that if all the fields involved--both in the JOIN and limits on any fields--were key fields, then the operation should be approximately O(log N). But, now that I think about it: a)A simple limit operation or search operation on a KEY field should be O(log N) (i.e. doesn't MySQL build index tables or something on key fields to get approximately that behavior?), BUT b)I think you are right about the caution ... just thinking about it, it isn't clear that a JOIN will be O(log N) when the only condition is something like (table1.a = table2.b). It seems that a limiting condition (sex=MALE or datebirthday1 and datebirthday2 or whatever) would have to be applied first to a key field (by the optimizer?) or else the JOIN would be something like O(N) or maybe even worse. Thanks for pointing out the EXPLAIN keyword. That may be helpful. I'd be curious on any perspective ... what computational complexity is an inner join with no other conditions (i.e. the only condition is table1.a = table2.b)? I'd guess it isn't O(log N). Well that concludes my newbie questions. Thanks for the help. I'll now begin using MySQL ... --- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Complexity Limit Question [Newbie Question]
At 3:46 pm + 7/2/06, [EMAIL PROTECTED] wrote: Yes, you can do multi-way joins, and people often do. My biggest is 3-way, but some people do at least 5-way. My record is and 8-way join (7 tables, one twice). So there! ;-) Beware that it is easy to specify operations which will heavily load the system if you are not careful. Yes indeed. You need to think carefully about what indices you need on your tables. As has already been said, EXPLAIN SELECT is your friend! James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AUTOINCREMENT / UNIQUE Behavior [Newbie Question]
I remember in MySQL that you can define an integer table field as AUTOINCREMENT and UNIQUE (I might have the specific keywords wrong, but everyone will know what I mean). In the life of a database where there are frequent additions and deletions, 2^32 isn't that large of a number. When the integer field reaches 2^32-1 or whatever the upper limit is, what happens then? Will it try to reuse available values from records that have been deleted? Or is it always an error? Thanks, Dave. --- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTOINCREMENT / UNIQUE Behavior [Newbie Question]
In the last episode (Feb 06), David T. Ashley said: I remember in MySQL that you can define an integer table field as AUTOINCREMENT and UNIQUE (I might have the specific keywords wrong, but everyone will know what I mean). In the life of a database where there are frequent additions and deletions, 2^32 isn't that large of a number. When the integer field reaches 2^32-1 or whatever the upper limit is, what happens then? Will it try to reuse available values from records that have been deleted? Or is it always an error? It will roll over and return a duplicate key error on the first insert of a low-numbered value that still exists. If you think you're going to generate more than 2 billion records, use a BIGINT which will never roll over (well, if you inserted 2 billion records per second, it would roll over in ~270 years). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question: listing open issues regardless of project
Hello. Please, I don't need comments telling me that I'm missing the semicolon or that comments such as doesn't work are worthless. I know there's something inherently wrong with this query (obviously . . . since it doesn't give me the results that I am looking for). Does anyone havea constructive comment as to how to do this? Please, could you answer what are you going to get in the last columns. You've said that you had problems with the last seven columns, but from the logic of your query I see that you just want a column, which changes its value depending on the value in other field. Am I correct? Please provide a sample output (what you want to see in the results) in case I'm wrong. Now I'm not talking about syntax, but rather about sense of the query. Kraer, Joseph wrote: I am sending this message to both Eventum and MySQL support lists. I am trying to write a select statement in the MySQL Query Browser (v. 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is 4.3.10). My goal is to list certain data from all open issues, across projects, as well as listing some issue details contained in custom fields. I don't have a problem getting the data for the first eight columns of my query. My problem lies with listing the next seven columns, which come from custom fields. I thought of using a PHP script but this is a one-time deal and my knowledge of PHP is as poor as that of MySQL. As Eventum users may know, headings for custom fields are extracted from cells in the eventum_custom_field_option table. I thought that IF statements would do the job, but I get a syntax error (1064). Obviously, they are not the way to go. Nevertheless, here's the complete query so you can get an idea of where I want to go: SELECT DISTINCT eventum_issue.iss_id AS Issue ID, eventum_project_priority.pri_title AS Priority, eventum_user.usr_full_name AS Assigned, eventum_project.prj_title AS Project Name, eventum_project_category.prc_title AS Category, eventum_status.sta_title AS Status, eventum_issue.iss_updated_date AS Last Update Date, eventum_issue.iss_summary AS Summary, IF eventum_custom_field.fld_id =3D 47 THEN eventum_custom_field_option.cfo_value AS Product Name ELSE IF eventum_custom_field.fld_id =3D 59 THEN eventum_custom_field_option.cfo_value AS Project Name ELSE IF eventum_custom_field.fld_id =3D 4 THEN eventum_custom_field_option.cfo_value AS Change Requester ELSE IF eventum_custom_field.fld_id =3D 1 THEN eventum_custom_field_option.cfo_value AS Change Type ELSE IF eventum_custom_field.fld_id =3D 2 THEN eventum_custom_field_option.cfo_value AS Requested Completion Date ELSE IF eventum_custom_field.fld_id =3D 46 THEN eventum_custom_field_option.cfo_value AS BSA Lead ELSE IF eventum_custom_field.fld_id =3D 37 THEN eventum_custom_field_option.cfo_value AS Developer FROM eventum_issue, eventum_custom_field, eventum_custom_field_option INNER JOIN eventum_project_priority, eventum_issue_user, eventum_user, eventum_project, eventum_project_category, eventum_status eventum_issue_custom_field WHERE eventum_issue.iss_pri_id =3D eventum_project_priority.pri_id AND eventum_issue.iss_id =3D eventum_issue_user.isu_iss_id AND eventum_issue_user.isu_usr_id =3D eventum_user.usr_id AND eventum_issue.iss_prj_id =3D eventum_project.prj_id AND eventum_issue.iss_prc_id =3D eventum_project_category.prc_id AND eventum_issue.iss_sta_id =3D eventum_status.sta_id AND (eventum_issue.iss_closed_date IS NULL OR (eventum_issue.iss_closed_date IS NOT NULL AND (eventum_issue.iss_sta_id !=3D 5 OR eventum_issue.iss_sta_id !=3D 6 OR eventum_issue.iss_sta_id !=3D 9))) ORDER BY eventum_issue.iss_id Please, I don't need comments telling me that I'm missing the semicolon or that comments such as doesn't work are worthless. I know there's something inherently wrong with this query (obviously . . . since it doesn't give me the results that I am looking for). Does anyone have a constructive comment as to how to do this? Thank you very much in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To
RE: Newbie Question: listing open issues regardless of project
Gleb, What I need to have is a total of 16 columns: issue ID, priority, assigned (to), project name, category, status, last update date, summary, product name, project name (not the same as before), change requester, change type, requested completion date, lead, developer, impacted dept. I have no problems generating the first eight or the last columns. The seven in between are the issue as they are not columns per se. They are cells in different tables. I need to extract the contents of certain cells, based on certain IDs, to be used as the column headings in the output to my query. Then, I need to look into other tables to fill those columns. This is what I need help with: how do I generate those columns? I guess they could be generated separately and then I could put both of my outputs together. Unfortunately, time is running out. TIA, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 6:52 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project Hello. Please, I don't need comments telling me that I'm missing the semicolon or that comments such as doesn't work are worthless. I know there's something inherently wrong with this query (obviously . . . since it doesn't give me the results that I am looking for). Does anyone have a constructive comment as to how to do this? Please, could you answer what are you going to get in the last columns. You've said that you had problems with the last seven columns, but from the logic of your query I see that you just want a column, which changes its value depending on the value in other field. Am I correct? Please provide a sample output (what you want to see in the results) in case I'm wrong. Now I'm not talking about syntax, but rather about sense of the query. Kraer, Joseph wrote: I am sending this message to both Eventum and MySQL support lists. I am trying to write a select statement in the MySQL Query Browser (v. 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is 4.3.10). My goal is to list certain data from all open issues, across projects, as well as listing some issue details contained in custom fields. I don't have a problem getting the data for the first eight columns of my query. My problem lies with listing the next seven columns, which come from custom fields. I thought of using a PHP script but this is a one-time deal and my knowledge of PHP is as poor as that of MySQL. As Eventum users may know, headings for custom fields are extracted from cells in the eventum_custom_field_option table. I thought that IF statements would do the job, but I get a syntax error (1064). Obviously, they are not the way to go. Nevertheless, here's the complete query so you can get an idea of where I want to go: SELECT DISTINCT eventum_issue.iss_id AS Issue ID, eventum_project_priority.pri_title AS Priority, eventum_user.usr_full_name AS Assigned, eventum_project.prj_title AS Project Name, eventum_project_category.prc_title AS Category, eventum_status.sta_title AS Status, eventum_issue.iss_updated_date AS Last Update Date, eventum_issue.iss_summary AS Summary, IF eventum_custom_field.fld_id =3D 47 THEN eventum_custom_field_option.cfo_value AS Product Name ELSE IF eventum_custom_field.fld_id =3D 59 THEN eventum_custom_field_option.cfo_value AS Project Name ELSE IF eventum_custom_field.fld_id =3D 4 THEN eventum_custom_field_option.cfo_value AS Change Requester ELSE IF eventum_custom_field.fld_id =3D 1 THEN eventum_custom_field_option.cfo_value AS Change Type ELSE IF eventum_custom_field.fld_id =3D 2 THEN eventum_custom_field_option.cfo_value AS Requested Completion Date ELSE IF eventum_custom_field.fld_id =3D 46 THEN eventum_custom_field_option.cfo_value AS BSA Lead ELSE IF eventum_custom_field.fld_id =3D 37 THEN eventum_custom_field_option.cfo_value AS Developer FROM eventum_issue, eventum_custom_field, eventum_custom_field_option INNER JOIN eventum_project_priority, eventum_issue_user, eventum_user, eventum_project, eventum_project_category, eventum_status eventum_issue_custom_field WHERE eventum_issue.iss_pri_id =3D eventum_project_priority.pri_id AND eventum_issue.iss_id =3D eventum_issue_user.isu_iss_id AND eventum_issue_user.isu_usr_id =3D eventum_user.usr_id AND eventum_issue.iss_prj_id =3D eventum_project.prj_id AND eventum_issue.iss_prc_id =3D eventum_project_category.prc_id AND eventum_issue.iss_sta_id =3D eventum_status.sta_id AND (eventum_issue.iss_closed_date IS NULL OR (eventum_issue.iss_closed_date IS NOT NULL
RE: Newbie Question: listing open issues regardless of project
I see it! Tito, you have to pivot those fields out of your custom fields/custom field values table(s). One of the best places to do that is into a temporary table so that you can join your pivoted rows into the rest of the report. I am assuming that the `eventum_custom_field` table has a field something like `issue_id` that associates a field with an issue. I am also assuming that there is only one custom field of any one type per issue. CREATE TEMPORARY TABLE tmpCustFields SELECT cf.issue_id , MAX(if(fld_id=47,cfo.cfo_value,NULL)) as ProductName , MAX(if(fld_id=59,cfo.cfo_value,NULL)) as ProjectName , MAX(if(fld_id=4,cfo.cfo_value,NULL)) as ChangeRequester , MAX(if(fld_id=1,cfo.cfo_value,NULL)) as ChangeType , MAX(if(fld_id=2,cfo.cfo_value,NULL)) as ReqCompletionDate , MAX(if(fld_id=46,cfo.cfo_value,NULL)) as BSALead , MAX(if(fld_id=37,cfo.cfo_value,NULL)) as Developer FROM eventum_custom_fields cf LEFT JOIN eventum_custom_field_options cfo ON cf.cf_id = cfo.cfo_custom_field_id GROUP BY cf.issue_id; You will need to modify the column names in the ON clause to match the actual names of the columns that you need to relate an option to a field or an issue (whichever works). This query builds your middle columns into a table of their own. I hope that once you get them this far, JOINing this temp table to the rest of the tables you need to build your query will look pretty straight-forward. Look at the data to see what we did SELECT * from tmpCustFields limit 100; Hope that helps! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kraer, Joseph [EMAIL PROTECTED] wrote on 12/06/2005 10:39:01 AM: Gleb, What I need to have is a total of 16 columns: issue ID, priority, assigned (to), project name, category, status, last update date, summary, product name, project name (not the same as before), change requester, change type, requested completion date, lead, developer, impacted dept. I have no problems generating the first eight or the last columns. The seven in between are the issue as they are not columns per se. They are cells in different tables. I need to extract the contents of certain cells, based on certain IDs, to be used as the column headings in the output to my query. Then, I need to look into other tables to fill those columns. This is what I need help with: how do I generate those columns? I guess they could be generated separately and then I could put both of my outputs together. Unfortunately, time is running out. TIA, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 6:52 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project Hello. Please, I don't need comments telling me that I'm missing the semicolon or that comments such as doesn't work are worthless. I know there's something inherently wrong with this query (obviously . . . since it doesn't give me the results that I am looking for). Does anyone have a constructive comment as to how to do this? Please, could you answer what are you going to get in the last columns. You've said that you had problems with the last seven columns, but from the logic of your query I see that you just want a column, which changes its value depending on the value in other field. Am I correct? Please provide a sample output (what you want to see in the results) in case I'm wrong. Now I'm not talking about syntax, but rather about sense of the query. Kraer, Joseph wrote: I am sending this message to both Eventum and MySQL support lists. I am trying to write a select statement in the MySQL Query Browser (v. 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is 4.3.10). My goal is to list certain data from all open issues, across projects, as well as listing some issue details contained in custom fields. I don't have a problem getting the data for the first eight columns of my query. My problem lies with listing the next seven columns, which come from custom fields. I thought of using a PHP script but this is a one-time deal and my knowledge of PHP is as poor as that of MySQL. As Eventum users may know, headings for custom fields are extracted from cells in the eventum_custom_field_option table. I thought that IF statements would do the job, but I get a syntax error (1064). Obviously, they are not the way to go. Nevertheless, here's the complete query so you can get an idea of where I want to go: SELECT DISTINCT eventum_issue.iss_id AS Issue ID, eventum_project_priority.pri_title AS Priority, eventum_user.usr_full_name AS Assigned, eventum_project.prj_title AS Project Name, eventum_project_category.prc_title AS Category, eventum_status.sta_title AS Status
Re: Newbie Question: listing open issues regardless of project
Hello. So it is clear now, that you should have 16 columns and to build dynamically the column headings. The usual way to do such things in SQL is prepared statements. See: http://dev.mysql.com/doc/refman/5.0/en/sqlps.html And good example of how to use them (though it is an article about stored routines) here: http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html You will need to use user variables as well: http://dev.mysql.com/doc/refman/5.0/en/example-user-variables.html CONCAT function: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Control flow functions: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Correlated subqueries: http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html Hope that'll be enough to make your query work. Kraer, Joseph wrote: Gleb, What I need to have is a total of 16 columns: issue ID, priority, assigned (to), project name, category, status, last update date, summary, product name, project name (not the same as before), change requester, change type, requested completion date, lead, developer, impacted dept. I have no problems generating the first eight or the last columns. The seven in between are the issue as they are not columns per se. They are cells in different tables. I need to extract the contents of certain cells, based on certain IDs, to be used as the column headings in the output to my query. Then, I need to look into other tables to fill those columns. This is what I need help with: how do I generate those columns? I guess they could be generated separately and then I could put both of my outputs together. Unfortunately, time is running out. TIA, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 6:52 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Question: listing open issues regardless of project
Shawn, Glen, and everyone else, Thank you very much! I do believe that I have enough material to work on it now! I appreciate your help very much! Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 11:03 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project Hello. So it is clear now, that you should have 16 columns and to build dynamically the column headings. The usual way to do such things in SQL is prepared statements. See: http://dev.mysql.com/doc/refman/5.0/en/sqlps.html And good example of how to use them (though it is an article about stored routines) here: http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html You will need to use user variables as well: http://dev.mysql.com/doc/refman/5.0/en/example-user-variables.html CONCAT function: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Control flow functions: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Correlated subqueries: http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html Hope that'll be enough to make your query work. Kraer, Joseph wrote: Gleb, What I need to have is a total of 16 columns: issue ID, priority, assigned (to), project name, category, status, last update date, summary, product name, project name (not the same as before), change requester, change type, requested completion date, lead, developer, impacted dept. I have no problems generating the first eight or the last columns. The seven in between are the issue as they are not columns per se. They are cells in different tables. I need to extract the contents of certain cells, based on certain IDs, to be used as the column headings in the output to my query. Then, I need to look into other tables to fill those columns. This is what I need help with: how do I generate those columns? I guess they could be generated separately and then I could put both of my outputs together. Unfortunately, time is running out. TIA, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 6:52 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question to both lists on listing open issues regardless of project
I am trying to write a select statement in the MySQL Query Browser (v. 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is 4.3.10). My goal is to list certain data from all open issues, across projects, as well as listing some issue details contained in custom fields. I don't have a problem getting the data for the first eight columns of my query. My problem lies with listing the next seven columns, which come from custom fields. I thought of using a PHP script but this is a one-time deal and my knowledge of PHP is as poor as that of MySQL. As Eventum users may know, headings for custom fields are extracted from cells in the eventum_custom_field_option table. I thought that IF statements would do the job, but I get a syntax error (1064). Obviously, they are not the way to go. Nevertheless, here's the complete query so you can get an idea of where I want to go: SELECT DISTINCT eventum_issue.iss_id AS Issue ID, eventum_project_priority.pri_title AS Priority, eventum_user.usr_full_name AS Assigned, eventum_project.prj_title AS Project Name, eventum_project_category.prc_title AS Category, eventum_status.sta_title AS Status, eventum_issue.iss_updated_date AS Last Update Date, eventum_issue.iss_summary AS Summary, IF eventum_custom_field.fld_id = 47 THEN eventum_custom_field_option.cfo_value AS Product Name ELSE IF eventum_custom_field.fld_id = 59 THEN eventum_custom_field_option.cfo_value AS Project Name ELSE IF eventum_custom_field.fld_id = 4 THEN eventum_custom_field_option.cfo_value AS Change Requester ELSE IF eventum_custom_field.fld_id = 1 THEN eventum_custom_field_option.cfo_value AS Change Type ELSE IF eventum_custom_field.fld_id = 2 THEN eventum_custom_field_option.cfo_value AS Requested Completion Date ELSE IF eventum_custom_field.fld_id = 46 THEN eventum_custom_field_option.cfo_value AS BSA Lead ELSE IF eventum_custom_field.fld_id = 37 THEN eventum_custom_field_option.cfo_value AS Developer FROM eventum_issue, eventum_custom_field, eventum_custom_field_option INNER JOIN eventum_project_priority, eventum_issue_user, eventum_user, eventum_project, eventum_project_category, eventum_status eventum_issue_custom_field WHERE eventum_issue.iss_pri_id = eventum_project_priority.pri_id AND eventum_issue.iss_id = eventum_issue_user.isu_iss_id AND eventum_issue_user.isu_usr_id = eventum_user.usr_id AND eventum_issue.iss_prj_id = eventum_project.prj_id AND eventum_issue.iss_prc_id = eventum_project_category.prc_id AND eventum_issue.iss_sta_id = eventum_status.sta_id AND (eventum_issue.iss_closed_date IS NULL OR (eventum_issue.iss_closed_date IS NOT NULL AND (eventum_issue.iss_sta_id != 5 OR eventum_issue.iss_sta_id != 6 OR eventum_issue.iss_sta_id != 9))) ORDER BY eventum_issue.iss_id Please, I don't need comments telling me that I'm missing the semicolon or that comments such as doesn't work are worthless. I know there's something inherently wrong with this query (obviously . . . since it doesn't give me the results that I am looking for). Does anyone have a constructive comment as to how to do this? Thank you very much in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Question: listing open issues regardless of project
I am sending this message to both Eventum and MySQL support lists. I am trying to write a select statement in the MySQL Query Browser (v. 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is 4.3.10). My goal is to list certain data from all open issues, across projects, as well as listing some issue details contained in custom fields. I don't have a problem getting the data for the first eight columns of my query. My problem lies with listing the next seven columns, which come from custom fields. I thought of using a PHP script but this is a one-time deal and my knowledge of PHP is as poor as that of MySQL. As Eventum users may know, headings for custom fields are extracted from cells in the eventum_custom_field_option table. I thought that IF statements would do the job, but I get a syntax error (1064). Obviously, they are not the way to go. Nevertheless, here's the complete query so you can get an idea of where I want to go: SELECT DISTINCT eventum_issue.iss_id AS Issue ID, eventum_project_priority.pri_title AS Priority, eventum_user.usr_full_name AS Assigned, eventum_project.prj_title AS Project Name, eventum_project_category.prc_title AS Category, eventum_status.sta_title AS Status, eventum_issue.iss_updated_date AS Last Update Date, eventum_issue.iss_summary AS Summary, IF eventum_custom_field.fld_id = 47 THEN eventum_custom_field_option.cfo_value AS Product Name ELSE IF eventum_custom_field.fld_id = 59 THEN eventum_custom_field_option.cfo_value AS Project Name ELSE IF eventum_custom_field.fld_id = 4 THEN eventum_custom_field_option.cfo_value AS Change Requester ELSE IF eventum_custom_field.fld_id = 1 THEN eventum_custom_field_option.cfo_value AS Change Type ELSE IF eventum_custom_field.fld_id = 2 THEN eventum_custom_field_option.cfo_value AS Requested Completion Date ELSE IF eventum_custom_field.fld_id = 46 THEN eventum_custom_field_option.cfo_value AS BSA Lead ELSE IF eventum_custom_field.fld_id = 37 THEN eventum_custom_field_option.cfo_value AS Developer FROM eventum_issue, eventum_custom_field, eventum_custom_field_option INNER JOIN eventum_project_priority, eventum_issue_user, eventum_user, eventum_project, eventum_project_category, eventum_status eventum_issue_custom_field WHERE eventum_issue.iss_pri_id = eventum_project_priority.pri_id AND eventum_issue.iss_id = eventum_issue_user.isu_iss_id AND eventum_issue_user.isu_usr_id = eventum_user.usr_id AND eventum_issue.iss_prj_id = eventum_project.prj_id AND eventum_issue.iss_prc_id = eventum_project_category.prc_id AND eventum_issue.iss_sta_id = eventum_status.sta_id AND (eventum_issue.iss_closed_date IS NULL OR (eventum_issue.iss_closed_date IS NOT NULL AND (eventum_issue.iss_sta_id != 5 OR eventum_issue.iss_sta_id != 6 OR eventum_issue.iss_sta_id != 9))) ORDER BY eventum_issue.iss_id Please, I don't need comments telling me that I'm missing the semicolon or that comments such as doesn't work are worthless. I know there's something inherently wrong with this query (obviously . . . since it doesn't give me the results that I am looking for). Does anyone have a constructive comment as to how to do this? Thank you very much in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question to both lists on listing open issues regardless of project
Kraer, Joseph wrote: snipped I thought that IF statements would do the job, but I get a syntax error (1064). Obviously, they are not the way to go. Nevertheless, here's the complete query so you can get an idea of where I want to go: Your if statement is wrong. The syntax is: if ( condition, value_if_true, value_if_false ) You can also use 'case' statements: case when condition then value_if_true else value_if_false end Substitute your: - condition - value_if_true - value_if_false into the above syntax. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Question on Update
I am trying to update a couple of rows in a table by doing the following: update table set column5 = number1 where column 1 = number2 and column1 = number3 but it is not working. I tried listing the conditions separated by commas (where column 1 = number2, column1 = number3) and also didn't work. What am I doing wrong? Thanks in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question on Update
Kraer, Joseph wrote: I am trying to update a couple of rows in a table by doing the following: update table set column5 = number1 where column 1 = number2 and column1 = number3 This appears to be correct syntax. but it is not working. We can't help you if you don't tell us what you mean by not working. Do you get an error message? If so, what is it? Do you get unexpected results? If so, tell us what you expected and what you got. I tried listing the conditions separated by commas (where column 1 = number2, column1 = number3) and also didn't work. What am I doing wrong? Because that isn't valid syntax. Don't make things up. Read the manual instead. UPDATE syntax is described in detail http://dev.mysql.com/doc/refman/5.0/en/update.html. Thanks in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Question on Update
I guess that the problem is the reserved word table Try it: Update `table` Set column5=number1 where column 1 = number2 and column1 = number3 But the sintax is ok Regards! -Mensaje original- De: Kraer, Joseph [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 30 de Noviembre de 2005 10:59 a.m. Para: mysql@lists.mysql.com Asunto: Newbie Question on Update I am trying to update a couple of rows in a table by doing the following: update table set column5 = number1 where column 1 = number2 and column1 = number3 but it is not working. I tried listing the conditions separated by commas (where column 1 = number2, column1 = number3) and also didn't work. What am I doing wrong? Thanks in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question on Update
Kraer, Joseph [EMAIL PROTECTED] wrote on 11/30/2005 11:58:56 AM: I am trying to update a couple of rows in a table by doing the following: update table set column5 = number1 where column 1 = number2 and column1 = number3 but it is not working. I tried listing the conditions separated by commas (where column 1 = number2, column1 = number3) and also didn't work. What am I doing wrong? Thanks in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp UPDATE statements are evaluated on a row-by-row basis. What you actually told MySQL to do was to change any rows where column1 has BOTH values at the same time. Since one column in a single row cannot possibly have two values at once (not even SET columns because they do not have more than one set of values at a single time) this condition cannot possibly be met and the UPDATE will never happen. Make sense? You said: WHERE column1=number2 AND column1=number3 The AND is a logical comparator meaning that both comparisons must be true for the WHERE clause to be true and your desired UPDATE to occur. What I think you wanted to do is to UPDATE two rows. One row where column1=number2 and the other row where column1=number3. That is an OR situation not an AND situation: WHERE column1=number2 OR column1=number3 Another way to write that condition is with an IN clause : WHERE column1 IN (number2, number3) HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Newbie Question on Update
Hi, In your query you try to update on the condition column 1 = number2 and column1 = number3. I think what you want is column 1 = number2 or column1 = number3 . If you use and it will try and found a record in column which has a value = number2 and at the same time = number3. hth, melanie From: Kraer, Joseph [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Newbie Question on Update Date: Wed, 30 Nov 2005 11:58:56 -0500 I am trying to update a couple of rows in a table by doing the following: update table set column5 = number1 where column 1 = number2 and column1 = number3 but it is not working. I tried listing the conditions separated by commas (where column 1 = number2, column1 = number3) and also didn't work. What am I doing wrong? Thanks in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Question on Update
Thank you, Shawn, for understanding what I meant and for explaining the issue so clearly. I apologize to all others if I wasn't clear enough, but, yes, I wanted to update two separate rows. Now, I understand why an OR is needed; I'll study the other option too. Thank you, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 30, 2005 12:13 PM To: Kraer, Joseph Cc: mysql@lists.mysql.com Subject: Re: Newbie Question on Update Kraer, Joseph [EMAIL PROTECTED] wrote on 11/30/2005 11:58:56 AM: I am trying to update a couple of rows in a table by doing the following: update table set column5 = number1 where column 1 = number2 and column1 = number3 but it is not working. I tried listing the conditions separated by commas (where column 1 = number2, column1 = number3) and also didn't work. What am I doing wrong? Thanks in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp UPDATE statements are evaluated on a row-by-row basis. What you actually told MySQL to do was to change any rows where column1 has BOTH values at the same time. Since one column in a single row cannot possibly have two values at once (not even SET columns because they do not have more than one set of values at a single time) this condition cannot possibly be met and the UPDATE will never happen. Make sense? You said: WHERE column1=number2 AND column1=number3 The AND is a logical comparator meaning that both comparisons must be true for the WHERE clause to be true and your desired UPDATE to occur. What I think you wanted to do is to UPDATE two rows. One row where column1=number2 and the other row where column1=number3. That is an OR situation not an AND situation: WHERE column1=number2 OR column1=number3 Another way to write that condition is with an IN clause : WHERE column1 IN (number2, number3) HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Newbie question: UPDATE
Hello. I'm not a PHPMyAdmin guru, but at least LOAD DATA LOCAL feature (if it is present in PHPMyAdmin) can be disabled due to some security reasons. See: http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html Joe Herman wrote: To those thinking of answering this question, There is additional information. For some reason, I found out that the version of PHPMyAdmin at my web hosting provider was missing the Insert data from a text file into the table feature found at the bottom of the page after clicking on the name of the table. I thought that the reason for this was because they were using an older version of PHPMyAdmin, so he was kind enough to download and install the recent stable version into a folder under my hosting account via ftp. He told me this copy was for me alone to use, since he could not upgrade it for the entire system. After I hung up with him, I launched the version of PHPMyAdmin he installed for me and went to look for the feature. Unfortunately, the feature was still not there! Is this feature special, an add on to PHPMyAdmin? Or should it be there with the standard installation? If it is special, where can it be found? Thank you for your help. Joe. -Original Message- From: Joe Herman [mailto:[EMAIL PROTECTED] Sent: Sunday, November 27, 2005 12:21 AM To: mysql@lists.mysql.com Subject: Newbie question: How to import data into a table? Hello folks, What is the best way to import an excel spreadsheet, or text file for that matter into a table? Is there a way to do this with PHPMyAdmin? Thank you so much for your kind help. Joe -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question on Update
Joseph update table set column5 = number1 where column 1 = number2 and column1 = number3 but it is not working. I tried listing the conditions separated by commas (where column 1 = number2, column1 = number3) and also didn't work. What am I doing wrong? No commas in the WHERE clause. WHERE ... AND ... ' 'Where column 1 = ...' is wrong. If by 'column 1' you mean 'column1', your condition can hold only when number2=number3, in which case the 'and' clause is apparently superfluous. Otherwise, you'll have to tell us what you mean by not working. PB - Kraer, Joseph wrote: I am trying to update a couple of rows in a table by doing the following: update table set column5 = number1 where column 1 = number2 and column1 = number3 but it is not working. I tried listing the conditions separated by commas (where column 1 = number2, column1 = number3) and also didn't work. What am I doing wrong? Thanks in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.10/189 - Release Date: 11/30/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question: UPDATE
Hello. I'm not PHPMyAdmin guru, but at least LOAD DATA LOCAL feature (if it is present in PHPMyAdmin) can be disabled due to some security reasons. See: http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html Joe Herman wrote: To those thinking of answering this question, There is additional information. For some reason, I found out that the version of PHPMyAdmin at my web hosting provider was missing the Insert data from a text file into the table feature found at the bottom of the page after clicking on the name of the table. I thought that the reason for this was because they were using an older version of PHPMyAdmin, so he was kind enough to download and install the recent stable version into a folder under my hosting account via ftp. He told me this copy was for me alone to use, since he could not upgrade it for the entire system. After I hung up with him, I launched the version of PHPMyAdmin he installed for me and went to look for the feature. Unfortunately, the feature was still not there! Is this feature special, an add on to PHPMyAdmin? Or should it be there with the standard installation? If it is special, where can it be found? Thank you for your help. Joe. -Original Message- From: Joe Herman [mailto:[EMAIL PROTECTED] Sent: Sunday, November 27, 2005 12:21 AM To: mysql@lists.mysql.com Subject: Newbie question: How to import data into a table? Hello folks, What is the best way to import an excel spreadsheet, or text file for that matter into a table? Is there a way to do this with PHPMyAdmin? Thank you so much for your kind help. Joe -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question: UPDATE
To those thinking of answering this question, There is additional information. For some reason, I found out that the version of PHPMyAdmin at my web hosting provider was missing the Insert data from a text file into the table feature found at the bottom of the page after clicking on the name of the table. I thought that the reason for this was because they were using an older version of PHPMyAdmin, so he was kind enough to download and install the recent stable version into a folder under my hosting account via ftp. He told me this copy was for me alone to use, since he could not upgrade it for the entire system. After I hung up with him, I launched the version of PHPMyAdmin he installed for me and went to look for the feature. Unfortunately, the feature was still not there! Is this feature special, an add on to PHPMyAdmin? Or should it be there with the standard installation? If it is special, where can it be found? Thank you for your help. Joe. -Original Message- From: Joe Herman [mailto:[EMAIL PROTECTED] Sent: Sunday, November 27, 2005 12:21 AM To: mysql@lists.mysql.com Subject: Newbie question: How to import data into a table? Hello folks, What is the best way to import an excel spreadsheet, or text file for that matter into a table? Is there a way to do this with PHPMyAdmin? Thank you so much for your kind help. Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question: How to import data into a table?
Hello folks, What is the best way to import an excel spreadsheet, or text file for that matter into a table? Is there a way to do this with PHPMyAdmin? Thank you so much for your kind help. Joe
Re: Newbie Question
Jeffrey G. Ubalde wrote: Good day list! I would just like to ask a somewhat not so intelligent question. What is the downside of indexing almost all of the fields in a table? Is it advisable? Indexes have to be built... so if you did that, for every query that alters the data many indexes will have to be written. A lot of extra overhead if they will never be used. Look to see where indexes are needed by the types of queries you are writing. Add a slow query log to my.cnf. This will give you a very good idea of where you might need some indexes. Then trace the queries to make sure the indexes you've created are being used. Cheers, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
I would just like to ask a somewhat not so intelligent question. What is the downside of indexing almost all of the fields in a table? Is it advisable? Indexes have to be built They also consume disk space, so if you're tight on hard drives, they might put you over the top. -- Scott Noyes [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
Just to amplify what Michael has said If you had a table with 50 columns and created a separate index for each of those columns, then you'd probably get some performance benefit from having those indexes. But you'd also have to absorb the following overhead: 1. For each new row that you created, you'd have to add an entry to each of the 50 indexes on the table. 2. For each row that you deleted, you'd have to remove an entry from each of the 50 indexes on the table. 3. For each row that you changed, you'd have to change the index entries on all columns that changed. 4. For each index you build, you'd have to pay for building that index and you'd have to pay for whatever storage that index used. That adds up to a lot of overhead, likely far more than the benefit you got by indexing the columns in the first place. Rather than putting indexes on every column database designers tend to put them in the following places: 1. A unique index on the primary key (which is required on every foreign key to enable Referential Integrity). 2. A unique or non-unique index (as appropriate) on each foreign key to help performance of Referential Integrity and joins, which are frequently on foreign keys. 3. A unique or non-unique index (as appropriate) on any other columns where query performance is critical. 4. A non-unique index on the column (or combination of columns) that you want as your clustering key. (The clustering key governs the physical sequence in which data is stored.) [NOTE: I mostly use DB2 and clustering is a big deal there but I'm not sure if it works the same way in MySQL.] Rhino - Original Message - From: Michael J. Pawlowsky [EMAIL PROTECTED] To: Jeffrey G. Ubalde [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, November 14, 2005 8:19 PM Subject: Re: Newbie Question Jeffrey G. Ubalde wrote: Good day list! I would just like to ask a somewhat not so intelligent question. What is the downside of indexing almost all of the fields in a table? Is it advisable? Indexes have to be built... so if you did that, for every query that alters the data many indexes will have to be written. A lot of extra overhead if they will never be used. Look to see where indexes are needed by the types of queries you are writing. Add a slow query log to my.cnf. This will give you a very good idea of where you might need some indexes. Then trace the queries to make sure the indexes you've created are being used. Cheers, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question: number of connections
My RaQ4 is throwing the Too many connections error. Now, it is getting hit pretty hard by traffic, but I wonder if my coding might be exacerbating this error. Due to my relatively lazy structure of includes and functions, my pages might have half a dozen or so of these (most are to the same database): $connection = mysql_connect($db_host, $db_username, $db_password); mysql_select_db($db_name, $connection); Is that creating half a dozen connections, even though it's just one web user? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question: number of connections
In one case I do need to jump back and forth between databases that are on different physical servers. What's the most efficient way to handle this? On Aug 2, 2005, at 11:14 AM, Devananda wrote: Brian Dunning wrote: My RaQ4 is throwing the Too many connections error. Now, it is getting hit pretty hard by traffic, but I wonder if my coding might be exacerbating this error. Due to my relatively lazy structure of includes and functions, my pages might have half a dozen or so of these (most are to the same database): $connection = mysql_connect($db_host, $db_username, $db_password); mysql_select_db($db_name, $connection); Is that creating half a dozen connections, even though it's just one web user? Yep. Each call to mysql_connect creates a new connection to the database. If you need to connect to multiple database servers then you obviously need multiple connections, but if you are only connecting to one database server, then you should really reuse a single connection. FYI, the process of creating a new connection requires substantially more processing time than executing a simple query (ie, mysql_select_db). One common practice is to have a single include file which creates the database connection and instantiates a global variable with the handle to that connection. Then, all pages which include that file merely use that handle to communicate with the database. Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question: number of connections
Brian Dunning wrote: In one case I do need to jump back and forth between databases that are on different physical servers. What's the most efficient way to handle this? On Aug 2, 2005, at 11:14 AM, Devananda wrote: Brian Dunning wrote: My RaQ4 is throwing the Too many connections error. Now, it is getting hit pretty hard by traffic, but I wonder if my coding might be exacerbating this error. Due to my relatively lazy structure of includes and functions, my pages might have half a dozen or so of these (most are to the same database): $connection = mysql_connect($db_host, $db_username, $db_password); mysql_select_db($db_name, $connection); Is that creating half a dozen connections, even though it's just one web user? Yep. Each call to mysql_connect creates a new connection to the database. If you need to connect to multiple database servers then you obviously need multiple connections, but if you are only connecting to one database server, then you should really reuse a single connection. FYI, the process of creating a new connection requires substantially more processing time than executing a simple query (ie, mysql_select_db). One common practice is to have a single include file which creates the database connection and instantiates a global variable with the handle to that connection. Then, all pages which include that file merely use that handle to communicate with the database. Regards, Devananda vdv Create two connections at the beginning of your script/page, then query each as you need to. For example, $connA = mysql_connect(A.server.com,...); mysql_select_db($db_name,$connA); $connB = mysql_connect(B.server.com,...); mysql_select_db($another_db_name,$connB); $hdl_some_query_on_A = mysql_query(SELECT something FROM sometable, $connA); # this query goes to A.server.com $hdl_some_query_on_B = mysql_query(SELECT some_other_thing FROM another_table,$connB); # this one goes to B.server.com and so on. By storing the resource returned from mysql_connect, you can tell mysql_query which connection to use, and thus utilize multiple connections (but only create each connection once). Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie question create table
hi seniors, I'm trying to create a table, here my table properties, create table user ( UserID int primary, Password varchar (20), User_stats int multi ); i'm still confuse in User_stats properti's that is multi, what really use 'multi' is ? Thx before the guide, Aji __ Yahoo! Messenger Show us what our next emoticon should look like. Join the fun. http://www.advision.webevents.yahoo.com/emoticontest -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question about test database...
Okay, so MySQL is now installed and running. It looks like a database named test was created with no tables. Is this just used for installation purposes? Can it now be deleted or is there a reason to keep it? Or does it have some magical purpose like the mysql database? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question about test database...
on one level, the test database is there just have something to log into after intall to prove it's working.. some init.d startup scripts (I'm thinking RedHat here) try to log into the database as an authenticated user after each start up and when doing a 'status' call just to make sure it's alive. some scrupulously paranoid sysadmins prefer to delete it the moment the system comes up, other leave it there for convenience.. On Mon, 2005-03-14 at 19:52, Jeff Justice wrote: Okay, so MySQL is now installed and running. It looks like a database named test was created with no tables. Is this just used for installation purposes? Can it now be deleted or is there a reason to keep it? Or does it have some magical purpose like the mysql database? Jeff -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie question
This may be more of a PHP question than a MySQL question but here goes: I have a small database with two tables in it. It's a database of Botanical Gardens in the US. Table 1 contains the botanical gardens and has 8 fields: (I'm abbreviating for brevity and clarity): id, name, address, town, state_id, zip, phone, url Table 2 contains the states referred to in the 'state_id' field and itself has 3 fields id, abbreviation, name So, 'id' in the 'state' table is linked to the 'gardens' table via that table's 'state_id' field. Now, in pulling data out of the database to display on a web page I have all of my connection stuff working and the query of 'select * from gardens' along with this php code: td align=center?php echo $row_Recordset1['botgard_name']; ?/td td align=center?php echo $row_Recordset1['botgard_address']; ?/td td align=center?php echo $row_Recordset1['botgard_town']; ?/td td align=center?php echo $row_Recordset1['state_id']; ?/td td align=center?php echo $row_Recordset1['botgard_zip']; ?/td td align=center?php echo $row_Recordset1['botgard_phone']; ?/td td align=center?php echo $row_Recordset1['botgard_url']; ?/td works fine, pulling records out of the 'gardens' table and displaying it on a web page, but, of course, I'm seeing the 'state_id' instead of either the 'state_abbreviation' or the 'state_name'. I am at a complete and total loss as to how to edit my code at this point so that, before displaying anything, i can grab either 'state_abbr' or 'state_name' from the states table, properly associated with the 'state_id' and display the actual state abbreviation or state name in my web page. I'm so close, yet so far. I know if I can be shown once how this works, I'll be able to apply the solution again in the future. Thanks in advance for any help with this. Sincerely, Joshua -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie question
You need to do a join on the tables, Simplest way is Select * from gardens a, state b where a.state_id = b.id Assuming id in the state table is actually what your planning on joining on. Try to do it on the mysql command line before doing in code to make sure you actually have the data you need. -Original Message- From: jsf [mailto:[EMAIL PROTECTED] Sent: Wednesday, 23 February 2005 3:08 PM To: mysql@lists.mysql.com Subject: newbie question This may be more of a PHP question than a MySQL question but here goes: I have a small database with two tables in it. It's a database of Botanical Gardens in the US. Table 1 contains the botanical gardens and has 8 fields: (I'm abbreviating for brevity and clarity): id, name, address, town, state_id, zip, phone, url Table 2 contains the states referred to in the 'state_id' field and itself has 3 fields id, abbreviation, name So, 'id' in the 'state' table is linked to the 'gardens' table via that table's 'state_id' field. Now, in pulling data out of the database to display on a web page I have all of my connection stuff working and the query of 'select * from gardens' along with this php code: td align=center?php echo $row_Recordset1['botgard_name']; ?/td td align=center?php echo $row_Recordset1['botgard_address']; ?/td td align=center?php echo $row_Recordset1['botgard_town']; ?/td td align=center?php echo $row_Recordset1['state_id']; ?/td td align=center?php echo $row_Recordset1['botgard_zip']; ?/td td align=center?php echo $row_Recordset1['botgard_phone']; ?/td td align=center?php echo $row_Recordset1['botgard_url']; ?/td works fine, pulling records out of the 'gardens' table and displaying it on a web page, but, of course, I'm seeing the 'state_id' instead of either the 'state_abbreviation' or the 'state_name'. I am at a complete and total loss as to how to edit my code at this point so that, before displaying anything, i can grab either 'state_abbr' or 'state_name' from the states table, properly associated with the 'state_id' and display the actual state abbreviation or state name in my web page. I'm so close, yet so far. I know if I can be shown once how this works, I'll be able to apply the solution again in the future. Thanks in advance for any help with this. Sincerely, Joshua -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie question
On Tuesday, February 22, 2005 22:08, jsf wrote: This may be more of a PHP question than a MySQL question but here goes: I have a small database with two tables in it. It's a database of Botanical Gardens in the US. Table 1 contains the botanical gardens and has 8 fields: (I'm abbreviating for brevity and clarity): id, name, address, town, state_id, zip, phone, url Table 2 contains the states referred to in the 'state_id' field and itself has 3 fields id, abbreviation, name So, 'id' in the 'state' table is linked to the 'gardens' table via that table's 'state_id' field. Now, in pulling data out of the database to display on a web page I have all of my connection stuff working and the query of 'select * from gardens' along with this php code: You need join the two tables: SELECT t1.name, t1.address, t1.town, t2.abbreviation, t1.zip, t1.phone, t1.url FROM table1 as t1 INNER JOIN table2 as t2 ON (t1.state_id = t2.id); Obviously you will have to modify this example since I don't have the exact info for your tables (such as names). You can find more info on joins here: http://dev.mysql.com/doc/mysql/en/join.html Also, you can alias the columns as well, to make them easier to reference in your code ie. t1.name as name td align=center?php echo $row_Recordset1['botgard_name']; ?/td td align=center?php echo $row_Recordset1['botgard_address']; ?/td td align=center?php echo $row_Recordset1['botgard_town']; ?/td td align=center?php echo $row_Recordset1['state_id']; ?/td td align=center?php echo $row_Recordset1['botgard_zip']; ?/td td align=center?php echo $row_Recordset1['botgard_phone']; ?/td td align=center?php echo $row_Recordset1['botgard_url']; ?/td works fine, pulling records out of the 'gardens' table and displaying it on a web page, but, of course, I'm seeing the 'state_id' instead of either the 'state_abbreviation' or the 'state_name'. I am at a complete and total loss as to how to edit my code at this point so that, before displaying anything, i can grab either 'state_abbr' or 'state_name' from the states table, properly associated with the 'state_id' and display the actual state abbreviation or state name in my web page. I'm so close, yet so far. I know if I can be shown once how this works, I'll be able to apply the solution again in the future. Thanks in advance for any help with this. Sincerely, Joshua -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy newbie question re: option file and passwords
Hello. The mysql program uses user sebyte and password for user ddj. When mysql starts it calls for load_defaults(), which put the arguments from your config file before the command line options that you specified. And than uses the last given argument. It looks like mysql was invoked with -hlocalhost -uroot -px -hlocalhost -usebyte Sebastian Tennant [EMAIL PROTECTED] wrote: Hi there, For some reason, despite entering my passwords in ~/.my.cnf, I still have to enter a password on the command line to login to mysql. I have tried this with each of the accounts I have created and all return the same error: $ mysql -u sebyte ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) What am I doing wrong? Here are the accounts I have created, and below that is my ~/.my.cnf. I have 'x'ed out my actual passwords in this post but is there something wrong with the syntax I am using. I have verified that the file IS being read each time I attempt to login. Any help much appreciated. TIA Sebastian mysql select host,user,password from user; +---+--+--+ | host | user | password | +---+--+--+ | localhost | root | 4be7c493348ee750 | | localhost | guest| | | localhost | debian-sys-maint | 7985ba067899ea77 | | localhost | sebyte | 5722c7a41e81cbb3 | | localhost | ddj | 7b17b74d22ac2a88 | +---+--+--+ 5 rows in set (0.08 sec) # -*- mode: shell-script -*- # ~/.my.cnf # [mysql] host=localhost user=root password='xx' host=localhost user=sebyte password='x' host=localhost user=ddj password='xxx' [mysqladmin] host=localhost user=root password='xx' -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy newbie question re: option file and passwords
On Fri, 28 Jan 2005 09:47:24 +0200, Gleb Paharenko wrote: Hello. The mysql program uses user sebyte and password for user ddj. When mysql starts it calls for load_defaults(), which put the arguments from your config file before the command line options that you specified. And than uses the last given argument. It looks like mysql was invoked with -hlocalhost -uroot -px -hlocalhost -usebyte I see... Thanks a lot. Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Easy newbie question re: option file and passwords
Hi there, For some reason, despite entering my passwords in ~/.my.cnf, I still have to enter a password on the command line to login to mysql. I have tried this with each of the accounts I have created and all return the same error: $ mysql -u sebyte ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) What am I doing wrong? Here are the accounts I have created, and below that is my ~/.my.cnf. I have 'x'ed out my actual passwords in this post but is there something wrong with the syntax I am using. I have verified that the file IS being read each time I attempt to login. Any help much appreciated. TIA Sebastian mysql select host,user,password from user; +---+--+--+ | host | user | password | +---+--+--+ | localhost | root | 4be7c493348ee750 | | localhost | guest| | | localhost | debian-sys-maint | 7985ba067899ea77 | | localhost | sebyte | 5722c7a41e81cbb3 | | localhost | ddj | 7b17b74d22ac2a88 | +---+--+--+ 5 rows in set (0.08 sec) # -*- mode: shell-script -*- # ~/.my.cnf # [mysql] host=localhost user=root password='xx' host=localhost user=sebyte password='x' host=localhost user=ddj password='xxx' [mysqladmin] host=localhost user=root password='xx' -- CC me by all means but a follow-up will usually do. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie-question: Error when compiling MySQL 4.1.9 with BerkeleyDB 4.3.27
Hi, greetings to the list. I'm in process of compiling MySQL 4.1.9 using BerkeleyDB 4.3.27. When i start make, after configuring i get the following error-message : --snipp-- ha_berkeley.cc:103: error: type specifier omitted for parameter `db_notices' ha_berkeley.cc:103: error: syntax error before `)' token --snipp-- As db_notices is nowhere declared i look around in the sources and find the following in /bdb/build_win32/db.h : --snipp-- typedef enum { DB_NOTICE_LOGFILE_CHANGED } db_notices; --snipp-- So i put it into ha_berkeley.h and it seems to work , but then the next errormessages come up , which haven't such a simple solution : --snipp-- ha_berkeley.cc:139: error: invalid conversion from `void (*)(const char*, char*)' to `void (*)(const DB_ENV*, const char*, const char*)' ha_berkeley.cc:141: error: `set_noticecall' undeclared (first use this function) ha_berkeley.cc:141: error: (Each undeclared identifier is reported only once for each function it appears in.) ha_berkeley.cc:150: error: `DB_VERB_CHKPOINT' undeclared (first use this function) ha_berkeley.cc: In function `int berkeley_commit(THD*, void*)': ha_berkeley.cc:215: error: `txn_commit' undeclared (first use this function) ha_berkeley.cc: In function `int berkeley_rollback(THD*, void*)': ha_berkeley.cc:228: error: `txn_abort' undeclared (first use this function) ha_berkeley.cc: At global scope: ha_berkeley.cc:291: error: type specifier omitted for parameter `db_notices' ha_berkeley.cc:291: error: syntax error before `)' token ha_berkeley.cc: In function `void berkeley_noticecall(...)': ha_berkeley.cc:293: error: `notice' undeclared (first use this function) ha_berkeley.cc:295: error: `DB_NOTICE_LOGFILE_CHANGED' undeclared (first use this function) ha_berkeley.cc: In function `int berkeley_cmp_packed_key(DB*, const DBT*, const DBT*)': ha_berkeley.cc:387: error: 'const struct __db_dbt' has no member named ' app_private' ha_berkeley.cc:387: error: 'const struct __db_dbt' has no member named ' app_private' ha_berkeley.cc: In member function `virtual int ha_berkeley::open(const char*, int, unsigned int)': ha_berkeley.cc:547: error: `txn_begin' undeclared (first use this function .. --snipp (and so on) -- So, does anybody know a solution for this ? My setup : Suse Linux 9.1 BDB 4.3.27(installed in /usr/local/bdb-4.3.9, symlinked to /usr/local/bdb-4.3) MySQL 4.1.9 Here's how i configure (i always create a little script): --snipp-- #!/bin/sh # # configure make template-script # # generated 2005.01.19 19:24,06 by make-mk # # (c) H. Rueter 01/2005 # CPPFLAGS=-I/usr/local/bdb-4.3/include LDFLAGS=-L/usr/local/bdb-4.3/lib export CPPFLAGS LDFLAGS make clean rm config.cache ./configure \ --prefix=/usr/local/mysql-4.1.9 \ --mandir=/usr/man \ --enable-shared \ --enable-static \ --enable-thread-safe-client \ --enable-assembler \ --enable-local-infile \ --with-isam \ --with-pthread \ --with-unix-socket-path=/var/run/mysql.sock \ --with-mysqld-user=mysql \ --with-openssl=/usr \ --with-extra-charsets=all \ --with-berkeley-db=/usr/local/bdb-4.3 \ --with-berkeley-db-includes=/usr/local/bdb-4.3/include \ --with-berkeley-db-libs=/usr/local/bdb-4.3/lib \ make checkinstall --snipp-- Thanks in advance Harry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question: segmentation fault in mysql_init
At 19:32 +0100 12/13/04, Alina Bikowska wrote: Description: When I try to call mysql_init() several times in my program it finishes with segmentation fault. This happens in different places of my program but always in mysql_init(). This is the gdb output: #0 0x007ba6ae in malloc_consolidate () from /lib/tls/libc.so.6 #1 0x007b9e6a in _int_malloc () from /lib/tls/libc.so.6 #2 0x007b925d in malloc () from /lib/tls/libc.so.6 #3 0x001716ae in my_malloc () from /usr/local/lib/mysql/libmysqlclient.so.12 #4 0x0016c648 in mysql_init () from /usr/local/lib/mysql/libmysqlclient.so.12 #5 0x0804dee2 in myconnect () at databaseFunctions.c:14 #6 0x0804dfc0 in give_geneValue_with_key (table=0x8051fe8 \selfRoot_shell\, id=9463) at databaseFunctions.c:37 #7 0x08049a27 in selfProfiler () at library.c:232 #8 0x0804952c in negativeSelection () at library.c:176 #9 0x0804a92d in negativeSelectionSentinel () at library.c:420 #10 0x0804edad in communicator (arg=0x755d00) at communicator.c:60 #11 0x08048cdf in main () at primary.c:169 Here are the functions I use. The first function is only to make a connection to database: MYSQL* myconnect() { MYSQL *my_connection=malloc(sizeof(MYSQL *)); my_connection=mysql_init (NULL); I don't think this solves your problem, but I'm curious: Why do you allocate memory and assign it to my_connection, and then immediately throw that memory away by assigning my_connection the value of mysql_init()? That's a memory leak right there. if(mysql_real_connect (my_connection, \130.225.16.5\, \ala\, \alaSdb\, \ala\, 0, NULL, 0)) { return my_connection; } else { fprintf (stderr, \Connection failed !!!\\n\); if (mysql_errno (my_connection)) { fprintf (stderr, \Connection error %d: %s\\n\, mysql_errno (my_connection), mysql_error (my_connection)); return NULL; } } return NULL; } This function and a couple of others similar are to withdraw some data from database. All of them use myconnection() function; char* give_geneValue_with_key(char *table,int id) { MYSQL* my_connection=myconnect(); MYSQL_RES *result=malloc(sizeof(MYSQL_RES*)); MYSQL_ROW row; char temp[1]; sprintf(temp,\SELECT geneValue FROM %s where id=\'%d\'\,table,id); if(mysql_real_query (my_connection,temp,strlen(temp))) return \0\; if(!(result=mysql_store_result(my_connection))) return \0\; row=mysql_fetch_row(result); mysql_free_result(result); mysql_close(my_connection); return row[0]; } If you have any ideas what can be a problem, please let me know. Alina Binkowska -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question: segmentation fault in mysql_init
Description: When I try to call mysql_init() several times in my program it finishes with segmentation fault. This happens in different places of my program but always in mysql_init(). This is the gdb output: #0 0x007ba6ae in malloc_consolidate () from /lib/tls/libc.so.6 #1 0x007b9e6a in _int_malloc () from /lib/tls/libc.so.6 #2 0x007b925d in malloc () from /lib/tls/libc.so.6 #3 0x001716ae in my_malloc () from /usr/local/lib/mysql/libmysqlclient.so.12 #4 0x0016c648 in mysql_init () from /usr/local/lib/mysql/libmysqlclient.so.12 #5 0x0804dee2 in myconnect () at databaseFunctions.c:14 #6 0x0804dfc0 in give_geneValue_with_key (table=0x8051fe8 \selfRoot_shell\, id=9463) at databaseFunctions.c:37 #7 0x08049a27 in selfProfiler () at library.c:232 #8 0x0804952c in negativeSelection () at library.c:176 #9 0x0804a92d in negativeSelectionSentinel () at library.c:420 #10 0x0804edad in communicator (arg=0x755d00) at communicator.c:60 #11 0x08048cdf in main () at primary.c:169 Here are the functions I use. The first function is only to make a connection to database: MYSQL* myconnect() { MYSQL *my_connection=malloc(sizeof(MYSQL *)); my_connection=mysql_init (NULL); if(mysql_real_connect (my_connection, \130.225.16.5\, \ala\, \alaSdb\, \ala\, 0, NULL, 0)) { return my_connection; } else { fprintf (stderr, \Connection failed !!!\\n\); if (mysql_errno (my_connection)) { fprintf (stderr, \Connection error %d: %s\\n\, mysql_errno (my_connection), mysql_error (my_connection)); return NULL; } } return NULL; } This function and a couple of others similar are to withdraw some data from database. All of them use myconnection() function; char* give_geneValue_with_key(char *table,int id) { MYSQL* my_connection=myconnect(); MYSQL_RES *result=malloc(sizeof(MYSQL_RES*)); MYSQL_ROW row; char temp[1]; sprintf(temp,\SELECT geneValue FROM %s where id=\'%d\'\,table,id); if(mysql_real_query (my_connection,temp,strlen(temp))) return \0\; if(!(result=mysql_store_result(my_connection))) return \0\; row=mysql_fetch_row(result); mysql_free_result(result); mysql_close(my_connection); return row[0]; } If you have any ideas what can be a problem, please let me know. Alina Binkowska -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question about index (why are they not updating?)
Hello. Cardinality of index is updated by running ANALYZE TABLE or myisamchk -a. See: http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html I've created a table with several indexes as follows: $query = CREATE TABLE `data_raw` ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, run_id VARCHAR(20) DEFAULT 'error_internal' NOT NULL, time_run DATETIME DEFAULT '-00-00 00:00:00' NOT NULL, ...clip. PRIMARY KEY (id), INDEX x_run_id (run_id), INDEX x_comp_code (comp_code), INDEX x_time_run (time_run), INDEX x_url (url) ) COMMENT = 'Raw data samples' ; $query executed with php mysql_query. The table is created OK. I add many records to the table with $query = INSERT INTO data_raw( id, run_id, time_run, time_sample, comp_code, url, url_index, err_number, err_desc, err_src ) VALUES( 0, '$run_id', '$time_run', '$ts', '$data[1]', '$data[2]', $data[3], $data[4], '$data[5]', '$data[6]' ); Data is added OK. But, the indexes are not updated! Running myPHPAdmin shows: Indexes: Documentation Keyname Type Cardinality Action Field PRIMARY PRIMARY 12932 Edit Drop id x_run_id INDEX None Edit Drop run_id x_comp_code INDEX None Edit Drop comp_code x_time_run INDEX None Edit Drop time_run x_url INDEX None Edit Drop url Access is slow. If I do an ALTER TABLE ... ADD INDEX ... indexes are updated and subsequent INSERTS update the indexes. I'm sure I'm doing something simple wrong but need a clue as to what? Any help much appreciated. Richard Bell [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question about index (why are they not updating?)
I've created a table with several indexes as follows: $query = CREATE TABLE `data_raw` ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, run_id VARCHAR(20) DEFAULT 'error_internal' NOT NULL, time_run DATETIME DEFAULT '-00-00 00:00:00' NOT NULL, ...clip. PRIMARY KEY (id), INDEX x_run_id (run_id), INDEX x_comp_code (comp_code), INDEX x_time_run (time_run), INDEX x_url (url) ) COMMENT = 'Raw data samples' ; $query executed with php mysql_query. The table is created OK. I add many records to the table with $query = INSERT INTO data_raw( id, run_id, time_run, time_sample, comp_code, url, url_index, err_number, err_desc, err_src ) VALUES( 0, '$run_id', '$time_run', '$ts', '$data[1]', '$data[2]', $data[3], $data[4], '$data[5]', '$data[6]' ); Data is added OK. But, the indexes are not updated! Running myPHPAdmin shows: Indexes: Documentation Keyname Type Cardinality Action Field PRIMARY PRIMARY 12932 Edit Drop id x_run_id INDEX None Edit Drop run_id x_comp_code INDEX None Edit Drop comp_code x_time_run INDEX None Edit Drop time_run x_url INDEX None Edit Drop url Access is slow. If I do an ALTER TABLE ... ADD INDEX ... indexes are updated and subsequent INSERTS update the indexes. I'm sure I'm doing something simple wrong but need a clue as to what? Any help much appreciated.
Newbie question about web users
Hello all, Basic question about creating a database that will be accessed by many users. Do I create one web user account with no password in the mysql.user table or add each user to the mysql.user table as they join up to my site? (Lots of books and documentation says how to create users, the privelege system etc, but none seem to address this basic structural question - presumably because it's obvious to anyone who already knows). I'm planning a mysql and PHP site. Taking a mundane example - lets say a second hand bookstore has a site which lets users search through a list of books, and lets subscribed users select favorite books, 0-n number of books, in a table fav_books. Each fav_book tuple is then related to at least one user. When the favourite book comes into stock the store sends a message to the user to let them know it's available. (I said it was a mundane example!). If the bookstore created a default user then I suppose anyone can alter anyone elses data in the fav_book table (undesirable) ? Would they create a table of authenticated_users for example instead of putting them in the mysql.user table, then use PHP to retain a record of which user is logged in at that time and so which tuples in the fav_book table they can change? Alternatively if each user is listed in mysql.user table they would all get the same priveleges and the bookstore would have to create a seperate fav_book table for each user to contain just their data. With as many tables as users it would be surely impossible to run background processes which check the new stock against books people are looking for? As I say it's a basic question about web use of a database - hope the answer isn't too obvious. Thanks for any help matt - ALL-NEW Yahoo! Messenger - all new features - even more fun!
Re: Newbie question about web users
It's not a simple question at all. Your users actually DON'T talk to the database, your PHP application does. Your users converse with your PHP code. Your web server will have one layer of authentication just to allow the user to view the pages your site is composed of. Assume for a second that anyone can see any page on your site (no security), you can still set up application-level security by requiring your users to enter more information once they get to your site (like a login screen). Where you store your login information is up to you but since you already have a MySQL database at your disposal you can put it in there. Since your application needs to be able to verify that a particular username/password combination is valid running a query against the database, it doesn't make it try to login with the user's credentials in order to verify those credentials. When I am designing web-based applications I usually need only 2 logins. One will be used by the application for read-only access. The second will be used only when the application needs to update something. Notice I was talking about the application doing those things. Most users won't get direct access to your applicaiton data (usually) as it's only there to run the application. Now, within a MySQL database can be a set of tables (that you design) that contain everything your application needs to know about each user's account information, login permissions, and any other rights they have within the application (among other things). This will be information used by your website to decide if a user has the correct privileges to perform some kind of action. Your application will use one of *its* logins to connect to that database in order to add, edit, or delete rows from those tables. The users don't interact with this data. So, user security basically boils down to two layers. The first layer is determined by the security settings on the web server. The second is managed by your application code. By keeping user security and application security separate, you improve the chances that your users will be unable to read your databases directly. Keep reading and researching. There are a lot of things to worry about when you start locking-down a web-based application. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matthew Benton [EMAIL PROTECTED] wrote on 11/04/2004 03:53:57 PM: Hello all, Basic question about creating a database that will be accessed by many users. Do I create one web user account with no password in the mysql.user table or add each user to the mysql.user table as they join up to my site? (Lots of books and documentation says how to create users, the privelege system etc, but none seem to address this basic structural question - presumably because it's obvious to anyone who already knows). I'm planning a mysql and PHP site. Taking a mundane example - lets say a second hand bookstore has a site which lets users search through a list of books, and lets subscribed users select favorite books, 0-n number of books, in a table fav_books. Each fav_book tuple is then related to at least one user. When the favourite book comes into stock the store sends a message to the user to let them know it's available. (I said it was a mundane example!). If the bookstore created a default user then I suppose anyone can alter anyone elses data in the fav_book table (undesirable) ? Would they create a table of authenticated_users for example instead of putting them in the mysql.user table, then use PHP to retain a record of which user is logged in at that time and so which tuples in the fav_book table they can change? Alternatively if each user is listed in mysql.user table they would all get the same priveleges and the bookstore would have to create a seperate fav_book table for each user to contain just their data. With as many tables as users it would be surely impossible to run background processes which check the new stock against books people are looking for? As I say it's a basic question about web use of a database - hope the answer isn't too obvious. Thanks for any help matt - ALL-NEW Yahoo! Messenger - all new features - even more fun!
Re: Newbie question about web users
The answer is you create one user for the PHP (webserver) process to use. Then you keep track of user permissions in your application code. This probably involves creating your own user table within your own database and storing users of your site there. Then in the other tables you associate items with users, like in your fav_book table one of the fields would be user_id, and would allow you to figure out which user the favorite entry belonged to. You do not create mysql users to represent application users however as the users don't access the database, the webserver does. On Thu, 2004-11-04 at 20:53 +, Matthew Benton wrote: Hello all, Basic question about creating a database that will be accessed by many users. Do I create one web user account with no password in the mysql.user table or add each user to the mysql.user table as they join up to my site? (Lots of books and documentation says how to create users, the privelege system etc, but none seem to address this basic structural question - presumably because it's obvious to anyone who already knows). I'm planning a mysql and PHP site. Taking a mundane example - lets say a second hand bookstore has a site which lets users search through a list of books, and lets subscribed users select favorite books, 0-n number of books, in a table fav_books. Each fav_book tuple is then related to at least one user. When the favourite book comes into stock the store sends a message to the user to let them know it's available. (I said it was a mundane example!). If the bookstore created a default user then I suppose anyone can alter anyone elses data in the fav_book table (undesirable) ? Would they create a table of authenticated_users for example instead of putting them in the mysql.user table, then use PHP to retain a record of which user is logged in at that time and so which tuples in the fav_book table they can change? Alternatively if each user is listed in mysql.user table they would all get the same priveleges and the bookstore would have to create a seperate fav_book table for each user to contain just their data. With as many tables as users it would be surely impossible to run background processes which check the new stock against books people are looking for? As I say it's a basic question about web use of a database - hope the answer isn't too obvious. Thanks for any help matt - ALL-NEW Yahoo! Messenger - all new features - even more fun! -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question on multiple tables
Hi. You may read: http://dev.mysql.com/doc/mysql/en/JOIN.html http://dev.mysql.com/doc/mysql/en/SELECT.html There is a good description. Searching in more than two tables (3 in my example) may be done in such way: select t1.* from t1,t2,t3 where t1.a=t2.a and t1.b=t3.b But using left or inner joins depends on results you are expecting. Steve Grosz [EMAIL PROTECTED] wrote: I am just now getting the hang of searching through 2 tables, and getting the corresponding information using a pri foreign key (ID1=ID1) kinda thing. How is this done with more than 2 tables? I had asked on a different forum, but I got some funky select statement using a left inner join, and lots of other statements that didn't make a lot of sense to me. Is that the way it would need to be done? A inner join? If so why? Thanks! Steve -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question on multiple tables
I am just now getting the hang of searching through 2 tables, and getting the corresponding information using a pri foreign key (ID1=ID1) kinda thing. How is this done with more than 2 tables? I had asked on a different forum, but I got some funky select statement using a left inner join, and lots of other statements that didn't make a lot of sense to me. Is that the way it would need to be done? A inner join? If so why? Thanks! Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
**newbie question** renaming a database
using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in order to do something as simple as this. is there another way? as mysql user 'root' i've tried: mysql rename database old_db to new_db thnx, cheers! -a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **newbie question** renaming a database
Carolina wrote: using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in order to do something as simple as this. is there another way? as mysql user 'root' i've tried: mysql rename database old_db to new_db thnx, cheers! -a You may think this is simple, but you have to make sure all users are out of the database, and all records are flushed before it could be renamed. It is much safer to shutdown the server and rename the directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **newbie question** renaming a database
At 11:16 -0500 10/7/04, gerald_clark wrote: Carolina wrote: using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in order to do something as simple as this. is there another way? as mysql user 'root' i've tried: mysql rename database old_db to new_db thnx, cheers! -a You may think this is simple, but you have to make sure all users are out of the database, and all records are flushed before it could be renamed. It is much safer to shutdown the server and rename the directory. It's simple conceptually, but complex to actually implment. In addition to the issues gerald mentions, remember that if you have InnoDB tables, they aren't actually stored in the database directory unless you're using individual tablespaces -- and even then, there is an entry for them in the InnoDB data dictionary that is stored in the shared tablespace. And those entries include the database name. If you rename the database directory, those entries become invalid. Also, if you have foreign key relationship, there are similar difficulties. Might be better to create a new database and then RENAME TABLE each table from the original database into the new database. Then drop the original database. The strategy of shutting down the server, renaming the database directory, and restartingg the server does work fine _if_ your database contains only MyISAM (or ISAM) tables, though. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question - Input limitations on mysql client?
As I mentioned in my post, I did wind up using a perl script, and I will continue down that path in the future. I was mainly wondering about limits to the mysql client for quick-n-dirty testing (as someone who is learning MySQL, as opposed to developing for a production environment). Thanks for pointing out the mysqlimport tool - that sounds like what I should really be using instead of cut-n-paste... Ted At 08:08 PM 9/29/2004, Andrew Kreps wrote: On Wed, 29 Sep 2004 10:05:29 -0400, Ted Byrne [EMAIL PROTECTED] wrote: Is the apparent line length limit a restriction imposed by the client? (And does it apply if you are piping or redirecting output from another process or a file?) It sounds like you should either be using a scripting language (like Perl or PHP), or using the command line tool mysqlimport or LOAD DATA INFILE from the mysql client. Any of those methods should allow you to insert long rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question - Input limitations on mysql client?
Greetings, I posted this to the MySQL Newbie forum, but have not received a response. Any feedback would be appreciated. -tb Environment: mysql server v4.0.13 (Win2K) mysql client v5.0.0.0(Win2K) In attempting to (manually) insert records with some rather lengthy strings, I pasted the insert statements into the client window; I wound up getting mismatched quotes, although the original statements that I copied were properly quoted. After taking a closer look at what was happening, it appeared that the lines being pasted in were getting truncated at about 256 characters. I was able to eliminate most of the errors by splitting the insert statements across multiple lines (but wound up with newline characters mid-string in some of the values). Eventually, I wound up including the inserts in a perl script, which was successful Is the apparent line length limit a restriction imposed by the client? (And does it apply if you are piping or redirecting output from another process or a file?) Thanks in advance, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question - Input limitations on mysql client?
On Wed, 29 Sep 2004 10:05:29 -0400, Ted Byrne [EMAIL PROTECTED] wrote: Is the apparent line length limit a restriction imposed by the client? (And does it apply if you are piping or redirecting output from another process or a file?) It sounds like you should either be using a scripting language (like Perl or PHP), or using the command line tool mysqlimport or LOAD DATA INFILE from the mysql client. Any of those methods should allow you to insert long rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question about blob data types ie. Storing .xls in blob
Is it possible to store an excel file into a blob field in mysql? If so how do I go about importing the file into the blob field, and get it back out into an .xls file? Thanks in advnace Jack Taffar AOG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question about blob data types ie. Storing .xls in blob
Good binary storage article w/ sample code: http://php.dreamwerx.net/forums/viewtopic.php?t=6 On Mon, 27 Sep 2004 16:47:37 -0500, Jack Taffar [EMAIL PROTECTED] wrote: Is it possible to store an excel file into a blob field in mysql? If so how do I go about importing the file into the blob field, and get it back out into an .xls file? Thanks in advnace Jack Taffar AOG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie question on scrolling through a table one record at a time
Many thanks for your reply Tom. I will read it more closely over the week-end, but wanted to say thanks straight away. Kerry -Original Message- From: Tom Horstmann [mailto:[EMAIL PROTECTED] Sent: 20 August 2004 15:52 To: 'Kerry Frater'; 'MySQL List' Subject: RE: newbie question on scrolling through a table one record at a time Hi Kerry, The problem is more fundamental with the scrolling through the records/rows of Master. [..] It is not sensible to allow 200 million pieces of data to be transferred to the Delphi PC to build a local Dataset to scroll through. [..] Getting the first row is easy select * from Master order by MasterRef limit 1; It is, but LIMIT mostly is executed after getting all records meeting the WHERE-condition. That might be quite slow. but getting the next record isn't as straight forward. Mainly because I have no idea of what the next value of MasterRef is. All I know is that I want to get the next row in sequence. None of the papers I have or have seen addresses this issue. Either this concept is not required in SQL programming or it is so obvious that it doesn't need explaining. Either way I can't see the wood for the trees. If MasterRef is a unique value column then the next record would be: select * from Master order by MasterRef limit 1 where MasterRef MyCurrentMasterRefValue; This simply raises questions 1) how to get the previous row (presume you use the DESCENDING keyword of the table, 2) how to test for Begining and End of Table and 3) what to do if the column being ordered on is not unique. I ran through the same problem the last days (still). This is how i did it: At first you need a unique key (auto-increment). I name it id. If you have records that are non-unique by the column you want to sort them, try to add other columns to sort on to get them as unique as you can. Having non-unique records isn't a problem as long there are not many beeing equal. How to move through records: Starting with a value of '', always SELECT the record having a higher value in that columns you sorted on than the last one. As you said, that's all, if there all records are unique. You will need LIMIT to get through equal records. It's quite easy having an example: record key 1a 2a 3b 4b 5c Get the first record (assuming your id starts with 0): SELECT key FROM table WHERE key='' AND id-1 ORDER BY key,id LIMIT 0,1 Store these: old_key = key old_id = id Get the next record: old_key = SELECT key FROM table WHERE key=old_key AND idold_id ORDER BY key,id LIMIT 1,1 The LIMIT has to be 1 here to get the second record. As long as key stays equal to old_key, increase the limit by one for each record fetched. When getting a new value for key, set the limit to 1 again. You need to find a good relation between the amount of equal records and the columns you use to sort and move on. 500 records having the same key will make things slow, but using a long WHERE- clause will do, too. I tested this for upto 3 records. If there is an index on all columns used in the SELECTs, speed seems to be stable upto that size. hth, TomH -- PROSOFT EDV-Loesungen GmbH Co. KGphone: +49 941 / 78 88 7 - 121 Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0 Geschaeftsfuehrer: Axel-Wilhelm Wegmann [EMAIL PROTECTED] AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie question on scrolling through a table one record at a time
Hi all, I don't think this is the right list for the question but I am hoping someone in the list will be able to point me in the right direction. I am testing the use of Delphi with MySQL (using Micoolap's DAC) to access and manipulate a number of tables in a databse. Some of the more complex structures I want to do are quite clear on how to implement them with many papers and books published. My problem is the technique on implementing a far more fundamental issue. Let us say I have a couple of tables with a large number of rows (Master and Detail) with a common (indexed for performance) column MasterRef. Getting the rows from table Detail is straight forward by using a Query select * from Detail where Master.MasterRef = Detail.MasterRef The problem is more fundamental with the scrolling through the records/rows of Master. Reading previously posted information, it comes to light that if I open a table to scroll through using an application navigator then the app creates and uses a local dataset. Not a big issue if the database is local, on a high speed connection, or has a relatively small number of rows. But what if Master has 1 million rows with 200 columns. It is not sensible to allow 200 million pieces of data to be transferred to the Delphi PC to build a local Dataset to scroll through. I note there is the concept of LIMIT. This looks good until I try to see how to implement its usage in the real world. The concept of creating an app that only works on one (or a small number of rows) at a time is eluding me at the moment. Getting the first row is easy select * from Master order by MasterRef limit 1; but getting the next record isn't as straight forward. Mainly because I have no idea of what the next value of MasterRef is. All I know is that I want to get the next row in sequence. None of the papers I have or have seen addresses this issue. Either this concept is not required in SQL programming or it is so obvious that it doesn't need explaining. Either way I can't see the wood for the trees. If MasterRef is a unique value column then the next record would be: select * from Master order by MasterRef limit 1 where MasterRef MyCurrentMasterRefValue; This simply raises questions 1) how to get the previous row (presume you use the DESCENDING keyword of the table, 2) how to test for Begining and End of Table and 3) what to do if the column being ordered on is not unique. Are there any known papers, documents, references, books etc that go through these issues. Or can someone tell me that the posts I have been reading are no longer applicable and that when I program using Table components that it doesn't download a complete large dataset and that I only get one row at a time which takes away the concern, and the need to manage the data scroll directly. JOIN is not an option because in my project one form can have up to 9 DETAIL tables showing with the Master table. I am at the point where I have done a lot of reading and now want to look at the reality of implementation. Which means I have a little knowledge which is a dangerous thing. I want to change that status. Many thanks Kerry
RE: newbie question on scrolling through a table one record at a time
Hi Kerry, The problem is more fundamental with the scrolling through the records/rows of Master. [..] It is not sensible to allow 200 million pieces of data to be transferred to the Delphi PC to build a local Dataset to scroll through. [..] Getting the first row is easy select * from Master order by MasterRef limit 1; It is, but LIMIT mostly is executed after getting all records meeting the WHERE-condition. That might be quite slow. but getting the next record isn't as straight forward. Mainly because I have no idea of what the next value of MasterRef is. All I know is that I want to get the next row in sequence. None of the papers I have or have seen addresses this issue. Either this concept is not required in SQL programming or it is so obvious that it doesn't need explaining. Either way I can't see the wood for the trees. If MasterRef is a unique value column then the next record would be: select * from Master order by MasterRef limit 1 where MasterRef MyCurrentMasterRefValue; This simply raises questions 1) how to get the previous row (presume you use the DESCENDING keyword of the table, 2) how to test for Begining and End of Table and 3) what to do if the column being ordered on is not unique. I ran through the same problem the last days (still). This is how i did it: At first you need a unique key (auto-increment). I name it id. If you have records that are non-unique by the column you want to sort them, try to add other columns to sort on to get them as unique as you can. Having non-unique records isn't a problem as long there are not many beeing equal. How to move through records: Starting with a value of '', always SELECT the record having a higher value in that columns you sorted on than the last one. As you said, that's all, if there all records are unique. You will need LIMIT to get through equal records. It's quite easy having an example: record key 1a 2a 3b 4b 5c Get the first record (assuming your id starts with 0): SELECT key FROM table WHERE key='' AND id-1 ORDER BY key,id LIMIT 0,1 Store these: old_key = key old_id = id Get the next record: old_key = SELECT key FROM table WHERE key=old_key AND idold_id ORDER BY key,id LIMIT 1,1 The LIMIT has to be 1 here to get the second record. As long as key stays equal to old_key, increase the limit by one for each record fetched. When getting a new value for key, set the limit to 1 again. You need to find a good relation between the amount of equal records and the columns you use to sort and move on. 500 records having the same key will make things slow, but using a long WHERE- clause will do, too. I tested this for upto 3 records. If there is an index on all columns used in the SELECTs, speed seems to be stable upto that size. hth, TomH -- PROSOFT EDV-Loesungen GmbH Co. KGphone: +49 941 / 78 88 7 - 121 Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0 Geschaeftsfuehrer: Axel-Wilhelm Wegmann [EMAIL PROTECTED] AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie question: subareas
Just been using mysql for a couple of days, so this is possibly old ground, but i have searched and can't find any solution. i'm sure it must have come up before. The project is to allow resources to be shared amongst community groups throughout uk. there are a number of attributes that can be searched on, but the one i'm having difficulty with is geographical area. areas can also be subareas of other areas. for instance there could be areas england, wales, newtown, london, lambeth, southwark so if someone searches england then intuitively the subareas should be searched as well... i thought of a possible method, but its very weird and i'd appreciate comments on it or suggestions for different ways. area_id varchar(6); england - A wales - B newtown - BA london - AA lambeth - AAA southwark - AAB and then searches could be as precise as required ... another option, from a friend more experienced than me is: have extra columns for country/county/borough/town/street/etc your front end for querying would eitehr present the options/boxes for searching within the country/town etc. or you could just present one search box, which takes the search criterion, and checks each of the different columns in turn, starting with the largest geographical type, the country. have aggregated indexes (country+country+town) wont get you anything, so dont bother with that. so to summise create table locations ( area_id serial, country char(16), country varchar(64), borough varchar(64), town varchar(64) ); the problem wit that table layout, is you have no way of checking if for twwo entries with different spelling/case. e.g. London/london. the data values arent strongly checked. cheers in advance. = -- ae I give mankind no more than a chance in a thousand. But I would not be human if I did not place my stake on this one chance. Albert Camus http://www.sei.ukshells.co.uk/ ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]