Hi everyone,
My second time back to Daniweb looking for assistance with PHP. :)
Okay, here's my PHP, and I'll explain what it does.
dbConnect();
if ($userclient == '2438') {
dbOpenDatabase($GLOBALS['res_2438_contacts']);
} else {
dbOpenDatabase($GLOBALS['db_contacts']);
}
/************************************************
* THE IMPORTANT BITS - CHANGE BELOW *
************************************************/
//Product ID for Safety First Pack. We can change to get different results, or even expand on this to get App sales too.
$product_id = "1";
//Sets the bonus period in months. We now do this in DB.
//$bonus_period = "6";
/************************************************
* On to the Tracker. *
************************************************/
// Get the staff names from the database where the username is claus.
$staffsql = "SELECT staff_name FROM cms_table_staff WHERE username='$username'";
$staffquery = mysql_query($staffsql);
// Now we have the staff name, we can select any sales they've made from the sales table, between the start date and end date - and do some other nifty things.
list ($staff_name) = mysql_fetch_row($staffquery);
// Get the start in Unix Time and duration in months for a bonus period, then convert the number of months to Unix Time and add it on to the start date.
// Eg: $startdate = 12345678900 and $period = 6 (months)
$datesql = "SELECT startdate, period FROM cms_bonus_tracker WHERE staff_name='$staff_name'";
$datequery = mysql_query($datesql);
list($startdate, $period) = mysql_fetch_row($datequery);
//Work out the number of months to add on to the start date. ('Period' column states this duration. Bonus Months is the count, mktime states the start date + the bonus period in Utime)
$bonus_months = $period;
$move = $bonus_months - 1;
$bonus_period = mktime(0, 0, 0, gmdate(m, $startdate)+$move, 1);
$bonus_start_month = gmdate(n,$startdate);
$month_now = date(n);
$time_now = date(U);
$months_since_start = $month_now - $bonus_start_month;
// Get total sales to date to use later.
$msql = "SELECT createtime FROM cms_sales WHERE createby='$staff_name' AND createtime > '$startdate' AND createtime < '$time_now' AND productdesc = '$product_id'";
$mquery = mysql_query($msql);
while(list($created_time) = mysql_fetch_row($mquery)) {
$this_val = gmdate(n, $created_time);
$sales_array[$this_val]++;
}
//Averages for the user specific part of the tracker.
$actual = $months_since_start;
while($actual > 0) {
$total_sold_for_period = $total_sold_for_period + $sales_array[$bonus_start_month];
$bonus_start_month++;
$actual--;
}
$average_over_complete_months = $total_sold_for_period / $months_since_start;
$average_over_complete_months = number_format($average_over_complete_months,1);
$months_left = $bonus_months - $months_since_start;
$current_packs = $current_packs + $sales_array[$bonus_start_month];
//Get the bonus values.
$bonussql = "SELECT id, lvl_one, lvl_two, lvl_three, staff_name FROM cms_bonus_tracker WHERE staff_name='$staff_name'";
$bonusquery = mysql_query($bonussql);
while (list ($id, $lvl_one, $lvl_two, $lvl_three) = mysql_fetch_row($bonusquery)) { // THEN SOME HTML HERE }
/**************************************** THIS IS WHERE I HAVE THE PROBLEM. *********************************************/
$all_sql = "SELECT staff_name FROM cms_bonus_tracker ORDER BY staff_name";
$all_query = mysql_query($all_sql);
while (list($all_staff_name) = mysql_fetch_array($all_query)) {
echo "<tr>";
echo "<td class='theboxsmalltext' width='22%' align='center' valign='middle'>".$all_staff_name."</td>";
echo "<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M1</td>";
echo "<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M2</td>";
echo "<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M3</td>";
echo "<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M4</td>";
echo "<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M5</td>";
echo "<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M6</td>";
echo "<td class='theboxsmalltext' width='25%' align='center' valign='middle'>".$persons_packs."</td>";
echo "<td class='theboxsmalltext' width='23%' align='center' valign='middle'>AVERAGE</td>";
echo "</tr>";
}
?>I may have missed copying over a couple "}" - but generally, the whole thing works, apart from the fact that I can't get the right data in the last bit of the code. I've only copied over the PHP, there are various HTML sections which hold everything nicely.
Okay, so my goal is this;
All the code above works perfectly, until I hit a table where only certain staff members can see data. I'm supposed to be pulling the monthly sales (of 6 months) of each staff member, then having their current total (Which is where $persons_packs is) and then the average of how many they need to sell. All that I can do, but I can't seem to join up the staff names from the bonus tracker table, to the staff names in the sales table - to get the proper results for all 16 people who are listed.
I have 2 tables. One is sales, the other is specific to the bonus tracker. Each sale of ID "1" should be thrown in to the HTML table for certain staff members to see everyones progress. My main problem is - whilst I can get each of the 17 staff members, and list them in the table, I can't get their monthly sales, their current total nor their average number to sell per month to display.
I understand that I'm doing something very wrong in the last part of the code - but god knows what, i've tried all sorts of array methods to select each members sales, etc etc. Is there anyone our there who can help? :)
Thanks in advance guys!
Edit: removed all that nasty whitespace. :)
![[IMG]](http://imageshack.us/photo/my-images/847/68348204.png/){kind=link}