Hi,

I have tried different ways of doing this but still can't get the average value for the column VehCondition, can anyone see what I'm doing wrong? It doesn't return a value for $ConditionRating;

PHP Code:


$Vehicles2=mysql_fetch_array("SELECT avg( VehCondition ) as ConditionRating, Type FROM vehicles where Userid='$_SESSION[userid]' GROUP BY Type"); 

$ConditionRating=$Vehicles2['ConditionRating'];
echo $ConditionRating; 


try doing it with....

PHP Code:

$Vehicles2=mysql_fetch_array(mysql_query("SELECT avg( VehCondition ) as ConditionRating, Type FROM vehicles where Userid='$_SESSION[userid]' GROUP BY Type")); 


forgot the mysql_query
</span></span>

There were no results returned from the query, the vehicle type can be either trains or road vehicles so if the query is grouping the results by type, would it need to be output differently?

Basically, I want to get the condition rating for all vehicles of type train. Is this query similar to what it should be?

PHP Code:

$Vehicles2=mysql_fetch_array(mysql_query("SELECT avg( VehCondition ) as ConditionRating FROM vehicles where Userid='$_SESSION[userid]' and Type='Train'"));
 
$VehCondition=$Vehicles2['ConditionRating'];
 
echo $VehCondition; 


What do you get when you do:

PHP Code:

$query = mysql_query("SELECT avg( VehCondition ) as ConditionRating FROM vehicles where Userid='$_SESSION[userid]' and Type='Train'") or die(mysql_error())

echo mysql_num_rows($query); 


Either there's an error with your query (in which case the "mysql_error()" bit will tell you what's wrong), or it'll (probably) output that 0 records were returned - meaning your MySQL is syntactically correct, however there's a flaw in the query's logic.

Thanks for your suggestions, the first error it returned was that there wasn't a vehicles table and the error was that Vehicles should have a capital letter.

I tried it again and it returned 1 but not sure why because there are two vehicles where type=train in the database.

I tried outputing the result but it was still empty.

The vehicles in the Vehicle table have VehCondition ratings between 1 - 100, how can I get the vehicle ratings and get the average of them?

Hmm, I'm not 100% sure. Without knowing the data and table structures, I can't say I'm all that sure.

It might not, although does:

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

Shed any new light on things (it's a user-friendly approach to the avg() function)?

Is there any way you can export the mysql table for us and post it here?

tristanperry, thanks for the link but that was the one I used originally

Arenlor, the table consists of the following:

PHP Code:

Vehicleid int (autonumber), PK
Userid varchar
VehicleName varchar
Type varchar
VehCondition int 


Thanks,

Is UserID unique?

Cause....is the person doing this, have a Type 'Train'?

IMO, I feel you're getting the average of one row of data with that query.

Try it with this...

PHP Code:

$Vehicles2=mysql_fetch_assoc(mysql_query("SELECT avg( VehCondition ) as ConditionRating FROM vehicles where Type='Train'")); 


I took out the userID requirement of the query, and made it a fetch_assoc.

Try this with fetch_array and fetch_assoc, see if those two seem to work.
</span></span>

Type is case sensitive, make sure you've entered it correctly.

Thanks kbfirebreather and Arenlor, the query worked

$query = mysql_query("SELECT avg( VehCondition ) as ConditionRating FROM vehicles where Type='Train'") or die(mysql_error())

echo $ConditionRating;