|
PHP - Display Data In Tabular Format By Year And Month
Hi,
I want to display my data in this format
format.JPG 54.52KB
0 downloads
Expense data i am getting like this
<td><?php
$in = "SELECT sales_invoice.invoice_id as INID, DATE_FORMAT(sales_invoice.date_invoiced,'%M') AS month, sales_invoice_line_items.invoice_id, SUM(sales_invoice_line_items.sub_total) AS Total, SUM(sales_invoice_line_items.tax_amount) AS total_tax FROM sales_invoice INNER JOIN sales_invoice_line_items ON sales_invoice.invoice_id=sales_invoice_line_items.invoice_id GROUP BY sales_invoice.invoice_id, DATE_FORMAT(sales_invoice.date_invoiced, '%Y-%m')";
$in1 = mysql_query($in) or die (mysql_error());
$total=0;
$tax =0;
while($income = mysql_fetch_array($in1))
{
$total +=$income['Total'];
$tax +=$income['total_tax'];
echo $total - $tax; }
?>
</td>
And Profit i am getting like this
<td><?php
$in = "SELECT purchase_invoice.invoice_id as INID, DATE_FORMAT(purchase_invoice.date_invoiced,'%M') AS month, purchase_invoice_line_items.invoice_id, SUM(purchase_invoice_line_items.sub_total) AS Total, SUM(purchase_invoice_line_items.tax_amount) AS total_tax FROM purchase_invoice INNER JOIN purchase_invoice_line_items ON purchase_invoice.invoice_id=purchase_invoice_line_items.invoice_id GROUP BY purchase_invoice.invoice_id, DATE_FORMAT(purchase_invoice.date_invoiced, '%Y-%m')";
$in1 = mysql_query($in) or die (mysql_error());
$total=0;
$tax =0;
while($income = mysql_fetch_array($in1))
{
$total +=$income['Total'];
$tax +=$income['total_tax'];
echo $total - $tax; }
?>
</td>
I dont know how to display data like this. Do i need to change the the table structure in my database? Please suggest
Similar TutorialsThanks to aleX_hill and RussellReal for the help so far. I'm trying to utilise the code before to find the next month. However, it needs to be used with the current year. So I altered the below <?php $nextMonth = date("m") + 1; $daysInNextMonth = date("t",mktime(0,0,0,$nextMonth)); echo $daysInNextMonth;?> to #NEXT MONTH $nextMonth = date("m") + 1; echo $nextMonth; $daysInNextMonth = date("t",mktime(0,0,0,$nextMonth)); echo $daysInNextMonth ; $year = date("o"); echo $year; I can then pass it into my url query like so: <a href="<?php echo $url_path; ?>after=<?php echo $year; ?>-<?php echo $nextMonth; ?>-01&before=<?php echo $year; ?>-<?php echo $nextMonth; ?>-<?php echo $daysInNextMonth; ?>">Next Month</a> Brilliant I thought, until it comes to December 2010, the next month needs to be January 2011. Now I'm bamboozled. How do I get around this one? Many thanks, your help is always appreciated! Hello this code is supposed to sort by year and month, but it doesn't. It sort by year and months are random. Any suggestions? Many thanks,
$sortdata =[];
$namecolumn = array_column($data, '0');
foreach($data as $datecolumn){
$dateexplode = explode('/', $datecolumn[3]);
$sortdata[] = (isset($dateexplode[1])) ? $dateexplode[1] : ((isset($dateexplode[0])) ? $dateexplode[0] : '');
}
array_multisort($namecolumn, (($sorttype=='DESC') ? SORT_DESC : SORT_ASC), $sortdata, (($sorttype=='DESC') ? SORT_DESC : SORT_ASC), $data);
Edited May 13 by Barand code tags added Hello, not sure if lack of sleep or just staring at code too long. This is what I have: Code: [Select] while($rowy = mysql_fetch_array( $resulty )) { mpdate = YYYY-MM-DD $date = $rowy['mpdate']; $year = explode('-', $date); $month = date("F", mktime(0, 0, 0, $year[1])); ?> <?php echo $year[0]; ?> <br> <?php echo $month; ?> <br> <?php echo $rowy['mptitle']; } ?> I figure if I use: if ($year[0] == date('Y"){ echo month and entry stuff } but I keep getting errors. What I am trying to do is sort everything out into year month and day ie: 2012 March Entry 1 Entry 2 February Entry 1 Entry 2 Entry 3 etc... This is what I am getting 2012 March Entry 1 2012 March Entry 2 2012 February Entry 1 etc... Is there a way to fix this? How to acheive it friends? i want my current codes to show results accouding to current month followed by year, the precious month goes to the page two.
would really appreciate if someone could help me acheive it
$tableName="records"; Edited by lovephp, 02 December 2014 - 11:50 AM. Hi Guys, Been a while since I've been on here, but I'm currently bashing my head against a wall trying to figure this one out. Basically I have a calendar that outputs Monday through Sunday, and the user clicks on a date and outputs it to the URL.... ie. ?day=$day&month=$month&year=$year. Now the next part I need to find out what day (0-6) say the 25th Feb 2011 will be wday 5... now how can I do this in code? And before anyone says why not output it to url, I cannot as the first week and last weeks use different code Cheers James right now i have my date system to say "X amount of seconds ago, X hours ago, X days ago, X years ago, How could i turn mktime() (1290874161) into a mm/dd/yyyy format? so it would say like, "Nov 27, 2010 at Hour:Minute" (whatever the hour/minute is) Hi, I've got a date picker on a form which puts data into a database in the YYYY-MM-DD format. Just wondering how I could also put the name of the month (extracted from that) as well as just the year into separate columns. Ie: To use the field race_date from the form to also fill the 'race_month' and 'race_year' columns in the database. This code obviously only fills the 'race_date' column so far: Code: [Select] global $_POST; $race_date = $_POST["race_date"] ; .... $query = "INSERT INTO 10k_races (race_date, race_month, race_year)" . "VALUES ( '$race_date', '$race_month', '$race_year')"; Sorry for the beginner question, here I'm trying to retrieve data from the database and display it in the table format. But only table headers are printed, and not the actual values. The count variable is echoing 2 saying that data is present and correctly retrieved. Can anyone help?
<?php
include 'connect.php';
error_reporting(E_ALL ^ E_DEPRECATED);
error_reporting(E_ERROR | E_PARSE);
$sql="SELECT * FROM `resources` as r INNER JOIN `project_resources` as pr
ON r.res_id =pr.res_id WHERE project_id='$_POST[project_id]'";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
if($result === FALSE) {
die(mysql_error());
}
echo "$count";
echo '<table>
<tr>
<th>Resource ID</th>
<th>Resource Name</th>
<th>Email</th>
<th>Phone Number</th>
<th>Reporting Manager</th>
<th>Role</th>
<th>Designation</th>
</tr>';
while ($row = mysql_fetch_array($result)) {
echo '
<tr>
<td>'.$row['res_id'].'</td>
<td>'.$row['res_name'].'</td>
<td>'.$row['email'].'</td>
<td>'.$row['phone_number'].'</td>
<td>'.$row['reporting_manager'].'</td>
<td>'.$row['role'].'</td>
<td>'.$row['designation'].'</td>
</tr>';
}
echo '
</table>';
?>
Edited by mac_gyver, 22 September 2014 - 07:25 AM. code tags please Hello,
I am trying to display the data from two tables with proper format. But Its not happening
Here is my 1st table - orders
2.PNG 10.12KB
0 downloads
and my 2nd table - order_line_items
1.PNG 14.92KB
0 downloads
I want to display like this
3.PNG 4.03KB
0 downloads
Here is my code
$query = $mysqli->query("SELECT orders.order_id, orders.company_id, orders.order_for, order_line_items.order_id, order_line_items.item, order_line_items.unit,SUM(order_line_items.unit_cost * order_line_items.quantity) AS 'Total', order_line_items.tax from orders INNER JOIN order_line_items ON orders.order_id = order_line_items.order_id where orders.order_quote = 'Order' GROUP BY order_line_items.id"); ?>
<table id="dt_hScroll" class="table table-striped">
<thead><tr>
<th>Order ID</th>
<th>Company</th>
<th>Contact Person</th>
<th>Products</th>
<th>Total</th>
</tr>
</thead>
<tbody> <?php
while($row = $query->fetch_array())
{
?>
<tr>
<td><?php echo $row['order_id']; ?></td>
<td><?php echo $row['company_id']; ?></td>
<td><?php echo $row['contact_person'] ?></td>
<td><?php echo $row['item']; ?></td>
<td><?php echo $row['Total']; ?> %</td>
</tr>
<?php
}
But here order ID, Company ID, Contact Person are also repeating thrice with item in order_line_items table
Please suggest me how to do this
I have a file upload script that will eventually process a ton of files. I would like to upload them into sub-directories according to what year, month, and day they are uploaded.
A typical tree should look like this:
attachments/
--/2014
-----/January
--------/01
--------/02
--------/03 , etc.
-----/February
--------/01
--------/04
--------/09
--------/18
--------/20, etc
-----/March, etc
--/2015, etc.
So a file called image.jpg uploaded on 10/31/2014 would have a URL of attachments/2014/October/31/image.jpg. I understand that every time a file is uploaded, the script would have to detect through FTP whether or not folders for the year, month, and day exist, and if they don't create them. My problem is that I have no idea what the logic of this script would be. What order should I do things in? Is there a way to use maybe foreach to detect/create the folders? Any input would be appreciated.
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! Hello. I'm new to pHp and I would like to know how to get my $date_posted to read as March 12, 2012, instead of 2012-12-03. Here is the code: Code: [Select] <?php $sql = " SELECT id, title, date_posted, summary FROM blog_posts ORDER BY date_posted ASC LIMIT 10 "; $result = mysql_query($sql); while($row = mysql_fetch_assoc($result)) { $id = $row['id']; $title = $row['title']; $date_posted = $row['date_posted']; $summary = $row['summary']; echo "<h3>$title</h3>\n"; echo "<p>$date_posted</p>\n"; echo "<p>$summary</p>\n"; echo "<p><a href=\"post.php?id=$id\" title=\"Read More\">Read More...</a></p>\n"; } ?> I have tried the date() function but it always updates with the current time & date so I'm a little confused on how I get this to work. Hi, I'm wanting to find rows whose date is within the next week of the current month of the current year. The format of the date is, for example: 2010-10-28 Any ideas guys? Thanks lots! I have set up a function to validate user input date in yyyy-mm-dd format taking into account the following possibilities:
the length of the month
February 29th when the year is not a leap year
When I run the code, I do not get any feedback whatsoever, when it should.
This is the code below and I would appreciate your thoughts:
if(!function_exists('checkdate'))
{
function checkdate($date, $checkyear, $currentmonth)
{
if($checkyear == 0)//if it is not a leap year
{
// if current month is february
if($currentmonth == 2)
{
if(!preg_match("/^(20)\d\d[-](0[1-9]|1[012])[-](0[1-9]|1[0-9]|2[0-8])$/", $date))
{
// set error for date field
return ($error = 'Invalid Date!');
}
return ($error = 'valid Date!');
}
// if current months are april, june, september or november
elseif($currentmonth == 4 || $currentmonth == 6 || $currentmonth == 9 || $currentmonth ==11)
{
if(!preg_match("/^(20)\d\d[-](0[1-9]|1[012])[-](0[1-9]|1[0-9]|2[0-8]|3[0])$/", $date))
{
// set error for date field
return ($error = 'Invalid Date!');
}
return ($error = 'valid Date!');
}
else
{
if(!preg_match("/^(20)\d\d[-](0[1-9]|1[012])[-](0[1-9]|[12][0-8]|3[01])$/", $date))
{
// set error for date field
return ($error = 'Invalid Date!');
}
return ($error = 'valid Date!');
}
}
elseif($checkyear == 1)//if it is a leap year
{
// if current month is february
if($currentmonth == 2)
{
if(!preg_match("/^(20)\d\d[-](0[1-9]|1[012])[-](0[1-9]|1[0-9]|2[0-9])$/", $date))
{
// set error for date field
return ($error = 'Invalid Date!');
}
return ($error = 'valid Date!');
}
// if current months are april, june, september or november
elseif($currentmonth == 4 || $currentmonth == 6 || $currentmonth == 9 || $currentmonth ==11)
{
if(!preg_match("/^(20)\d\d[-](0[1-9]|1[012])[-](0[1-9]|1[0-9]|2[0-9]|3[0])$/", $date))
{
// set error for date field
return ($error = 'Invalid Date!');
}
return ($error = 'valid Date!');
}
else
{
if(!preg_match("/^(20)\d\d[-](0[1-9]|1[012])[-](0[1-9]|[12][0-9]|3[01])$/", $date))
{
// set error for date field
return ($error = 'Invalid Date!');
}
return ($error = 'valid Date!');
}
}
}
$todate = getdate();
$currentmonth = $todate['mon'];
$checkyear = date('L');
$date = 2014-02-31;
echo checkdate($date, $checkyear, $currentmonth);
Edited by terungwa, 26 July 2014 - 02:43 PM. Ok, so I have made 12 columns in mysql for each month.........I will have an event manager page that you can select each month that the event will take place in 0 is false 1 is true.........so I need to know how I can edit this code so that if the current month equals whatever month is checked, it will display..........is there a way to do this????????or would I need to use a date format instead of varchar and use a format such as yyyy-mm-dd and value = 0000/mm/00 so that only the month will display and then it could equal current month? Code: [Select] <?php // if no id is specified, list the available articles if(!isset($_GET['eventid'])) { $query = "SELECT eventid, event, startdate, enddate FROM Registration ORDER BY eventid"; $result = mysql_query($query) or die('Error : ' . mysql_error()); // create the article list while($row = mysql_fetch_array($result, MYSQL_NUM)) { list($eventid, $event, $startdate, $enddate) = $row; $as .= "<p><a href=\"viewevent.php?eventid=$eventid\">$startdate - $enddate --- $event</a></p>\r\n"; } $Events = 'Events'; } else { // get the article info from database $query = "SELECT event, description, startdate, enddate, location, subevent1, subevent2, subevent3, subevent4, subevent5, subevent6, subevent7, subevent8, price1, price2, price3, price4, price5, price6, price7, price8 FROM Registration WHERE eventid=".$_GET['eventid']; $result = mysql_query($query) or die('Error : ' . mysql_error()); $row = mysql_fetch_array($result, MYSQL_ASSOC); $startdate = $row['startdate']; $enddate = $row['enddate']; $location = $row['location']; $description = $row['description']; $event= $row["event"]; $subevent1 = $row['subevent1']; $subevent2 = $row['subevent2']; $subevent3 = $row['subevent3']; $subevent4 = $row['subevent4']; $subevent5 = $row['subevent5']; $subevent6 = $row['subevent6']; $subevent7 = $row['subevent7']; $subevent8 = $row['subevent8']; $title1 = $row['title1']; $title2 = $row['title2']; $title3 = $row['title3']; $title4 = $row['title4']; $title5 = $row['title5']; $title6 = $row['title6']; $title7 = $row['title7']; $title8 = $row['title8']; $price1 = $row['price1']; $price2 = $row['price2']; $price3 = $row['price3']; $price4 = $row['price4']; $price5 = $row['price5']; $price6 = $row['price6']; $price7 = $row['price7']; $price8 = $row['price8']; $date1 = $row['date1']; $date2 = $row['date2']; $date3 = $row['date3']; $date4 = $row['date4']; $date5 = $row['date5']; $date6 = $row['date6']; $date7 = $row['date7']; $date8 = $row['date8']; } ?> Code: [Select] <table width="410" border="1" cellspacing="0" cellpadding="0"> <tr> <td width="339" bgcolor="#999999" scope="col">EVENTS</td> </tr> <tr> <td class="afasd"><?php echo date("F",strtotime("-0 month")); ?></td> </tr> <tr> <td><?php echo $as; ?></td> </tr> <tr> <td class="adfaf"><?php echo date("F",strtotime("+1 month")); ?></td> </tr> <tr> <td><?php echo $as; ?></td> </tr> <tr> <td class="jasdfjs"><?php echo date("F",strtotime("+2 month")); ?></td> </tr> <tr> <td><?php echo $as; ?></td> </tr> </table> does this make sense? I have a payment form with the following code... Code: [Select] <!-- Expiration Month --> <span class="group"> <label class="innerlabel" for="expMonth">Month</label><!-- --> <select id="expMonth" name="expMonth"> <option></option> <option>01</option> <option>02</option> <option>03</option> <option>04</option> <option>05</option> <option>06</option> <option>07</option> <option>08</option> <option>09</option> <option>10</option> <option>11</option> <option>12</option> </select> </span> How is that data represented as far as Data-Type?? Is it treated as a String or an Integer?? How can I ensure that when I sent it to the Payment Gateway that it is treated as a Two-Digit value?? Thanks, Debbie Hello, i have two fields. a beginning year and an ending year. How can i make new fields out of the years in between the beginning and ending years. i hope that makes sense. 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 Please check the img link and you will understand Code: [Select] for ($index = 1; $index <= $numdays; $index++) { $users="SELECT invoice_date, DAY(invoice_date) as invoiceday, sum(total_amount) as fullamount from invoices WHERE MONTH(invoice_date)='".$month."' AND YEAR(invoice_date)='".$year."' GROUP BY invoiceday"; $res=mysql_query($users); $row=mysql_num_rows($res); while($fetch=mysql_fetch_array($res)){ echo "<tr id='". ( ( $j %2 == 0 ) ? 'row3' : 'row4' ) . "'>"; $explodedate=explode("-",$fetch['invoice_date']); $days=$explodedate[2]; $month1=$explodedate[1]; $year1=$explodedate[0]; $dates=date("jS", strtotime($fetch['invoice_date'])); echo "<td style='text-align:left;padding-left:12px'>".$index."</td>"; if($index==$dates){ echo "<td style='text-align:right;padding-right:12px'>".$fetch['fullamount']."</td>"; }else{ echo "<td style='text-align:right;padding-right:12px'>--</td>"; } echo "</tr>"; } } $total="SELECT SUM(total_amount) as fulltotal from invoices where MONTH(invoice_date)='".$month1."' AND YEAR(invoice_date)='".$year1."';"; $totres=mysql_query($total); $totfetch=mysql_fetch_assoc($totres); if($totfetch['fulltotal']==""){ echo "<tr><td>NOTHING FOUND</td></tr>"; }else{ ECHO "<TR>"; echo "<td style='color:#BD0000;padding-left:12px;border:1px solid #ccc;width:20%;height:40px;text-align:left'>TOTAL AMOUNT FOR ".$displaydate."</td>"; echo "<td style='color:#BD0000;border:1px solid #ccc;width:20%;height:40px;text-align:right;padding-right:12px'>".$totfetch['fulltotal']."</TD></TR>"; } echo "</table>"; |
|||||||