PHP, MySQL and searches

[ratpH1nk]

Hello all,

I have recently constructed a DB in MySQL whose columns contain for example user name, #of apples and the date (0000-00-00 format) I need to construct a web page accessible form/search that will allow me to search a date range, get all the apples and oranges that <user> name has in those date ranges and display total. total apples, total oranges. Example:
names = user_name
apples = apples

Date Range: 2003-03-10 - 2003-03-15

Name: Apples: Oranges:
Bob 10 5
Sally 15 5

Smithy 20 5

Total: 45 15

I think that should be straightforward, but for the life of me, I have looked at several examples on the web, but none have seemed to work right.

Thanks for any assistance you could provide. If more information is needed

[bastien]

simple

<?

//initliaze counters
$ttl_apples=0;
$ttl_oranges=0;

//assumption
//1. users can have more than one row in the table

$sql="select * from mytable where date between daterangevar1 and daterangevar2 group by username";

$result=mysql_query($sql);

//start table
echo "<table>";

//get the data array
while ($rows=mysql_fetch_array($result)){
    $or=$rows['oranges'];
    $ap=$rows['apples'];
    $us=$rows['username'];

echo "<tr><td>$us</td><td>$ap</td><td>$or</td></tr>";

$ttl_apples+=$ap;
$ttl_oranges+=$or;

}   //close the while loop

//echo the totals
echo "<tr><td>totals:</td><td>$ttl_apples</td><td>$ttl_oranges</td></tr>";

//close the table
echo "</table>";

?>

note: this does need to include the db access information
more error handling is needed

hth😉

[ratpH1nk]

WOW! That is awesome!!

A quesiton though, the totals that you wrote up. They are in the form ttl_var+=

Are there any tricks for averaging? Like averaging the number of oranges?

Thanks again!!

-ph1nk

[bastien]

can, depending on where / what you are averaging use

a. the database to do it for you with the AVG function in the sql statement

or

b. add another counter incremented at the number of users and then just divide the $ttl_oranges by the $ttl_users

for an average per user

hth

[ratpH1nk]

Soooo. define a new ttl

$ttl_average=0

ttl_average('ttl_users/ttl_oranges')

or is that toally offbase?

Thanks again!

pH1nk

[bastien]

would think that you need the equal sign

$ttl_avg_oranges=0;
$ttl_avg_apples=0;

ttl_avg_oranges =ttl_users/ttl_oranges;
$tt_avg_apples=ttl_users/ttl_apples;

hth