having some serious issues PLEASE help asap. I can't get to sleep until i finish

[mitchberry]

ok, this is going to be kind of hard to explain, but any and all help is appreciated.

 

Basically here's what i have. 

 

in my DB there is a table called "contractors" which is basically a list of employees and their info.

 

also, i have a table called "whiteboard" which means something in my business.  whiteboard is clients info. 

 

my employees work from home. whenever they close a deal, they enter certain into into this site.  it knows who they are because they are logged in. when they put the info for the client in, it puts it in the whiteboard table with a reference to who "owns" that deal. ("user_username" field). 

 

once that is submitted, it re-directs them to a page that lists out all of their sales (clients) and tells them how much commission is coming to them and various other things they need to know.

 

as an admin, i go in there and change things in the client's info as needed like.... if the employee has been paid for that one client.  it does a lot of other calculations automatically when i alter certain things.   

 

well, i have all of this information, and want to make various admin statistic pages.

 

now i'm getting to my point.

 

the client record has who owns it, how much money they are paying us, the commission amount that the employee is getting for that deal, whether it's been drafted by our bank or not, the amount due etc.      that info is NOT in the employee's record itself..

 

so what needs to happen typically is that it will do a search saying Select all from client where user_username = (whoever is logged in)  and it displays info accordingly..

 

so....

 

my point....

 

I am building another page for myself as an admin.. it shows a list of all of the employees and builds a table.  In this table, i want it to show several things, but.. for all intents and purposes, we'll say i want it to show the total commission due..

 

basically what it needs to do (i think)  is do a for loop where it starts to build a table displaying all of the users...  and while it's building a row for a user (lets say User X) 

 

while  it's on user X, go and pull all of user X's client records from the other table,and then do the math and make a solid number and then insert that number into the appropriate field, then repeat process for the next..

 

that seems logical...

 

here's the problem i had.

 

Apparently, you can't do nested While loops.......  So, i did a join instead, but what happens is it shows

 

user    commission

X        20

Y        30

X        50

X        40

Y        20

 

etc.... 

 

i need it to show

 

X      110

Y      50

 

 

so...... to support this long explanation, i have provided my code...  PLEEEEEEEASSEEEEEE help

 

for the record, the code below doesn't have any of the calculations etc, because i can't seem to TAME the information....  it runs wild

 

this is the output of said file (at least the important parts)....

 

todd 450

admin 0

todd 1012.5

admin 0 

 

how the hell do i combine those numbers?

 

 

#########################################

 

<?php
include "head.php";
include "menu.php";


$_SESSION["contractor_login"] 		= $contractor_login; // store session data
$_SESSION["contractor_password"] 	= $contractor_password; // store session data


$checkusername =		mysql_query("SELECT * FROM contractors WHERE user_username = '".$contractor_login."'");	
$checkpassword = 		mysql_query("SELECT * FROM contractors WHERE user_password = '".$contractor_password."'"); 


///// check if the user is admin.. if not, don't let them in
$result1 = mysql_query("SELECT * FROM contractors WHERE user_username = '".$contractor_login."' AND user_password = '".$contractor_password."' ");
while($r=mysql_fetch_array($result1))
{	
      
   $admin_check=$r["user_isadmin"];


}//end while

if ($admin_check == "yes")
{

////// now the fun stuff

echo "<span class=\"smallfont\">welcome: ".$_SESSION["contractor_login"]."</span>";
?>
<table width="1000px" border="0px" >
<tr>
<td>
</td>
</tr>
</table>
<?
echo "<table class=\"table\" cellpadding=\"3px\" cellspacing=\"0\">
<tr bgcolor=\"#006666\">	
<td align=\"center\"><span class=\"smallwhitefont\">Update</span></td>
<td align=\"center\"><span class=\"smallwhitefont\">User</span></td>
    <td align=\"center\"><span class=\"smallwhitefont\">Emp ID</span></td>
    <td align=\"center\"><span class=\"smallwhitefont\">First Name</span></td>
    <td align=\"center\"><span class=\"smallwhitefont\">Last Name</span></td>
<td align=\"center\"><span class=\"smallwhitefont\">Rate</span></td>
<td align=\"center\"><span class=\"smallwhitefont\">Pounds</span></td>
<td align=\"center\"><span class=\"smallwhitefont\">Balance</span></td>
<td align=\"center\"><span class=\"smallwhitefont\">Leads</span></td>
<td align=\"center\"><span class=\"smallwhitefont\">Avg &#163</span></td>
<td align=\"center\"><span class=\"smallwhitefont\">CPC</span></td>
<td align=\"center\"><span class=\"smallwhitefont\">adm</span></td> 


</tr>";



/*  this WORKS, but doesn't do what i want it to do...	

$result5 = mysql_query("SELECT * FROM whiteboard");

	while($row = mysql_fetch_array( $result5 )) 
	{
	$ass = $row["user_payout_status"];
//figure out who is who
	}
*/

///////////\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

$query = "SELECT * ".
"FROM contractors, whiteboard ".
"WHERE contractors.user_username = whiteboard.user_username";

$result = mysql_query($query) or die(mysql_error());

// for later use maybe $result = mysql_query("SELECT * FROM contractors ORDER BY user_last DESC"); 

while($row = mysql_fetch_array( $result )) 
{
// Print out the contents of each row into a table

echo "<tr>
<td align=\"center\">

<form method=\"post\" action=\"client_update.php\">
<input type=\"hidden\" name=\"client_id\" value=\"".$row["user_uid"]."\"/>
<input name=\"update\" type=\"image\" src=\"images/update.jpg\" />
</form>";

echo "</td>";
echo "<td align=\"center\"><span class=\"smallfont\">".$row["user_username"];
echo "</span></td>";
echo "<td align=\"center\"><span class=\"smallfont\">".$row["emp_id"];
echo "</span></td>";
echo "<td align=\"center\"><span class=\"smallfont\">".$row["user_first"];
echo "</span></td>";
echo "<td align=\"center\"><span class=\"smallfont\">".$row["user_last"];
echo "</span></td>";
echo "<td align=\"center\"><span class=\"smallfont\">".$row["user_commission_rate"];
echo "</span></td>";
echo "<td align=\"center\"><span class=\"smallfont\">(pounds) calcneeded";
echo "</span></td>";
echo "<td align=\"center\"><span class=\"smallfont\">" .$row["user_commission"]."<";
echo "</span></td>";
echo "<td align=\"center\"><span class=\"smallfont\">".$row["user_leads"];
echo "</span></td>";
echo "<td align=\"center\"><span class=\"smallfont\"> avg";
echo "</span></td>";
echo "<td align=\"center\"><span class=\"smallfont\"> cpc";
echo "</span></td>";
echo "<td align=\"center\"><span class=\"smallfont\">".$row["user_isadmin"];
echo "</span></td>";

echo "</tr>";
}
echo"</table>";

}

else
{
echo"<span class=\"smallfont\">You do not have access to this page.</span>";
}




include"footer.php"; ?>

[corbin]

Don't feel like reading the entire post to be honest....

 

 

Sounds like you need SUM() and GROUP BY though.

 

 

Also, instead of storing a username in the whiteboards table, you should be storing a user_id that references a user_id in the users table (or what ever you call your table that holds user details).

 

 

Numeric indexes are smaller/faster, and a INT takes up less room than anything over 4 bytes.

[mitchberry]

I figured out how to make what you said work.. you were right on...

 

i may run into a problem if i have to do more than one sum though.....

 

 

thanks