Re: Need Help Converting Character Sets
Thanks to all of you for your very helpful suggestions! I was pulled off on a different project for a few days, and I am now just getting back to this one. Sorry for my absence. First, some more information: My table definition: CREATE TABLE `Articles` ( `articleID` int(11) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) NOT NULL DEFAULT '', `author` varchar(200) NOT NULL DEFAULT '', `body` text NOT NULL, `intro` text NOT NULL, `caption` text NOT NULL, `credits` text NOT NULL, `articleDate` date NOT NULL DEFAULT '-00-00', `imageTitle` varchar(255) NOT NULL DEFAULT '', `imageAltText` varchar(255) NOT NULL DEFAULT '', `imageWidth` float NOT NULL DEFAULT '1', `imageHeight` float NOT NULL DEFAULT '1', `imageFile` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`articleID`), FULLTEXT KEY `search1` (`title`,`author`,`body`,`caption`,`credits`) ) ENGINE=MyISAM AUTO_INCREMENT=89 DEFAULT CHARSET=latin1 First problem - CHARSET should be utf8. The data for this table comes from a web page (charet utf8). I copy/paste word files into gedit (on linux) and then copy/paste from gedit to a text boxes on the web page input form. I had thought I was stripping out all the funky characters by using a simple ascii editor like gedit, but obviously not. After looking at the mysqldump for the table in a hex editor, I discovered I have these characters scatter throughout the body and intro columns: #8220; #8221; #8217; #8212; #8230; #8617; I tried converting the columns into utf8 with the command ALTER TABLE table_name CONVERT TO CHARACTER SET utf8; but all the special characters are still there. I tried converting to blob and back to utf8, and that didn't change anything. (I had to first drop the fulltext key to convert to blob). ALTER TABLE t1 CHANGE c1 c1 BLOB; ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8; The above special characters are still in the body column when I view the dump file with a hex editor. Is there a way to replace these special characters with the appropriate utf8 characters (eg #8220; - 22 hex) within the text columns body and intro columns? Thanks, Mark On Fri, Sep 28, 2012 at 7:59 AM, Rick James rja...@yahoo-inc.com wrote: Thanks for that link! That's another subtle issue I had not noted. There are so many combinations, that it is hard to say do this: * Incoming bytes are latin1 / utf8 / Microsquish control characters. * You do/don't have SET NAMES (or equivalent) * The database/table/column is declared latin1/utf8/other. * The problem is on ingestion / on retrieval. The thing mentioned involved 2 steps: ALTER TABLE ... MODIFY COLUMN BINARY (or BLOB); -- to forget any charset knowledge ALTER TABLE ... MODIFY COLUMN CHARACTER SET ...; -- coming from BINARY, this does not check the encoding. (sorry, don't have the link handy) -Original Message- From: h...@tbbs.net [mailto:h...@tbbs.net] Sent: Thursday, September 27, 2012 2:24 PM To: Mark Phillips Cc: Mysql List Subject: Re: Need Help Converting Character Sets 2012/09/24 16:28 -0700, Mark Phillips I have a table, Articles, of news articles (in English) with three text columns for the intro, body, and caption. The data came from a web page, and the content was cut and pasted from other sources. I am finding that there are some non utf-8 characters in these three text columns. I would like to (1) convert these text fields to be strict utf-8 and then (2) fix the input page to keep all new submissions utf- 8. 91) For the first step, fixing the current database, I tried: update Articles set body = CONVERT(body USING ASCII); However, when I checked one of the articles I found an apostrophe had been converted into a question mark. (FWIW, the apostrophe was one of those offending non utf-8 characters): Before conversion: I stepped into the observatory?s control room ... After conversion: I stepped into the observatory?s control room... Is there a better way to accomplish my first goal, without reading each article and manually making the changes? I do not remember where on the MySQL website this is, but there was an article about converting from character sets in version 4 to those in version 5, when UTF-8 first was supported. It sounds to me that maybe the tricks shown there would be useful to you, since, in effect, through MySQL MySQL was fooled into accepting for UTF-8 that which was not. Conversion to binary string was mentioned. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Need Help Converting Character Sets
I have a table, Articles, of news articles (in English) with three text columns for the intro, body, and caption. The data came from a web page, and the content was cut and pasted from other sources. I am finding that there are some non utf-8 characters in these three text columns. I would like to (1) convert these text fields to be strict utf-8 and then (2) fix the input page to keep all new submissions utf-8. 91) For the first step, fixing the current database, I tried: update Articles set body = CONVERT(body USING ASCII); However, when I checked one of the articles I found an apostrophe had been converted into a question mark. (FWIW, the apostrophe was one of those offending non utf-8 characters): Before conversion: I stepped into the observatory’s control room ... After conversion: I stepped into the observatory?s control room... Is there a better way to accomplish my first goal, without reading each article and manually making the changes? (2) For the second goal, insuring that all future articles are utf-8, do I need to change the table structure or the insert query to insure I get the correct utf-8 characters into the database? Thanks, Mark
OT: SQL Question
My question is not specific to MySQL, even though I am using a MySQL db for this project. I have a servlet/jsp/MySQL web site in production, and there are about 2,000 records in the flights table. One of the foreign keys is teacher_id. Up to this point, there is a one to many relationship between teacher_id and the data in the flights table. I need to change the data model to allow for a many to many relationship between teacher_id and the data in the flight table. What is the best way to do this? Thanks, Mark
Re: Need Help Writing Simple Query
On Sun, Jul 25, 2010 at 8:49 PM, Nguyen Manh Cuong cuong.m...@vienthongso.com wrote: Hi Mark, Please test this query: select test1.*, (select name from test2 where test2.id=test1.`v_id` limit 1) as name_1, (select name from test2 where test2.id=test1.`h_id` limit 1) as name_2 from test1; - test1 table: col1v_idh_id America 1 2 - test2 table: id name 2 SAM 1 UNCLE - Original Message - From: Mark Phillips m...@phillipsmarketing.biz To: Mysql List mysql@lists.mysql.com Sent: Monday, July 26, 2010 8:29:00 AM Subject: Need Help Writing Simple Query I have been away from sql for awhile, and can't seem to figure out how to write a simple query for two tables. Table 1 has many columns, two of which are hID and vID. Table 2 has two columns, ID and name. The hID and vID in table 1 correspond to the IDs in table 2. I want to make a query so I get all the columns from table 1, but substitute the names from table 2 for the hID and vID values. For example, Table 1: col 1, col 2, hID, vID, col 3 AB1 2 C Table 2: ID, name 1fred 2sam Query result: col1, col 2, hName, vName, col 3 A Bfred sam C Thanks! Mark -- Best Regards, Cuongmc. -- Nguyen Manh Cuong Phong Ky Thuat - Cong ty Vien Thong So - VTC Dien thoai: 0912051542 Gmail : philipscu...@gmail.com YahooMail : philipscu...@yahoo.com Thanks! That did the trick. Mark
Need Help Writing Simple Query
I have been away from sql for awhile, and can't seem to figure out how to write a simple query for two tables. Table 1 has many columns, two of which are hID and vID. Table 2 has two columns, ID and name. The hID and vID in table 1 correspond to the IDs in table 2. I want to make a query so I get all the columns from table 1, but substitute the names from table 2 for the hID and vID values. For example, Table 1: col 1, col 2, hID, vID, col 3 AB1 2 C Table 2: ID, name 1fred 2sam Query result: col1, col 2, hName, vName, col 3 A Bfred sam C Thanks! Mark
Re: Questions on Database Design
Thanks to Martin and John for their help! Mark On Sat, Oct 3, 2009 at 5:53 PM, Martin Gainty mgai...@hotmail.com wrote: enforcing by username/password to the DB is your safest method and if you want to really be safe put ssh access onto the MySQL Server here is how to install SSH and MySQL onto Ubuntu http://ubuntuforums.org/showthread.php?t=388073 and to access SSHClient http://dev.mysql.com/doc/refman/5.1/en/windows-and-ssh.html HTH Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Sat, 3 Oct 2009 18:11:59 -0600 From: john.l.me...@gmail.com To: m...@phillipsmarketing.biz CC: mysql@lists.mysql.com Subject: Re: Questions on Database Design Mark Phillips wrote: On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty mgai...@hotmail.com wrote: depends on the relationship of the Data Tables and the Users that use them for instance if I was to setup a table of outgoing calls from 2 distinct individuals : Me calls to HarvardMedicalSchool, MassGeneral, SomervilleHospital and AMA VereinDesKrankRufscalls to Biff,Tony,EdSoprano and Destiny so as you can see the difference between my calls and Vereins calls should never be joined as Vereins customers are distinctly not mine and mine are not his Moreover my contact table would contain Degrees and titles where Vereins customers have no need for that So in this case it would make perfect sense for my Database to be separate and distinct from Vereins database..if for no other reason than the schemas are completely difference With an emphasis on security once Verein initiates populating his records on your DB by populating the same tables and using the same join relationships it will be impossible to force him to not use those tables or even to restrich his access to the slave server while you're updating the master You can restrict access by GRANT SELECT on the tables to Verein but that would last only a week or 2 until Verein requests update and insert access to the DB. Once the INSERT and UPDATE grants are made you wont be able to separate his records from yours Keep the 2 separate is my suggestion..MySQL is inexpensive and HW is cheap so this should be a low cost solution for you Keep us apprised and any feel free to inquire on any operational details you may require. Thanks! To make sure I understand. Even if the schemas are the same, if the data is not related, nor is meant to be combined in some way (eg rolled up or summed in some way), then creating a separate database for each user is a better way to go; or at least a meaningful way to go. A side benefit is greater security from the stand point that user a cannot get to user b's data. Can't I achieve the same level of security if each row has a userID, and all queries use a where userID=xxx clause? Mark no, don't confuse that with database security. There are too many ways to get around that sort of trick through SQL injection attacks. Read http://dev.mysql.com/doc/refman/5.4/en/privilege-system.html for a starter on privileges and security. But as long as you're not needing to regularly combine and aggregate the data then creating separate databases is a reasonable option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com -- Hotmail: Trusted email with powerful SPAM protection. Sign up now.http://clk.atdmt.com/GBL/go/177141665/direct/01/
Questions on Database Design
I am new at database design, and my question relates to the trade-offs between putting all data in one database or several for mysql. For example, say I have an application where a users login from their mobile phones and read/write data to a database. Say there are roughly 10-15 tables in the database and each user will add approximately 20,000 records per year. Each user should not have access to data from another user. Users have to register in some way to create their database in the first place. When does it make sense to give each user their own database versus putting all the data into one database (ie one set of tables) and with multiple userIDs? 10 users? 1,000 users? Never? Thanks! Mark
Re: Questions on Database Design
On Sat, Oct 3, 2009 at 2:47 PM, John Meyer john.l.me...@gmail.com wrote: Mark Phillips wrote: I am new at database design, and my question relates to the trade-offs between putting all data in one database or several for mysql. For example, say I have an application where a users login from their mobile phones and read/write data to a database. Say there are roughly 10-15 tables in the database and each user will add approximately 20,000 records per year. Each user should not have access to data from another user. Users have to register in some way to create their database in the first place. When does it make sense to give each user their own database versus putting all the data into one database (ie one set of tables) and with multiple userIDs? 10 users? 1,000 users? Never? It's not so much how many users you have (though that may be a question of data storage more than databases) as to what are they doing? Are the actions related? If they are, then have one database with each user having access to their records and their records only, which can easily be done with terms of database security.. John, Thanks. The data is private to each user; there is no sharing of data. I am not sure what you mean by are the actions related Each user is reading/writing independently of each other. Would that argue for separate databases? Mark
Re: Questions on Database Design
On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty mgai...@hotmail.com wrote: depends on the relationship of the Data Tables and the Users that use them for instance if I was to setup a table of outgoing calls from 2 distinct individuals : Me calls to HarvardMedicalSchool, MassGeneral, SomervilleHospital and AMA VereinDesKrankRufscalls to Biff,Tony,EdSoprano and Destiny so as you can see the difference between my calls and Vereins calls should never be joined as Vereins customers are distinctly not mine and mine are not his Moreover my contact table would contain Degrees and titles where Vereins customers have no need for that So in this case it would make perfect sense for my Database to be separate and distinct from Vereins database..if for no other reason than the schemas are completely difference With an emphasis on security once Verein initiates populating his records on your DB by populating the same tables and using the same join relationships it will be impossible to force him to not use those tables or even to restrich his access to the slave server while you're updating the master You can restrict access by GRANT SELECT on the tables to Verein but that would last only a week or 2 until Verein requests update and insert access to the DB. Once the INSERT and UPDATE grants are made you wont be able to separate his records from yours Keep the 2 separate is my suggestion..MySQL is inexpensive and HW is cheap so this should be a low cost solution for you Keep us apprised and any feel free to inquire on any operational details you may require. Thanks! To make sure I understand. Even if the schemas are the same, if the data is not related, nor is meant to be combined in some way (eg rolled up or summed in some way), then creating a separate database for each user is a better way to go; or at least a meaningful way to go. A side benefit is greater security from the stand point that user a cannot get to user b's data. Can't I achieve the same level of security if each row has a userID, and all queries use a where userID=xxx clause? Mark Date: Sat, 3 Oct 2009 14:38:25 -0700 Subject: Questions on Database Design From: To: mysql@lists.mysql.com I am new at database design, and my question relates to the trade-offs between putting all data in one database or several for mysql. For example, say I have an application where a users login from their mobile phones and read/write data to a database. Say there are roughly 10-15 tables in the database and each user will add approximately 20,000 records per year. Each user should not have access to data from another user. Users have to register in some way to create their database in the first place. When does it make sense to give each user their own database versus putting all the data into one database (ie one set of tables) and with multiple userIDs? 10 users? 1,000 users? Never? Thanks! Mark -- Hotmail: Free, trusted and rich email service. Get it now.http://clk.atdmt.com/GBL/go/171222984/direct/01/
Re: Questions on Database Design
On Sat, Oct 3, 2009 at 4:02 PM, John Meyer john.l.me...@gmail.com wrote: John, Thanks. The data is private to each user; there is no sharing of data. I am not sure what you mean by are the actions related Each user is reading/writing independently of each other. Would that argue for separate databases? Mark Are the actions of a similar nature (i.e. they're all writing the same type of data and the databases themselves would be similar if not the same)? Each user will write the same type of data to the same schema. So the databases schemas would be identical. Is there any sort of application that would traverse all of those databases at once? Not really necessary from the user's perspective. Also keep in mind that multiple databases increases your complexity. I think we'd have a better idea if we knew a little more of the specifics of this application. Sure, no great military secrets here. The application is a mobile softball (baseball, basketball, soccer, etc.) score book. The data for each pitch (softball = pitch type, who made what play, what the batter did, errors, etc.) is entered on the cell phone, and stored in MySQL tables in order to create game and season stats for a team and each player. This can also apply to other sports. Each user is a team manager or scorekeeper. There really isn't any need for team A to see/access team B's stats. A league may want to do a special type of roll-up, but this app is really just for each team. I am sure an app could be written to do the roll-up, but that is not the main focus. I think by your discussion, it may make sense to have separate databases for each user instead of add a userID column to many of the tables to separate each user's data from the other users. Does that make sense? Mark
Need help with a query
I am running mysql 4.0.24 on Debian sarge. I have a table with two columns, team and division, both varchar(255). There are some errors in the table where division has a value but team is blank. Given that I am getting new data, and the data entry folks may create a record with a blank division and a team, I thought I would avoid any issues with team or division being blank as follows: SELECT DISTINCT division, team FROM registered WHERE team!='' OR DIVISION!='' That returns what I expected - all division-team fields have data Then I decided to order the output, so I added an ORDER BY clause and some parenthesis to make the sql more readable: SELECT DISTINCT division, team FROM registered WHERE (team!='' OR DIVISION!='') ORDER BY division, team But, I still get records with a blank team field (even if I remove the parenthesis). I finally found a solution, but I do not understand it: SELECT DISTINCT division, team FROM registered WHERE (team!='' AND DIVISION!='') ORDER BY division, team Why does the ORDER BY clause require an AND in the WHERE clause to work correctly?? I do not understand the logic. Thanks for any insight you can share with me. -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query
On Monday 23 January 2006 03:33 pm, Michael Stassen wrote: Mark Phillips wrote: I am running mysql 4.0.24 on Debian sarge. I have a table with two columns, team and division, both varchar(255). There are some errors in the table where division has a value but team is blank. Given that I am getting new data, and the data entry folks may create a record with a blank division and a team, I thought I would avoid any issues with team or division being blank as follows: You should change your app to enforce your rules. That is, your app should prevent your data entry folks from entering incomplete records. Otherwise, it's garbage in, garbage out. You are absolutely correct. However, it is not my app nor do I control how the data is input into it. I just get a dump of the data to work with. :-( SELECT DISTINCT division, team FROM registered WHERE team!='' OR DIVISION!='' That returns what I expected - all division-team fields have data I doubt it. You've joined your two conditions with OR, so your WHERE condition will be true for any row with at least one of the two conditions met. Only a row with *both* fields blank would be excluded. Remember, NOT(A OR B) = NOT(A) AND NOT(B) so you should have used AND. You see? A row you don't want has team = '' OR DIVISION = '' so a row you do want has NOT(team = '' OR DIVISION = '') which is equivalent to team != '' AND DIVISION != '' I feel so stupid. Not sure why I missed that - it is so basic! Well, that is my first Home Simpson of the week Doh! Thanks ! Then I decided to order the output, so I added an ORDER BY clause and some parentheses to make the sql more readable: SELECT DISTINCT division, team FROM registered WHERE (team!='' OR DIVISION!='') ORDER BY division, team The parentheses are irrelevant. The ORDER BY cannot have changed which rows were returned. Perhaps the ordering facilitated noticing the unwanted results. But, I still get records with a blank team field (even if I remove the parenthesis). I finally found a solution, but I do not understand it: SELECT DISTINCT division, team FROM registered WHERE (team!='' AND DIVISION!='') ORDER BY division, team As I explained above. Why does the ORDER BY clause require an AND in the WHERE clause to work correctly?? I do not understand the logic. ORDER BY has nothing to do with it. Thanks for any insight you can share with me. Michael -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question regarding running 'mysql' from a bash script
There is a column in my table with string values, but the strings have spaces in them. I want to read the unique column values into an array in my bash script, so I can use these values in a separate query on this table. How do I get the values into an array? For example, the column teams in my table | team |. Red Sox Chicago Colleens Athletics Kenosha Comets Red Sox and I want to create the equivalent statement, but using what I find in the table declare -a TEAMS=(Red Sox Chicago Colleens Athletics Kenosha Comets) I tried the following: declare -a TEAMS=(`mysql -u $USER -h $HOST --password=$PASSWORD -D $DATABASE -B --disable-column-names --exec select distinct team from registered order by team`) but I get the following array (using the data above) (Red Sox Chicago Colleens Athletics Kenosha Comets) How do I either (1) add quotes around each row entry returned so I get the right array, or (2) fill the array in a different way? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question regarding running 'mysql' from a bash script
Oops - I forgot my version of mysql - 4.0.22 running on Red Hat Linux 7.3 2.96-113 kernel 2.4.20-30.7. Mark On Thursday 12 January 2006 10:43 am, Mark Phillips wrote: There is a column in my table with string values, but the strings have spaces in them. I want to read the unique column values into an array in my bash script, so I can use these values in a separate query on this table. How do I get the values into an array? For example, the column teams in my table | team |. Red Sox Chicago Colleens Athletics Kenosha Comets Red Sox and I want to create the equivalent statement, but using what I find in the table declare -a TEAMS=(Red Sox Chicago Colleens Athletics Kenosha Comets) I tried the following: declare -a TEAMS=(`mysql -u $USER -h $HOST --password=$PASSWORD -D $DATABASE -B --disable-column-names --exec select distinct team from registered order by team`) but I get the following array (using the data above) (Red Sox Chicago Colleens Athletics Kenosha Comets) How do I either (1) add quotes around each row entry returned so I get the right array, or (2) fill the array in a different way? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question regarding running 'mysql' from a bash script
On Thursday 12 January 2006 11:40 am, George Law wrote:
Mark,
Sql is an alias to mysq -u.
What about something like :
declare -a TEAMS=(`echo query|sql|sed 's/$/,/g'|sed 's/^//g'|sed
's/$//'`)
since the query returns the results 1 per line, the first sed prefixes
each line with a quote
second sed replaces the newline with quote comma, turning it into 1 line
string. Last sed drops off the last quote
I gave this command and bash didn't complain :-) so I assume it worked.
I actually ended up with something similar:
declare TEAM_QUERY=`mysql ${ACCESS_PARAMS} --exec select distinct team from
registered order by team`
declare -a TEAMS=(`echo ${TEAM_QUERY}|sed 's/^//g'|sed 's/$//g'`)
Commas are not used in the definition of an array - just spaces between quoted
strings.
Thanks for your help!
-Original Message-
From: Mark Phillips [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 12, 2006 12:44 PM
To: MYSQL List
Subject: Question regarding running 'mysql' from a bash script
There is a column in my table with string values, but the strings have
spaces
in them. I want to read the unique column values into an array in my
bash
script, so I can use these values in a separate query on this table. How
do I
get the values into an array?
For example, the column teams in my table
| team |.
Red Sox
Chicago Colleens
Athletics
Kenosha Comets
Red Sox
and I want to create the equivalent statement, but using what I find in
the
table
declare -a TEAMS=(Red Sox Chicago Colleens Athletics Kenosha
Comets)
I tried the following:
declare -a TEAMS=(`mysql -u $USER -h $HOST --password=$PASSWORD -D
$DATABASE -B --disable-column-names --exec select distinct team from
registered order by team`)
but I get the following array (using the data above)
(Red Sox Chicago Colleens Athletics Kenosha Comets)
How do I either (1) add quotes around each row entry returned so I get
the
right array, or (2) fill the array in a different way?
Thanks!
--
Mark Phillips
Phillips Marketing, Inc
[EMAIL PROTECTED]
602 524-0376
480 945-9197 fax
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Mark Phillips
VP Softball
Arcadia Little League
www.ArcadiaLittleLeague.org
[EMAIL PROTECTED]
602 524-0376
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question regarding running 'mysql' from a bash script
On Thursday 12 January 2006 12:47 pm, Jake Peavy wrote:
On 1/12/06, Mark Phillips [EMAIL PROTECTED] wrote:
There is a column in my table with string values, but the strings have
spaces
in them. I want to read the unique column values into an array in my bash
script, so I can use these values in a separate query on this table. How
do I
get the values into an array?
For example, the column teams in my table
| team |.
Red Sox
Chicago Colleens
Athletics
Kenosha Comets
Red Sox
snip
but I get the following array (using the data above)
(Red Sox Chicago Colleens Athletics Kenosha Comets)
How do I either (1) add quotes around each row entry returned so I get the
right array, or (2) fill the array in a different way?
Thanks!
Use the bash internal variable IFS, Mark:
OLDIFS=$IFS
IFS=$'\n'
for team in `mysql --skip-column-names -B -e SELECT team FROM teamnames;
do
echo [$team];
done
IFS=$OLDIFS
I actually ended up with this:
declare TEAM_QUERY=`mysql ${ACCESS_PARAMS} --exec select distinct team from
registered order by team`
declare -a TEAMS=(`echo ${TEAM_QUERY}|sed 's/^//g'|sed 's/$//g'`)
Thanks for your help!
enjoy,
-jp
--
Mark Phillips
VP Softball
Arcadia Little League
www.ArcadiaLittleLeague.org
[EMAIL PROTECTED]
602 524-0376
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Connecting
Here are the results of alias and type [EMAIL PROTECTED]:~$ alias alias ls='ls --color=auto' [EMAIL PROTECTED]:~$ type mysql mysql is /usr/bin/mysql And for the emily account: [EMAIL PROTECTED]:/home/mark$ alias alias ls='ls --color=auto' [EMAIL PROTECTED]:/home/mark$ type mysql mysql is /usr/bin/mysql The error from my PATH is from how I used it. I left out the 'echo' command. I get the same results in the emily account [EMAIL PROTECTED]:/home/mark$ $PATH bash: /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games: No such file or directory [EMAIL PROTECTED]:/home/mark$ echo $PATH /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games However, Michael hit the nail on the head! I have a .my.cnf file that was causing the problem. I had the line usermark which should be user=mark That also explains the funny error message I was getting. Thanks for all your help, everything is now working!! Mark On Thursday 22 December 2005 06:28 am, Michael Stassen wrote: Mark Phillips wrote: David, This is what I got: [EMAIL PROTECTED]:~$ aliases bash: aliases: command not found Your shell is bash, so the correct command is `alias`. [EMAIL PROTECTED]:~$ which mysql /usr/bin/mysql Since you are using bash, it's a better idea to use `type` instead of `which`. On some systems (Solaris 7, for example), `which` can give bogus results in bash. So, try type mysql [EMAIL PROTECTED]:~$ $PATH bash: /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games: No such file or directory [EMAIL PROTECTED]:~$ Something is wrong with mark's PATH. See the error at the end? I don't have a command 'aliases', but the other tests seem to say all I have is mysql running as mysql. When I am logged in as 'emily' I get: [EMAIL PROTECTED]:/home/mark$ which mysql /usr/bin/mysql [EMAIL PROTECTED]:/home/mark$ echo $PATH /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games [EMAIL PROTECTED]:/home/mark$ Any other thoughts? The error message from mysql when I try to log in is strange. Why all the spaces? mysql: unknown option '--user mark' I don't believe that error came from mysql. Indeed, mysql doesn't care which unix user runs it, it only cares which mysql user you say you are. Because it works as expected when Emily runs it, I don't believe mysql is the problem. I believe David is correct that there is something in mark's environment that is causing the problem. Check the output of `alias` and `type mysql`, and check mark's .my.cnf file, if he has one. Thanks! Mark Michael -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Help Connecting
I have the following setup - mysql 4.0.24 running on Debian Linux stable. I set up a user 'mark' with a password. When I log into my Linux box as user 'mark', I cannot connect to mysql - I get this funny error message: [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p mysql: unknown option '--user mark' [EMAIL PROTECTED]:~$ If I su to another user, I can login as mark to mysql [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 128 to server version: 4.0.24_Debian-10sarge1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql I don't get it - why can't I use the '-u mark' option with mysql when I am logged into my own Linux user account? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Connecting
David, How do I do that? Thanks! Mark On Wednesday 21 December 2005 11:37 pm, Logan, David (SST - Adelaide) wrote: Hi Mark, Have you checked to see if you any aliases set? It might be using that instead of the mysql command. May well be worth checking your path to ensure you aren't picking up a script called mysql or something similar. 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: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 5:02 PM To: MYSQL List Subject: Need Help Connecting I have the following setup - mysql 4.0.24 running on Debian Linux stable. I set up a user 'mark' with a password. When I log into my Linux box as user 'mark', I cannot connect to mysql - I get this funny error message: [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p mysql: unknown option '--user mark' [EMAIL PROTECTED]:~$ If I su to another user, I can login as mark to mysql [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 128 to server version: 4.0.24_Debian-10sarge1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql I don't get it - why can't I use the '-u mark' option with mysql when I am logged into my own Linux user account? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Connecting
David, This is what I got: [EMAIL PROTECTED]:~$ aliases bash: aliases: command not found [EMAIL PROTECTED]:~$ which mysql /usr/bin/mysql [EMAIL PROTECTED]:~$ $PATH bash: /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games: No such file or directory [EMAIL PROTECTED]:~$ I don't have a command 'aliases', but the other tests seem to say all I have is mysql running as mysql. When I am logged in as 'emily' I get: [EMAIL PROTECTED]:/home/mark$ which mysql /usr/bin/mysql [EMAIL PROTECTED]:/home/mark$ echo $PATH /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games [EMAIL PROTECTED]:/home/mark$ Any other thoughts? The error message from mysql when I try to log in is strange. Why all the spaces? mysql: unknown option '--user mark' Thanks! Mark On Wednesday 21 December 2005 11:55 pm, Logan, David (SST - Adelaide) wrote: Try typing at the command prompt $ aliases $ which mysql $ echo $PATH The first one will list any aliases that have been setup. The second will tell you the directory the system thinks it is getting mysql from and the third will list your PATH environment variable. If you see an alias named mysql, that may well be your culprit. If you installed from an rpm (I don't know much about the debian apt-get thingy) you should find the mysql command in /usr/bin 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: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 5:21 PM To: mysql@lists.mysql.com Cc: Logan, David (SST - Adelaide) Subject: Re: Need Help Connecting David, How do I do that? Thanks! Mark On Wednesday 21 December 2005 11:37 pm, Logan, David (SST - Adelaide) wrote: Hi Mark, Have you checked to see if you any aliases set? It might be using that instead of the mysql command. May well be worth checking your path to ensure you aren't picking up a script called mysql or something similar. 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: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 5:02 PM To: MYSQL List Subject: Need Help Connecting I have the following setup - mysql 4.0.24 running on Debian Linux stable. I set up a user 'mark' with a password. When I log into my Linux box as user 'mark', I cannot connect to mysql - I get this funny error message: [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p mysql: unknown option '--user mark' [EMAIL PROTECTED]:~$ If I su to another user, I can login as mark to mysql [EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 128 to server version: 4.0.24_Debian-10sarge1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql I don't get it - why can't I use the '-u mark' option with mysql when I am logged into my own Linux user account? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
YAQQ (Yet Another Query Question)
I am using MySQL 4.0.x on a Linux machine with a JSP/Servlet front-end to display the data. I have a table with experimental data for each flight of a rocket. Conceptually, it looks like (with many more columns): Flights +---+--+--+ | flight_id | data1_id | data2_id | +---+--+--+ | 1 |1 |1 | | 2 |1 |3 | | 3 |1 |1 | | 4 |2 |2 | | 5 |2 |3 | | 6 |1 |1 | | 7 |1 |1 | | 8 |4 |4 | | 9 |1 |2 | |10 |1 |2 | |11 |1 |1 | +---+--+--+ The data1_id and data2_id are indexes for the data recorded for that flight. I want to summarize the data. One such summary is to count the number of different data1_id's and data2_id's. For example: Flight Result Summary index: 1 2 3 4 data1_id8 2 0 1 data2_id5 3 2 1 I can think of 2 ways to make this summary table. 1. Issue 4 queries per data_id of the form SELECT COUNT(flight_id) FROM Flights WHERE data1_id=** where ** is set to the values 1,2,3,4. For the table above, I would have to issue a total of 8 queries. 2. Issue one query of the form SELECT flight_id FROM Flights and do the counting in my Java code. A simple loop through the ResultSet could count the different values for the data_ids. My questions are: 1. Is there a better way than these two options for getting the data I want? A single query per data_id? 2. Generally, what is the most efficient way to do this? Is is better to issue more queries that gather the calculated data or better to issue one query for the raw data and then do the calculations in Java? I am sure there are many factors that effect the answer to this question - server resources, code design, etc. However, I am interested in a best practices type of answer or general rule of thumb from the sage experts on the list. Thanks for any insights you can provide! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: YAQQ (Yet Another Query Question)
Nigel, Thanks! Mark On Wednesday 14 December 2005 09:42 am, nigel wood wrote: Mark Phillips wrote: Flights +---+--+--+ | flight_id | data1_id | data2_id | +---+--+--+ | 1 |1 |1 | | 2 |1 |3 | | 3 |1 |1 | | 4 |2 |2 | | 5 |2 |3 | | 6 |1 |1 | | 7 |1 |1 | | 8 |4 |4 | | 9 |1 |2 | |10 |1 |2 | |11 |1 |1 | +---+--+--+ The data1_id and data2_id are indexes for the data recorded for that flight. I want to summarize the data. One such summary is to count the number of different data1_id's and data2_id's. For example: Flight Result Summary index: 1 2 3 4 data1_id 8 2 0 1 data2_id 5 3 2 1 select sum(if(data1_id =1,1, 0)) as data1_id_1, sum(if(data1_id =2, 1, 0)) as data1_id_2, etc , etc sum(if(data2_id =1,1, 0)) as data2_id_1, sum(if(data2_id =2, 1, 0)) as data2_id_2 etc, etc from flights add composite indexes if required for speed. Nigel -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: YAQQ (Yet Another Query Question)
Nigel, Again, thanks - that is the rule of thumb I was looking for! Mark On Wednesday 14 December 2005 09:57 am, nigel wood wrote: Mark Phillips wrote: 2. Generally, what is the most efficient way to do this? Is is better to issue more queries that gather the calculated data or better to issue one query for the raw data and then do the calculations in Java? I am sure there are many factors that effect the answer to this question - server resources, code design, etc. However, I am interested in a best practices type of answer or general rule of thumb from the sage experts on the list. Sorry only just spotted the second half. Processing in MySQL will be faster than pulling the dataset back and processing it. This is particularly true if the database server is remote from the servlet container. The chief reason is that processing it on the client add the time needed to copy the raw data over the network. In Java or C.* data processing performance can be on a par with MySQL once the data is obtained, against an interpreted language such as PHP or Perl the database's performance will always win hands down even if temporary tables are needed. If the rocket data doesn't change rapidly the MySQL query cache will also improve preformance. This feature speeds things by remembering the answer to your query and replying with a cached version until the rockets table is next updated. Nigel -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: YAQQ (Yet Another Query Question)
Thanks to everyone for their help. Using Nigel's suggestion, I was able to gather all the summary data in one query. Those nested if()'s are really useful! FWIW, you can see the summary stats at http://rockets.phillipsoasis.com Just click on Hopi Rockets and scroll to the bottom of the page. My small contribution to science education! This list is great! Mark On Wednesday 14 December 2005 09:42 am, nigel wood wrote: Mark Phillips wrote: Flights +---+--+--+ | flight_id | data1_id | data2_id | +---+--+--+ | 1 |1 |1 | | 2 |1 |3 | | 3 |1 |1 | | 4 |2 |2 | | 5 |2 |3 | | 6 |1 |1 | | 7 |1 |1 | | 8 |4 |4 | | 9 |1 |2 | |10 |1 |2 | |11 |1 |1 | +---+--+--+ The data1_id and data2_id are indexes for the data recorded for that flight. I want to summarize the data. One such summary is to count the number of different data1_id's and data2_id's. For example: Flight Result Summary index: 1 2 3 4 data1_id 8 2 0 1 data2_id 5 3 2 1 select sum(if(data1_id =1,1, 0)) as data1_id_1, sum(if(data1_id =2, 1, 0)) as data1_id_2, etc , etc sum(if(data2_id =1,1, 0)) as data2_id_1, sum(if(data2_id =2, 1, 0)) as data2_id_2 etc, etc from flights add composite indexes if required for speed. Nigel -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Help with a query
I have a table with several columns. The ones of interest are flight_id, angle, and baseline. I want to find the flight_ids for the flights with the maximum and minimum altitudes, where altitude=baseline*tan(radians(angle)). For example, Flights +++---+ | flight_id | angle| baseline | +++---+ | 1 | 37.0 | 100.0 | | 2 | 50.0 | 100.0 | | 3 | 48.0 | 100.0 | | 4 | 40.0 | 100.0 | | 5 | 44.0 | 100.0 | | 6 | 40.0 | 100.0 | | 7 | 45.0 | 100.0 | | 8 | 44.0 | 75.0 | | 9 | 57.8 | 75.0 | +++---+ The result I am looking for are: Maximum altitude: +++ | flight_id | altitude | +++ | 2 | 119.17536 | | 9 | 119.17536| +++ Minimum altitude: +---+---+ | flight_id | altitudeM | +---+---+ | 8 | 72.42666 | +---+---+ Thanks for any help you can provide! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Re: Need Help with a query
I forgot to copy the list as well Mark -- Forwarded Message -- Subject: Re: Need Help with a query Date: Sunday 11 December 2005 06:47 pm From: Mark Phillips [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Rhino, My apologies for leaving out the version of mysql. I agree 1000% with your rant - it was an oversight on my part. I have mysql 4.0.24 on my development machine and 4.1.11 on my production machine. Thanks for the query - it works very well in 4.1.11. I think in 4.0.24 I need to use a temporary table. Thanks again! Mark On Sunday 11 December 2005 04:19 pm, you wrote: - Original Message - From: Mark Phillips [EMAIL PROTECTED] To: MYSQL List mysql@lists.mysql.com Sent: Sunday, December 11, 2005 12:38 PM Subject: Need Help with a query I have a table with several columns. The ones of interest are flight_id, angle, and baseline. I want to find the flight_ids for the flights with the maximum and minimum altitudes, where altitude=baseline*tan(radians(angle)). For example, Flights +++---+ | flight_id | angle| baseline | +++---+ | 1 | 37.0 | 100.0 | | 2 | 50.0 | 100.0 | | 3 | 48.0 | 100.0 | | 4 | 40.0 | 100.0 | | 5 | 44.0 | 100.0 | | 6 | 40.0 | 100.0 | | 7 | 45.0 | 100.0 | | 8 | 44.0 | 75.0 | | 9 | 57.8 | 75.0 | +++---+ The result I am looking for are: Maximum altitude: +++ | flight_id | altitude | +++ | 2 | 119.17536 | | 9 | 119.17536| +++ Minimum altitude: +---+---+ | flight_id | altitudeM | +---+---+ | 8 | 72.42666 | +---+---+ Thanks for any help you can provide! I do wish posters to this list would get in the habit of volunteering which version of MySQL they are using, particularly for SQL questions! The answer to almost every SQL question is it depends on which version of MySQL you are using. It's very tedious to give the answer for every version MySQL, as in: If you're using Version 3.x, the answer is A. If you're using Version 4.0.x the answer is B. If you're using Version 4.1.x, the answer is C. etc. [By the way, I don't mean to single you out with this mini-rant; it's just a general observation.] Therefore, I'm going to assume you are using Version 4.1 or higher; in other words, you use a version which supports subqueries. If you are on an earlier version, please reply to the list and explain which version you are on. Perhaps someone will be willing to show you alternatives that will work for you. I should also explain that I am _not_ on a version of MySQL which supports subqueries. However, my main database is DB2 which does support subqueries and the SQL used by DB2 and MySQL is very very similar so this _untested_ answer should be pretty close to what you need. I think the best answer to your question is to use subqueries. I'm going to express the answer in pseudocode first to give you a general sense of the answer, then give you something that should be pretty close to a final answer that will work on your system. Pseudocode (for maximum altitude): select flight_id, baseline*tan(radians(angle)) as max_altitude from Flights where baseline*tan(radians(angle)) in (subquery that gets largest altitude from table) In real SQL, that should end up looking like this: select flight_id, baseline*tan(radians(angle)) as max_altitude from Flights where baseline*tan(radians(angle)) in (select max(baseline*tan(radians(angle))) from Flights) To get the query for the minimum altitude, use the exact same query except replace the max function with the min function in the subquery and change the 'as' for the outer query from 'max_altitude' to 'min_altitude'. For what it's worth, I got slightly different numbers in DB2 so I did not have a tie for maximum altitude: my maximum altitude was for flight 2 at 119.17535 (versus 119.09797 for flight 9). Perhaps the tan() or radians() functions in DB2 are slightly more precise? The 'in' that introduces the subqueries could potentially be replaced by '=' but 'in' is safer. If you use 'in' and there is more than one flight tied for the highest altitude, the query works fine. If you use '=' and there is more than one flight tied for the highest altitude, the query will almost certainly fail - it does in DB2! - because '=' implies that only one row in the outer query can have the maximum altitude; therefore the query fails if more than one row matches. The most tedious part of these queries is typing the 'baseline*tan
