PHP/MySQL Help Needed

steroyle · 2 Apr 2011 at 22:00

I am trying to create a script to process a csv of my bank statement that I have downloaded from my online banking.

I have imported the csv file and info has been saved into my table called "data" I am then flagging each row to say whether it is wages, bills, food shopping etc.
This information is then saved into my "data_type" table.

I now want to create a results page where all the totals for each data type is added up e.g.

Food Shopping: £X.XX
Bills: £X.XX

It is all test data so figures won't add up but hopefully someone understands what I am trying to do.

steroyle · 2 Apr 2011 at 22:11

Thanks whitecrook, that looks perfect I'll give it a go.

steroyle · 3 Apr 2011 at 16:17

I have use this but I am getting strange results, I think it might be due to the left join, any ideas?

Code:

$sql = "SELECT SUM(data.amount), data_type.name FROM `data` LEFT JOIN data_type on data.t_id = data_type.id GROUP BY data_type.name";
			 
$result = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_array($result))
{
	echo $row['data_type.name'] . " = " . $row['SUM(data.amount)'];
	echo "<br />";
}

steroyle · 3 Apr 2011 at 19:01

This is the results im getting after changing to SUM(`amount`) as mySumOfAmount:

= -1552.06
Bills = -13.00
= -1543.72
Bills = -21.34
= -1466.11
Bills = -98.95
= -1531.04
Bills = -98.95
Wages = 64.93
= -1500.00
Bills = -98.95
Wages = 33.89
Bills = -98.95
Food Shopping = -1500.00
Wages = 33.89

The data type for the amount is float.

Copy of sql here, thanks for looking.

steroyle · 3 Apr 2011 at 19:59

Thanks for checking it orderoftheflame, I have just tried it via phpmyadmin and can now see the same results you got but when doing it through php it doesn't work I must have something wrong.

steroyle · 3 Apr 2011 at 20:06

Just tried your edit code and im still getting weird results (it created 6 tables, one for each row) it must be something to do with me using xampp.

I'm fairly new to php so the obvious things aren't always obvious too me

steroyle · 3 Apr 2011 at 20:44

Fixed it, I had my code within a foreach that was looping each row hence the 6 tables. Moved it outside and it is working fine. Thanks to everyone who helped.