Foreach-ing through mysql query returning null :s

[Zephni]

Ok, this may be just because I have been programming all day and my mind has gone blank (happens alot), but this is my PHP script:

 

<?php
$query_distinct_item_types = mysql_query("SELECT DISTINCT name FROM item_types");
while($item_types = mysql_fetch_array($query_distinct_item_types)){
	$distinct_item_types[] = $item_types['name'];
}

foreach($distinct_item_types as $item){
	$query_item_total = mysql_query("SELECT item_type, SUM(price) WHERE item_type='$item' FROM costs GROUP BY item_type");
	while($item_total = mysql_fetch_array($query_total_price)){
		$item_totals[] = $item_total['SUM(price)'];
	}
}

$item_summery = $item_totals;
?>

 

$item_summery which is = to $item_totals is returning null, any idea's?

 

[Psycho]

Ideas? Yeah, you are doing it wrong. Don't know why $item_summery is null. Are you treating it as an array (which it would be) or as a number/string value? Second, you aren't even checking that the queries are succeeding. If they fail, the while() loops never run. Lastly, if all you want is a total then you can get that with ONE single query instead of all those loops that will put unneeded stress on the server.

 

EDIT: You shouldn't be using the item "name" to associate the records in "costs" to the item types. Use a unique id as the foreign key reference. And, I'm not sure why you even need the first query. Are there records in the "costs" table that don't have an associated item type? And, I see now that your second query is malformed and would be failing. Which is the specific reason for your problem.

 

[Zephni]

I was var_dumping($item_summery), could you show me the query that would return this in one as an array, i need the array to be like this:

array('item_type1' => '$total_for_item', 'item_type2' => '$total_for_item' ...ect)

Thank you for reply

[Psycho]

This will get you the sum by item type in a single query and get the sum of all those records using array_sum()

$query = "SELECT costs.item_type, SUM(costs.price) as sum
          FROM costs
          JOIN item_types ON costs.item_type = item_type.name
          GROUP BY costs.item_type";
$result = mysql_query($query) or die(mysql_error());

//Dump results into array so individual sums can be used
while($row = mysql_fetch_assoc($result))
{
    $item_totals[$row['item_type']] = $row['sum'];
}

$item_summery = array_sum($item_totals);

 

However, if you only need the total and not the total by each type, then it is even easier

$query = "SELECT SUM(costs.price) as sum
          FROM costs
          JOIN item_types ON costs.item_type = item_type.name";
$result = mysql_query($query) or die(mysql_error());

$item_summery = mysql_result($result, 0);

[Zephni]

Thank you very much =) It works, I only had to change the fact that it wasn't item_type.name I was looking for it was item_type.id because the cost.item_type was refferenced to by ID so now I have something like:

 

     array(3) { [2]=> string(2) "37" [3]=> string(5) "68.46" [4]=> string(2) "40" }

 

Now I just need to think of how to grab the name from the item_types table that holds the item name against the id, but i'm sure that wont be too hard... maybe.

 

You have made me think about using MYSQL to its full ability! I actualy had never looked in to it enough to realise you could do more complex querys like that, thanks!

[Zephni]

Here is the final code that works, thanks for your help

 

<?php
$query = "SELECT costs.item_type, SUM(costs.price) as sum
          FROM costs
          JOIN item_types ON costs.item_type = item_types.id
          GROUP BY costs.item_type";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_assoc($result))
{
	$query_item_name = mysql_query("SELECT name FROM item_types WHERE id='".$row['item_type']."'");
	$item_name = mysql_fetch_array($query_item_name);
	$item_name = $item_name['name'];
	$item_totals[$item_name] = $row['sum'];
}

$item_summery = /*array_sum($item_totals)*/$item_totals;

var_dump($item_totals);
?>

 

Your most probly gonna say there was an easier way to do that aswell...

[Psycho]

Your most probly gonna say there was an easier way to do that aswell...

 

You are absolutely right! I already stated that running loops is bad practice. Learn to do JOINs and use them properly! That is the whole point of having a relational database, so you can "relate" data across tables. You only needed to change the SELECT clause to pull the item name instead of the id.

//Run ONE query to get the sum of prices for each item by NAME
$query = "SELECT item_types.name, SUM(costs.price) as sum
          FROM costs
          JOIN item_types ON costs.item_type = item_types.id
          GROUP BY costs.item_type";
$result = mysql_query($query) or die(mysql_error());

//Create array of each item name as key and sum as value
$item_totals = array();
while($row = mysql_fetch_assoc($result))
{
    $item_totals[$row['name']] = $row['sum'];
}

//Create variable with the sum of all totals
$item_summery = array_sum($item_totals);

[Zephni]

*Looks up JOIN*

 

Your awesome thankyou