Why Wont This Add Up? A few people confused...

Noodleman

I havent been playing with php for long, but it is my understanding that the following code should work.

Firstly, the loop goes through each record of the database, and checks if any of the DB entrys match the if statement, if it does, it adds a number between 1&5 to $total.

When I display $total it gives me each number of the entry, rather than a total of them all being added up..

$total = 0;
While ($myrow = mysql_fetch_row($result)) {
If ($myrow[6] == "poor") {$total = $total + 1; }
If ($myrow[6] == "LessThanSatisfactory") {$total = $total + 2; }
If ($myrow[6] == "Satisfactory") {$total = $total + 3; }
If ($myrow[6] == "MoreThanSatisfactory") {$total = $total + 4 ; }
If ($myrow[6] == "Excellent") {$total = $total + 5; }
}
echo "$total";

Tekime

Can you provide a specific example of the ouput?

AstroTeg

Rewritten to be a little cleaner/easier to read:

$total = 0;
while($myrow = mysql_fetch_row($result)) 
{
	if($myrow[6] == "poor") 
		$total +=  1; 
	if($myrow[6] == "LessThanSatisfactory") 
		$total += 2; 
	if($myrow[6] == "Satisfactory") 
		$total += 3; 
	if($myrow[6] == "MoreThanSatisfactory") 
		$total += 4 ; 
	if($myrow[6] == "Excellent") 
		$total += 5;
}  // while($myrow = mysql_fetch_row($result)) 
echo $total;	// <-- no quotes!

My thoughts are on that last statement. You had echo "$total" which tells PHP to treat $total as a string, but it contains an integer. Just ditch the quotes. I wouldn't expect this to fix the problem though. For troubleshooting, I'd recommend putting another "echo $total . '<br>';" after all your if statements, but still inside your while loop. Then run the code and watch the value of $total each time. It will yield clues to what's happening...

Weedpacket

Something that may help is to refine the original query - if you're only looking at the 6th field, there's no point selecting the 0th, 1st, 2nd, 3rd, 4th, and 5th fields as well (or the 7th, 8th,...). Select just the one you want, and use mysql_fetch_assoc() or mysql_fetch_array() so that you can refer to the field as $myrow['grade'] (or whatever it's called). That will eliminate the chance that you're inadvertently looking at the wrong field.

That said, I suspect this entire loop could be replaced by a single SELECT in MySQL with a sum and a bit of code to map "poor"=>1, "LessThanSatisfactory"=>2, etc.

Noodleman

As requested,

Take this as an example

$myrow[6] for example, is the following values from the DB.

poor
LessThanSatisfactory
poor
Excellent
MoreThanSatisfactory
poor

I would expect the code to give each one of those results, a numerical value, so in the example list above, it should create the following values.

1
2
1
5
4
1

I would expect the $total += to add up each value. For example,

1+2+1+5+4+1 = 14

when doing echo "$total"; I would expect it to display 14. Instead, the result I am getting is 121541 a list of each value that was given to each word.

AstroTeg, Thanks for the no quotes idea, i will try that when im back in the office on monday.

Weedpacket, Currently i am only writing this for one field, but when i get this working, I will be doing it over about thirty fields. If you could provice me with an example query to execute, to map the value's to a number, it would be most appreciated, I couldnt work out how to do it, so went for doing it in the php code, not the query.

Thanks for the sugestions.

nemonoman

$total is returning a string, and the string as appending with all your +=...

I don't get this, frankly, and think that something else is afoot.

Anyway, try the following, and all should be well:

while($myrow = mysql_fetch_row($result))
{$total=$total-0;

I wonder if its the spaces?
$total +=  1;

try
$total +=1;

bubblenut

Ahh a foot is afoot!!! 😉
Basicall reiterating what Weed said in code,

$sql="select column_name from table_name where condition='condition'";
$res=mysql_query($res);
$total=0;
while($row=mysql_fetch_row($res)) {
  switch($row[0]) {
    case 'poor':
      $total+=1;
      break;
    case 'LessThanSatisfactory':
      $total+=2;
      break;
    case 'Satisfactory':
      $total+=3;
      break;
    case 'MoreThanSatisfactory':
      $total+=4;
      break;
    case 'Excellent':
      $total+=5;
      break;
  }
}

However I would say one thing about your data structure, I think it would be easier (and certainly quicker) if you stored the result level as a numeric level as well as a textual level in the database, it would make indexing a lot quicker for queries such as "find all the pieces of work which attained an excellent grade".
HTH
Bubble

Weedpacket

Originally posted by bubblenut

However I would say one thing about your data structure, I think it would be easier (and certainly quicker) if you stored the result level as a numeric level as well as a textual level in the database, it would make indexing a lot quicker for queries such as "find all the pieces of work which attained an excellent grade".

Reading this I recall that MySQL has an enum type - and looking at that page you can retrieve either the label ("poor", etc.,) or the numeric index (1, etc.) depending on which you want. Then of course the query can just SUM() those indices.

Noodleman

Thankyou,

It was something as simple as a space!

/me bangs head on desk

Thanks for your sugestions, and also examples oh how to tidy up the code 🙂

Best way to learn i say, is to dive in the deep end hehe.

Many Thanks peeps 🙂