Jump to content

RedMist

Members
  • Posts

    17
  • Joined

  • Last visited

    Never

Everything posted by RedMist

  1. I think it's sum( IF( price BETWEEN 0 and 250, price, 0 )) Asum But that's perfect! Thank you very much indeed! (Mark as solved please. I can't see how to do it myself!)
  2. Ooh, now this looks like it could work!!!.. but I'm getting an 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 '), price, 0 ) Asum FROM orders But I can't see an error there. Right number of brackets, etc.. Does it need an END IF somewhere?
  3. Oh... by the way... MySQL client version: 5.0.45 table : orders fields : order_date | price (Other fields dont matter)
  4. I'd like to analyse orders made in the last 6 months by price and numbers, broken down by price range. I've managed to create a MySQL which breaks down the orders by numbers... SELECT year(order_date) y, month(order_date) m, count(*) total, sum( price BETWEEN 0 and 250 ) A, sum( price BETWEEN 251 and 500 ) B, sum( price BETWEEN 501 and 1000 ) C, sum( price BETWEEN 1001 and 2500 ) D, sum( price BETWEEN 2501 and 5000 ) E, sum( price BETWEEN 5001 and 15000 ) F, sum( price BETWEEN 15001 and 25000 ) G, sum( price > 25000 ) H FROM orders WHERE order_date > date_sub(curdate(), INTERVAL 6 MONTH) GROUP BY y,m You get something like this.. y m total A B C D E F G H 2008 9 6 2 1 3 0 0 0 0 0 2008 10 13 10 1 0 2 0 0 0 0 2008 11 43 32 9 2 0 0 0 0 0 2008 12 40 35 3 1 1 0 0 0 0 2009 1 9 8 1 0 0 0 0 0 0 2009 2 8 5 3 0 0 0 0 0 0 Which is the number of orders in each price bracket. i.e in Feb 09, we had 5 orders under $250.00 My question is, how can I now return the total value of all the orders in each price bracket? i.e in Feb 09, we had 5 orders under $250.00, worth $1100 If necessary, I can use a completely different MySQL statement.
  5. Check out http://www.fpdf.org/ I used it recently and it's reasonably easy.
  6. In case anyone reads this thread, and needs to know the answer, this is what I did.... SELECT * FROM mytable WHERE STR_TO_DATE(concat(year,'/',month,'/01'), '%Y/%m/%d') > date_sub(curdate(), INTERVAL 6 month) Thanks for the help
  7. I didn't want to show what i've tried already, because I thought there might be a better way, but I was trying something like htis..... SELECT * FROM stats WHERE DATE(CONCAT('01', '-', month, '-', year)) > datesub(curdate(), interval 6 month) But obviously it doesn;t work, and I could not get it to...
  8. Is there no way to "make" a date, on the fly, from the month and year numbers, and use that in a WHERE? I have scoured the MySQL online manual, and I'm sure there must be a way.
  9. When you do a $itema[] = $qryqresult['item']; this adds an element to the end of the array called $itema. array ("a","b","c","d","e")....... Then, using implode you gradually build up a long string "a,b,c,d,e" and search another table for "a,b,c,d,e" and it doens't find anything? is this really what you want? To debug: 4 lines down, add a line print_r($itema); to see what I mean. Also, instead of $qeuryitem = mysql_query("select moneyexcellent from itemstats where item = '".$itemarray."'") or die(mysql_error()); do this.. $SQL="select moneyexcellent from itemstats where item = '".$itemarray."'"; echo $SQL; $qeuryitem = mysql_query($SQL) or die(mysql_error()); (Also watch out for that spelling mistake in qeuryitem )
  10. Glad I could help. I know what it like to be stumped!!!
  11. Unfortunately, I have inherited this schema so I cannot change it.
  12. I have data stored in the following format... year, month, total (all smallints) e.g. 2008 9 6 2008 8 6 2008 7 50 2008 6 12 2007 5 77 2007 3 19 How can I write a query to list the last 6 months totals? MySQL 5.1
  13. Well it's tricky, because I don't know the data your reading. however......... I dont know if you need the foreach at all, just remove the loop and try $chk = $row[$names]>0?'checked = "checked"':""; .... but I diont know what it meant to do....soo.......
  14. Some good advice. I stored the data in a serialized session variable, and all is well. Thanks guys.
  15. If I understand your code correctly, the problem is your foreach loop constantly overwrites $chk so it's only valid for the very last field in $row.
  16. I'm not tried this, but I think... $file = 'file.exe'; exec("zip -9 file.zip $file"); header("Location: file.zip"); exit; Should make it as small as possible, but it's also slower.
  17. After I display a table of data on my screen, I'd like to show a "Download CSV file" link. Is there a way to do this, without going to a new page and outputing header, etc, and without creating a physical file on the server disk. I'd like it to act as if there is a physical file on the server, so when you click, you get the download dialog box appear. I don't want to refresh the same page, as the MySQL query is quite intensive, but I don't want to lose the data on screen when I download the CSV either. (BTW, I'm ok generating CSV output, just the mechanics of downloading I need help with) (BTW, H! - First Post - I'm Rob, PHP programmer of a few years, and willing to help)
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.