MySQL, SubSelect, is this possible?

So, I have a donation table. It stores a $ value and a column box where they choose monetary or in kind donation. in-kind donations can have a $ but usually they don’t. An in-kind donation would be if someone donated a physical item ex, lawnmower, table, chair, so on. I then have donors. Finally, I have a Leadership Giving Group. Based on certain amounts a donor gets put into one of the Leadership Giving Groups. So, for $100 the donor is put in Other Donor group, for $1000 they are put in the next one up and so on. I need to sum all the donations but exclude all in kind donations because those(even if they have a monetary value) don’t count for where to place the donor in the Leadership Group.

TABLES:

Donor: (PK)DonorID, Address, Phone,
Donation: (PK)DonationID, DonationType, DonationAmount, Description, Date_Received,
LGG: (PK)LeaderGrp, LGDescrip

LeaderGrp is not a AI, it is a name of each Leaderhip Group: Other Donor, Founder’s Club, Friend’s Circle, and so on. This will always be unique so I made it the PK but if for some reason and AI field I can do that.

I can post my code if that would make it easier but I feel as though it will turn into a huge wall of text for people. However, this is a website and other programming languages I have used in this project are js and php so if it could be possible in php that is an option as well. I could also give you the link the website and let you view the information because currently it is all made up, obviously the real data can’t be shared if that would be helpful. Just ask for it because I didn’t really want to post it when some people might just go in and start adding data to my tables.

Code:

<?php require_once('header.php') ?>
<!--dont modify above this line-->

<?php 
//on ANY page you need to use the database, you need to include the line below ONCE (before doing any db operations)
require_once('connect.php');

function checkAmount($amount, $min, $max)
{
if ($amount >= $min && $amount <= $max)
return true;
else
return false;

} 


//array types of users
$founders_club = array();
$headmasters_circle = array();
$dragons_circle = array();
$green_and_white_society = array();
$seventies_society = array();
$millennium_society = array();
$dome_society = array();
$tunnel_society = array();
$friends_circle = array();
$others = array();

// fiscal year
$currentyear = date("Y");
$selectedyear = $_POST["selectedYear"];
if($selectedyear == "")
$selectedyear = $currentyear;
$startdate = $selectedyear . "-07-01";
$enddate = $selectedyear + 1 . "-06-30";

//we need to put all donors inside the dropdown box, this is how to do it
//the actual query
$result = mysql_query("SELECT * FROM donor;") or die (mysql_error());
echo "<h2>Publication List</h>";
echo "<div>Choose Fiscal Year:";
//this will iterate through every record in the resultset and create an option box
echo "<form id=\"fiscal\" action=\"q1.php\" method=\"POST\">
<select name=\"selectedYear\" onchange=\"document.getElementById('fiscal').submit();\">";
for($i=$currentyear; $i >= 2011; $i--)
{
    if ($i == $selectedyear)
    echo "<option value='".$i."' selected=\"selected\">".$i."</option>";
    else
    echo "<option value='".$i."'>".$i."</option>";
}
echo "</select></form>";
echo "</div><br>";
//this will iterate through every record in the resultset and create an option box
while($row = mysql_fetch_array($result))
{

$result2 = mysql_query("SELECT SUM(DonationAmount) FROM donations Where DonorID = ".$row['DonorID'] . " and donations.Date_Received BETWEEN '" . $startdate . "' and '" . $enddate . "'" ) or die (mysql_error());
    while($row2 = mysql_fetch_array($result2))
        {
        $amount = $row2['SUM(DonationAmount)'];         

        if (checkAmount($amount, 50000, 999999))    
        array_push($founders_club, $row['DisplayName']);

        if (checkAmount($amount, 25000, 49999))
        array_push($headmasters_circle, $row['DisplayName']);

        if (checkAmount($amount, 10000, 24999))
        array_push($dragons_circle, $row['DisplayName']);

        if (checkAmount($amount, 5000, 9999))
        array_push($green_and_white_society, $row['DisplayName']);

        if (checkAmount($amount, 2500, 4999))
        array_push($seventies_society, $row['DisplayName']);

        if (checkAmount($amount, 1000, 2499))
        array_push($millennium_society, $row['DisplayName']);

        if (checkAmount($amount, 500, 999))
        array_push($dome_society, $row['DisplayName']);

        if (checkAmount($amount, 250, 499))
        array_push($tunnel_society, $row['DisplayName']);

        if (checkAmount($amount, 100, 249))
        array_push($friends_circle, $row['DisplayName']);

        if (checkAmount($amount, -1, 99))
        array_push($others, $row['DisplayName']);

        }

}


        //display results
    if(count($founders_club) > 0)   
    {
    echo "<h2>Founder's Club</h2>";
    for ($i = 0; $i < count($founders_club); $i++) 
    echo $founders_club[$i] . "<br>";
    }

    if(count($headmasters_circle) > 0)  
    {
    echo "<h2>Headmaster's Circle</h2>";
    for ($i = 0; $i < count($headmasters_circle); $i++) 
    echo $headmasters_circle[$i] . "<br>";
    }

    if(count($dragons_circle) > 0)  
    {
    echo "<h2>Dragon's Circle</h2>";
    for ($i = 0; $i < count($dragons_circle); $i++) 
    echo $dragons_circle[$i] . "<br>";
    }

    if(count($green_and_white_society) > 0) 
    {
    echo "<h2>Green and White Society</h2>";
    for ($i = 0; $i < count($green_and_white_society); $i++) 
    echo $green_and_white_society[$i] . "<br>";
    }

    if(count($seventies_society) > 0)   
    {
    echo "<h2>1970's Society</h2>";
    for ($i = 0; $i < count($seventies_society); $i++) 
    echo $seventies_society[$i] . "<br>";
    }

    if(count($millennium_society) > 0)  
    {
    echo "<h2>Millennium Society</h2>";
    for ($i = 0; $i < count($millennium_society); $i++) 
    echo $millennium_society[$i] . "<br>";
    }

    if(count($dome_society) > 0)    
    {
    echo "<h2>Dome Society</h2>";
    for ($i = 0; $i < count($dome_society); $i++) 
    echo $dome_society[$i] . "<br>";
    }

    if(count($tunnel_society) > 0)  
    {
    echo "<h2>Tunnel Society</h2>";
    for ($i = 0; $i < count($tunnel_society); $i++) 
    echo $tunnel_society[$i] . "<br>";
    }

    if(count($friends_circle) > 0)  
    {
    echo "<h2>Friends' Circle</h2>";
    for ($i = 0; $i < count($friends_circle); $i++) 
    echo $friends_circle[$i] . "<br>";
    }

    if(count($others) > 0)  
    {
    echo "<h2>Other Donors</h2>";
    for ($i = 0; $i < count($others); $i++) 
    echo $others[$i] . "<br>";      
    }

?>







<!--dont modify below this line-->
<?php require_once('footer.php') ?>

On a side note, I asked a question yesterday and the response was given to me in this nice little box that showed a table name and all of table column headers. Is there a way I can do that in the question so I can maybe illustrate a little better and make my question clearer?

Thanks everyone,

Joel

Leave a Reply

*

Hire Me
Follow Me!
Search
Most Popular Articles & Pages
Because your vote is Important
Sorry, there are no polls available at the moment.
Categories