Cannot Print Sum

mj99

I can't get the following query to print the results as a sum. Instead it always returns this:

20000003006004005000

which is just all the numbers in sequence. What is wrong? Is this not an array?

$query12 = "SELECT SUM(value) FROM value LEFT JOIN horsevalue ON horsevalue.value_id = value.id WHERE horsevalue.horse_id = $horse_id AND horsevalue.value_id = ".$row['id']." GROUP BY value";
$result12 = mysql_query($query12) or die(mysql_error());
while($row12 = mysql_fetch_array($result12)){

echo $row12['SUM(value)'];

}

laserlight

Well, the most obvious answer is that everything is working okay, except that since you did not print a separator in between your numbers, you get an output that looks like one huge number when actually it is several numbers in a row.

Try something like:

echo $row12['SUM(value)'] . '</br />';

mj99

Here's what I got. That did put a break after each number but still no total.

200

300
600
400
500

It is starting to seem that these numbers are not seen as an array by PHP. Does that sound right?

Is there any way to manipulate them into a total using some other function?

laserlight

That did put a break after each number but still no total.

That implies that your query is incorrect. What total are you looking for? You might want to provide the relevant database schema.

It is starting to seem that these numbers are not seen as an array by PHP. Does that sound right?

They do not form an array. Each number is a field from one row of the result set. Each row of the result set is turned into an array. So each number is an element in that array, namely the element $row12['SUM(value)'].

mj99

Here are the two tables in question, assuming it is not necessary to see more. I just need to come up with a way to add the numbers together. I would think that by their very existence they could be dynamically added into a product. Guess it's not that easy though.

Each table here includes only a few rows of data because I could not submit more.

-- Table structure for table `horsevalue`

CREATE TABLE horsevalue (
horse_id varchar(10) default NULL,
value_id varchar(10) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `horsevalue`

INSERT INTO horsevalue VALUES ('1', '124');
INSERT INTO horsevalue VALUES ('1', '109');
INSERT INTO horsevalue VALUES ('1', '38');

-- Table structure for table `value`

CREATE TABLE value (
id int(10) NOT NULL auto_increment,
breed_id int(10) NOT NULL default '0',
att_id int(10) NOT NULL default '0',
choice_id int(10) NOT NULL default '0',
value float default NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=266 ;

-- Dumping data for table `value`

INSERT INTO value VALUES (263, 13, 26, 198, 2000);
INSERT INTO value VALUES (264, 13, 26, 197, 3000);
INSERT INTO value VALUES (265, 13, 26, 206, 2000);

laserlight

Here are the two tables in question

What is the relationship between the two tables?

I just need to come up with a way to add the numbers together.

Add what numbers together? You need to be clear on what you are trying to do.

mj99

I need to show the total of $row12['SUM(value)'].

For instance , 2000 instead of

200

300
600
400
500

laserlight

I need to show the total of $row12['SUM(value)'].

sigh
No, think in terms of what you want to do in the database.

If you really want a simple but sub-standard answer, the solution is trivial:

$query12 = "SELECT SUM(value) FROM value
            LEFT JOIN horsevalue ON horsevalue.value_id = value.id
            WHERE horsevalue.horse_id = $horse_id AND horsevalue.value_id = ".$row['id']."
            GROUP BY value";
$result12 = mysql_query($query12) or die(mysql_error());
$total = 0;
while($row12 = mysql_fetch_array($result12)) {
    $total += $row12['SUM(value)'];
}
echo $total;

But now part of the business logic lies in the PHP code. It is usually better if you can place all the business logic in the SQL query since the database is likely to know better how to optimise getting exactly what you want from it.

mj99

I think my new question is "when is an array not an array?".

And how can it possibly be treated as an array since it, after all, looks an awful lot like one.

Maybe it is a bug.

Sxooter

Why are you grouping by the value?

Shouldn't you be grouping by horse_id or nothing at all?

laserlight

I think my new question is "when is an array not an array?".

And how can it possibly be treated as an array since it, after all, looks an awful lot like one.

You need to read my explanation again. $row12 is indeed an array, but on each iteration of the loop, it is assigned a different array corresponding to the next row in your result set. $row12['SUM(value)'] is not an array. It is an element of the array $row12 and corresponds to a particular field in the current row of your result set.

Read the PHP manual on [man]mysql_fetch_array/man for more information.

Oh, and please answer Sxooter's questions too.

mj99

Thanks Sxooter. Actually the results are the same with either grouping as well as no grouping at all.

Laserlight, your explanation does seem to hit the nail on the head.

I'll look at your docs link now. Hopefully there's a piece of code there that will get me the elusive total I am looking for.

Of course, I could answer questions all day long by giving out links to the PHP docs.

Thanks

laserlight

I'll look at your docs link now. Hopefully there's a piece of code there that will get me the elusive total I am looking for.

I doubt you will find the answer there though. That explains more on how to retrieve rows from your result set, and less on how to get the result set that you want.

In this case, I suspect that I have already given you the answer you need, except that there is a better answer that is not yet known that involves tweaking SQL, not PHP :p

Sxooter

mj99 wrote:
Thanks Sxooter. Actually the results are the same with either grouping as well as no grouping at all.

I sincerely doubt that. It sounds like you're trying to do the sum in the php code instead of the sql. So something like this:

$query = "select sum(value) as valuesum, id from table group by id"; # your query might be a bit different
$res = pg_query($query);
while ($row=pg_fetch_assoc){
    print $row['id'];
    print $row['valuesum'];
}

That's just a code fragment, you'll have to beat it into shape.

mikell

Here's a total shot in the dark, but might be what you're looking for.
The query below should give you the sum of all values for a horse with horse_id = $horse_id.

$sum = 0;
$query12 = "SELECT SUM(value) as sum 
            FROM value LEFT JOIN horsevalue ON horsevalue.value_id = value.id
            WHERE horsevalue.horse_id = $horse_id";

//this should print once.
echo ("\$query12: $query12<br>");

$result12 = mysql_query($query12) or die(mysql_error());

//note that this is no longer a while loop
if($row12 = mysql_fetch_array($result12))
{
   $sum = $row12['sum'];
}
echo ("sum of values for horse $horse_id: $sum <br>");

I suspect that the code you posted is embedded in a loop (which you did not bother to show anyone). If so, make sure when you replace your code with this code that it is only executed once.

If that doesn't help, you should focus on getting the query right first, then worry about embedding it in PHP. If we had the same data as you (all the insert statements) and knew what data you wanted to retrieve, we would know what query to use. Then your question could be "how do i embed this query (that actually works) in php?", which is easier to answer.