Re: Moving Database from PC to Apple
Hi David mysqldump is a command-line program which you can run through the terminal window. The Terminal application is in the Utilities directory under the Applications directory. The unix command-line interface is amazingly useful; even though it may seem a bit intimidating at first, it is well worth getting used to. You will probably want to drag the Terminal application down to the Dock so you won't have to dig around for it every time. (Or you can just open the Spotlight window with Command-Space and then type Terminal) You can also do the same thing with a gui tool, the MySQL administrator. There are three programs in this suite and they are all very nice (the newest one, the MySQL workbench, still crashes a bit but I'm sure that will improve.) You can download the gui tools here: http://dev.mysql.com/downloads/gui-tools/5.0.html and then from the MySQL Administrator program, choose Backup. Good luck. I use a MacBook Pro for MySQL work also (mostly developing things that will run on a linux server) and I have been very pleased with it. Douglas Sims [EMAIL PROTECTED] On Oct 7, 2006, at 5:01 PM, David Blomstrom wrote: Thanks. Is this something I can do through phpMyAdmin? I'm not used to working with MySQL directly and don't understand exactly what this command means: shell mysqldump [options] --all-databasesDoes shell mean I have to be working in some sort of command line program? Also, if I can't figure this out and have to resort to creating new databases, should I just ignore the Collation feature, presumably letting it set a default setting, or should I enter a particular value? Thanks. - Original Message From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, October 7, 2006 2:26:19 PM Subject: Re: Moving Database from PC to Apple At 04:00 PM 10/7/2006, you wrote: I recently purchased a MacBook Pro laptop and hired someone to help me set up Apache, PHP and MySQL on it. Now I want to import my database tables from my PC. So my main question is this: Is there a quick, simple of importing an entire database? If not, I figured I'd simply export each database table as an SQL file on my PC, then copy all the SQL files to my laptop's desktop and import them through phpMyAdmin one by one. Also, when I create a new database on my Mac, what should I choose for Collation - or should I just leave it alone (presumably the default setting)? The following default settings are already registered: Language: English (en_utf_8) MySQL Connection Collation: utf8_general_ci Thanks. David, Try MySQLDump which is set up to do just this. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making varchar field to act like numeric field
You can use CAST or CONVERT to see the data as a numeric type.
If the table is very big and you're going to be querying it
intensely, you might want to create a separate column to store the
numeric data.
mysql select cast('34' AS decimal);
+---+
| cast('34' AS decimal) |
+---+
| 34.00 |
+---+
1 row in set (0.00 sec)
mysql select cast('hi' AS decimal);
+---+
| cast('hi' AS decimal) |
+---+
| 0.00 |
+---+
1 row in set, 1 warning (0.00 sec)
Douglas Sims
[EMAIL PROTECTED]
On Sep 27, 2006, at 10:24 PM, [EMAIL PROTECTED] wrote:
I am looking for any suggestions to this problem. I have a table
with a
varchar field. This field can hold textual or numeric data, but it is
stored in a varchar field so the database sees it all as text.
I need to be able to search and sort this field as if it were numeric.
For example, here is some sample data
2.5
4
2
6
7
6.2
3.4
6
I need to be able query the table to get the rows within a certain
range,
for example, between 4 and 7:
select * from table where field1=4 and field1=7
This doesn't work because the column is not a numeric data type.
Is there
anyway to dynamically cast the data to a numeric format so I can use
MySQL's numeric sorting?
I can't change the field's data type because it also needs to be
able to
hold textual data. Thank you for your help.
--
Steve Musumeche
CIO, Internet Retail Connection
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
Neat-o. I think that's much better than the query I proposed with the subselect. However, it doesn't give you price from the last sale of the product, instead it gives you highest price the product was sold for. Also, it can give you multiple rows for each product_code if there are multiple sales at the same price. Here is a small modification to Peter's query which will give you exactly one row for each product code showing the price at the last sale of that product. (Assuming you have a synthetic key, perhaps an autoincrement field, called id) Also, an index on the product_code field will help the speed of this query a lot. (I don't understand why the subselect query is still faster - I don't think it should be.) SQL is rather fun. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND (t1.date_sold t2.date_sold OR (t1.date_sold=t2.date_sold AND t1.idt2.id) WHERE t2.product_code IS NULL ORDER BY t1.product_code; Douglas Sims [EMAIL PROTECTED] On Sep 28, 2006, at 10:12 AM, Peter Brawley wrote: Mike, What I need to do is find the last price_sold for each product_code. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND t1.price_sold t2.price_sold WHERE t2.product_code IS NULL ORDER BY t1.product_code; There's a bit of discussion at http://www.artfulsoftware.com/ queries.php#7/ PB - mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count of children
By default it doesn't, but you can change that behaviour. Quoting from this page http://dev.mysql.com/doc/refman/5.0/en/stored- procedures.html : Recursive stored procedures are disabled by default, but can be enabled on the server by setting the max_sp_recursion_depth server system variable to a nonzero value. See Section 5.2.3, “System Variables”, for more information. There is also a very thorough article discussing stored procedures in MySQL which gives an example of tree traversal here: http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html Douglas Sims [EMAIL PROTECTED] On Sep 27, 2006, at 9:40 AM, João Cândido de Souza Neto wrote: You must do that by a some language script, unfortunatly mysql is no recursive. André Hänsel [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Sorry, my english sentence was imprecise. :) I want the count of all sub-entries AND THEIR sub-sub-entries. Example: A / \ B C / \ D E \ F So I want to know that C has 3 sub-nodes. -Ursprüngliche Nachricht- Von: Rob Desbois [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 27. September 2006 15:48 An: André Hänsel; mysql@lists.mysql.com Betreff: re: Count of children André, Your sentence 'I want the count of all sub-entries for a specific entry' converts straight into SQL: 'I want' SELECT the count of all entries COUNT(*) FROM myTable with a specific parent WHERE parent_id = 5 You've missed one of the major benefits of SQL - it's designed to read like an English sentence! --Rob _ _ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email _ _ -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where url = 'x' with url a TEXT field
I think you have to specify a key length when you use an index on a text field... mysql alter table t2 add index i2(t1(3)); That would create an index (called i2) on the first 3 characters of field t1 of table t2. I think that's right? Douglas Sims [EMAIL PROTECTED] On Sep 27, 2006, at 8:53 PM, Peter Van Dijck wrote: Hi, since urls can be longer than 255 chars, I made the url field a TEXT field. The problem is, I can't make an index on it, so doing 'WHERE url='xxx'' becomes a very sloow query. Any ideas for solutions? Am I mistaken in the idea that I can't make an index on a TEXT field? Thanks! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
You could do something like that by using a derived table (subselect) to pick the max date for each product sale and then joining that on the products table again to pick up the amounts. Note that you must use mysql 5.x to be able to use subselects. Also this will return multiple rows for a given account if there are more than one sale on the last day each item has been sold. (There are several ways around that if it's a problem). I just ran such a query on the table I'm working on right now to test the idea... there are about 3000 rows in this table so the 0.52 second query time seems high, but there's only a primary key index. I think indexing on the transaction date (date_xact) would probably help in this example... mysql select t1a.account, maxdate, amount from (select account, max (date_xact) maxdate from transactions t1 group by account) t1a left join transactions t2 on t1a.account=t2.account and maxdate=t2.date_xact order by t1a.account; +-++---+ | account | maxdate| amount| +-++---+ | 0 | 2005-08-17 | -15.06| | 1 | 2006-07-24 | 26790.00 | | 2 | 2006-07-14 | 1500.00 | | 2 | 2006-07-14 | 2040.00 | | 2 | 2006-07-14 | 2520.00 | | 3 | 2006-07-14 | -193.98 | | 3 | 2006-07-14 | -328.98 | | 3 | 2006-07-14 | -418.21 | | 4 | 2006-07-14 | -186.00 | | 4 | 2006-07-14 | -252.96 | | 4 | 2006-07-14 | -312.48 | | 5 | 2006-07-14 | -43.50| | 5 | 2006-07-14 | -59.16| | 5 | 2006-07-14 | -73.08| | 9 | 2006-06-27 | 60.06 | | 9 | 2006-06-27 | 196.77| | 10 | 2006-03-27 | 60.04 | | 11 | 2006-04-13 | 65.00 | | 12 | 2006-06-23 | -272.03 | | 13 | 2006-02-16 | 100.00| | 14 | 2006-07-14 | 114.75| | 14 | 2006-07-14 | 156.06| | 14 | 2006-07-14 | 192.78| | 15 | 2006-07-24 | -18240.00 | | 15 | 2006-07-24 | -8550.00 | | 16 | 2006-07-11 | -800.00 | | 17 | 2004-07-07 | -51.87| | 17 | 2004-07-07 | -50.49| | 17 | 2004-07-07 | -27.31| | 18 | 2006-06-01 | 288.77| | 19 | 2006-05-11 | 175.00| | 20 | 2006-01-05 | 50.00 | | 21 | 2006-07-15 | 152.90| | 22 | 2006-07-19 | -600.00 | | 23 | 2006-05-31 | 10.00 | | 24 | 2005-07-29 | -277.83 | | 25 | 2005-11-08 | -178.00 | | 26 | 2006-03-24 | 94.24 | | 26 | 2006-03-24 | 74.40 | | 26 | 2006-03-24 | 248.00| | 27 | 2006-03-24 | 22.04 | | 27 | 2006-03-24 | 17.40 | | 27 | 2006-03-24 | 58.00 | | 28 | 2006-06-07 | 185.00| | 29 | 2006-03-27 | 136.00| | 30 | 2006-07-18 | 398.16| | 31 | 2006-02-04 | 500.00| | 32 | 2006-04-06 | 64.00 | | 35 | 2006-04-15 | 1000.00 | | 37 | 2005-12-23 | 200.00| | 38 | 2006-05-12 | -51.04| | 39 | 2005-07-31 | 1191.00 | | 40 | 2006-05-29 | 65.00 | +-++---+ 53 rows in set (0.52 sec) Douglas Sims [EMAIL PROTECTED] On Sep 27, 2006, at 11:36 PM, mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM in WHERE
Hi Ahmad I tested that example query with version 5.0.19. According to the manual, (http://dev.mysql.com/doc/refman/5.0/en/ansi-diff- subqueries.html) derived tables (subqueries in the from clause) should work in versions 4.1.x and up, so I'm not sure why it didn't work for you. In most cases you can rewrite queries which use derived tables as queries with joins, but I think that would be very hard to do in this case. The key bit of logic in this doesn't actually require there to be a derived table. The inner query: SELECT amount, @total:[EMAIL PROTECTED] AS tot FROM t ORDER BY TransactionDate will give you a result set with a running total, and then you can use whatever logic you need to give you the first one or more rows where @total exceeds the threshold (e.g. 100) (Be sure to initialize that @total variable before the SELECT) The easiest way to do this, of course, is as a subselect of another query but you could also do it in the perl/python/php/whatever layer which is sending this query to the database. Can you send a transcript of what you tried, including the SHOW CREATE TABLE statement? Douglas Sims [EMAIL PROTECTED] On Sep 24, 2006, at 10:09 AM, Ahmad Al-Twaijiry wrote: doesn't work :( , tested with 4.1.21 On 9/20/06, Douglas Sims [EMAIL PROTECTED] wrote: Following is one way of doing what you want. mysql show create table t; +--- + --- -+ | Table | Create Table | +--- + --- -+ | t | CREATE TABLE `t` ( `TransactionDate` datetime default NULL, `amount` float default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--- + --- -+ 1 row in set (0.00 sec) mysql select * from t; +-++ | TransactionDate | amount | +-++ | 2006-01-02 00:00:00 | 20 | | 2006-01-04 00:00:00 | 178| | 2006-01-07 00:00:00 | 32.43 | | 2006-01-09 00:00:00 | 3 | | 2006-01-11 00:00:00 | -1000 | | 2006-01-15 00:00:00 | 33.9 | +-++ 6 rows in set (0.00 sec) mysql set @total=0; Query OK, 0 rows affected (0.00 sec) mysql select amount as amount1, tot as tot1 from (select amount, @total:[EMAIL PROTECTED] as tot from t order by TransactionDate) AS Tx where Tot100; +-+--+ | amount1 | tot1 | +-+--+ | 178 | 198 | | 32.43 | 230.43305176 | | 3 | 233.43305176 | +-+--+ 3 rows in set (0.00 sec) Good luck! Douglas Sims [EMAIL PROTECTED] On Sep 19, 2006, at 4:02 PM, Quentin Bennett wrote: No, I don't think it is. I think you want to have a query that will return 'n' rows where the sum of Total is = 100 If your table is ID Total 1 10 2 20 3 30 4 40 5 50 it would return 1 10 2 20 3 30 4 40 (sum total = 100) but if your table was ID Total 1 100 2 20 3 30 4 40 5 50 it would return 1 100 only. Have I got it right. Using only SQL, your best bet would be a stored procedure, otherwise its really application logic to select the rows one at a time and keep a running total. HTH Quentin -Original Message- From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Sent: Wednesday, 20 September 2006 2:24 a.m. To: Price, Randall Cc: Edward Macnaghten; mysql@lists.mysql.com Subject: Re: SUM in WHERE Actually is this possible with simple SQL command in Mysql ? On 9/19/06, Price, Randall [EMAIL PROTECTED] wrote: I tried it also with 5.0.24-community-nt and it still didn't work! Randall Price Microsoft Implementation Group Secure Enterprise Computing Initiatives Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 19, 2006 10:06 AM To: Edward Macnaghten Cc: mysql@lists.mysql.com Subject: Re: SUM in WHERE I tried it also with 4.1.21-log and still didn't work ! On 9/19/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote: I tried that before and it also doesn't work, is it because I'm using mysql version 4.1.19 ? On 9/19/06, Edward Macnaghten [EMAIL PROTECTED] wrote: Ahmad Al-Twaijiry wrote: Hi everyone snip SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 ORDER BY ID -- Ahmad Fahad AlTwaijiry -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List
Re: SUM in WHERE
Ahh... I think I understand what you are trying to do now. The query will keep the cumulative total of the Total column... so, your data is 22, 41, 10, 40, 30, 20... After the 22 the cumulative total is 22 After the 41 the cumulative total is 22+41 or 63 After the 10 the cumulative total is 63+10 or 73 After the 40 the cumulative total is 73+40 or 113 After the 30 the cumulative total is 113+30 or 143 ... So, you see, it is never exactly equal to 100 so the query doesn't return any rows. I think you are looking for the first row (ordered by the ID column) where the value is exactly 100. That would be the row with id=7. Here is a query which will give you that: SELECT * FROM tbl_name WHERE total=100 ORDER BY id LIMIT 1,1 Douglas Sims [EMAIL PROTECTED] On Sep 24, 2006, at 3:27 PM, Ahmad Al-Twaijiry wrote: Hi I need the result to be 100 not to more or less than 100 here is my query : mysql select version() ; ++ | version() | ++ | 4.1.21-log | ++ 1 row in set (0.00 sec) my table : CREATE TABLE `tbl_name` ( `ID` int(11) NOT NULL auto_increment, `Total` int(11) NOT NULL default '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 mysql select * from tbl_name; ++---+ | ID | Total | ++---+ | 1 |22 | | 2 |41 | | 3 |10 | | 4 |40 | | 5 |30 | | 6 |20 | | 7 | 100 | | 8 | 100 | | 9 |50 | | 10 |50 | ++---+ 10 rows in set (0.31 sec) mysql set @total=0; mysql select Total as amount1, tot as tot1 from (select Total,@total:[EMAIL PROTECTED] as tot from tbl_name order by ID) as Tx where Tot100; I will get : +-+--+ | amount1 | tot1 | +-+--+ | 40 | 113 | | 30 | 143 | | 20 | 163 | | 100 | 263 | | 100 | 363 | | 50 | 413 | | 50 | 463 | +-+--+ 7 rows in set (0.00 sec) but for =100 I will get mysql set @total=0; Query OK, 0 rows affected (0.00 sec) mysql select Total as amount1, tot as tot1 from (select Total,@total:[EMAIL PROTECTED] as tot from tbl_name order by ID) as Tx where Tot=100; Empty set (0.00 sec) Thanks On 9/24/06, Douglas Sims [EMAIL PROTECTED] wrote: Hi Ahmad I tested that example query with version 5.0.19. According to the manual, (http://dev.mysql.com/doc/refman/5.0/en/ansi-diff- subqueries.html) derived tables (subqueries in the from clause) should work in versions 4.1.x and up, so I'm not sure why it didn't work for you. In most cases you can rewrite queries which use derived tables as queries with joins, but I think that would be very hard to do in this case. The key bit of logic in this doesn't actually require there to be a derived table. The inner query: SELECT amount, @total:[EMAIL PROTECTED] AS tot FROM t ORDER BY TransactionDate will give you a result set with a running total, and then you can use whatever logic you need to give you the first one or more rows where @total exceeds the threshold (e.g. 100) (Be sure to initialize that @total variable before the SELECT) The easiest way to do this, of course, is as a subselect of another query but you could also do it in the perl/python/php/whatever layer which is sending this query to the database. Can you send a transcript of what you tried, including the SHOW CREATE TABLE statement? Douglas Sims [EMAIL PROTECTED] On Sep 24, 2006, at 10:09 AM, Ahmad Al-Twaijiry wrote: doesn't work :( , tested with 4.1.21 On 9/20/06, Douglas Sims [EMAIL PROTECTED] wrote: Following is one way of doing what you want. mysql show create table t; +--- + --- -+ | Table | Create Table | +--- + --- -+ | t | CREATE TABLE `t` ( `TransactionDate` datetime default NULL, `amount` float default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--- + --- -+ 1 row in set (0.00 sec) mysql select * from t; +-++ | TransactionDate | amount | +-++ | 2006-01-02 00:00:00 | 20 | | 2006-01-04 00:00:00 | 178| | 2006-01-07 00:00:00 | 32.43 | | 2006-01-09 00:00:00 | 3 | | 2006-01-11 00:00:00 | -1000 | | 2006-01-15 00:00:00 | 33.9 | +-++ 6 rows in set (0.00 sec) mysql set @total=0; Query OK, 0 rows affected (0.00 sec) mysql select amount as amount1, tot as tot1 from (select amount, @total:[EMAIL PROTECTED] as tot from t order by TransactionDate) AS Tx where Tot100; +-+--+ | amount1 | tot1
Re: Count Fields of a Table
Hi David If you are using mysql 5.0 and up, you can select from the INFORMATION_SCHEMA database to get this information and much more. Following is an example using a database called test and a table called t To get the column names, use SELECT column_name FROM information_schema.columns WHERE table_schema='test' AND table_name='t'; mysql use test; Database changed mysql describe t; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | TransactionDate | datetime | YES | | | | | amount | float| YES | | | | +-+--+--+-+-+---+ 2 rows in set (0.07 sec) mysql select * from information_schema.columns where table_schema='test' and table_name='t'; +---+--++- +--++-+--- +--+ +---+---+ ++-++--- +-++ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | +---+--++- +--++-+--- +--+ +---+---+ ++-++--- +-++ | | test | t | TransactionDate | 1|| YES | datetime | NULL | NULL | NULL | NULL ||| datetime || | select,insert,update,references || | | test | t | amount | 2|| YES | float | NULL | NULL | 12 | NULL ||| float || | select,insert,update,references || +---+--++- +--++-+--- +--+ +---+---+ ++-++--- +-++ 2 rows in set (0.01 sec) mysql select count(*) from information_schema.columns where table_schema='test' and table_name='t'; +--+ | count(*) | +--+ | 2| +--+ 1 row in set (0.32 sec) mysql select column_name from information_schema.columns where table_schema='test' and table_name='t'; +-+ | column_name | +-+ | TransactionDate | | amount | +-+ 2 rows in set (0.08 sec) Douglas Sims [EMAIL PROTECTED] On Sep 22, 2006, at 9:54 AM, davidvaz wrote: Hello, Is there any way to find out, using only plain SQL, the number of fields of a given table. describe gives me the number of fields as result, but I need to get only that. Is it possible? Is it also possible to get only the fields name? Thanks David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql pushing data to client
Hi David Sybase and MS-SQL have a built-in stored procedure called xp_cmdshell which lets you execute shell commands from within a stored procedure or otherwise within the database process. MySQL doesn't (I'm fairly sure) provide anything like that (although I think someone had written a UDF in C that might do that). I don't think that's really a problem with MySQL, on the contrary it's a good thing because xp_cmdshell is potentially a big security issue, as any shell program would be running as the same user who's running the MySQL server. It will probably work better if you put the triggering code in the part of your application (like the PHP page, whatever) that changes the data you want to be notified about, instead of in the database itself. Douglas Sims [EMAIL PROTECTED] On Sep 21, 2006, at 3:14 PM, Dave at Mysql wrote: I am looking for a way to write a client program that will wake up when there is new data in the database, much like replication. So instead of my client pulling the database on some fixed interval, I would like the mysql daemon to push the data to my client when there is new data. I assume this is possible given that it is done with replication. Does anybody have information on how to implement a client program like this? I'm using PHP, but I'm not opposed to using C or C++ for this kind of functionality. Thanks. David Godsey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to delete all rows....
You might also look at TRUNCATE table... http://dev.mysql.com/doc/refman/5.0/en/truncate.html I believe that DELETE will not reclaim the storage space while TRUNCATE does, although I didn't see that in the documentation when I looked just now... ? Douglas Sims [EMAIL PROTECTED] On Sep 20, 2006, at 2:55 AM, Peter Lauri wrote: DELETE FROM table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 10:35 AM To: mysql@lists.mysql.com Subject: How to delete all rows Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM in WHERE
Following is one way of doing what you want. mysql show create table t; +--- +--- -+ | Table | Create Table | +--- +--- -+ | t | CREATE TABLE `t` ( `TransactionDate` datetime default NULL, `amount` float default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--- +--- -+ 1 row in set (0.00 sec) mysql select * from t; +-++ | TransactionDate | amount | +-++ | 2006-01-02 00:00:00 | 20 | | 2006-01-04 00:00:00 | 178| | 2006-01-07 00:00:00 | 32.43 | | 2006-01-09 00:00:00 | 3 | | 2006-01-11 00:00:00 | -1000 | | 2006-01-15 00:00:00 | 33.9 | +-++ 6 rows in set (0.00 sec) mysql set @total=0; Query OK, 0 rows affected (0.00 sec) mysql select amount as amount1, tot as tot1 from (select amount, @total:[EMAIL PROTECTED] as tot from t order by TransactionDate) AS Tx where Tot100; +-+--+ | amount1 | tot1 | +-+--+ | 178 | 198 | | 32.43 | 230.43305176 | | 3 | 233.43305176 | +-+--+ 3 rows in set (0.00 sec) Good luck! Douglas Sims [EMAIL PROTECTED] On Sep 19, 2006, at 4:02 PM, Quentin Bennett wrote: No, I don't think it is. I think you want to have a query that will return 'n' rows where the sum of Total is = 100 If your table is ID Total 1 10 2 20 3 30 4 40 5 50 it would return 1 10 2 20 3 30 4 40 (sum total = 100) but if your table was ID Total 1 100 2 20 3 30 4 40 5 50 it would return 1 100 only. Have I got it right. Using only SQL, your best bet would be a stored procedure, otherwise its really application logic to select the rows one at a time and keep a running total. HTH Quentin -Original Message- From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Sent: Wednesday, 20 September 2006 2:24 a.m. To: Price, Randall Cc: Edward Macnaghten; mysql@lists.mysql.com Subject: Re: SUM in WHERE Actually is this possible with simple SQL command in Mysql ? On 9/19/06, Price, Randall [EMAIL PROTECTED] wrote: I tried it also with 5.0.24-community-nt and it still didn't work! Randall Price Microsoft Implementation Group Secure Enterprise Computing Initiatives Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 19, 2006 10:06 AM To: Edward Macnaghten Cc: mysql@lists.mysql.com Subject: Re: SUM in WHERE I tried it also with 4.1.21-log and still didn't work ! On 9/19/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote: I tried that before and it also doesn't work, is it because I'm using mysql version 4.1.19 ? On 9/19/06, Edward Macnaghten [EMAIL PROTECTED] wrote: Ahmad Al-Twaijiry wrote: Hi everyone snip SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 ORDER BY ID -- Ahmad Fahad AlTwaijiry -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: make mysqldump to sort columns alphabetically
One way you could solve this is to conform the column orders between the two versions. For example, if one table, t6, has columns id, name, and address and the same table in the second database is id, address, name, you could just ALTER the second database t6 table to be id, name, address: mysql describe t6; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | id | int(9) | NO | PRI | 0 | | | address | varchar(32) | YES | | | | | name| varchar(32) | YES | MUL | | | +-+-+--+-+-+---+ 3 rows in set (0.00 sec) mysql alter table t6 change column address address varchar(32) after name; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql describe t6; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | id | int(9) | NO | PRI | 0 | | | name| varchar(32) | YES | MUL | | | | address | varchar(32) | YES | | | | +-+-+--+-+-+---+ 3 rows in set (0.00 sec) Here is the mysql documentation on ALTER TABLE: http://dev.mysql.com/ doc/refman/5.0/en/alter-table.html Douglas Sims [EMAIL PROTECTED] On Sep 8, 2006, at 12:27 PM, Nikita Tovstoles wrote: Hi, I'm diffing two versions of a schema produced using mysqldump and would like to know if there's a way to make mysqldump sort entries inside CREATE statements (say alphabetically or in some other way)? Currently some of the column declarations are juxtaposed between the versions and thus produce false diffs. Mysql 5.0, InnoDB thanks a lot -nikita -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
) from t5; +-+ | max(id) | +-+ | 100 | +-+ 1 row in set (0.01 sec) mysql select count(*) from t5; +--+ | count(*) | +--+ | 100 | +--+ 1 row in set (0.72 sec) mysql select count(*) from t5; +--+ | count(*) | +--+ | 100 | +--+ 1 row in set (0.71 sec) Finally, just to make sure there wasn't anything funny going on with caching, I stopped and restarted the server and did the same queries again: mysql select max(id) from t5; +-+ | max(id) | +-+ | 100 | +-+ 1 row in set (0.01 sec) mysql select count(*) from t5; +--+ | count(*) | +--+ | 100 | +--+ 1 row in set (0.70 sec) Thus, if you're not going to be deleting rows from the table, selecting the MAX() of an AUTO INCREMENT field should be a lot faster than a COUNT(*) and will give the same answer. But in any case, 20 seconds for a table with only a million rows seems rather high. Finally, I wondered how this would compare to MyISAM table performance. I created a table (T6) from the innodb table as follows (MyISAM is configured as the default database type on this MySQL). Note that it took only 2.31 seconds to create the table and put a million rows in it! mysql create table t6 select * from t5; Query OK, 100 rows affected (2.31 sec) Records: 100 Duplicates: 0 Warnings: 0 mysql show create table t6; +--- +--- ---+ | Table | Create Table | +--- +--- ---+ | t6| CREATE TABLE `t6` ( `id` int(9) NOT NULL default '0', `name` varchar(32) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--- +--- ---+ 1 row in set (0.00 sec) mysql select count(*) from t6; +--+ | count(*) | +--+ | 100 | +--+ 1 row in set (0.00 sec) mysql select max(id) from t6; +-+ | max(id) | +-+ | 100 | +-+ 1 row in set (0.43 sec) mysql select max(id) from t6; +-+ | max(id) | +-+ | 100 | +-+ 1 row in set (0.43 sec) But then I realized that the CREATE TABLE... SELECT... syntax doesn't get the constraints or indexes from the old table, so I added a primary key index and did the same queries again: mysql alter table t6 add primary key(id); Query OK, 100 rows affected (4.74 sec) Records: 100 Duplicates: 0 Warnings: 0 mysql select max(id) from t6; +-+ | max(id) | +-+ | 100 | +-+ 1 row in set (0.00 sec) mysql select count(*) from t6; +--+ | count(*) | +--+ | 100 | +--+ 1 row in set (0.00 sec) I wonder if the reason for the 20-second SELECT COUNT(*) which you are seeing might not have more to do with memory allocation on the server? Or perhaps Dan's suggestion that the InnoDB primary index holds the entire row might be the clue. How big are your rows? Note to self: stop going to Starbucks for coffee just before bedtime. Douglas Sims [EMAIL PROTECTED] On Sep 7, 2006, at 12:18 AM, Dan Nelson wrote: In the last episode (Sep 07), [EMAIL PROTECTED] said: Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? It might, because in an InnoDB table, your primary index also holds your row data. So it's actually your largest index. A full scan of a secondary index on your primary key may very well run faster than a scan of the primary index itself, for the purposes of SELECT COUNT(*). Best way to find out is to try it :) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
Hi Prasad A primary key automatically creates an index and not-null and unique constraints, too. So you don't need to explicitly create an index on a primary key column. If your queries are going to have WHERE clauses (as they most likely are) I'm not sure how the small-index suggestion would make the query any faster - the WHERE clause would preclude the use of that index in computing the rows - but I'm probably missing something here. When you say that you need to know the number of rows returned before executing the query, do you mean before you start getting rows back or before you actually execute the query? I don't think it's possible to know how many rows the query will return without actually executing it, but you might well want to know how many rows you get before you start processing rows. Have you looked at the SQL_CALC_FOUND_ROWS option on SELECT, and the accompanying FOUND_ROWS() function? http://dev.mysql.com/doc/refman/ 5.0/en/information-functions.html It will tell you the total number of rows which would have been found if you hadn't used a LIMIT clause. I think it is a connection- specific function; if you created a second statement handle and did a SELECT FOUND_ROWS() on the same connection, perhaps that would give what you want. Douglas Sims [EMAIL PROTECTED] On Sep 6, 2006, at 11:29 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? Regards Prasad -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Monday, September 04, 2006 9:53 AM To: Prasad Ramisetti (WT01 - Broadband Networks) Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: problem with InnoDB In the last episode (Sep 04), [EMAIL PROTECTED] said: Actually there is some requirement, where I need to know the number of rows that I will get for my queries before actually executing the query. Could you please suggest some way for this. Your best bet is to create an index on the smallest column you can find (maybe even create a char(1) and leave it empty), and SELECT COUNT(*) FROM innotable USE INDEX (smallcolumn). That way mysql only has to scan a small secondary index instead of the main table index. -- Dan Nelson [EMAIL PROTECTED] The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditional Insert
Hi Ravi
You are correct. I was just sitting here thinking about this after I
sent that and realized that what I said was incorrect; the REPLACE
command will not do what you want, because it delete a record instead
of updating it, it has no way to know what the previous value is.
You probably should define a stored procedure and call that. Here is
an example which seems to work:
mysql show create table ravitest;
+--
+---
+
| Table| Create
Table
|
+--
+---
+
| ravitest | CREATE TABLE `ravitest` (
`username` varchar(16) NOT NULL,
`points` int(10) unsigned default NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--
+---
+
1 row in set (0.00 sec)
mysql show create procedure r1;
+---+--
+---
---+
| Procedure | sql_mode | Create
Procedure
|
+---+--
+---
---+
| r1| | CREATE PROCEDURE `r1`(n VARCHAR(16), p INT)
BEGIN
DECLARE RowsFound INT DEFAULT 0;
SELECT COUNT(*) INTO RowsFound FROM ravitest WHERE username=n;
IF RowsFound=0 THEN
INSERT INTO ravitest VALUES (n, p);
ELSE
UPDATE ravitest SET ravitest.points=ravitest.points
+p where ravitest.username=n;
END IF;
END |
+---+--
+---
---+
1 row in set (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 3 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('ravi', 15);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('Elaine', 5);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
| Elaine | 5 |
+--++
8 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:01 AM, Ravi Kumar. wrote:
Hi Douglas,
Thanks for your prompt reply. I read through the description of
REPLACE and
tested it as well. It seems just fine, but the idea that it would
DELETE and
then insert if a match is found, seems like a warning to me.
I need to know this: can it happen that in between this delete and
insert,
another thread inserts a matching record?
Do we have any alternative?
Thanks,
Ravi.
-Original Message-
From: Douglas Sims [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 29 August, 2006 10:48 AM
To: Ravi Kumar.
Cc: mysql@lists.mysql.com
Subject: Re
Re: Conditional Insert
Much better. Good job.
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:26 AM, Johan Höök wrote:
Hi Ravi,
you can take a look at:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
which might take care of your problem.
/Johan
Douglas Sims skrev:
Hi Ravi
You are correct. I was just sitting here thinking about this
after I sent that and realized that what I said was incorrect; the
REPLACE command will not do what you want, because it delete a
record instead of updating it, it has no way to know what the
previous value is. You probably should define a stored procedure
and call that. Here is an example which seems to work:
mysql show create table ravitest;
+--
+
-
--+ | Table| Create
Table
|
+--
+
-
--+ | ravitest | CREATE TABLE
`ravitest` (
`username` varchar(16) NOT NULL,
`points` int(10) unsigned default NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--
+
-
--+ 1 row in set (0.00 sec)
mysql show create procedure r1;
+---+--
+
-
-
-
---+ | Procedure |
sql_mode | Create
Procedure
|
+---+--
+
-
-
-
---+ | r1
| | CREATE PROCEDURE `r1`(n VARCHAR(16), p INT)
BEGIN
DECLARE RowsFound INT DEFAULT 0;
SELECT COUNT(*) INTO RowsFound FROM ravitest WHERE username=n;
IF RowsFound=0 THEN
INSERT INTO ravitest VALUES (n, p);
ELSE
UPDATE ravitest SET ravitest.points=ravitest.points
+p where ravitest.username=n;
END IF;
END |
+---+--
+
-
-
-
---+ 1 row in set
(0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 3 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('ravi', 15);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('Elaine', 5);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
| Elaine | 5 |
+--++
8 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:01 AM, Ravi Kumar. wrote:
Hi Douglas,
Thanks for your prompt reply. I read through the description of
REPLACE and
tested it as well. It seems just fine, but the idea that it would
DELETE and
then insert if a match is found, seems like a warning to me.
I need to know this: can it happen that in between this delete
Re: select between date
You have a table containing birthdates (date field, including year)
and you want to display all rows for which the birthday will occur in
the next week (seven days).
You tried this query:
SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as
a017tkhlahir,
MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh
FROM a017
MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and
MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY))
AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and
DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY))
ORDER BY bulan, tarikh
but found a problem - that sometimes birthdates on or after the 25th
in months with 31 days will not show.
This could happen in December, after December 25, because then the
month field will be 12 but the month field for DATEADD(CURDATE(),
INTERVAL 7 DAY) will be 1 and so nothing will match the clause
BETWEEN 12 and 1
I don't immediately see that this would be a problem in other
months. For example:
mysql select 'fish' from t1 where 3 between 12 and 1;
Empty set (0.00 sec)
mysql select 'fish' from t1 where 3 between 1 and 12;
+--+
| fish |
+--+
| fish |
+--+
1 row in set (0.03 sec)
One solution is to create a new date from the birthday in the table
by taking the year from the current date and the month and day from
the birthdate and then checking to see if that date is in the next
seven days, that is, between CURDATE() and DATEADD(CURDATE(),
INTERVAL 7 DAY). Here is an example of how that might work:
mysql show create table birthdays;
+---
+---
---+
| Table | Create
Table
|
+---
+---
---+
| birthdays | CREATE TABLE `birthdays` (
`name` varchar(32) default NULL,
`birthdate` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---
+---
---+
1 row in set (0.00 sec)
mysql select * from birthdays;
+++
| name | birthdate |
+++
| P.G. Wodehouse | 1881-10-15 |
| John Marquand | 1893-11-10 |
| Ian Flemming | 1908-05-28 |
| John Grisham | 1955-02-08 |
| Jeffrey Archer | 1940-04-15 |
| Keanu Reeves | 1964-09-02 |
| Fred MacMurray | 1908-08-30 |
+++
7 rows in set (0.00 sec)
mysql SELECT * FROM birthdays WHERE STR_TO_DATE(CONCAT_WS('-', YEAR
(CURDATE()), MONTH(birthdate), DAY(birthdate)), '%Y-%m-%d') BETWEEN
CURDATE() AND ADDDATE(CURDATE(), INTERVAL 7 DAY);
+++
| name | birthdate |
+++
| Keanu Reeves | 1964-09-02 |
| Fred MacMurray | 1908-08-30 |
+++
2 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 10:13 PM, Penduga Arus wrote:
On 8/3/06, Penduga Arus [EMAIL PROTECTED] wrote:
On 8/1/06, Chris [EMAIL PROTECTED] wrote:
Did you look at the link David sent you?
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
thanks.. I manage to do that.. below is my solution. please advice if
there is any better solution
SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as
a017tkhlahir,
MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh
FROM a017
MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and
MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY))
AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and
DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY))
ORDER BY bulan, tarikh
I have notice problem with my sql statment above, when it run on the
25th for the month which have 31 days the statment isnt valid anymore.
FYI my a017tkhlahir is in date format (-mm-dd)
Please advice.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditional Insert
Hi Ravi
Standard SQL syntax doesn't provide for that. You could wrap the
whole thing in a transaction, possibly in a stored procedure, but
this would be rather awkward.
However, MySQL has a command called REPLACE which I think will do
exactly what you want:
http://dev.mysql.com/doc/refman/5.0/en/replace.html
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 12:08 AM, Ravi Kumar. wrote:
Dear Members,
I wish to insert and update conditionally into a table using a
single SQL
statement. That is if a record already exists in a table, the
statement
should update it, else it should insert it. Probably something like
this:
if (select * from UserPoints where username = 'ravi') then
(update UserPoints set points = points + 5 where username = 'ravi')
else
(insert into UserPoints (username, points) values ('ravi', 0))
end if
I can not do it pragmatically using PHP, because the environment is
highly
concurrent and it may result in a 'race condition' often.
The version of MySQL we are using is 4.1.14-standard-log.
Please reply soon, it is urgent.
Have a nice day!
Thanks and regards,
Ravi.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: First View
I was lucky enough to have gotten a copy of MySQL 10 (aka MySQL X) from the source tree before it was pulled. The query optimizer used predictive algorithms with temporal displacement logic, which meant that it could and did frequently return results in negative time, before the query was even asked. The full-text search used natural-language processing so effectively that you could search for breakfast and it would know whether to return bacon and eggs or a cheese danish depending on what you felt like that morning. By version 10.13, you could just search a full-text index for something or whatever and it would return exactly the rows you needed. The whole thing ran in under 2K of RAM. I had a copy running on the office coffee machine for a while until people started getting caffeine headaches just from reading reports. The BLOB type could store not only binary data but actual THINGS, like books or clothing or building materials. There was a weight limit, but you could get around that by setting something in the config file. Also, there was a bug in the refrigeration stored procedure, so you had to be very careful with perishables, like, say, fish. On Aug 25, 2006, at 12:50 AM, mos wrote: At 06:30 PM 8/24/2006, David Hillman wrote: On Aug 24, 2006, at 6:13 PM, Daniel Kasak wrote: That's just how software develops. People start with the later- versioned product, with full features and zero bugs, and progressively remove features and add bugs, while decreasing the version number. I've looked all over the web, and found nothing, so hopefully someone here can help... where can I download MySQL 10 from? ;) -- David, MySQL 10 came out quite a while ago and is now obsolete. MySQL 10 of course had been totally free and since it worked so well, it nearly killed off all the competition. Legislation was quickly passed in order to make things fair for other database manufacturers by forcing the programmers to make radical changes to the MySQL 10 engine. The engine had far too many features and worked too well so the programmers have been stripping out features and adding bugs for the past 5 years. They also had to slow it down because people were getting results in 0ms which led people to think the results were inaccurate even though they weren't. Delay loops had to be added along with random disk access to give people the impression a lot of work was being processed by the MySQL engine. They also discovered getting perfect answers each time had created its own problems. Accounting systems written in MySQL 10 produced highly accurate Income Statements and Balance Sheets correctly the first time they were run, causing much embarrassment at stock holders meetings for revealing improprieties by the board. Bugs had to be added to the MySQL engine and the SQL syntax had to be obfuscated in order to introduce calculation mistakes so the CEO's could have someone to blame and thereby avoid criminal prosecution. Since MySQL 10 was totally free for both personal and commercial use without any restrictions whatsoever, royalties were quickly introduced to try and limit the number of people using the product. All of this of course took years to accomplish. What we're left with is what we have now. g,dr Mike (Just kidding Monty - Please don't key my car!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query
I think you are trying to use a regular expression in the like phrase. I wasn't aware that MS SQL can do regular expressions at all? I've been using 2000 - perhaps the newer version can? In MySQL, instead of saying: LA.LastName LIKE '[A-E]%' try this: LA.LastName REGEXP '^[A-E]' You can read more about MySQL regular expressions here: http:// mysql.com/doc/refman/5.0/en/regexp.html Also, are you using spellcheck with Outlook Express? Because if so, Microsoft keeps trying to rename MySQL to be Myself. I think they want to take over everything. :-) Good luck. Douglas Sims [EMAIL PROTECTED] On Aug 21, 2006, at 10:57 AM, Jesse wrote: I have the following query which works in MS SQL Server, but I'm sure the syntax is different for Myself. I'm sure it's in the area of the LIKE clause. I don't know how to do this with Myself. Can anyone point me in the right direction?: SELECT LA.FirstName,LA.LastName,LA.EMailAddress, LA.UserName, U.Password FROM LocalAdvisors LA JOIN Users U ON U.UserName=LA.UserName WHERE EMailAddress '' AND EMailAddress IS NOT NULL AND (EMailPermission is NULL or EMailPermission=1) AND LA.LastName LIKE '[A-E]%' ORDER BY LastName,FirstName Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Header to post data to another site
It sounds as if you're using the PHP HTTP module. You probably want the HTTP_Client module instead as it has a method for doing POSTs. In general you should use GET for HTTP requests which only solicit information from another server and don't request that server to change anything stored on it. For requests that change data on another server, you should use POST. Here is some documentation for HTTP_Client: http://pear.php.net/ manual/en/html/package.http.http-client.html I've pretty much gotten away from sending any username/password pairs in unencrypted http. If you send HTTPS, the header part is not encrypted but the body is.* If you send cookies they are in the header and thus subject to snooping, but since authentication cookies should be only valid for a limited time, I don't think this is too much of a problem. I think if I were doing what you describe I would use HTTPS and put both the un/pw and data in the body of the request, rather than trying to send one request to get a cookie and then use that cookie to send the actual data. Douglas Sims [EMAIL PROTECTED] *er, did I state that correctly? On Aug 16, 2006, at 4:27 PM, mos wrote: At 09:34 AM 8/16/2006, you wrote: Hi all, I am working on a problem where I need to select data from my own database, and then post it to another website (using the Get) method. After having wasted about 4 days trying http_request, $_Get and whatnot, I stumbled upon the header command. So far, the only way I have been able to actually post data to the other site is through this header command: header (Location:$url); This has the drawback that the user gets to see the URL I am sending him to, because it contains a password. I have tried to omit 'Location', and although it doesn't generate an error, the info also doesn't reach the intended website. I hope there is a smarter way to have PHP perform this task, without me actually having to reveal sensitive info to the user. Is there anyone willing to point me in the right direction? Kind regards, Dirk Dirk, I've come up with 2 solutions. I'd try and create a temporary cookie on the user's machine before going to the other site, then the site can read the username/pw in the cookie. Of course you should find some way of encrypting the cookie contents to prevent someone from reading it. MD5()? Of course the username/ pw must exist on both webservers which could be a pain to maintain. And of course you'd have to prevent him from re-using the cookie later on or decoding the original username/pw etc.. So using a cookie has a slight security risk. You could also have your web page (webserver #1) generate a unique random id (maybe an MD5 based on the his PHP session # or some other unique random ID). But don't send this info to the user just yet! Instead have your webserver #1 contact webserver #2 using either MySQL via TCP/IP or some other 3rd party program see http:// dev.mysql.com/doc/refman/5.0/en/windows-and-ssh.html, and store this random id in the database of webserver #2. Then and only then redirect the user to webserver #2 with this random id in the URL. You should of course use SSH when contacting the other MySQL server. This tells webserver#2 to expect a user in the next 2 minutes to allow him to connect to web server #2 if it finds this random id in the table. After 2 minutes the id will expire. And of course after the user connects to webserver#2, you would delete the random id (or have it expire in 24 hours etc.-it's up to you). Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
I think this will do it, although it takes three queries. I'm assuming the id values are unique, even if there can be gaps (that's what you might get with an AUTO_INCREMENT field). If the values are not guaranteed to be unique then this may not give what you want (if there are multiple rows with the same value you're looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not three fives.) SELECT @id:=5; SELECT * FROM t WHERE id(SELECT MAX(id) FROM t WHERE id@id) ORDER BY id DESC LIMIT 1; SELECT * FROM t WHERE [EMAIL PROTECTED] LIMIT 1; SELECT * FROM t WHERE id(SELECT MIN(id) FROM t WHERE id@id) ORDER BY id ASC LIMIT 1; But as to putting that in one statement... it might be better just to do it as three. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 9:32 AM, Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. Regards, Michael Dan Julson wrote: Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Michael DePhillips www.star.bnl.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
D'oh. Very good. I wish I'd thought of that. In response to Michael DePhillips' point about the UDF - I believe that in MySQL 5.x UDFs can't query tables. In Oracle, SQL Server, etc. they can and I'm sure they will in the future. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 10:17 AM, [EMAIL PROTECTED] wrote: Here's a single query version of Douglas's solution: select @id:=6; select distinct t.testid, (select max(testid) from t where testid @id) as previousId, (select min(testid) from t where testid @id) as nextId from t where [EMAIL PROTECTED] Donna Douglas Sims [EMAIL PROTECTED] 08/14/2006 10:47 AM To Michael DePhillips [EMAIL PROTECTED] cc Dan Julson [EMAIL PROTECTED], mysql@lists.mysql.com Subject Re: Query Question I think this will do it, although it takes three queries. I'm assuming the id values are unique, even if there can be gaps (that's what you might get with an AUTO_INCREMENT field). If the values are not guaranteed to be unique then this may not give what you want (if there are multiple rows with the same value you're looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not three fives.) SELECT @id:=5; SELECT * FROM t WHERE id(SELECT MAX(id) FROM t WHERE id@id) ORDER BY id DESC LIMIT 1; SELECT * FROM t WHERE [EMAIL PROTECTED] LIMIT 1; SELECT * FROM t WHERE id(SELECT MIN(id) FROM t WHERE id@id) ORDER BY id ASC LIMIT 1; But as to putting that in one statement... it might be better just to do it as three. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 9:32 AM, Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. Regards, Michael Dan Julson wrote: Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Michael DePhillips www.star.bnl.gov -- 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql naming convention
The example you give is a form of hungarian notation, wherein an abbreviation representing the type of the variable is the first part of the variable name. Hungarian notation is not generally considered good practice for a variety of reasons; it is usually unnecessary, it interferes with the readability of the code, and since there is no actual connection between the variable name and the type, thus there is no guarantee that the notation will be correct. (For example, if you declare a variable as one type in a C++ program and then later change the declaration, the type given in the variable name will then be incorrect. This happens a lot in practice.) Table names, column names, and database names all exist in distinct namespaces in MySQL. Thus, if you give a table name Users there is no chance of ambiguity if you also name a column in that or another table users so there is no advantage to be had by including tbl in the name. I like to name tables and columns so as to make the queries read most like plain English. Thus, for example, I might name a table Users which would contain columns for username, address, telephone, etc. I got into a long argument with a good programmer I know about whether or not to name tables in the singular or plural, e.g. Users or User. I was arguing for the plural and he insisted you should never name a table in the plural. I don't think it really makes much difference. Here is an explanation of how I like to name tables and columns. I'd like to hear what other people think of this. I don't like to include the table name in the column name. Thus, in the Users table you might have columns named Address, City, and State but it isn't good practice to name these columns Users_Address, Users_City etc. If you do it this way your queries will look like SELECT Name, Address, City FROM Users or SELECT Users.Name, Users.Address FROM ... Compare that to SELECT Users.User_Name, Users.User_City FROM ... For tables which exist just to represent many-to-many relationships I like to name the tables with the names of the tables which are related joined by an underscore. For example, if I have a table Users and another table Permissions (storing perhaps different things a user can do, like insert into accounts or update transactions) then the table showing which users have which permissions I would name Users_Permissions. There is a good argument to be made for including the table name in the id field, such as Users.User_ID instead of Users.ID. If the key columns are the same you can do a join with simpler syntax: SELECT Users.Name, Permissions.Name FROM Users LEFT JOIN Users_Permissions USING (User_ID) LEFT JOIN Permissions USING (Permission_ID); However, I still prefer to name primary key id columns just id and then name the corresponding foreign key columns in related tables after the singular form of the names of tables they are related to. E.g.: mysql create table Users (id int(6) unsigned primary key, name varchar(32)); Query OK, 0 rows affected (0.08 sec) mysql create table Permissions (id int(6) unsigned primary key, name varchar(32)); Query OK, 0 rows affected (0.01 sec) mysql create table users_permissions (user int(6) unsigned default NULL, user int(6) default NULL, UNIQUE KEY x (user, permission)) SELECT Users.Name, Permissions.Name FROM Users LEFT JOIN Users_Permissions ON Users.id=user_permissions.user LEFT JOIN Permissions on permissions.id=user_permissions.permission; If you've never read Donald Knuth going on about Literate Programming you might check this out: http://www-cs-faculty.stanford.edu/~uno/ lp.html Just like with parenthesis styles, you can name database objects whatever you want and it will still work. But good nomenclature makes it all so much more clear. Douglas Sims [EMAIL PROTECTED] On Aug 11, 2006, at 4:08 AM, Barry wrote: Hello everyone! I am looking for a standard naming convention for databases. For example: is it good to use tablenames in column names like: table = tb_id,tb_text,tb_name and such. Probably there is some kind of overall naming convention out there, looked on google and such but only found conventions that people personally liked but no standards. Thanks for any help :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld-nt 100% CPU Utilization?
Can you do a show processlist from the MySQL client? This might help you to figure out if it is a specific query that's gumming up the works. Douglas Sims [EMAIL PROTECTED] On Jul 19, 2006, at 6:35 PM, Robinson, Eric wrote: Our MySQL-based medical application has been running fine for 3 years. Last week, mysqld-nt started jumping up to 100% CPU and staying there until someone restarts the service. mysqlcheck found no errors in the database. I dumped it to disk and read it back in to create a fresh copy of the database, but it is still happening. Any ideas? -- Eric Robinson Disclaimer - July 19, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what are those MySQL files for?
Those are the files which contain the data in each table in your MySQL databases. I think the .myd files contain the data, the .myi files contain indexes, and the .frm files contain schema information. Douglas Sims [EMAIL PROTECTED] On Jun 30, 2006, at 11:47 PM, Octavian Rasnita wrote: Hi, In the /data/database_name directory I have found a few files I don't know what they are used for. I have seen that some of them are pretty big. I don't think it is safe to delete them, but can I do something to decrease their size at least? Here are those files and their sizes in MB: 1 #sql-2a91_cdf.frm 397 #sql-2a91_cdf.MYD 253 #sql-2a91_cdf.MYI 1 #sql-6094_2.frm 397 #sql-6094_2.MYD 1 #sql-6094_2.MYI 2 #sql-6094_2.TMD Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find matching tables that have specific field name.
Hello William If you are using MySQL version 5.0 and up, you can select from the TABLE table in the INFORMATION_SCHEMA database. This database contains information about all of the structures in the MySQL server. For example, if you are looking for all of the tables in a database called PHXTC, you could do this: mysql select table_name from information_schema.tables where table_schema='phxtc'; ++ | table_name | ++ | accounts | | check_items| | checks | | discounts | | employees | | familygroups | | items | | items_accounts | | majorgroups| | periods| | refs | | rev_period | | revenuecenters | | servicecharges | | t | | taxclass_rates | | taxclasses | | taxrates | | tendermedia| ++ 19 rows in set (0.11 sec) Note that there is quite a lot of other useful information in INFORMATION_SCHEMA also. In this example i ust selected table names from one database. You can do this to see all of the tables in INFORMATION_SCHEMA (or any other MySQL database): mysql show tables from information_schema; +---+ | Tables_in_information_schema | +---+ | CHARACTER_SETS| | COLLATIONS| | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | KEY_COLUMN_USAGE | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | STATISTICS| | TABLES| | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | +---+ 16 rows in set (0.00 sec) And then to see specific information about one of these structures, the MySQL-specific command is quite useful: mysql describe tables; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | TABLE_CATALOG | varchar(512) | YES | | | | | TABLE_SCHEMA| varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | | | | VERSION | bigint(21) | YES | | | | | ROW_FORMAT | varchar(10) | YES | | | | | TABLE_ROWS | bigint(21) | YES | | | | | AVG_ROW_LENGTH | bigint(21) | YES | | | | | DATA_LENGTH | bigint(21) | YES | | | | | MAX_DATA_LENGTH | bigint(21) | YES | | | | | INDEX_LENGTH| bigint(21) | YES | | | | | DATA_FREE | bigint(21) | YES | | | | | AUTO_INCREMENT | bigint(21) | YES | | | | | CREATE_TIME | datetime | YES | | | | | UPDATE_TIME | datetime | YES | | | | | CHECK_TIME | datetime | YES | | | | | TABLE_COLLATION | varchar(64) | YES | | | | | CHECKSUM| bigint(21) | YES | | | | | CREATE_OPTIONS | varchar(255) | YES | | | | | TABLE_COMMENT | varchar(80) | NO | | | | +-+--+--+-+-+---+ 21 rows in set (0.07 sec) Good luck! Douglas Sims [EMAIL PROTECTED] On Jun 7, 2006, at 3:19 PM, William Scott wrote: Dear Sir, I have a database with over 80 tables. Is there an easy way to find table names that has PERSON_ID field using SELECT query? Thanks in advance for any help. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL (GPL License)
I believe that if you are only using MySQL for your company's internal needs, whether from a web server or for deployment to other company-owned locations, you don't need a commercial license. For example, if your company owns fifiteen stores, you could set up a MySQL-based point-of-sale system at each one without needing a commercial license. You only need to release your source code if you release your compiled code. Also, I believe the GPL requirement for sharing only applies if you have modified MySQL's object code, i.e. compiled your code into it or it into your code or linked object code to it. If you are simply installing it as a database and communicating to it through DBI or ODBC or some other means which uses sockets or ports, you don't need to release your code under the GPL. Thus, you hardly ever need to purchase a commercial license. Please note that this is just my understanding. I hope someone will correct if I have misstated anything here. However, it is very reasonable and desirable to support MySQL as a company, as they save us all tons of money over Oracle, MS-SQL, etc., in addition to providing an excellent product. So even if you don't need the commercial license, if your company depends upon MySQL, buying a commercial license, paying for training, attending conferences, or buying lots of t-shirts is nice. Douglas Sims [EMAIL PROTECTED] On Jun 7, 2006, at 7:58 PM, Logan, David (SST - Adelaide) wrote: Hi Michael, I believe you can purchase a commercial license taking away the GPL provisions from your software if you do not wish to GPL your own software. You can enquire on the MySQL website. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Michael Louie Loria [mailto:[EMAIL PROTECTED] Sent: Thursday, 8 June 2006 9:45 AM To: mysql@lists.mysql.com Subject: MySQL (GPL License) Hello, I would like to inquire about the GPL License used by MySQL. Here's our scenario We developed our owned software needed by our operations using MySQL community edition under Windows platform. GPL says that we should distribute/share the source code. But I think it isn't even of interest or beneficial to others because it was done on the company specs. If we were required to distribute/share our source code. What distribution methods can be used? like uploading the source code in a site? or when someone walks in and asks for the source code, we should share it to them. Thanks, I just need to have some clarifications about the GPL Mic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying tables sans data from one database to another
Perhaps the easiest way is with CREATE TABLE... SELECT. For example, if I have a database called NYCNH (by coincidence, I do!) which contains a table called checks and I want to copy that table to a database called TEST I could do this: mysql create table test.checks select * from nycnh.checks; Query OK, 80030 rows affected (0.88 sec) Records: 80030 Duplicates: 0 Warnings: 0 and that would create a copy of the structure and data from checks and put it in the database test. I believe this will not copy triggers or indexes from the original table. If you need to do this, you can do it by using the mysqldump program to dump the table (just the structure or the structure and data) from the original database and then load it into the new one: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Good luck! Douglas Sims [EMAIL PROTECTED] On Jun 7, 2006, at 8:09 PM, murthy gandikota wrote: How can I copy tables from one database to another on the same host? Thanks for your help Murthy __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL (GPL License)
Ouch. Thanks for the clarification. Two additional thoughts: 1) Does this apply (I think not) even if you don't compile with or link with the MySQL database? If you just connect to it with ports or sockets, as we usually do with web applications, you still don't need a commercial license? 2) $595/year is still a lot cheaper than most of the alternatives such as MS-SQL or Oracle. I got into this debate with our Microsoft rep over lunch about a year ago. My company does consulting with a couple of large (US Fortune-500 companies) and unfortunately one of these is wedded to MS. I work with MS-SQL quite a lot and generally find it inferior to MySQL and as Randy (the MS rep) was talking how you really did need to buy commercial licenses for MySQL, I pointed out that no you really didn't for web applications. (He went on to point out what great support MS SQL had - I quickly agreed and said that MS SQL had the best support we could ever ask for... it's called Google. Randy still paid for the lunch :-) Douglas Sims [EMAIL PROTECTED] On Jun 7, 2006, at 10:20 PM, mos wrote: At 08:15 PM 6/7/2006, you wrote: I believe that if you are only using MySQL for your company's internal needs, whether from a web server or for deployment to other company-owned locations, you don't need a commercial license. For example, if your company owns fifiteen stores, you could set up a MySQL-based point-of-sale system at each one without needing a commercial license. You only need to release your source code if you release your compiled code. Also, I believe the GPL requirement for sharing only applies if you have modified MySQL's object code, i.e. compiled your code into it or it into your code or linked object code to it. If you are simply installing it as a database and communicating to it through DBI or ODBC or some other means which uses sockets or ports, you don't need to release your code under the GPL. Thus, you hardly ever need to purchase a commercial license. Please note that this is just my understanding. I hope someone will correct if I have misstated anything here. Unfortunately that's not what MySQL AB licensing person told me. The license is more strict than that. If your company distributes an application that uses MySQL database inside the company (even inside the same building), and you don't give the other dept the source code (so it falls outside the gpl license) then the dept receiving the application needs to have a MySQL license. In other words, the complete application source code must follow the application. If you have a commercial application running in Windows, and expect to sell a lot of applications, it will cost you $595 per database server *per year*. See https://shop.mysql.com/network.html?rz=s2. I didn't realize myself it is now a per server/per year pricing either and it came as quite a shock to me system. This can add up if you have a thousand applications in circulation because each customer needs to pay $595/year. If this is too pricey for you, there are open source databases out there that are free to use and free to distribute. FireBird and ProgreSQL come to mind. And there are other commercial databases where you pay up front and have no distribution fees whatsoever. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL (GPL License)
Oh, one other thing. The group that I work with at the one large company (call it company X) which Randy the MS rep was taking to lunch... is about to upgrade some servers and spend somewhere around $250k on new MS SQL server licenses. I'm not really sure why. (Oxygen deprivation could explain it, except we seem to have plenty of oxgyen.) How can I possibly explain to them that we can port all of these web apps to MySQL or some other database (I don't care... MySQL is my first choice but Postgres is very nice but we could use database ABC or Wally's DB or use flat files written in Mandarin Chinese for all I care) for less money than we will spend on software on one upgrade cycle? Does anyone else have similar experiences? Douglas Sims [EMAIL PROTECTED] On Jun 7, 2006, at 11:15 PM, Douglas Sims wrote: Ouch. Thanks for the clarification. Two additional thoughts: 1) Does this apply (I think not) even if you don't compile with or link with the MySQL database? If you just connect to it with ports or sockets, as we usually do with web applications, you still don't need a commercial license? 2) $595/year is still a lot cheaper than most of the alternatives such as MS-SQL or Oracle. I got into this debate with our Microsoft rep over lunch about a year ago. My company does consulting with a couple of large (US Fortune-500 companies) and unfortunately one of these is wedded to MS. I work with MS-SQL quite a lot and generally find it inferior to MySQL and as Randy (the MS rep) was talking how you really did need to buy commercial licenses for MySQL, I pointed out that no you really didn't for web applications. (He went on to point out what great support MS SQL had - I quickly agreed and said that MS SQL had the best support we could ever ask for... it's called Google. Randy still paid for the lunch :-) Douglas Sims [EMAIL PROTECTED] On Jun 7, 2006, at 10:20 PM, mos wrote: At 08:15 PM 6/7/2006, you wrote: I believe that if you are only using MySQL for your company's internal needs, whether from a web server or for deployment to other company-owned locations, you don't need a commercial license. For example, if your company owns fifiteen stores, you could set up a MySQL-based point-of-sale system at each one without needing a commercial license. You only need to release your source code if you release your compiled code. Also, I believe the GPL requirement for sharing only applies if you have modified MySQL's object code, i.e. compiled your code into it or it into your code or linked object code to it. If you are simply installing it as a database and communicating to it through DBI or ODBC or some other means which uses sockets or ports, you don't need to release your code under the GPL. Thus, you hardly ever need to purchase a commercial license. Please note that this is just my understanding. I hope someone will correct if I have misstated anything here. Unfortunately that's not what MySQL AB licensing person told me. The license is more strict than that. If your company distributes an application that uses MySQL database inside the company (even inside the same building), and you don't give the other dept the source code (so it falls outside the gpl license) then the dept receiving the application needs to have a MySQL license. In other words, the complete application source code must follow the application. If you have a commercial application running in Windows, and expect to sell a lot of applications, it will cost you $595 per database server *per year*. See https://shop.mysql.com/network.html?rz=s2. I didn't realize myself it is now a per server/per year pricing either and it came as quite a shock to me system. This can add up if you have a thousand applications in circulation because each customer needs to pay $595/year. If this is too pricey for you, there are open source databases out there that are free to use and free to distribute. FireBird and ProgreSQL come to mind. And there are other commercial databases where you pay up front and have no distribution fees whatsoever. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatically add +1 every 30mins
You probably don't want to do it with events or as a cron job. As
it sounds from your description the amount of gold is a function of
time, perhaps time since they started playing or joined the game.
Thus, instead of updating every 30 minutes, when you are selecting
the current amount of gold, just select it as a function of the time
since they were online or signed up for the game.
You can use a time difference function, either TIMEDIFF or subtract
the UNIX_TIMESTAMP value of the start date/time from the
UNIX_TIMESTAMP() function of the curent date/time, and then just
divide appropriately.
More info about MySQL date/time functions: http://dev.mysql.com/doc/
refman/5.0/en/date-and-time-functions.html
Example: To get the number of 30-minute increments since some date,
mysql SELECT ROUND((UNIX_TIMESTAMP()-UNIX_TIMESTAMP('2006-05-30
3:45'))/(60*30));
+-+
| ROUND((UNIX_TIMESTAMP()-UNIX_TIMESTAMP('2006-05-30 3:45'))/(60*30)) |
+-+
| 162 |
+-+
1 row in set (0.00 sec)
Here is a reference to the MySQL documentation on date and time
functions, which is really good: http://dev.mysql.com/doc/refman/5.0/
en/date-and-time-functions.html
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Jun 2, 2006, at 7:56 AM, Miles Thompson wrote:
Dan,
Did not know about events in MySQL. That's a terrific feature.
Miles
At 09:44 AM 6/2/2006, Dan Buettner wrote:
Alex, as Miles noted, this could easily be accomplished with an
external cron event. Doesn't have to be written in an external
language like PHP or perl, even - could be a self-contained
crontab entry a la:
0,30 * * * * /path/to/mysql -u user -psecret database_name -e
update table_name set gold = gold + 1 /dev/null
To accomplish this within MySQL, one option might be 5.1's events:
http://dev.mysql.com/doc/refman/5.1/en/events.html
http://dev.mysql.com/doc/refman/5.1/en/create-event.html
Hope this helps,
Dan
Alex Major wrote:
Hi there. I've posted this up on both this list, and the php list
as I'm not sure
whether this is something that I'd need to do with the php or mysql.
Basically, I am making an add-on to my small website which is a
mini online
game. Every user will have gold, and every 30mins I'd like their
amount of
gold to go up by 1 (or say a variable say $goldupdateamount).
I'd like to know which would be the best way of doing this, and
if there is
a command in mysql which would achieve this.
Regards, Alex.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.8.1/354 - Release Date:
6/1/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment Question
Hi Mark
People_id is the column with auto increment? You can verify that it
really does have auto_increment by using the describe command. For
example:
mysql describe checks;
+-+--+--+-+-
++
| Field | Type | Null | Key | Default |
Extra |
+-+--+--+-+-
++
| id | int(14) unsigned | NO | PRI | |
auto_increment |
| original_check | int(14) unsigned | YES | |
||
| hotel_pos | int(7) | YES | |
||
| number | int(5) unsigned | YES | |
||
| revenuecenter | int(9) unsigned | YES | MUL |
||
| open_time | datetime | YES | MUL |
||
| close_time | datetime | YES | |
||
| employee| int(8) unsigned | YES | MUL |
||
| cashier | int(8) unsigned | YES | |
||
| restauranttable | int(3) unsigned | YES | |
||
| covers | int(4) | YES | |
||
+-+--+--+-+-
++
11 rows in set (0.15 sec)
If that's set up correctly, you should be able to leave that column
out of the list, e.g.
$people = INSERT INTO people (people_full_name, people_isactor,
people_isdirector) VALUES ( 'Jim Carey', 1, 0), (2, 'Tom Shadyac',
0, 1), ( 'Lawrence Kasdan', 0, 1), ( 'Kevin Kline', 1, 0), ( 'Ron
Livingston', 1, 0), ('Mike Judge', 0, 1);
$results = mysql_query($people) or die(mysql_error());
Thus I could insert into the table I describe above as:
mysql insert into checks (original_check, hotel_pos, number) values
(123, 1, 456), (124, 1, 456), (125, 1, 443);
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select count(*) from checks;
+--+
| count(*) |
+--+
| 5542 |
+--+
1 row in set (0.04 sec)
mysql select * from checks limit 5539, 3;
+--++---++---
+---++--+-+-
++
| id | original_check | hotel_pos | number | revenuecenter |
open_time | close_time | employee | cashier | restauranttable | covers |
+--++---++---
+---++--+-+-
++
| 5540 | 123| 1 | 456| NULL
| || NULL | NULL| NULL|
NULL |
| 5541 | 124| 1 | 456| NULL
| || NULL | NULL| NULL|
NULL |
| 5542 | 125| 1 | 443| NULL
| || NULL | NULL| NULL|
NULL |
+--++---++---
+---++--+-+-
++
3 rows in set (0.01 sec)
Douglas Sims
[EMAIL PROTECTED]
On Jun 1, 2006, at 4:12 AM, Mark Sargent wrote:
Hi All,
if a table has an auto_incremented primary key why does the below
code require the people_id to be manually inserted? I got this from
Beginning PHP, Apache, MySQL Web Development book from Wrox.
Curious, as it seems to defeat the purpose of auto_increment, no?
Cheers.
$people = INSERT INTO people (people_id, people_full_name,
people_isactor, people_isdirector) VALUES (1, 'Jim Carey', 1, 0),
(2, 'Tom Shadyac', 0, 1), (3, 'Lawrence Kasdan', 0, 1), (4, 'Kevin
Kline', 1, 0), (5, 'Ron Livingston', 1, 0), (6, 'Mike Judge', 0, 1);
$results = mysql_query($people) or die(mysql_error());
Mark Sargent
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Effective-dating
Does anyone know of a thorough discussion of effective dating on the
web, particularly with respect to MySQL, or have any opinions you
could share?
I've worked with effective-dated tables in MS SQL Server and never
been particularly awe-struck by how well it works. I can think of
three ways of doing it:
1) Store a Begin date and an End date for each row and then
reference the row with 'WHERE {transaction date} BETWEEN {begin} AND
END. This inevitably winds up with overlapping rows that shouldn't
be or gaps where you don't want them, and also requires an extra date
column, but the select queries are simplest. Also, what about
indexing the dates?
2) Store an Expires date with each row, but then to find the actual
row you have to do a subselect or some messy joins and I'm not at all
confident this will be optimized reasonably.
3) Store an Effective as of date with each row but this has
essentially the same problem as 2.
None of the SQL books on my shelf even mentions this, including
Jeremy Zawodny's Hi-Performance MySQL and the MySQL Reference
Manual.
This page is interesting but doesn't explain the different options
nor try to analyze which is best and under what circumstances: http://
llamasery.com/forums/showthread.php?p=34945
Strangely enough, most of what I find by googling the topic
effective dating has to do with meeting girls efficiently - which
is also interesting, but outside the scope of this list and not
immediately relevant to the system I'm working on.
Douglas Sims
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database compatibility
I think it's better to pick one database and try to design and code to its strengths and weaknesses. I've worked on several projects in which the overall architectural plan involved writing a database abstraction layer to keep options open as to the specific relational db - MySQL, Oracle, MS SQL, etc. None of these projects has gone particularly well. I've worked on other projects where the focus was on making things work well with whatever database was chosen and not worrying so much about keeping the options open, and these projects have gone much better. And in neither case has the project actually ever switched databases, so the effort spent in trying to preserve compatibility was wasted. If you stick to the lowest common denominator in your SQL you won't be taking advantage of any of the unique benefits which the different servers bring. MySQL is my favorite database. For web work it has several significant advantages over other databases - the LIMIT clause alone is unique to MySQL and it's worth using MySQL just for that. For example, if you use MS SQL and .Net, the entire results of queries are sent from the DB server to the web server and pagination is done on the web server. This moves a lot more data over network connections, ties up more memory in the db and web server, and slows things down. With the LIMIT clause, only the rows to be displayed on the current page are sent over the wire. If you try to use only the ANSI standard features of MySQL you will miss out on this advantage. Here is an interesting article about Google's switch from MySQL to Oracle for AdWords: http://xooglers.blogspot.com/2005/12/lets-get- real-database.html I believe (can anyone from the big G confirm or correct?) that AdWords has been moved back to MySQL. Oracle has some neat features for handling trees and hierarchical data (CONNECT BY etc) which won't work in any other version of SQL. If you're going to use Oracle and you have data which is best represented in trees, it would be a mistake to not use Oracle's built- in tree features. Oracle is very nice, but it doesn't give you any speed over MySQL and you can put a man on the moon for less money. Microsoft SQL Server is very nice if you like that sort of thing and Sybase is pretty much the same thing only the port numbers have been changed to protect the innocent. I'm sure DB2 and Postgres and Informix and all the others are very nice too. Stored procedure syntax is significantly different among the major dbs, so if you're going to limit yourself to standard sql there's no point in using stored procedures. Which isn't really a bad thing on the whole. Good luck! On Mar 22, 2006, at 11:30 PM, David Griffiths wrote: That's a pretty difficult request: 1) What versions? MySQL 4.0 has no stored procedures, functions, views, subselects. 4.1 added subselects. 5.0 added the rest. Oracle 8i doesn't support ANSI-style outer joins (they use a proprietary format). 2) Different engines; MySQL supports federated, archive, MyISAM, InnoDB in 5.0 (there are probably a few others). Each engine has different DML (Data Manipulation Language - select, insert, update, delete) and DDL options (ALTER TABLE, CREATE TABLE, etc). 3) Built-in funtions vary widely (though there are some common ones, the format and structure can differ). That's just touching the surface (I have 5 minutes while a database machine reboots, so I thought I'd post a reply). I am not sure what you are after, but you might want to consider an existing ORM (Object-Relational) tool that does the SQL for you. Hibernate for Java is amazing, and NHibernate is now out for .NET (not sure if it's alpha, beta or production). If you are coding to experiment, I'd suggest you limit yourself to a few (MySQL-InnoDB is very popular, and Postgres). Both free, with lots of good online-documentation available. Check out this article: http://www.devx.com/dbzone/Article/20743 David ChadDavis wrote: Does anyone know of a resource ( on the web perhaps ) that discusses the core differences between the different database's sql. I'm trying to write code that produces the correct sql for a variety of databases. Such things as Oracle's SEQUENCES versus mysql's AUTO_INCREMENT are of interest. Maybe I'm asking too much to find a summary of such differences. But I'm only interested in using mainstream sql functinality, nothing complicated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Douglas Sims [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~BCP for mysql~
Try SELECT INTO OUTFILE... Here's the MySQL documentation on that: http://dev.mysql.com/doc/ refman/5.0/en/select.html Good luck! On Mar 22, 2006, at 1:29 AM, Mohammed Abdul Azeem wrote: Hi, Can we any use bcp to perform bulk copy for MYSQL as in SYBASE ? If not, is there any other tool which can do the same operation. I need to perform a bcp out from Mysql and then bcp in to Sybase. Please help me out regarding the same. Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Douglas Sims [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is the sql command to export the whole database ?
mysqldump -A -u {username} -p {thefilenameyouwanttoputitin}
On Mar 19, 2006, at 8:47 PM, tony vong wrote:
What is the sql command to export the whole database ?
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Douglas Sims
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Wildcards in Query
It sounds as if you need to use a regular expression. For very simple string comparisons, use =, as in _wbs='Fish'_ For more complex string comparisions with simple wildcards, use LIKE as in _wbs LIKE %fish% _For most complex comparisions, use a regular expression, as in _wbs REGEXP .\d_ In the REGEXP example I listed above, the pattern will match all strings which contain one character (.) followed by one digit (\d). If you want to only match strings which /start/ with one chracter followed by one digit, for example, you would say ^.\d. . represents any character \d represents only characters in the digits class (0-9) ^ means at the start of the string (if it's at the start of the regexp, otherwise it can mean not) Regular expressions are amazing things if used properly. http://www.mysql.com/doc/en/Pattern_matching.html Note that in MySQL, the wildcard characters are _ and %, where _ represents a single character and % represents any number of characters, unlike Access, where if I remember, # means one char and * means any number of characters (?) Good luck! For Jacque Scott wrote: I am converting over to mySQL from Access 2.0 and I am having a little trouble with a query. Here it is: SELECT Max(WBS) AS LastOfWBS FROM Projects Where((WBS)) Like txtEntryData(0).Text #%; I am trying to get the last WBS ID starting with a particular letter the user will type in the textbox. My criteria is that it has to start with a letter and the next character is a number. There can be letters or more numbers to the right of the first number. For example: A01C or B001, but not AB01. In Access we could use the following query: SELECT DISTINCT Max([Projects].[WBS]) AS LastOfWBS FROM Projects where (Projects.WBS) Like txtEntryData(0).Text #*; How can I insure when using mySQL that the second character is a number?
Re: can't install DBI on panther
I installed DBI and dbd:mysql on 10.3.2 a few months ago and had all sort of problems but I finally got it to work. I don't exactly remember what finally did it, though. I think it might have been running the install with sudo, as in: sudo perl -MCPAN ... etc. but I'm not sure. If you haven't tried that, perhaps it will work. I've been trying to install GD (gd-2.0.21) off and on for a few days now and meeting with the same frustration. I build zlib, libpng, jpeg-6b without any errors, but gd blows up when I make. Here is the specific part of the build which blows up: gcc -DHAVE_CONFIG_H -I. -I. -I. -I/usr/X11R6/include -I/usr/X11R6/include/freetype2 -I/usr/X11R6/include -g -O2 -MT gd_jpeg.lo -MD -MP -MF .deps/gd_jpeg.Tpo -c gd_jpeg.c -fno-common -DPIC -o .libs/gd_jpeg.lo gd_jpeg.c:41:21: jpeglib.h: No such file or directory gd_jpeg.c:42:20: jerror.h: No such file or directory gd_jpeg.c:54: error: parse error before cinfo gd_jpeg.c: In function `fatal_jpeg_error': I don't find gd_jpeg.lo anywhere, but I'm not sure what this means. I find gd_jpeg.Plo in the .deps directory. I'm sorry, I don't know what a .Plo or .lo file is. The .Plo file just contains #dummy. This is a bit off-topic from mysql or the original question, I'm afraid. tait sanders wrote: i've run both 'perl -MCPAN -eshell' and 'make DBI' and both come back reporting heaps of errors like the following: from Perl.xs:1: /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:380: 30: sys/types.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:411: 19: ctype.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:423: 23: locale.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:440: 20: setjmp.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:446: 26: sys/param.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:451: 23: stdlib.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:1749: error: parse error before STRLEN /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:1749: warning: type defaults to `int' in declaration of `STRLEN' /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:1749: warning: data definition has no type or storage class In file included from /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/handy.h:121, from /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:1805, from DBIXS.h:19, from Perl.xs:1: /usr/include/gcc/darwin/3.3/inttypes.h:33:72: sys/cdefs.h: No such file or directory /usr/include/gcc/darwin/3.3/inttypes.h:34:56: machine/ansi.h: No such file or directory In file included from /usr/include/gcc/darwin/3.3/inttypes.h:35, from /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/handy.h:121, from /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:1805, from DBIXS.h:19, from Perl.xs:1: /usr/include/gcc/darwin/3.3/stdint.h:24:27: machine/types.h: No such file or directory In file included from /usr/include/gcc/darwin/3.3/inttypes.h:35, from /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/handy.h:121, from /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:1805, from DBIXS.h:19, from Perl.xs:1: /usr/include/gcc/darwin/3.3/stdint.h:34: error: parse error before uint8_t /usr/include/gcc/darwin/3.3/stdint.h:35: error: parse error before uint16_t /usr/include/gcc/darwin/3.3/stdint.h:36: error: parse error before uint32_t /usr/include/gcc/darwin/3.3/stdint.h:37: error: parse error before uint64_t /usr/include/gcc/darwin/3.3/stdint.h:41: error: parse error before int_least8_t /usr/include/gcc/darwin/3.3/stdint.h:42: error: parse error before int_least16_t /usr/include/gcc/darwin/3.3/stdint.h:43: error: parse error before int_least32_t etc etc etc this just goes on and on... what am I to do to get DBI installed on my OS10.3?? please help ta tait On 22/01/2004, at 5:42 AM, Moritz von Schweinitz wrote: i've never used MT, but this kinda sounds as if you dont have the DBI installed: $ perl -MCPAN -eshell install DBI cheers, M. tait sanders wrote: hi all, not sure if this is the appropriate place to post this -- so please point me in the right direction if not here... I have a G4 with os10.3.2 server, mysql v4.0.16, and MoveableType 2.6 I'm trying to initialise the moveabletype system by running mt-load.cgi and am recieving the following error: Unsupported driver MT::ObjectDriver::DBI::mysql: Can't locate DBI.pm in @INC... I've checked in my mt.cfg file and I have added: ObjectDriver
Re: MYSQL Database
Hi You should check out: http://onlamp.com/ L.A.M.P. (Linux/Apache/MySQL/Perl(or PHP) are becoming the de facto standards for web-based applications, I think far eclipsing Java (JSP/Servlets) and Microsoft ASP/VB. Unlike Java (which is driven to a large degree by Sun's promotion) and ASP (heavily promoted by MS), LAMP has become so widespread because it's just really good (and cheap).http://news.netcraft.com/archives/web_server_survey.html I personally prefer to program in Perl, which is The Coolest Language Ever Invented, although Java has advantages. C/C++ for server-side programming are great if you have lots of money and time and are concerned handling massive amounts of traffic. ASP (Visual Basic) is really terrible. Although I do a lot of work in it, I don't like it. It does not have the same semantic versatility of C-based languages like Perl. And regular expressions in VB are a heinous pastiche of the true elegance of regular expressions in Perl. I'm sure many people will disagree vociferously with my opinions here and they may have good points also, which I have neglected. Programming languages are like indentation styles - you can do a very fine job with different ones, and yet most people become very particular about their own styles and hate working with others. One might also dispute my argument that LAMP is far more widespread than ASP or Java as the survey I cited doesn't really consider server-side programming language, just servers, but I suspect far more people are running mysql/[php|perl] on linux than anything else and the server-side languages used probably mirror this. Perhaps someone else can offer better statistics. In short, I would use Linux/Apache/MySQL/Perl. Now I'm afraid I will have roused the VB or Java crowds. Perhaps I should sign this with an assumed name? /Alfred E. Neuman/ Seena Blace wrote: Hi, I'm new to this group.I would like to know which frontend tools be good tuned with Mysql database like php,perl etc? I want to develop one application on linux on mysql database which eventually would be webbased.Please suggest what combination would be good. thx -Seena - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about IF statements...
Would something like this do what you want? SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0; Cory Hicks wrote: Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! Cory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about IF statements...
Like this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.13 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. umysql use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql create table time_daily (project_id int(3) primary key auto_increment, time_worked int(3), user_id varchar(3), period_id varchar(3)); Query OK, 0 rows affected (0.06 sec) mysql insert into time_daily (null, 3, 'clh', '27'); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'null, 3, 'clh', '27')' at line 1 mysql insert into time_daily (time_worked, user_id, period_id) values (3, 'clh', '27'); Query OK, 1 row affected (0.52 sec) mysql insert into time_daily (time_worked, user_id, period_id) values (0, 'clh', '27'); Query OK, 1 row affected (0.00 sec) mysql insert into time_daily (time_worked, user_id, period_id) values (5, 'clh', '27'); Query OK, 1 row affected (0.00 sec) mysql SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0 - ; ++---+ | project_id | total | ++---+ | 1 | 3 | | 3 | 5 | ++---+ 2 rows in set (0.18 sec) Douglas Sims wrote: Would something like this do what you want? SELECT project_id, SUM(time_worked) AS total FROM time_daily WHERE user_id='clh' AND period_id='27' GROUP BY project_id HAVING total0; Cory Hicks wrote: Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! Cory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing a dumpfile
Hi Mat
mysqldump produces files containing SQL statements. mysqlimport allows
you to load data from comma-delimited (or other) text files.
For example, the following line will dump the contents of the table
'goat_painters' in the database 'the_goat_database' into a file called
'goat_painters.sql':
mysqldump the_goat_database.goat_painters -u mat goat_painters.sql
And the file 'goatowners.sql' will contain something like this:
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES
('Hayes', '123 Maple Street', 3);
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES
('Delaney', '123 Birch Blvd', 4253);
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES
('Wesley', '418 Oak Ave', 92);
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES
('Doug', '418 Oak Ave', 7);
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES
('Rachel', '123 Maple Street', 326);
INSERT INTO table1 (name, address, number_of_goats_painted) VALUES
('Amy', '123 Maple Street', 0);
...
And you could recreate this information into the database with:
mysql the_goat_database -u mat goat_painters.sql
whereas if you executed the command (e.g., from within the mysql
command-line client):
SELECT INTO OUTFILE 'goat_painters.txt' name, address,
number_of_goats_painted FROM goat_painters;
you would get a file called goat_painters.txt containing something like
this:
'Hayes', '123 Maple Street', 3
'Delaney', '123 Birch Blvd', 4253
'Wesley', '418 Oak Ave', 92
'Doug', '418 Oak Ave', 7
'Rachel', '123 Maple Street', 326
'Amy', '123 Maple Street', 0
...
And you could then import that file directly into mysql using mysqlimport.
If you've used mysqldump, you will have a sql script (batch) file and
you should use the method like this:
mysql the_goat_database -u mat goat_painters.sql
I hope that helps.
Matthew Stuart wrote:
Right having just got to grips with the mysqldump command, I would
like to be able to know how to import the database back in to MySQL
should anything happen to my PC.
Does mysqlimport have to be done in the command line window like
mysqldump, and if so, how? It's just that I tried to import stating
terminated, enclosed, escaped, etc and by the time I had come to list
the db name to import in to and the path to the file I wish to import,
the window wouldn't let me type anymore. Why? Did it get as bored as I
did?
What syntax do you people out there use?
Mat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimization help
I think... you don't have an index on the Incident field itself, just
on (Date, Incident, Type, Task) which means that it concatenates those
fields and orders the result - thus this may be virtually useless if
you're looking for a specific incident within a large date range. Since
your query has a specific incident number, indexing that field would
probably help a lot.
Do a SHOW INDEXES FROM DeltaPAF;
To see the indexes that are actually there.
or
EXPLAIN SELECT Date FROM DeltaPAF WHERE Date=2003-12-11 AND
Date=2004-01-11 AND Incident=98996144;
to see which indexes MySQL is really using.
For example, in the table below, there are really only two indexes, the
one primary key index and the second name index. The Seq_in_index
column shows the fields that are included in the index but the ones that
aren't listed first will be much harder to find. Like a telephone
directory, which is ordered by lastname, firstname - both fields are
indexed but they are in the same index, so finding a specific firstname
still means a full table scan.
Good luck!
mysql describe test1;
++-+--+-++---+
| Field | Type| Null | Key | Default| Extra |
++-+--+-++---+
| name | varchar(20) | YES | MUL | NULL | |
| mydate | date| | PRI | -00-00 | |
| number | int(10) | | PRI | 0 | |
++-+--+-++---+
3 rows in set (0.00 sec)
mysql show indexes from test1;
+---++--+--+-+---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---++--+--+-+---+-+--++--++-+
| test1 | 0 | PRIMARY |1 | mydate | A
|NULL | NULL | NULL | | BTREE | |
| test1 | 0 | PRIMARY |2 | number | A
| 0 | NULL | NULL | | BTREE | |
| test1 | 1 | name |1 | name| A
|NULL | NULL | NULL | YES | BTREE | |
| test1 | 1 | name |2 | mydate | A
|NULL | NULL | NULL | | BTREE | |
| test1 | 1 | name |3 | number | A
|NULL | NULL | NULL | | BTREE | |
+---++--+--+-+---+-+--++--++-+
5 rows in set (0.15 sec)
Mike Schienle wrote:
Hi all -
I have a speed problem that I don't understand. I've been pretty
active with DB's for a few years, but I'm no expert, so let me know if
I'm missing the obvious. I have Paul DuBois' MySQL book (New Riders
edition) and Descartes and Bunce's Programming DBI book on my desk, so
feel free to reference something there if that will help.
Here's the table I'm working from and it's structure:
CREATE TABLE DeltaPAF (
Date DATE NOT NULL,
Type VARCHAR(4) NOT NULL,
Incident INT UNSIGNED NOT NULL,
Mgr VARCHAR(4) NOT NULL,
Site VARCHAR(40) NOT NULL,
Task ENUM('Proposed', 'Approved', 'Completed', 'Invoiced',
'Expired', 'Rejected', 'Cancelled') NOT NULL,
Webpage MEDIUMTEXT NOT NULL,
BudgetDECIMAL(12, 2) DEFAULT 0.00,
PRIMARY KEY (Date, Incident, Type, Task),
INDEX (Type, Mgr, Site)
);
I have about 125,000 records in the table and it's running on an older
400 MHz MacOS X 10.2.8 system. The MySQL version is 3.23.52.
The following query comes back with 210 records in about 0.6 seconds.
mysql SELECT Date FROM DeltaPAF WHERE Date=2003-12-11
- AND Date=2004-01-11 AND Incident=98996144;
However, this query comes back with 210 records in a little over 2
minutes.
mysql SELECT Budget FROM DeltaPAF WHERE Date=2003-12-11
- AND Date=2004-01-11 AND Incident=98996144;
Can someone clue me in how I might get the SELECT Budget query to
return in a similar time to the SELECT Date query? I tried adding an
index for Budget, knowing it shouldn't help, and it didn't. FWIW, the
Webpage fields average about 5K characters, but can be as much as 40K.
Mike Schienle, Custom Visuals
http://www.customvisuals.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL certification
Thanks, Stefan. Mike's article was interesting. The test was a bit harder than I anticipated. I should have paid more attention to column types and database name, among other things. But I did pass - at least, the preliminary report said pass, but also said that the exam will be reviewed and If you have met the passing score, you will receive your official certificate by mail. This puzzles me, because this exam isn't beta any more? Studying for and taking the exam was a good excercise and I'm glad I did. I learned things I didn't know about MySQL. Now I would like to take the Pro exam. I wrote out pages and pages of notes while studying. If anyone's interested, perhaps I will post those online. -Doug Stefan Hinz wrote: Douglas, I'm scheduled to take the MySQL certification exam tomorrow morning, thus currently intently cramming with the MySQL reference manual and writing out study notes etc. I'm not too worried as I've been using MySQL for years (although preping for this has been a good exercise and I've learned a number of useful things so far) but I wonder if anyone on this list has thoughts in general about the certification test or has taken this test and would like to offer any tips, hints, or cautions? I've taken the Core exam, but that was a long time ago (November 2002 :-). Mike Hillyer has taken (and passed) both exams, and he's written a nice summary about taking the Pro exam on his blog: http://www.vbmysql.com/mike/blog/archives/09.php If I do well on the test tomorrow, perhapas I'll post my study notes on-line; otherwise I may change my name, retire to the countryside and take up raising mangel-wurzels. Good luck! Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query]
Re: Join sintax question
Hi Giulio
I think you could do this by repeatedly left-joining the categories
table as in this:
SELECT AudioTrack.*
FROM AudioTrack A
LEFT JOIN AudioTracks_Categories C1 ON A.AudioTrack_id=C1.AudioTrack_id
LEFT JOIN AudioTracks_Categories C2 ON A.AudioTrack_id=C2.AudioTrack_id
LEFT JOIN AudioTracks_Categories C3 ON A.AudioTrack_id=C3.AudioTrack_id
...
WHERE C1.Category_id={first category} AND C2.Category_id={second
category} AND C3.Category_id={third category} ...
If you have indexes for the Audio_Tracks_Categories.Category_id column
and the AudioTrack.AudioTrack_id column, this should be more efficient
that you might think.
Also, I think a slightly more efficient way of doing the first query you
sent, the OR query, would be to GROUP BY the AudioTracks_Categories,
as this way it will have to join fewer rows from the AudioTrack table,
as this:
select AudioTrack.* from AudioTrack left join AudioTracks_ Categories on
AudioTrack.AudioTrack_id = AudioTracks_ Categories.AudioTrack_id where
AudioTracks_ Categories.Category_id = somevalue or AudioTracks_
Categories.Category_id = somevalue... GROUP BY Categories.Category_id
Does anyone else agree or disagree with this idea???
Good luck.
-Doug
Giulio wrote:
HI all,
I have two tables, let's say AudioTrack and Category
Every AudioTrack record can belong to one or more ( or none ) Categories.
I have created an intermediate table, AudioTracks_ Categories
containing only the IDs of AudioTrack and Category to keep track of
the link.
I can easily find AudioTrack records for a given Category id:
select AudioTrack.* from AudioTrack left join AudioTracks_ Categories
on AudioTrack.AudioTrack_id = AudioTracks_ Categories.AudioTrack_id
where AudioTracks_ Categories.Category_id = somevalue
I can also easily find AudioTrack records for a given Category id OR
others Category id, adding distinct to avoid row duplication for
AudioTrack records contained in more than one of the specyfied
categories:
select distinct AudioTrack.* from AudioTrack left join AudioTracks_
Categories on AudioTrack.AudioTrack_id = AudioTracks_
Categories.AudioTrack_id where AudioTracks_ Categories.Category_id =
somevalue or AudioTracks_ Categories.Category_id = somevalue...
Now the question is, how can I handle an AND query, I mean how can I
have returned only the AudioTrack records that belongs to ALL the
Category IDs I put in the query?
Hope the question was clear... :)
thanks in advance for your answer,
Giulio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL certification
I'm scheduled to take the MySQL certification exam tomorrow morning, thus currently intently cramming with the MySQL reference manual and writing out study notes etc. I'm not too worried as I've been using MySQL for years (although preping for this has been a good exercise and I've learned a number of useful things so far) but I wonder if anyone on this list has thoughts in general about the certification test or has taken this test and would like to offer any tips, hints, or cautions? If I do well on the test tomorrow, perhapas I'll post my study notes on-line; otherwise I may change my name, retire to the countryside and take up raising mangel-wurzels. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG IN MYSQL
I also ran the test, using MySQL 4.0.16, for apple-darwin6.6 (powerpc) on a Mac iBook G4 w/Panther and got no errors from mysqlcheck. You might try using mysqlbug to compose the bug report: http://www.mysql.com/doc/en/Bug_reports.html Hassan Schroeder wrote: Richard S. Huntrods wrote: I've submitted this problem three times now, and been ignored all three times. I guess bugs are simply not popular. Since the problem described below is 100% repeatable, Uh, well, maybe not quite :-) I've distilled the problem I'm having with DELETE/INSERT to an even simpler test case. I just ran your test case on a Win2000 box w/ MySql 4.0.14 (standard binary) without problem; mysqlcheck following it reports no problems with any table in the DB used. FWIW! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change from loop to single query
You probably want the IN comparison operator
(http://www.mysql.com/doc/en/Comparison_Operators.html)
For example:
UPDATE users SET status=no WHERE name IN ('Joe', 'Wally', 'Bob', 'Cynthia');
Of course, you can create this statement from the list of names by
joining all of the names with commas.
Good luck!
-Doug Sims
Jonathan Villa wrote:
I have a loop which is similar to the following:
while(array contains elements) {
UPDATE users SET status = no WHERE name = array[i]
}
great, it works but the query runs many times. I want to make only one
call to the database and have all the elements in the array be included
in the query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
