Some mysql questions

Robthedog

Hello! I am designing my first mysql/php database and there are a few things I can't get working... DAM! Could any of you help me with at least one of these questions?

I want to print some reports from the database - a webpage is not suitable for this. Is there a simple, free way of getting reports in WinXP from a mysql database (localhost)? Access, Excel or any other Office app that can import the data from mysql? How do you usually make reports in mysql? I actually found an application for this that i like very much, Navicat http://www.navicat.com/ but I can't pay 125$ for this function.
I can't get one of my queries to work...

SELECT count( shop_itemtrafik.item_id ) AS item, shop_itemtrafik.prodnummer, shop_prod.namn, shop_prod.chapter_pris, count( shop_itemtrafik.item_id ) * shop_prod.chapter_pris AS tillsammans
FROM shop_itemtrafik, shop_nollat
LEFT JOIN shop_prod ON shop_itemtrafik.prodnummer = shop_prod.prodnummer
WHERE shop_itemtrafik.chapter_id =4
GROUP BY shop_prod.namn
HAVING shop_itemtrafik.ld > MAX( shop_nollat.nollat )

MySQL said:

#1054 - Unknown column 'shop_itemtrafik.ld' in 'having clause'

The column that mysql nags about does exist. both fields in the having-clausule are date-fields and I just want to have all the results after a certain date... Without the having-clausule the query works fine. What's the prob? Am I doing something against mysql rules?

In PHP, how do I SUM or count the values returned from mysql to an array? The only way to sum up several rows (here one value per row) I found was this:
while($count = @mysql_fetch_row($sql)){
$sum=$sum+$count[0];
};

Actually, how do you store the values in a query with a result of many columns and many rows in a variable in PHP, and how do you echo it later - now I have only used a while loop to fill a table, until there are no more rows. Like this:

while ($row = mysql_fetch_row ($result)) {
echo "
<tr>
<td>".$row[1]."</td>
<td>".$row[2]."</td>

and so on.

I am very grateful for any help! I have only come this far by reading your posts on this forum. Thanks.

tomhath

1) Excel is pretty easy, just make an HTML table and put the right header on the response. But of course it only works if the user has excel installed on their machine:

http://www.phpfreaks.com/quickcode/HTML_to_Excel/219.php

2) Your query has a lot of problems. How to fix them all depends on what version of MySQL you are using.

3) Not sure what you are trying to do. SQL has a SUM function, but the while loop you have is okay.

Tracer

You can always check out

www.fpdf.org

To make Adobe PDF reports... or for OpenOffice integration.. read the latest version of PHP Architect here

PHP Architect

Robthedog

tomhath - Thanks for that Excel link! I'll try it out.

As for the mysql version - I'm not sure which version it is, I'll check it out. It should be pretty up to date anyway.

SQL has a SUM function...

That's just it. I don't want to sum up anything in my mysql query. I want to do some calculations on the results later in my php script - I just don't know how this is usually done. Both my while-loops work fine, but what if I dont want to list the results in a table - instead I want to do something else with them - In what kinds of variables do I store the results in PHP and how do I retrieve the results later?

//Rob