Re: Select Unique?
Michael, Thanks so much for the query. As I am surmising from your email, the LEFT JOIN is the better way to go for performance. If you have any reason to think I should go with the subquery, let me know! Thanks again, John On Dec 12, 2005, at 9:57 AM, Michael Stassen wrote: Rhino wrote: - Original Message - From: John Mistler [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, December 12, 2005 12:34 AM Subject: Select Unique? I have two tables 'table1', 'table2' with a matching column 'column1'. How can I return all rows from table2 where the entry for table2.column1 does not match any entries in table1.column1? SELECT * FROM table2 WHERE table2.column1 table1.column1 returns all the rows, rather than the unique rows in table2 ... Any ideas? SELECT * FROM table2 where table2.column1 not in (select distinct column1 from table1 where column1 not null) That should be where column1 IS NOT NULL). The 'distinct' in the subquery is not strictly necessary but should help performance. The WHERE clause in the subquery is often omitted but really shouldn't be. Rhino If you're interested in performance, you probably shouldn't use a subquery. If you put EXPLAIN in front, you'll see that mysql labels this a DEPENDENT SUBQUERY, meaning it will rerun the subquery for each row in the outer query. The optimizer *should* be smart enough to run the inner query once, then compare rows to that list using the index, but it isn't. As a test, I made a 25 row table and a copy missing 3 of those rows. The subquery version took twice as long to execute (.12 sec) as the left join version (.06 sec). The larger the tables involved, the larger the difference is likely to be. Michael (Test run on my iBook G4, OS X 10.3.9, mysql 4.1.15.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import Table?
Sorry to bother you once more on this David, but I am having trouble figuring out how to run a query on tables in two different databases. I normally use /usr/local/mysql/bin/mysql -h localhost -u username -ppassword -D databaseName -N -e SELECT ... as the initial string of the query. However, this only references one database. How can I issue a query over two tables, one in each database? Thanks, John On Dec 11, 2005, at 7:02 PM, Logan, David (SST - Adelaide) wrote: No problem, good to see a result. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 1:30 PM To: Logan, David (SST - Adelaide) Subject: Re: Import Table? Aha, that's it! I didn't think about the fact that you can run queries on tables in multiple databases in that manner. The end result is exactly the same. You made my day. Thanks! -John On Dec 11, 2005, at 6:35 PM, Logan, David (SST - Adelaide) wrote: Hi John, I would probably create a temporary database, use this, dump all the tables into that and then use only the table that you want, followed by a drop database. It would be a bit difficult to strip out one table AFAIK. I can't see anything in the mysql client options. eg. mysql -u root -p new databasename ./.sql file If you are embedding this into the app, is this perl or similar? You can either do a create tempdatabasename or use the test database that seems to be created by every mysql installation and just do a mysql -u root -p test ./.sql file Your comparisons can refer to permanent.tablename and temp.tablename. They will be logically and physically separate. eg. you can JOIN etc. so long as you have the permissions. Sorry I can't be of more help. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 12:46 PM To: Logan, David (SST - Adelaide) Subject: Re: Import Table? David, I should probably just give the big picture of what I need to accomplish. Here it is: I am on Mac OS X.3. The sql file was dumped from the same database at an earlier date using mysqldump. The database has 6 tables in it, one of which is the table I want to access -- 'theTable'. The end goal is to load 'theTable' from the dumped sql file into a temporary table on the server in order to do some comparisons between 'theTable' currently in the server and 'theTable' from the sql file. I am embedding these mysql commands into my MAC OS X application's code, so cutting and pasting data is not relevant to my situation. Thanks for sticking with me on this! -John On Dec 11, 2005, at 5:57 PM, Logan, David (SST - Adelaide) wrote: Hi John, Personally, I'd just do a cut and paste job on the .sql file unless it is too unmanageable. Not knowing your platform, and being a unixy type person, I would use sed or grep to strip out the lines that I need and then plonk them straight into another file. I don't know how you could accomplish that on a Windows platform. I hope I haven't misunderstood, is the .sql file come from another MySQL database or is this from a SQL server machine or similar? My interpretation of a .sql file is something akin to that created by mysqldump eg. a text file that has a number of SQL statements in it allowing you to recreate the table by using this as input. You can also use Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 12:14 PM To: mysql@lists.mysql.com; Logan, David (SST - Adelaide) Subject: Re: Import Table? Thanks for the response, David. How about if I want to import all the entries from one specific TABLE within that sql file into a table with identical columns on my MySQL server? Is there a way? Thanks, John On Dec 11, 2005, at 5:29 PM, Logan, David (SST - Adelaide) wrote: Hi John, If it is a .sql file, with all appropriate SQL statements already in place then you only have to do the following $ mysql -u -p databasename .sql file This will process all appropriate statements in the file. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 11:55 AM To: mysql@lists.mysql.com Subject: Import Table? Is there a command that will load in all of the data from
Re: Select Unique?
One further question on this topic ... What if I add a third table into the mix, so that: database contains -- table1 (column1, column2), table2 (column1, column2, column3), table3 (column3) What query will return all rows from table2 where the entry for table2.column1 does not match any entries in table1.column1 AND table2.column3 has a matching entry in table3.column3 The idea is that I want to further restrict the returned rows by requiring that the entry in table2.column3 is also found in an entry in table3.column3 Is there a nested left join that will work. Something else? Thanks, John On Dec 12, 2005, at 9:57 AM, Michael Stassen wrote: Rhino wrote: - Original Message - From: John Mistler [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, December 12, 2005 12:34 AM Subject: Select Unique? I have two tables 'table1', 'table2' with a matching column 'column1'. How can I return all rows from table2 where the entry for table2.column1 does not match any entries in table1.column1? SELECT * FROM table2 WHERE table2.column1 table1.column1 returns all the rows, rather than the unique rows in table2 ... Any ideas? SELECT * FROM table2 where table2.column1 not in (select distinct column1 from table1 where column1 not null) That should be where column1 IS NOT NULL). The 'distinct' in the subquery is not strictly necessary but should help performance. The WHERE clause in the subquery is often omitted but really shouldn't be. Rhino If you're interested in performance, you probably shouldn't use a subquery. If you put EXPLAIN in front, you'll see that mysql labels this a DEPENDENT SUBQUERY, meaning it will rerun the subquery for each row in the outer query. The optimizer *should* be smart enough to run the inner query once, then compare rows to that list using the index, but it isn't. As a test, I made a 25 row table and a copy missing 3 of those rows. The subquery version took twice as long to execute (.12 sec) as the left join version (.06 sec). The larger the tables involved, the larger the difference is likely to be. Michael (Test run on my iBook G4, OS X 10.3.9, mysql 4.1.15.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import Table?
Is there a command that will load in all of the data from a table within a database .sql file on disk? The only import option I am seeing is LOAD DATA INFILE which requires a text file already exported to disk. How about a way to load in the table data directly from the database file? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import Table?
Thanks for the response, David. How about if I want to import all the entries from one specific TABLE within that sql file into a table with identical columns on my MySQL server? Is there a way? Thanks, John On Dec 11, 2005, at 5:29 PM, Logan, David (SST - Adelaide) wrote: Hi John, If it is a .sql file, with all appropriate SQL statements already in place then you only have to do the following $ mysql -u -p databasename .sql file This will process all appropriate statements in the file. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Monday, 12 December 2005 11:55 AM To: mysql@lists.mysql.com Subject: Import Table? Is there a command that will load in all of the data from a table within a database .sql file on disk? The only import option I am seeing is LOAD DATA INFILE which requires a text file already exported to disk. How about a way to load in the table data directly from the database file? Thanks, John -- 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]
Select Unique?
I have two tables 'table1', 'table2' with a matching column 'column1'. How can I return all rows from table2 where the entry for table2.column1 does not match any entries in table1.column1? SELECT * FROM table2 WHERE table2.column1 table1.column1 returns all the rows, rather than the unique rows in table2 ... Any ideas? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copy table?
Is there a way to make an exact copy of a table and give the copy a new name? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LATEST_DATE
Is there a function that will return the latest date from a datetime column? something like LATEST_DATE(theColumn) -- 2004-10-15 15:17:00 Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query help
I need help coming up with the following query: My table: +-+--+ | rowID | dateOfPurchase | +-+--+ | 1 | '2004-1-17 08:00:00' | +-+--+ | 4 | '2004-1-17 08:03:20' | +-+--+ | 1 | '2004-1-17 08:05:45' | +-+--+ | 2 | '2004-1-17 08:07:11' | +-+--+ | 1 | '2004-1-17 08:09:03' | +-+--+ | 4 | '2004-1-17 08:11:56' | +-+--+ | 1 | '2004-1-17 08:13:24' | +-+--+ I would like to return all rowIDs that do not exist more than 3 times in the 08:00:00 - 09:00:00 hour of date '2004-1-17'. --2,4 Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforce value on select
Good idea. liang le's answer almost got it, but I couldn't make it work with string values quite right. Here is my situation: I am issuing a series of queries all-in-one like SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 1;SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 2;SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 3; expecting a return of aName otherItem aName otherItem aName otherItem but if one of those rowIDs does not exist, then I get aName otherItem aName otherItem and my app has no value for the non-existing row. I would like for the query to return an indication that the row did not exist, like: aName otherItem 0 or '' or 'NULL' (no nameColumn entries will ever = 0, or '', or NULL) aName otherItem Does that make more sense, and if so, is there a solution? Thanks, John Liang Le -- Your query: (SELECT IFNULL(a.nameColumn,'0') nameColumn, IFNULL(a.otherColumn,'0') otherColumn FROM theTable a WHERE a.rowID = 5) UNION (SELECT IFNULL(b.nameColumn,'0') nameColumn, IFNULL(b.otherColumn,'0') otherColumn FROM theTable b WHERE b.rowID = 5) ; Worked when the row DID NOT exist (like I asked for). However, when the row DID exist, it was returning: aName otherItem 0 0 The zeros are troublesome. It should look like: aName otherItem Thanks! on 10/10/04 10:46 PM, Michael Stassen at [EMAIL PROTECTED] wrote: Then how will you know the difference between a row with nameColumn = 0 (or '') and one that doesn't exist? What you are asking for seems very strange. You want the db to pretend there's a value for nonexistent rows. If rowID 5 should have the value 0, then I wonder why there isn't a row with rowID=5 and value=0. If it's just a matter of treating non-existent rows as having 0 value in your app, why don't you simply code that into your app? In other words, I find it hard to provide a solution, because I don't understand what you want. Perhaps if you explained it, someone could suggest how best to accomplish it. Michael John Mistler wrote: Thanks for the reply. There is a slight difference in what I need from the IFNULL function. It will only return the specified value if the column is null on a row that actually exists. I am needing a function that will return the specified value if the row does NOT exist. Any other ideas? SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set (I want a value like '0' or something) Thanks again! -John on 10/10/04 8:12 PM, liang lei at [EMAIL PROTECTED] wrote: --- John Mistler [EMAIL PROTECTED] Is there a way to force SOME value to be returned from a SELECT query when the result is empty set? For instance: SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set I would like for it to return some value, such as '' or 0 . . . Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] select ifnull(column,'0') from table -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Enforce value on select
Is there a way to force SOME value to be returned from a SELECT query when the result is empty set? For instance: SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set I would like for it to return some value, such as '' or 0 . . . Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforce value on select
Thanks for the reply. There is a slight difference in what I need from the IFNULL function. It will only return the specified value if the column is null on a row that actually exists. I am needing a function that will return the specified value if the row does NOT exist. Any other ideas? SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set (I want a value like '0' or something) Thanks again! -John on 10/10/04 8:12 PM, liang lei at [EMAIL PROTECTED] wrote: --- John Mistler [EMAIL PROTECTED] Is there a way to force SOME value to be returned from a SELECT query when the result is empty set? For instance: SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set I would like for it to return some value, such as '' or 0 . . . Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] select ifnull(column,'0') from table _ Do You Yahoo!? 150??MP3 http://music.yisou.com/ ??? http://image.yisou.com 1G??1000??? http://cn.rd.yahoo.com/mail_cn/tag/1g/*http://cn.mail.yahoo.com/event/mail_1g/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple query question
I have a table in which the first column is either 1 or 0. The second column is a number between 0 and 59. I need to perform a query that returns entries where: 1. IF the first column is 1, the second column is NOT 0 2. IF the first column is 0, the second column is anything. It seems simple, but I'm not getting it right. Any ideas? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE string segment?
I have a column that holds hard disk file location info such as: /Volumes/External HD/aFolder/aFile.pdf /Volumes/External HD/aFolder/anotherFile.pdf etc. . . (many files located in the same folder) Can anyone suggest a single statement (or multiple) that would update every file location located in this same folder to another location, say: /Volumes/External HD/aFolder/aChildFolder/aFile.pdf /Volumes/External HD/aFolder/aChildFolder/anotherFile.pdf etc . . . ? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE string segment?
Wow! That works. Thanks a bunch! While we're at it, I have one more hang-up: Is there a (DISTINCT?) statement that will select the distinct folder path(s) of every entry in the table? For instance, if I have three files: /Volumes/External HD/aFolder/aFile.pdf /Volumes/External HD/aFolder/anotherFile.pdf /Volumes/External HD/aFolder/aChildFolder/aThirdFile.pdf the query would return: /Volumes/External HD/aFolder/ /Volumes/External HD/aFolder/aChildFolder/ Any ideas? Thanks again, John on 9/1/04 8:03 PM, Michael Kruckenberg at [EMAIL PROTECTED] wrote: You can use the replace string function: update table set file_path=replace(file_path,'aFolder','aFolder/aChildFolder'); In each update aFolder will be replaced by the new path. John Mistler wrote: I have a column that holds hard disk file location info such as: /Volumes/External HD/aFolder/aFile.pdf /Volumes/External HD/aFolder/anotherFile.pdf etc. . . (many files located in the same folder) Can anyone suggest a single statement (or multiple) that would update every file location located in this same folder to another location, say: /Volumes/External HD/aFolder/aChildFolder/aFile.pdf /Volumes/External HD/aFolder/aChildFolder/anotherFile.pdf etc . . . ? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OS X Embedded Server
I have written an application for MAC OS X.3 that interfaces with MySQL through the Client/Server approach. I would like to simplify the installation process of the application by using the embedded MySQL server instead. However, I am having trouble figuring out how to: 1. Include the MySQL embedded server in my application bundle 2. Initiate the embedded server from within my application's code 3. Change my existing queries to work with the embedded server Anyone have experience with this? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBF to MySQL
I appreciated your first email, regardless of whether or not I was able to make it work. Any response is welcome! I am having some trouble with the final result using the CVS file: some of the content uses commas i.e. Chinchilla Zúñiga, Guillermo and gets split up between two fields. This whole process is pretty messy, but I'm sure I will find a solution. The tools I was directed to below at freshmeat.net are a little out of my league. I am a Macintosh user that programs in applescript, a little obj-C, and SQL. I don't know if I can even implement those classes. Thanks! John on 7/8/04 6:11 AM, Chinchilla Zúñiga, Guillermo at [EMAIL PROTECTED] wrote: I did it because I wrongly assummed this was obviuos, but then I resend to Mr. Mistler a more precisely email. I´m sorry. Cheers Guillermo -Mensaje original- De: Joshua J. Kugler [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 07 de Julio de 2004 05:37 p.m. Para: [EMAIL PROTECTED] Asunto: Re: DBF to MySQL I'm sure it did...DBF and XLS files are not plain text. What Chincilla gave your was a bit of code for importing CSV files, after they had been exported from Excel. I'm not sure *why* he gave you that code. You can do one of two things. 1) Open up those files in Excel (it will also open DBF files) and export them as CSV, or 2) find a class and programmatically import them (search freshmeat.net for 'dbf'). If you need more info, holler. j- k- On Tuesday 06 July 2004 07:55 pm, John Mistler said something like: For some reason, the imported information showed up as garbled nonsense. The file I was importing was an .xls file. Do you know if there is another TERMINATED BY I should be using? If not, I wonder how I can find out? The other question I have is: do I have to create a table within the MySQL database with exactly the right number of columns ahead of time for the import to work? - this is what I did. If so, is there a way to import info from a .dbf or .xls file without knowing the structure of the table ahead of time? Thanks, John on 7/6/04 3:59 PM, Chinchilla Zúñiga, Guillermo at [EMAIL PROTECTED] wrote: Try, for example: LOAD DATA INFILE 'myfile.cdf' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; -Mensaje original- De: John Mistler [mailto:[EMAIL PROTECTED] Enviado el: Martes, 06 de Julio de 2004 04:51 p.m. Para: [EMAIL PROTECTED] Asunto: DBF to MySQL I am wanting to parse the info in a .dbf file (or .xls file for that matter) and place it in a table in a MySQL database. Is this something that I can do with the server side MySQL application, or do I need to figure out a way to do it on the client side? Any description of the method would be very welcome! For what it is worth, I am a Mac OSX.3 user. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT DISTINCT?
Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
I am not certain from the documentation whether it is advisable to create a unique multi-column index on two columns that are already individually indexed. The individual indexes I assume I need for when I do a SELECT on those particular columns. The multi-column one I need for the reasons discussed below. Any one know? Thanks, John on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Certainly, it's called making a unique index on the field(s) you want to keep unique. Hope that helps. j- k- On Wednesday 07 July 2004 12:48 pm, John Mistler said something like: Is there a way to do an INSERT on a table only if no row already exists with the same info for one or more of the columns as the row to be inserted? That is, without using a method outside SQL? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DBF to MySQL
I am wanting to parse the info in a .dbf file (or .xls file for that matter) and place it in a table in a MySQL database. Is this something that I can do with the server side MySQL application, or do I need to figure out a way to do it on the client side? Any description of the method would be very welcome! For what it is worth, I am a Mac OSX.3 user. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBF to MySQL
For some reason, the imported information showed up as garbled nonsense. The file I was importing was an .xls file. Do you know if there is another TERMINATED BY I should be using? If not, I wonder how I can find out? The other question I have is: do I have to create a table within the MySQL database with exactly the right number of columns ahead of time for the import to work? - this is what I did. If so, is there a way to import info from a .dbf or .xls file without knowing the structure of the table ahead of time? Thanks, John on 7/6/04 3:59 PM, Chinchilla Zúñiga, Guillermo at [EMAIL PROTECTED] wrote: Try, for example: LOAD DATA INFILE 'myfile.cdf' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; -Mensaje original- De: John Mistler [mailto:[EMAIL PROTECTED] Enviado el: Martes, 06 de Julio de 2004 04:51 p.m. Para: [EMAIL PROTECTED] Asunto: DBF to MySQL I am wanting to parse the info in a .dbf file (or .xls file for that matter) and place it in a table in a MySQL database. Is this something that I can do with the server side MySQL application, or do I need to figure out a way to do it on the client side? Any description of the method would be very welcome! For what it is worth, I am a Mac OSX.3 user. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL challenge
O.K. you SQL gurus-- I have a difficult query for you that has me stumped. The table has two columns: rowID (MEDIUMINT-but *NOT* AUTO_INCREMENT) and theDate (DATETIME). I need it to find: 1. The COUNT of REPEATED instances of the rowID in the last month. - so if there are 5 rows with the same rowID in the last month, it would return 4 (I can only seem to get it to return 10 WHERE t1.rowID = t2.rowID AND t1.theDate t1.theDate) 2. The AVERAGE number of REPEATED instances of the rowID per week (Monday 00:00:00 through Monday 00:00:00 one week later) in the last month. If I need to add table columns I certainly can. THANKS! - John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL challenge
Wow, that was it! I changed the WHERE to (because I wasn't clear): . . . WHERE theDate BETWEEN SUBDATE(CURDATE(), INTERVAL 1 MONTH) AND CURDATE() . . . Now, I have just one more that I still am stumped by, if anyone (Roger or other) has a second: Given theTable with 2 columns: rowID (MEDIUMINT-but *NOT* AUTO_INCREMENT) and theDate (DATETIME) I need to find: 1. The average TIME ELAPSED between consecutive REPEATED instances of the rowID (GROUP BY rowID, I assume) between one month ago and now. - So, if there are 3 rows with rowID = 1 and 2 rows with rowID = 2, it would return the average time (total seconds, or HH:MM:SS) of ((time elapsed between row1 and row2 where rowID = 1, row 2 and row 3 where rowID = 1) AND (time elapsed between row1 and row2 where rowID = 2)). *Note that it would not use the time elapsed between row 1 and row 3 where rowID = 1) for the average calculation. 2. The average time elapsed between REPEATED instances of the rowID PER WEEK between one month ago and now. (This one might be as easy as using the WEEK() function as before . . .) Thanks, - John on 6/28/04 2:37 AM, Roger Baklund at [EMAIL PROTECTED] wrote: * John Mistler I have a difficult query for you that has me stumped. The table has two columns: rowID (MEDIUMINT-but *NOT* AUTO_INCREMENT) and theDate (DATETIME). I need it to find: 1. The COUNT of REPEATED instances of the rowID in the last month. - so if there are 5 rows with the same rowID in the last month, it would return 4 (I can only seem to get it to return 10 WHERE t1.rowID = t2.rowID AND t1.theDate t1.theDate) I'm not sure if I understand, but have you tried something like this: select rowID,COUNT(*) AS cnt from theTable where month(theDate) = month(curdate()) group by rowID having cnt1; If you by last month meant the last in the dataset, you could find the month by issuing: select @m:=month(max(theDate)) from theTable; You say you want the answer 4 when the count is 5...? You can subtract one from the count in the query: select rowID,COUNT(*)-1 AS cnt from theTable where month(theDate) = @m group by rowID having cnt0; 2. The AVERAGE number of REPEATED instances of the rowID per week (Monday 00:00:00 through Monday 00:00:00 one week later) in the last month. You want to group by week, you can get the week using the week() function. For weeks starting on monday, the second parameter should be 1. You want the average of the counts... try using a temporary table, something like this: create temporary table tmp1 select week(theDate,1) AS week, rowID, count(*)-1 AS cnt, from theTable where month(theDate) = @m group by week,rowID having cnt0; select week,avg(cnt) from tmp1 group by week; -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select compare to current date
This one I can help you with: SELECT year-field FROM table WHERE YEAR(year-field) = YEAR(CURDATE()); - John on 6/28/04 4:49 PM, Robb Kerr at [EMAIL PROTECTED] wrote: Need help with a SQL Select statement. I've got a table that consists of a list of years (1930-2014). I need to create a drop-down list on my page that consists of a list of years between 1930 and the current year. How do I construct this SELECT? See below... SELECT * FROM table WHERE 'year-field' = year of current date What's the syntax for year of current date? Thanx in advance for your help, Robb Kerr Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.digitaliguana.com/ http://www.cancerreallysucks.org http://www.cancerreallysucks.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COPY row?
Great! It works. I did have to eliminate the parentheses in the SELECT
part:
INSERT PRIVILEGES (login, Permission_ID)
SELECT ('newuser', Permission_ID)
FROM PRIVILEGES
WHERE login='user1'
had to be
INSERT PRIVILEGES (login, Permission_ID)
SELECT 'newuser', Permission_ID
FROM PRIVILEGES
WHERE login='user1'
Otherwise I got a Mistake in you SQL syntax error.
Thanks!
-John
on 6/11/04 6:36 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:
ABSOLUTELY! If you can create a SELECT statement that contains the NEW data
for the rows you want (regardless of data types. I only used integer
columns as an example) you can use that in your INSERT statement.
Longer example , smaller tables:
CREATE TABLE USERS (
login char(8),
name varchar(20),
... (other user information columns)
)
CREATE TABLE PERMISSION(
ID int auto_increment,
name varchar(20) primary key
)
CREATE TABLE PRIVILEGES (
login char(8),
Permission_ID int
)
I have used a structure like this to provide granular access to various
applications in the past. The PRIVILEGES table contains one row for each
type of permission someone has. Examples would be read public, read
confidential, read secret, edit public, edit confidential, edit
secret, etc. These were the entries in the PERMISSION TABLE. To grant
someone permission to do certain things an entry in the PRIVILEGES table
would look like this
INSERT PRIVILEGES (login, Permission_ID) VALUES ('user1', 1)
That would let 'user1' read public documents, get it? One row for each
level of permission they hold. OK, now I have been asked to duplicate a
set of permissions (multiple rows in the PRIVILEGES table) because someone
new was just appointed as the backup to 'user1'. I can write a SELECT
statement that looks like the rows I want to see like this:
SELECT ('newuser', Permission_ID)
FROM PRIVILEGES
WHERE login='user1'
That shows me all of the privileges that 'user1' had but I substituted the
name of 'new user' as a constant. In reality I could have used any formula
or a column from another table (by JOIN-ing that table to my FROM clause)
or any combination of data to create what ever new value I wanted to see in
that first column. Understand? You can build your results any way you want.
Adding the results of that query to my privileges table is as simple as
INSERT PRIVILEGES (login, Permission_ID)
SELECT ('newuser', Permission_ID)
FROM PRIVILEGES
WHERE login='user1'
The secret to making this work right and NOT screwing up your tables is to
get the SELECT statement correct *first* then prepend the INSERT clause to
it so that those result rows end up as new rows in your table. Your
destination table has an auto-incrementing ID column. You should not insert
values to that column (yes, you can under certain circumstances but this is
not one of them) so DO NOT include it in either the INSERT clause or the
SELECT clause.
Did this help or make it worse?
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
John Mistler
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
phia.netcc:
Fax to:
06/10/2004 11:12 Subject: Re: COPY row?
PM
This ALMOST does it for me, except for the update part. In your example,
you simply add a number to the value of the column i.e. SELECT (col1 + 1 .
.
.) for the update. In my situation, the column to be updated is a string.
So I need to replace the string value in the VARCHAR column with a new
string. Is there way to do this?
Thanks,
John
on 6/10/04 12:57 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:
Yes! but you will have to do some typing
Just use the INSERT ... SELECT command. You do NOT want list the PK
column
in your statements (so that it will autoincrement) so you will have to
type
out the rest of the column names. With a table that looks like:
CREATE TABLE testme (
id int auto_increment primary key,
col1 int,
col2 int,
...
col37 int
)
You would use a statement like:
INSERT testme (col1, col2, ..., col37)
SELECT col1, col2, ... , col37
FROM testme
WHERE conditions go here
Whatever rows the WHERE clause matched would be added to the table
creating
your duplicate rows. Because you DID NOT list the autoincrement column,
all
of those new rows end up with new numbers.
Now, if you know what changes to you want to make at the time of the
copying, you can define those changed in the SELECT statement and do it
all
at once.
SELECT (col1 + 1, col2 +1, col3, ...
That would give you incremented numbers for col1 and col 2 but the rest
of
the fields would be the same, get it?
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
John Mistler
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
phia.netcc:
Fax to:
06/10/2004 03:40 Subject: COPY row?
PM
COPY row?
Is there a COPY row or DUPLICATE row command? I would like to duplicate a row with in a table with 38 columns (auto-incrementing the Primary Key on the copied row, of course) and then alter the entry in ONE of its columns. Can this be done without doing a SELECT, then INSERT, then UPDATE? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error on Outfile
I just encountered this yesterday on my Macintosh, and it turned out that user mysql did not have permission to write to the destination folder. I simply had to change the permissions on that folder to include Read Write access to user mysql. -John on 6/1/04 8:09 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: input: select firstname,middlename,lastname,county,state,dob,ssn into outfile '/Users/user/temp/test.csv' fields terminated by ',' lines terminated by '\n' from iffinet.subjects where client_id='1' and ostatus='complete'; output: ERROR 1: Can't create/write to file '/Users/timbest/test.csv' (Errcode: 13) I¹ve granted full rights to anyone who accesses /Users/user/temp. Any ideas as to what I can do to fix this? VR/Tim Best IT cell: 504-231-1084 fax: 206-338-6162 [EMAIL PROTECTED] http://www.best-it.biz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INTO OUTFILE error
Can anyone guess why I am getting this error: ERROR 1: Can't create/write to file '/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13) when using this statement: mysql SELECT * INTO OUTFILE '/Users/johnmistler/Desktop/TestFile.txt' FROM theTable WHERE column1 != 'thisString'; ? I am logging in as root for the test. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE error
Would there be any obvious reasons for its inability to access the desktop? I have used that desktop directory many times before in UNIX. Thanks, John on 6/1/04 12:49 AM, Egor Egorov at [EMAIL PROTECTED] wrote: John Mistler [EMAIL PROTECTED] wrote: Can anyone guess why I am getting this error: ERROR 1: Can't create/write to file '/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13) when using this statement: mysql SELECT * INTO OUTFILE '/Users/johnmistler/Desktop/TestFile.txt' FROM theTable WHERE column1 != 'thisString'; ? I am logging in as root for the test. It means that MySQL server can not access to the /Users/johnmistler/Desktop/ directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Batch Update?
Let's say I have a table A with 25 columns. I also have an identical table B with 25 columns (column names, indexes, everything the same). I want to get the entry of column 2 of every row of table B, and update column 2 of every row of table A WHERE the rowID from table A matches the rowID of table B. Is there a way to do this in one fell swoop, rather than doing one at a time: UPDATE tableA SET column2 = 'column2EntryFromRowXOfTableB' WHERE T2RowID = T1RowID; With 10,000+ rows to update, even scripted in a repeat loop, this seems quite tedious. In effect, I'm saying Copy the column2 entry from one table to column2 of another table, as long as their row IDs match. Any ideas on a batch-type update? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATETIME question
Given a column DATETIMEcolumn (-MM-DD HH:MM:SS), is there a SELECT statement that will: select all entries whose (TIME) of DATETIMEcolumn is BETWEEN 'HH:MM:SS' AND 'HH:MM:SS', but whose (DATE) is anything? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mac Developer Question
A couple of questions for any Mac users willing to help: I have created an application for OS X.3 that talks to the MySQL server with shell commands. In order for those commands to work, I have to include a proper username and password to access the MySQL database designated for my app within the shell command each time a shell command is sent. Assuming that there is no way around this (the security feature being necessary for internet and corporate use but not necessary for my purposes), I have to: 1. On installation of my app, have the user provide their MySQL root password. 2. Create the database for the application using the root password and then GRANT priveleges to a username and password on that database (predefined in my code). What I am having trouble with is the proper language to do the following: If the user doesn't know their root password, and chooses to reset the root password, given variable rootPassword (what they entered): do shell script kill cat /usr/local/mysql/bin/mysql/data/localhost.pid do shell script /usr/local/mysql./bin/mysqld_safe --skip-grant-tables do shell script /usr/local/mysql/bin/mysqladmin -u root flush-priveleges password 'rootPassword' do shell script /usr/local/mysql/bin/mysqladmin shutdown do shell script /usr/local/mysql./bin/mysqld_safe do shell script /usr/local/mysqlbin/mysql -h localhost -u root --password = 'rootPassword' -e \CREATE DATABASE IF NOT EXISTS theDatabase/ do shell script /usr/local/mysqlbin/mysql -h localhost -u root --password = 'rootPassword' -e \GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE ON theDatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY 'pswd'\ I can't get past the first line. Any ideas what is wrong with it? In the larger picture, I am wondering if there is a way to avoid all of this. Ideally, I would like for the application to come packaged with MySQL, to install MySQL automatically (not sure what happens if MySQL already exists on the system), and to never ask the user for a password. In fact, to just run MySQL in the background as if it were a part of my application. Any ideas on this? However, in the short term, in order to get it all working on my own computer I will need to get the above language correct. Thanks for any input! -John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mac Developer Question
I'm having trouble getting this line to work. I have tried: kill `cat /usr/local/mysql/bin/mysql/data/localhost.pid` - Not a directory kill `cat /usr/local/mysql/data/localhost.pid` - Permission denied I'm not sure if I have the wrong hostname, or if the command is more flawed than that. Furthermore, I need the command to work without requiring a password or other response, because it is issued as a unix shell command to initiate resetting the root password: do shell script kill `cat /usr/local/mysql/bin/mysql/data/localhost.pid` do shell script /usr/local/mysql./bin/mysqld_safe --skip-grant-tables do shell script /usr/local/mysql/bin/mysqladmin -u root flush-priveleges password 'rootPassword' do shell script /usr/local/mysql/bin/mysqladmin shutdown do shell script /usr/local/mysql./bin/mysqld_safe on 5/22/04 7:52 AM, Paul Bingman at [EMAIL PROTECTED] wrote: On Sat, 22 May 2004, John Mistler wrote: do shell script kill cat /usr/local/mysql/bin/mysql/data/localhost.pid The kill command takes a numeric PID as its argument. You need to get the pid out of the pidfile. So to get the above to work you have to enclose cat and the filename in backticks: kill `cat /usr/local/mysql/bin/mysql/data/localhost.pid` This will run cat /usr/local/mysql/bin/mysql/data/localhost.pid, getting the numeric pid from the file, then pass the pid to kill. paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mac Developer Question
Aha, this revealed the .pid: /usr/local/mysql-standard-4.0.18-apple-darwin6.8-powerpc/data/John-Mistlers- Computer.local.pid Now, this brings up a new question. Is there a sudo find command I can use to locate the proper .pid on any given computer? I am hard coding this into an application that will reset the root password to whatever the user enters. How can this be done if I don't know the location of the .pid file ahead of time on their system? Thanks, John P.S. Is the host name in my example local or John-Mistlers-Computer.local? on 5/22/04 12:01 PM, Hassan Schroeder at [EMAIL PROTECTED] wrote: John Mistler wrote: I'm having trouble getting this line to work. I have tried: kill `cat /usr/local/mysql/bin/mysql/data/localhost.pid` - Not a directory kill `cat /usr/local/mysql/data/localhost.pid` - Permission denied I'm not sure if I have the wrong hostname, or if the command is more flawed than that. Could be both; to start with, though, it should be pretty easy to confirm exactly where *.pid is, eh? If you're sure that the base directory is '/usr/local/', run sudo find /usr/local -type f -name '*.pid' -print and see what turns up. Note: On my Powerbook, mysql data files (including .err, .pid) are located at /sw/var/mysql -- and I think they were installed there by default, because I'd never have chosen that :-) And the file name on my system is the actual hostname'.pid', *not* just 'localhost.pid', in case that reference is literal... HTH! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT almost every column
Is there a SELECT statement, if I have a table with 50 columns, to select every column EXCEPT the last one? Can I do this without typing the name of all 49 columns? If so, then what if I want to exclude the last TWO columns? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT almost every column
Hmmm. The reason I asked was that the last column in the table is TEXT, and
might contain up to 5000 text characters. I'm trying to make the query as
efficient as possible, and I don't know if that much data will make a
noticeable speed difference?
Thanks,
John
on 5/14/04 2:31 PM, Justin Swanhart at [EMAIL PROTECTED] wrote:
--- John Mistler [EMAIL PROTECTED] wrote:
Is there a SELECT statement, if I have a table with
50 columns, to select
every column EXCEPT the last one? Can I do this
without typing the name of
all 49 columns?
If so, then what if I want to exclude the last TWO
columns?
Thanks,
John
There is no construct in SQL to select X number of
columns from a table.
The traditional answer to this question would normally
be use views, but since MySQL doesn't support them
that doesn't help you very much.
Unless the extra columns are long text columns or
contain BLOBS, then I see no harm in just selecting
them along with the rest of the other columns by using
select * from
If you are accessing the database from a programming
environment then you could do the following:
[pseudo code]
$sql = desc $NAME_OF_TABLE
$result = exec($sql)
$rows = fetch_result_into_array($result)
destroy($result)
$cnt = count($rows) - $NUMBER_OF_COLUMNS_TO_OMIT
if ($cnt = 0)
{ error(to few columns);
return;
}
$sql = select
for ($i=0;$i $cnt-1;$i++)
{ $sql = $sql + $ary[$i][Field] + ,
}
$sql = $sql + $ary[$cnt][Field]
$sql = $sql + FROM $NAME_OF_TABLE_TO_SELECT_FROM
$sql = $sql + WHERE $WHERE_CLAUSE
$sql = $sql + HAVING $HAVING_CLAUSE
$sql = $sql + GROUP BY $GROUP_BY_CLAUSE
$sql = $sql + ORDER BY $ORDER_BY_CLAUSE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another Trailing Spaces Issue
I am using 4.0.18. I also checked out the Bugs Fixed report--and it kind of looks like #2295 applies, although I can't be certain. It definitely returns rows without trailing spaces, even though the SELECT asks for rows WITH trailing spaces. Hmmm... on 5/2/04 2:05 AM, Matt W at [EMAIL PROTECTED] wrote: Hi John, What version do you use? In 4.0.18, they fixed some bugs that were introduced in 4.0.17 related to trailing spaces on indexed TEXT-family columns: http://dev.mysql.com/doc/mysql/en/News-4.0.18.html I see 3 Bugs fixed entries with trailing spaces in them. If you're not using 4.0.17, what you're seeing IS a bug and should be reported if it hasn't already been. Matt - Original Message - From: John Mistler Sent: Friday, April 30, 2004 1:39 PM Subject: Another Trailing Spaces Issue The TINYTEXT format solves the problem of storing the string with spaces at the end. Now, I'm having trouble SELECTING a row WHERE the TINYTEXTcolumn = theStringWithTheSpacesAtTheEnd; If the string is theString + space and another row has an entry with theString (+ no space), the query returns BOTH rows. Is there a way to get around returning the latter entry? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distributing mySQL
I'm glad you brought this up, because I am trying to figure out the same thing. I have created an application for OS 10.3 which stores its data in a MySQL database. I would like to be able to install the app on other computers from a CD, without having to set up MySQL separately on each computer. This would require the installer to search for MySQL first, and if it exists, update it to version 4.0.18; otherwise, install MySQL 4.0.18. I have read something about a MySQL embedded server, which might be the answer. If anybody has experience with this and could offer advice, I would be more than appreciative. When I find out anything useful on my own, I would be happy to offer you what I learn, too. Feel free to check in with me. - John on 5/2/04 4:48 AM, Peter Laurens at [EMAIL PROTECTED] wrote: Hello all, I have a query about distributing mySQL. I am a student developer, but have very little experience with databases, I develop in Cocoa for OS X (as well as a little bit of Windows stuff). An application I am writing stands to benefit a lot from having a server application available. It's a simple project management app, and at the moment does very rudimentary networking. One plan is to really enhance this and develop a server app for my project manager, so multiple users could work on projects in the same office and keep all project information up to date via the one server. I think it would be great to use mySQL to store project information for access via my server app (is it even necessary to have write a server app myself, or could I just build in the ability to communicate with the mySQL database over the network?). Anyway, basically my question is this: I'd love to be able to distribute both the server app and the client app on CD, will it be a problem (from a technical standpoint, not legal) to create and set up the mySQL database (whether or not mySQL is already installed) from a simple installer? Thanks for any advice and help you may be able to offer, it's most appreciated, - Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexing
I know this is an elementary question, but I am getting two sets of instructions from different MySQL manuals about setting an index on a prefix of a column of a table. One says to use: KEY indexName (colName(length)) and the other says to use INDEX indexName (colName(length)) Are both all right? Any light shed on indexing columns would be much appreciated. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing a space
Is there a way to force an invisible space character to be stored at the END of a string in a column (either VARCHAR or CHAR), so that when it is retrieved the space at the end is not cut off? theString + space or even, theString + space + space + space, etc. Currently, I can only get the string back as theString Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another Trailing Spaces Issue
The TINYTEXT format solves the problem of storing the string with spaces at the end. Now, I'm having trouble SELECTING a row WHERE the TINYTEXTcolumn = theStringWithTheSpacesAtTheEnd; If the string is theString + space and another row has an entry with theString (+ no space), the query returns BOTH rows. Is there a way to get around returning the latter entry? Thanks, John on 4/30/04 2:42 AM, Matt W at [EMAIL PROTECTED] wrote: Hi John, I *think* VARCHAR is *supposed* to work that way, but doesn't in MySQL. So you'll have to use TINYTEXT. Its storage requirements are the same as VARCHAR(255) and it behaves the same way, except for, I think, 3 things: 1) the trailing space thing, obviously; 2) it can't have a DEFAULT value; and 3) you can't index the whole column -- but you can INDEX (col(255)), which has the same effect. :-) Hope that helps. Matt - Original Message - From: John Mistler Sent: Friday, April 30, 2004 3:54 AM Subject: Storing a space Is there a way to force an invisible space character to be stored at the END of a string in a column (either VARCHAR or CHAR), so that when it is retrieved the space at the end is not cut off? theString + space or even, theString + space + space + space, etc. Currently, I can only get the string back as theString Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT duplicate rows
Is there a way to use a SELECT statement (or any other, for that matter) that will look at every table in a database and return every row whose first 3 columns are duplicated in at least one other row in any of the tables? Essentially, a command to find duplicate entries in the database . . . Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT duplicate rows
Thanks for the response, Joshua. I am so very new to MySQL, that I am afraid I require more guidance. Is there a way to join ALL tables in a database rather than just one table to itself, or one particular table to another? SELECT * FROM allTables WHERE column1=column1 AND column2=column2 AND column3=column3; I know this syntax is off the mark--it should specify: table1.column1=table2.column1, etc. However, I need it to match columns on all of the tables in the database (of which there are many), rather than just two. Any ideas? Thanks, John on 4/21/04 12:57 AM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Yes, there is a way. It's called joins. :) I don't remember the exact syntax off the top of my head, but the approach is thus: Do a self join on the table and select records that match in their first three columns, but do not have the same primary key (you *do* have primary keys on your table, don't you?). If you don't add one for this excercise. j- k- On Tuesday 20 April 2004 11:22 pm, John Mistler said something like: Is there a way to use a SELECT statement (or any other, for that matter) that will look at every table in a database and return every row whose first 3 columns are duplicated in at least one other row in any of the tables? Essentially, a command to find duplicate entries in the database . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Escape characters
When issuing commands through the terminal (in Mac OS 10.3) to MySQL, I understand that if you surround a variable with \\` it will allow for characters such as - and space. Will it also allow for all other non-alphanumeric characters such as / and * and , etc.? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Panther
This was the answer for my installaton on Panther, as well. Scott's instructions provide those extra commands you might need. I thought I had followed the standard installation instructions VERY carefully, and yet I got stuck. This got me through . . . . on 4/14/04 12:26 AM, Daniel Lahey at [EMAIL PROTECTED] wrote: Just thought I'd share my experience with MySQL under Panther. I think the critical thing that got me going was that the /usr/local/mysql/data directory was owned by root instead of mysql. I changed that (chown mysql /usr/local/mysql/data) and everything seems to be a) Hunky b) Dory, thanks to Scott Haneda. (See below...) From Scott: For some reason I get a lot of email from users on Mac OS X that can not install MySql. I have written a step by step in hopes it will help. I really did little more than copy the notes on the MySql site, but alas, it seems some people are not following those correctly. Hopefully, people can point others to this link http://newgeo.com/mysql/ Cheers, Dan (aka Zippy Appletush) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to understand the license
I thought I read that if your app is not GPL and interacts with MySQL in any way, you must license MySQL. on 4/9/04 5:16 PM, Dan Bowkley at [EMAIL PROTECTED] wrote: Exactly. The license only becomes an issue when you distribute mysql itself. Essentially, the gist is you can't charge people for mysql; only mysql can do that. You could, OTOH, let folks get your php app, and provide a link so they can download mysql themselves. - Original Message - From: charles kline [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 09, 2004 4:54 PM Subject: Trying to understand the license Hi all, I am still a bit confused as to the license for using MySQL. If I create an application in PHP, that uses a MySQL database (for example a shopping cart application) and I want to sell this application (not open source), am I required to pay a license fee? I found this quote: 2. Free use for those who never copy, modify or distribute As long as you never distribute (internally or externally) the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL or other OSI approved license or not. Which I understand to mean, that as long as I am not distributing MySQL with my application, that I don't need to worry about it. Thanks for any help. - Charles -- 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]
Uninstall MySQL for Panther?
I am two days into troubleshooting this. I need some help badly. I installed MySQL 4.0.18 for Mac. I was able to get in at first: Welcome to MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 4.0.18-standard then I would type mysql create database mytest; and get: ERROR 1044: Access denied for user: '@localhost' to database 'mytest' I tried all kinds of commands to change the root password, and every single one would result in Access denied for user . . . I would like to install, instead, CompleteMySQL from the serverlogisitics.com site, which has interfaces to help newbies like me. However, it is only at version 4.0.15. So my question is: How can I UNINSTALL the current 4.0.18 version I already have, before installing the other one? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstall MySQL for Panther?
First I want to thank all of you guys for responding. I feel a new sense of confidence that I will be able to get this thing going! However, my problems have compounded. We now have a new problem. I took the advice of one archived postings and dragged the four files seemingly installed by the mysql package at the location (in Mac language) users:johnmistler:the four files to the trash. That didn't feel right to me once I did it, so I dragged them back. They were named: 1. cd 2. md5 3. md5 sum 4. mysql Now, when I go to the terminal and type: /usr/local/mysql/bin/mysql I get: tcsh: /usr/local/mysql/bin/mysql: Command not found This suggests to me that the mysql file it is being directed to is not there. However, I can verify that the file mysql exists at this path (in Mac Language): users:johnmistler:mysql It is listed as a document with Zero KB for its size (?). It definitely is one of the files that I dragged back from the trash. Should I attempt to fix all of this, or go ahead with an uninstall? If I do need to uninstall, how do I do it? I installed the latest PACKAGE from the MySQL.com site 4.0.18-standard along with the startup item package. If you guys still think I need to tough it out, what is the next step? Once again (embarrassingly) -- THANK YOU! John on 3/12/04 7:22 PM, Paul DuBois at [EMAIL PROTECTED] wrote: At 18:33 -0800 3/12/04, John Mistler wrote: I am two days into troubleshooting this. I need some help badly. I installed MySQL 4.0.18 for Mac. I was able to get in at first: Welcome to MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 4.0.18-standard Please show us the command you entered to start the mysql program. then I would type mysql create database mytest; and get: ERROR 1044: Access denied for user: '@localhost' to database 'mytest' The account name (@localhost) has no username before the '@' character, which indicates that you have connected as the anonymous user. This user has no privileges to create the mytest database. I tried all kinds of commands to change the root password, and every single one would result in Access denied for user . . . Please show what these commands were. No one can help you diagnose the problem without information to go on. I would like to install, instead, CompleteMySQL from the serverlogisitics.com site, which has interfaces to help newbies like me. However, it is only at version 4.0.15. So my question is: How can I UNINSTALL the current 4.0.18 version I already have, before installing the other one? Well ... I'm afraid we cannot tell you that, either, because although you've indicated that you installed MySQL 4.0.18 for Mac, you haven't told us how you installed it. Did you use the PKG distribution? Did you install from source? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
