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. :)

Recommended Answers

All 8 Replies

Try this:

//Get the bonus values.
$bonusquery =  mysql_query("SELECT * FROM `cms_bonus_tracker` WHERE `staff_name`='$staff_name'");
while ($list = mysql_fetch_array($bonusquery)) { ?>
Some HTML HERE
<?
}
$all_query = mysql_query("SELECT * FROM `cms_bonus_tracker` ORDER BY `staff_name`");
while ($list2 = mysql_fetch_array($all_query)){
$all_staff_name = $all_query[staff_name];
?>	
<tr>
<td class='theboxsmalltext' width='22%' align='center' valign='middle'><? echo $all_staff_name; ?></td>
<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M1</td>
<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M2</td>
<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M3</td>
<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M4</td>
<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M5</td>
<td class='theboxsmalltext' width='5%' align='center' valign='middle'>M6</td>
<td class='theboxsmalltext' width='25%' align='center' valign='middle'><? echo $persons_packs; ?></td>
<td class='theboxsmalltext' width='23%' align='center' valign='middle'>AVERAGE</td>
</tr>
<?
}
?>

Also you didn't define what $persons_packs is before you echo'd it.

Hi public-image,

Yeah sorry about that, I'd done this;

<? 		
$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)) { 
$sql_array = "SELECT p.createby FROM cms_sales JOIN cms_bonus_tracker n ON p.staff_name = n.createby AND staff_name = '$all_staff_name'";
$query_array = mysql_query($sql_array);
$persons_packs = mysql_fetch_row($query_array);
	
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>";
}
?>

... but that was FAR from working, haha!

Thanks very much for your post, I'll try it out! :)

Hmm, didn't seem to work (Even with modifications).

Punches out either negative values or the word "Array". :(

Is anyone able to assist with this? All the code is generally there, I just need it to work on a per-staff member basis, which prints out the details in a HTML table below the main part where all the same information is displayed for the staff member logged in. >_<

Thanks guys!

Can you tell me the following:

1. The database table(s) that you are using for the last part
2. The fields you are using from these tables

Hi public-image,

The tables are as follows;

cms_bonus_tracker
id, start_date, period, lvl_one, lvl_two, lvl_three, staff_name

Start Date is UTime, Period is "6" (Months), lvl_one-three are target numbers, eg: 100, 120, 140, staff name is full name, eg: John Smith.

cms_sales
createtime, createby, productdesc (are the fields I need to use from this table).

Create Time is also in UTime, createby is the full name, eg: John Smith, product description is "1".

I need to list all the names in the cms_bonus_tracker table, (16 or 17 results), then for each of those staff names, I need their current sales from $startdate to $timenow (declared up top, the code is in my first post), the rest I can solve. It's just I need the selected results from cms_sales to display the proper number of sales as per the staff members full name. This is then displayed in a HTML table.

Below is a link to a visual of my current progress. (Names have been blurred out for obvious reasons ^_^)

[IMG]http://img847.imageshack.us/img847/2940/68348204.png[/IMG]

As you can see, the first part displays the proper information... I just can't seem to make it run through and give each staff members progress, without using a million SQL's. (The idea is that it isn't hardcoded, and staff can be added/removed from the cms_bonus_tracker table without modifying the code. All the code to get the information in the top table, is in my first post - so what I need is something similar, but that loops through for each staff member.

Thanks helping me understand this more!

Hmm... Could try...

<?
// Create Query
$query = "SELECT p.createby, n.createby"."FROM cms_sales, cms_bonus_tracker"."WHERE staff_name =$all_staff_name";
$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
?>
Table
<? 
}
?>

I modified that code a little bit Public-image and it works! Not as greatly as it should be, there are 9 separate versions of code for each bit, but it works!

Thanks a bunch DaniWeb. :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.