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.

whitecrook · 2 Apr 2011 at 22:05

http://www.tizag.com/mysqlTutorial/mysqlsum.php

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 />";
}

suarve · 3 Apr 2011 at 16:37

Firstly, what results are you getting?

Have you tried naming your calculated fields E.g. SELECT SUM(`amount`) as mySumOfAmount - then use $row['mySumOfAmount']

Also, what datatype are you using for the amount column - is it unsigned by any chance?

Actually, if you post the mysql to creat your tables with a bit of sample data I'll have a look for you now

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.

orderoftheflame · 3 Apr 2011 at 19:45

Running the query you posted:

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

Against the database you posted in the SQL file gets the result I'd expect:

SUM( data.amount ) name
-31.04 Bills
64.93 Child Benefits
-1598.95 Food Shopping

I can confirm it's nothing to do with your SQL.

Edit: Had a bit of a bash with php, suarve was right, just aliasing the column names works fine to get the output good for me.

Here's the whole shabang I ended up with if you're interested.

Code:

<?php
$con = mysql_connect("localhost","root");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("test", $con);

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

echo "<table border='1'>
<tr>
<th>Name</th>
<th>Sum</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['sum'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

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.

orderoftheflame · 3 Apr 2011 at 20:00

steroyle said:
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.

See my edit.

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

orderoftheflame · 3 Apr 2011 at 20:11

steroyle said:
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

Six tables doesn't sound right at all.

PHP isn't my forte either.

I've just taken your php from post #4 and run it against my local mysql and I get this.

= -31.04
= 64.93
= -1598.95

Aliasing the name column fixes the missing names as well. Does sound like something to do with your setup though.

I'm using WampServer if it helps.

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.