Re: Possible corrupt data?
Marc, From what you say it seems a logical problem with your data, And it cannot be corrupted at database level. Check your application logs. You need to know why order does not succeed. Cheers Claudio On Jun 18, 2011 9:52 PM, sono...@fannullone.us wrote: I think I may have corrupt data. In one of my shopping cart installs, no orders were getting written to the orders table. Other tables, like the customers table, were being written to just fine. In phpMySQL, I ran check, optimize, repair, and analyze on the orders table, and the tests all returned 'OK'. So I decided to export the table, delete it, then recreate it and insert the data using the export file. No change. Then I thought I might have bad data. So I deleted the table again but this time only recreated it using the CREATE TABLE IF NOT EXISTS portion of the export. Without re-importing the data, I made a test order in the cart, it was successfully written to the table. Now I'm leery about re-importing the old data for fear it will put me back in the same boat. How can I test the data and clean it so that I can put it back? Thanks, Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Optimizing column widths
* Dan Nelson dnel...@allantgroup.com [110618 16:33]: In the last episode (Jun 18), Tim Johnson said: Is there an optimal 'alignment' for column widths for varchar types? I.E., divisible by 8 or 10 or by powers of 2? No. Varchar fields are stored using only as many bytes as are in that particular entry. The size in the column definition is simply the maximum allowed size. http://dev.mysql.com/doc/refman/5.5/en/char.html Thanks Dan. :) Just checking. -- Tim tim at johnsons-web dot com or akwebsoft dot com http://www.akwebsoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SELECT records less than 15 minutes old
Hi, I'm trying to write a statement that will return all records that match a particular order_id and that have a timestamp within the last 15 minutes. I thought that this should work: SELECT * FROM `records` WHERE `order_id` = $order_id AND (`time_stamp` = DATE_SUB(NOW(), INTERVAL 15 MINUTE)) but it returns zero rows, no matter what. If I up the interval to something huge, like 15000, it will then return records. Very strange. It's almost like it's using seconds, not minutes. Is my syntax wrong? Thanks, Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT records less than 15 minutes old
just a quick debug: SELECT time_stamp,DATE_SUB(NOW(), INTERVAL 15 MINUTE) FROM `records` WHERE `order_id` = $order_id order by time_stamp desc limit 10; what do you get? 2011/6/19 sono...@fannullone.us Hi, I'm trying to write a statement that will return all records that match a particular order_id and that have a timestamp within the last 15 minutes. I thought that this should work: SELECT * FROM `records` WHERE `order_id` = $order_id AND (`time_stamp` = DATE_SUB(NOW(), INTERVAL 15 MINUTE)) but it returns zero rows, no matter what. If I up the interval to something huge, like 15000, it will then return records. Very strange. It's almost like it's using seconds, not minutes. Is my syntax wrong? Thanks, Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: SELECT records less than 15 minutes old
On Jun 19, 2011, at 11:11 AM, Claudio Nanni wrote: just a quick debug: Thanks, Claudio. It turned out to be that NOW() was using the server's time and my timestamp was based on my timezone. After fixing that, the SELECT statement works properly. Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
calculate the total amount of revenue each month-year
Please help us calculate the total amount of revenue each month-year from the two databases below. The first database is the name of the item and price. The second database is the goods sold. I Will Make a recapitulation of every month to my total income (total only). I've always tried but failed. TABLE A (item name and price) NO : CODE : NAME : PRICE(USD) : -- 1 : A01 : NAME A : 20 2 : A02 : NAME B : 15 - TABLE B (items sold) - : NO : CODE : CITY : QTY : MONTH : YEAR : --- : 1: A01: PARIS : 20 : 1 : 2011 : : 2: A01: LONDON : 11 : 1 : 2011 : : 3: A02: PARIS : 15 : 1 : 2011 : : 4: A02: PARIS : 10 : 1 : 2011 : : 5: A01: PARIS : 7 : 2 : 2011 : : 6: A01: LONDON : 8 : 2 : 2011 : : 7: A02: LONDON : 10 : 2 : 2011 : the result will be like this NO : DATE (month year): Total (USD) --- 1 : 1 - 2011: 995 2 : 2 - 2011: 450 We have tried but does not match the sum qyt. After I check the price turns out to be called just only the price of A even for multiplication NAME A02 CODE. Over its support I thank you. Haidapesebe __ Information from ESET NOD32 Antivirus, version of virus signature database 6221 (20110619) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
Re: calculate the total amount of revenue each month-year
SELECT CONCAT(b.month, '-', b.year) AS Date, SUM(b.quantity * a.price) AS Total FROM items AS a, orders AS b WHERE a.code = b.code GROUP BY month, year; where items is Table A and orders is Table B Thanks Aveek On Jun 20, 2011, at 9:27 AM, HaidarPesebe wrote: Please help us calculate the total amount of revenue each month-year from the two databases below. The first database is the name of the item and price. The second database is the goods sold. I Will Make a recapitulation of every month to my total income (total only). I've always tried but failed. TABLE A (item name and price) NO : CODE : NAME : PRICE(USD) : -- 1 : A01 : NAME A : 20 2 : A02 : NAME B : 15 - TABLE B (items sold) - : NO : CODE : CITY : QTY : MONTH : YEAR : --- : 1: A01: PARIS : 20 : 1 : 2011 : : 2: A01: LONDON : 11 : 1 : 2011 : : 3: A02: PARIS : 15 : 1 : 2011 : : 4: A02: PARIS : 10 : 1 : 2011 : : 5: A01: PARIS : 7 : 2 : 2011 : : 6: A01: LONDON : 8 : 2 : 2011 : : 7: A02: LONDON : 10 : 2 : 2011 : the result will be like this NO : DATE (month year): Total (USD) --- 1 : 1 - 2011: 995 2 : 2 - 2011: 450 We have tried but does not match the sum qyt. After I check the price turns out to be called just only the price of A even for multiplication NAME A02 CODE. Over its support I thank you. Haidapesebe __ Information from ESET NOD32 Antivirus, version of virus signature database 6221 (20110619) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: calculate the total amount of revenue each month-year
I've tried it and succeeded. Thanks for the help Mr. Misra. Best regards, Haidarpesebe . - Original Message - From: Aveek Misra ave...@yahoo-inc.com To: HaidarPesebe haidarpes...@gmail.com Cc: MySQL Lists mysql@lists.mysql.com Sent: Monday, June 20, 2011 11:28 AM Subject: Re: calculate the total amount of revenue each month-year SELECT CONCAT(b.month, '-', b.year) AS Date, SUM(b.quantity * a.price) AS Total FROM items AS a, orders AS b WHERE a.code = b.code GROUP BY month, year; where items is Table A and orders is Table B Thanks Aveek On Jun 20, 2011, at 9:27 AM, HaidarPesebe wrote: Please help us calculate the total amount of revenue each month-year from the two databases below. The first database is the name of the item and price. The second database is the goods sold. I Will Make a recapitulation of every month to my total income (total only). I've always tried but failed. TABLE A (item name and price) NO : CODE : NAME : PRICE(USD) : -- 1 : A01 : NAME A : 20 2 : A02 : NAME B : 15 - TABLE B (items sold) - : NO : CODE : CITY : QTY : MONTH : YEAR : --- : 1: A01: PARIS : 20 : 1 : 2011 : : 2: A01: LONDON : 11 : 1 : 2011 : : 3: A02: PARIS : 15 : 1 : 2011 : : 4: A02: PARIS : 10 : 1 : 2011 : : 5: A01: PARIS : 7 : 2 : 2011 : : 6: A01: LONDON : 8 : 2 : 2011 : : 7: A02: LONDON : 10 : 2 : 2011 : the result will be like this NO : DATE (month year): Total (USD) --- 1 : 1 - 2011: 995 2 : 2 - 2011: 450 We have tried but does not match the sum qyt. After I check the price turns out to be called just only the price of A even for multiplication NAME A02 CODE. Over its support I thank you. Haidapesebe __ Information from ESET NOD32 Antivirus, version of virus signature database 6221 (20110619) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 6221 (20110619) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 6221 (20110619) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
