|
PHP - Total Margin For Each Month... (using Mysql Sum())
Hi friends,
I'm looking to try and echo the profits from a MySQL "totals" table for each month of the year... the table bascically consists of a "totals" column which contains the total amount spent on each order, a "suppliertotals" column which contains the total amount that it costs us to buy the goods for that order and an "orderdate" column which stores the order date as "d/m/y" (so 01/01/01). To work out the total margin from the complete table, I calculate the sum of totals minus the sum of suppliertotals. This is our margin. I'm currently doing the calculation with MySQL's sum() function, and using PHP explode to get the date, but I'm obviously doing something wrong since PHP is returning the same month several times with different amounts... Let me show you the code I have; I presume I need to use some kind of foreach statement, but I'm not sure where or how... hoping you can help! if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("onetelec_wsshop", $con); $result=mysql_query("SELECT orderdate FROM totals", $con); while($row = mysql_fetch_array($result)) { $ordmonth = explode("/", $row['orderdate']); $ordmonth = $ordmonth[1]; switch ($ordmonth) { case "01": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin = $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for January<br /><br />"; break; case "02": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin = $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for February<br /><br />"; break; case "03": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin = $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for March<br /><br />"; break; case "04": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin = $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for April<br /><br />"; break; case "05": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin = $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for May<br /><br />"; break; case "06": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin = $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for June<br /><br />"; break; case "07": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin = $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for July<br /><br />"; break; case "08": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin = $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for August<br /><br />"; break; case "09": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin = $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for September<br /><br />"; break; case "10": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin += $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for October<br /><br />"; break; case "11": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin += $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for November<br /><br />"; break; case "12": $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con); while($strow = mysql_fetch_array($stresult)) { $margin += $strow['sum(total)'] - $strow['sum(stotal)']; } echo $margin." for December<br /><br />"; break; } } Similar TutorialsHello I am very new to php and programming and I need a grand total of "$Total = odbc_result($result, "total");" but not sure of how to create it. so im trying to setup a page for my bills that i pay monthly so me and my gf can access it remotely whenever we need to. we have a few loans that we are paying off in installment loans every month so i created a table like the picture below (which is actually in excel but it looks about the same). i know how to pull via select and sum everything that i need but what i'm actually going for is this: every month on those certain due dates i'd like for the monthly payment to be subtracted from the balance automatically..and it show the reflected balance. i'm not sure how to go about such a thing though as i've never worked with dates and times. my logic is that i have to setup a beginning time stamp for it to work off of..and then every 30 days from that time stamp..subtract the payment. then again, there's not always 30 days in a month and it'd get off track. any ideas? Hi guys, Im having problem on how to achieved this one.. I would like to get the all event of this month (April ) Starting todays date.. Code: (php) [Select] $now = gmdate("Y-m-d", mktime(gmdate("H")+8, gmdate("i"), gmdate("s"), gmdate("m"), gmdate("d")+8, gmdate("Y"))); $week = gmdate("Y-m-d", mktime(gmdate("H")+8, gmdate("i"), gmdate("s"), gmdate("m"), gmdate("d")+30, gmdate("Y"))); if ($result = mysql_query("SELECT * FROM fiesta WHERE sta_date BETWEEN '$now' AND '$week' ORDER BY sta_date LIMIT 10")){ if (mysql_num_rows($result)) { $return = ''; while ($row = mysql_fetch_array( $result )) { $date = date("D",strtotime($row["sta_date"])); $return .= "<a href='sta.php' style='font-size:11px;' title='" . $row['fiesta_brgy'] . " " . $row['fiesta_town'] . "'>" . $row['fiesta_brgy'] . " " . $row['fiesta_town'] . "</a> ($date), "; } echo rtrim($return, ', '); } } The result of this query will include the events on May.. How can i display only the event on this month(April)? starting todays date. Thank you i want to make a monthly report the user selects month from drop down and i must get the specified dates of that month from the DB I am using ajax to get the dates This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=318996.0 How do I total unique id's with php or MySQL. For instance in MySQL I have... ID 1 1 3 4 2 1 1 3 How can I total all of the 1's, all of the twos, all of the threes and so forth. I have four 1's, one 2, two 3's and so forth... Hi, I have a table that contains posts and each post has a datetime field. Im trying to work out how I can show entries from today, this week and this month so I can have a link that shows all posts from today or this month etc. Any one know how I can do this ? My current piece of code that pulls the data from the db looks like this : Code: [Select] $texts= mysql_query("SELECT * FROM submittedtexts Order by id DESC LIMIT " . (($page - 1) * 6) . ", 6"); I want to add a bit that acts like : WHERE date = today Hope that makes sense, Im a bit of a newbie Thanks in advance, Scott Hello. I have a mysql database with an 'entrytime' field which contains the unix timestamp the record was added. How do I create a mysql query to select all records for a given month/year? For example, if I wanted to create a query to display all records for October 2010 how do I go about it? I am at a loss and can't find my answer via google. Thanks in advance for any help! I create a post just now but cannot seem to find it to add to it. Anyhow, I have 2 tables, I need to multiply qty from the one table to the mass from the other to get a total, which I have achieved with this code:
$sql_total_mass = "
SELECT jobs_assembly.assemble_qty, jobs.mass,
(jobs_assembly.assemble_qty * jobs.mass) AS 'sum'
FROM jobs_assembly
LEFT JOIN jobs on jobs_assembly.jobs_id = jobs.id
LEFT JOIN job_names ON jobs.job_names_id = job_names.job_id
WHERE jobs_assembly.assemble_date = '$link_date'
ORDER BY job_names.job_name, jobs.assembly
";
$result_total_mass = mysqli_query($conn, $sql_total_mass);
if (mysqli_num_rows($result_total_mass) > 0) {
while($row_total_mass = mysqli_fetch_assoc($result_total_mass)) {
echo $row_total_mass['sum'].'<br />';
} //while
} //if
Now I need to take all these totals and make a grand total. So basically add all the results from $row_total_mass[‘sum’] together and show it.
Basically I want to add up multiple tables and display a grand total on my page when the radio button is pressed. The radio button has values that connect to my database and the values link to and ID with a price. How can I use this code in order to work out a grand total? Many thanks. I have searched everywhere but found nothing. Dear all, I would like to find the total number of individual record in MySQL with php. For example: I have different department name stored in a column in MySQL. I would like to find out the total number of each department. But I do not know the names of the department How can I do it?? Counting the total of each department without knowing their names Thanks! Trying to get the below to generate the month in word form from a numeric entry in a database. Code: [Select] elseif($_GET['rma']=="calander"){ $sql101010="SELECT DISTINCT rma_year_issued FROM $tbl_name4 ORDER BY rma_year_issued"; $result101010=mysql_query($sql101010); while($row101010=mysql_fetch_array($result101010)){ extract($row101010); $content.='<a href="./acp_admincp.php?rma=calander&year='.$rma_year_issued.'">'.$rma_year_issued.'</a> <br />'; } if(isset($_GET['year'])){ $logout.=' | <a href="./acp_admincp.php?rma=calander">Back to RMA Calander</a>'; $rma_year_issued=$_GET['year']; $sql111010="SELECT DISTINCT rma_month_issued FROM $tbl_name4 WHERE rma_year_issued='$rma_year_issued' ORDER BY rma_month_issued"; $result111010=mysql_query($sql111010); while($row111010=mysql_fetch_array($result111010)){ extract($row111010); $months = array('Janurary', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'); for($i=0; $i <=11; $i++){ if($rma_month_issued=$i){ $rma_month_issued2=$months[$i]; } } $content=""; $content.='<a href="./acp_admincp.php?rma=calander&year='.$rma_year_issued.'&month='.$rma_month_issued.'">'.$rma_month_issued2.'</a> <br />'; } } } Basically in the data base I may have 2,5,7 under the year 2011. I want to get the numeric months into words for use in the text of the link. The above is returning December even though the entry in the database is 5. Hi.. I need help in getting the 3 Months Name from my table field FromMonth and ToMonth. Here is the scenario. First, I select FromMonth and ToMonth. Ex. FromMonth = 5 ToMonth = 7 So it means FromMonth is May ToMonth is July. Now I save the MonthNumber to my database: table- so_month FromMonth = 5 ToMonth = 7 Now I need to get the between Months Name from FromMonth to ToMonth which are (May, June, July). How does it possible? Thank you so much. So I am creating a list of events. There will be a drop down menu to change between months. I only want the months to be listed if an event during that month exists. So if I have events listed for September and November, list only September and November, skipping October. Any idea on how to approach this or if there are any functions for such a thing?
<html>
<head>
<title>Sum Html Textbox Values using jQuery/JavaScript</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script>
$(function() {
$(document).on('blur keyup', '.add, .sub', function(e) {
var sum = 0;
$('.add, .sub').each(function(i) {
if (!isNaN(this.value) && this.value.length != 0) {
if ($(this).hasClass('add')) {
sum += parseFloat(this.value);
}
else {
sum -= parseFloat(this.value);
}
}
});
$('#total').text(sum.toFixed(2));
})
})
</script>
</head>
<body>
<form method="post" action="calc.php">
Addition:
<br/>
Field 1: <input type="text" class="add" name="1" value="7500"/><br/>
Field 2: <input type="text" class="add" name="2" value=""/><br/>
Field 3: <input type="text" class="add" name="3" value=""/><br/>
Field 4: <input type="text" class="add" name="4" value=""/><br/><br/>
Total Addition:
<br/><br/>
Substraction:
<br/>
Field 1: <input type="text" class="sub" name="5" value=""/><br/>
Field 2: <input type="text" class="sub" name="6" value=""/><br/>
Field 3: <input type="text" class="sub" name="7" value=""/><br/>
Field 4: <input type="text" class="sub" name="8" value=""/><br/><br/>
Total Substraction:
<br/><br/>
Grand Total: <div id="total"></div>
<input type="submit" value="submit" />
</form>
</body>
</html>
Hello all, thanks for everything you do! I used a php from generator to make the form seen on this page: http://www.firstpresgreenville.org/forms/retirementform/retirementform-alt.html I need to be able to click on the check boxes and get a dollar amount total in the total field....So if you checked "single", the total field would read $40, and if you checked on "couple", the field would read $50...simple as that... Is there anyway to make that happen? This is the code I am working with now.... <?php # This block must be placed at the very top of page. # -------------------------------------------------- require_once( dirname(__FILE__).'/form.lib.php' ); phpfmg_display_form(); # -------------------------------------------------- function phpfmg_form( $sErr = false ){ $style=" class='form_text' "; ?> <form name="frmFormMail" action='' method='post' enctype='multipart/form-data' onsubmit='return fmgHandler.onsubmit();'> <input type='hidden' name='formmail_submit' value='Y'> <div id='err_required' class="form_error" style='display:none;'> <label class='form_error_title'>Please check the required fields</label> </div> <ol class='phpfmg_form' > <li class='field_block' id='field_0_div'><div class='col_label'> <label class='form_field'>Full Name(s)</label> <label class='form_required' >*</label> </div> <div class='col_field'> <input type="text" name="field_0" id="field_0" value="<?php phpfmg_hsc("field_0"); ?>" class='text_box'> <div id='field_0_tip' class='instruction'></div> </div> </li> <li class='field_block' id='field_1_div'><div class='col_label'> <label class='form_field'>Street Address</label> <label class='form_required' >*</label> </div> <div class='col_field'> <input type="text" name="field_1" id="field_1" value="<?php phpfmg_hsc("field_1"); ?>" class='text_box'> <div id='field_1_tip' class='instruction'></div> </div> </li> <li class='field_block' id='field_2_div'><div class='col_label'> <label class='form_field'>City</label> <label class='form_required' >*</label> </div> <div class='col_field'> <input type="text" name="field_2" id="field_2" value="<?php phpfmg_hsc("field_2"); ?>" class='text_box'> <div id='field_2_tip' class='instruction'></div> </div> </li> <li class='field_block' id='field_3_div'><div class='col_label'> <label class='form_field'>State</label> <label class='form_required' >*</label> </div> <div class='col_field'> <input type="text" name="field_3" id="field_3" value="<?php phpfmg_hsc("field_3"); ?>" class='text_box'> <div id='field_3_tip' class='instruction'></div> </div> </li> <li class='field_block' id='field_4_div'><div class='col_label'> <label class='form_field'>Zip Code</label> <label class='form_required' >*</label> </div> <div class='col_field'> <input type="text" name="field_4" id="field_4" value="<?php phpfmg_hsc("field_4"); ?>" class='text_box'> <div id='field_4_tip' class='instruction'></div> </div> </li> <li class='field_block' id='field_5_div'><div class='col_label'> <label class='form_field'>Phone</label> <label class='form_required' >*</label> </div> <div class='col_field'> <input type="text" name="field_5" id="field_5" value="<?php phpfmg_hsc("field_5"); ?>" class='text_box'> <div id='field_5_tip' class='instruction'>(###) ###-####</div> </div> </li> <li class='field_block' id='field_6_div'><div class='col_label'> <label class='form_field'>Email</label> <label class='form_required' >*</label> </div> <div class='col_field'> <input type="text" name="field_6" id="field_6" value="<?php phpfmg_hsc("field_6"); ?>" class='text_box'> <div id='field_6_tip' class='instruction'></div> </div> </li> <li class='field_block' id='field_7_div'><div class='col_label'> <label class='form_field'>Number of Attendees</label> <label class='form_required' >*</label> </div> <div class='col_field'> <input type="text" name="field_7" id="field_7" value="<?php phpfmg_hsc("field_7"); ?>" class='text_box'> <div id='field_7_tip' class='instruction'></div> </div> </li> <li class='field_block' id='field_8_div'><div class='col_label'> <label class='form_field'>Single or Couple?</label> <label class='form_required' >*</label> </div> <div class='col_field'> <?php phpfmg_checkboxes( 'field_8', "Single|Couple" );?> <div id='field_8_tip' class='instruction'></div> </div> </li> <li class='field_block' id='field_9_div'><div class='col_label'> <label class='form_field'>Amount Due</label> <label class='form_required' >*</label> </div> <div class='col_field'> <input type="text" name="field_9" id="field_9" value="<?php phpfmg_hsc("field_9"); ?>" class='text_box'> <div id='field_9_tip' class='instruction'>$40/ single or $50/couple (Includes one book and lunch)</div> </div> </li> <li class='field_block' id='field_10_div'><div class='col_label'> <label class='form_field'>Additional Comments</label> <label class='form_required' > </label> </div> <div class='col_field'> <textarea name="field_10" id="field_10" rows=4 cols=25 class='text_area'><?php phpfmg_hsc("field_10"); ?></textarea> <div id='field_10_tip' class='instruction'></div> </div> </li> <?php phpfmg_dependent_dropdown( 'field_11' );?> <li> <div class='col_label'> </div> <div class='form_submit_block col_field'> <input type='submit' value='Submit' class='form_button'> <span id='phpfmg_processing' style='display:none;'> <img id='phpfmg_processing_gif' src='<?php echo PHPFMG_ADMIN_URL . '?mod=image&func=processing' ;?>' border=0 alt='Processing...'> <label id='phpfmg_processing_dots'></label> </span> </div> </li> </ol> </form> <?php phpfmg_javascript($sErr); } # end of form function phpfmg_form_css(){ ?> Thanks so much! I am trying to compare values and if the month is not of 2 digits then the comparison goes wrong. Here is some hacked crap that doesn't work. if (strlen($_POST['m'] <= 1)){$month = "0".$_POST['m'];}else{$month = $_POST['m'];} if (strlen($_POST['d'] <= 1)){$day = "0".$_POST['d'];}else{$day = $_POST['d'];} <form> function GetDays(){ $Listd = '<select name="d">'; if (isset($_POST['d'])){$Listd .='<option value="'.$_POST['d'].'">'.$_POST['d'].'</option>';} for ($x = 1; $x <= date('d'); $x++) $Listd .= '<option value="'.$x.'">'.$x.'</option>'; $Listd .= '</select>'; return $Listd; } function GetMonths(){ $Listm = '<select name="m">'; if (isset($_POST['m'])){$Listm .='<option value="'.$_POST['m'].'">'.$_POST['m'].'</option>';} for ($x = 1; $x <= date('m'); $x++) $Listm .= '<option value="'.$x.'">'.$x.'</option>'; $Listm .= '</select>'; return $Listm; } Here is what I am comparing: Now: 201012141292327875 Submitted: 201110101292353200 // 2 digit month/day Now: 201012141292327984 Submitted: 2011991292353200 // not 2 digits month/day 2 hours is enough for me...can someone shed some light on this for me please? Hello everyone I am coding a membership system for my usersystem on my website and I needed a little help. I have searched around quite a bit and can't find a working code. In my database I have the date the VIP was purchased and I was wondering if anyone had a code which would check if they've had VIP for over a month and if so, delete it from the database. I'm new-ish to MySQL and PHP and can't seem to code a working one. Hello. I want to generate the initial of the day of all the days of a chosen month. Meaning, if I choose this month it will give me: TFSSMTWTFSSMTWTFSSMTWTFSSMTWTF Is that clear? Can anybody help me? thanks! I'm trying to get a total number from the db where the month is the same and the ip is different. This is a stats script that someone else wrote, and I'm just trying to get a month-to-month total. Right now, it pulls the information out and displays the month with how many different ips came that month, the problem is, it lists like this: June - 41 - 41 June - 1 - 1 June - 1 - 1 June - 1 - 1 June - 1 - 1 June - 1 - 1 June - 9 - 9 June - 2 - 2 June - 2 - 2 June - 2 - 2 June - 1 - 1 June - 3 - 3 June - 2 - 2 June - 4 - 4 June - 1 - 1 June - 13 - 13 June - 154 - 154 what I need to do is grab all those numbers and give a total for that month, in this case, June. This is my query Code: [Select] $query = "SELECT date, COUNT(referrer)'referrer' FROM app_analytics WHERE appid = $userid AND date BETWEEN '" .$y_month . "' AND '" . $t_month . "' GROUP BY referrer ORDER BY date"; Can anybody help out? Thanks in advance |
|||||||