from db to array to calculations

Hello all,

I am updating the site from the shooting club I attend to, but now I have stumbled on a little challenge. This is the case:

I need to enter a shooting score for a person ($knsa_nr) into the db, within the db i have 2 seperate tables that i need to enter the score in:
- schietbeurt: registers the person, which type of wapen was shot with (diopler, kkg or kkg (enum('j',n'))) and the dayscore (diopler_tot, kkg_tot or kkg_tot)
- standen: registers the person averages by the wapen for the last 3 shootingscores (*_3_gem) and total shootingscores (*_tot_gem)

Entering the shootingscore into schietbeurt isn't a challenge, that works. But into standen is a total different ballgame, I need some assistence on that one
What I need is the averages from the last 3 scores and of all scores as far as today so I put in a record followupnr (volgnr) in both tables, so to determine what the last 3 scores are
I thought I needed an array as best option, so I allready called multiple arrays in the code for the calculations. This is what I programmed this far, but I don't know what to do next.

//Here I insert the data in table schietbeurt
$sqlquery_schietbeurt = 
"INSERT INTO schietbeurt (`knsa_volgnr_sch`, `datum`, `volgnr`, `diopler`, `diopler_tot`, `kkg`, `kkg_tot`, `kkp`, `kkp_tot`)
VALUES ($knsa_nr, '".$invoer['datum']."', $volgnr, '".$invoer['diopler']."', ".$invoer['diopler_tot'].", '".$invoer['kkg']."', ".$invoer['kkg_tot'].", '".$invoer['kkp']."', ".$invoer['kkp_tot'].")";
$result_schietbeurt = mysql_query($sqlquery_schietbeurt, $vk);

if ($result_schietbeurt)
{			
//Here I call all records from table schietbeurt for the same person, don't know how i can retrieve 1 element from an array. So if possible i do want one SELECT_query

$sqlquery_diopler = "SELECT diopler FROM schietbeurt WHERE knsa_volgnr_sch LIKE '136498%' ORDER BY volgnr DESC";
$result_diopler = mysql_query($sqlquery_diopler, $vk);
$count_diopler = mysql_num_rows($result_diopler);

$sqlquery_kkg = "SELECT kkg FROM schietbeurt WHERE knsa_volgnr_sch LIKE '136498%' ORDER BY volgnr DESC";
$result_kkg = mysql_query($sqlquery_kkg, $vk);
$count_kkg = mysql_num_rows($result_kkg);

$sqlquery_kkp = "SELECT kkp FROM schietbeurt WHERE knsa_volgnr_sch LIKE '136498%' ORDER BY volgnr DESC";
$result_kkp = mysql_query($sqlquery_kkp, $vk);
$count_kkp = mysql_num_rows($result_kkp);

// Make the arrays
$diopler_gegevens = array();
$kkg_gegevens = array();
$kkp_gegevens = array();


for($i=0; $i <= $count_gegevens ; $i++)
{
$diopler_gegevens[$i] = mysql_fetch_array($result_diopler);
$diopler_kkg[$i] = mysql_fetch_array($result_kkg);
$diopler_kkp[$i] = mysql_fetch_array($result_kkp);

//Here needs to come the calculations for last 3 average and total average
}
}
else
{
//Error message
}

PHP:

So who can help me to fabricate the right code??
I also attached the original file when more info is needed..
Thanks Richard.

 

If your database is better designed, then you shouldn't need the second table.
Table schietbeurt should have the following fields:
-memberid
-weaponid
-date (timestamp)
-score
You would also have a members table with memberid, member_name, address, phone etc and a weapons table with weaponid, weapon_name and other characteristics unique to that weapon.
Once you start collecting daily scores in your schietbeurt table, calculating average and total scores would be a breeze.
something like SELECT avg(tot) FROM schietbeurt GROUP BY memberid, date DESC LIMIT 3

 

Yes, agree, you should look into the database schema design and layout.

Basically, values shouldn't be stored twice in different tables. Just keys to build joins/relationships on.

Once you have the database design right, building queries with the aggregate functions will be a breeze.

 

Both,

Thanxs for the slap in the face so I woke up. You are both right, I was thinking too difficult. I adjusted my db and it works fine
Now going up for the other challenges, I am not yet there.

Richard.