sorting sql query table values WordPress
- SOLVED
Hi everyone,
I have a problem that needs to be sorted out.
I am using cart66 e-commerce plugin for wordpress and in \reports' section of the admin side i can see list of all item from my shop with some statistics.
Now, default sorting for these is by NAME which is useless for me. What i would like to be able to do is to sort the table from attached image by TOTAL sales and income columns.
Now, here is the code from the page that shows reports:
<?php
$product = new Cart66Product();
$totalProductCount = count($product->getModelsNoClass());
if(Cart66Setting::getValue('page_product_report')){
$productPageInterval = ($totalProductCount > 50) ? 25 : 50;
$productPageInterval = (Cart66Setting::getValue('page_product_report_size')) ? Cart66Setting::getValue('page_product_report_size') : $productPageInterval;
}
else{
$productPageInterval = $totalProductCount;
}
$productPageInterval = (isset($_GET['perpage'])) ? $_GET['perpage'] : $productPageInterval;
$productPageStart = (isset($_GET['start'])) ? $_GET['start'] : 0;
$productPageEnd = (isset($_GET['end'])) ? $_GET['end'] : $productPageStart + $productPageInterval;
$products = $product->getModels('where id>0', 'order by name', $productPageStart . ',' . $productPageInterval);
if(Cart66Setting::getValue('page_product_report')){
echo "<h1>Reports are paged at ";
echo (Cart66Setting::getValue('page_product_report_size')) ? Cart66Setting::getValue('page_product_report_size') : 25;
echo " products per page.</h1>";
}
if(CART66_PRO) {
if(count($products)) {
$today = date('m/d/Y', Cart66Common::localTs());
$salesGrandTotal= 0;
$incomeGrandTotal = 0;
?>
<table class="Cart66TableMed">
<tr>
<th colspan="2"><?php _e( 'Product Name' , 'cart66' ); ?></th>
<?php $thisMonth = date('m/1/Y', Cart66Common::localTs()); ?>
<?php for ($i=5; $i >= 0; $i--): ?>
<th colspan="2"><?php echo date('M, Y', strtotime("$thisMonth - $i months")); ?></th>
<?php endfor; ?>
<th colspan="2" style="background-color: #EEE;"><?php _e( 'Total Sales' , 'cart66' ); ?></th>
</tr>
<tr>
<td colspan="2"> </td>
<?php for ($i=5; $i >= 0; $i--): ?>
<td style="font-weight: bold; border-left: 1px solid #ccc;"><?php _e( 'sales' , 'cart66' ); ?></td>
<td style="font-weight: bold; background-color: #eee;"><?php _e( 'income' , 'cart66' ); ?></td>
<?php endfor; ?>
<td style="font-weight: bold; background-color: #ddd; border-left: 1px solid #ccc;"><?php _e( 'sales' , 'cart66' ); ?></td>
<td style="font-weight: bold; background-color: #ddd;"><?php _e( 'income' , 'cart66' ); ?></td>
</tr>
<?php foreach($products as $p): ?>
<tr>
<td colspan="2" style="border-right: 1px solid #ccc;"><?php echo $p->name; ?></td>
<?php for ($i=5; $i >= 0; $i--): ?>
<?php
if(!isset($totals) || !is_array($totals)) { $totals = array(); }
$monthSales = $p->getSalesForMonth( date('n', strtotime("$thisMonth - $i months")), date('Y', strtotime("$thisMonth - $i months")) );
isset($totals[$i]) ? $totals[$i] += $monthSales : $totals[$i] = $monthSales;
?>
<?php
if(!isset($income) || !is_array($income)) { $income = array(); }
$monthIncome = $p->getIncomeForMonth( date('n', strtotime("$thisMonth - $i months")), date('Y', strtotime("$thisMonth - $i months")) );
isset($income[$i]) ? $income[$i] += $monthIncome : $income[$i] = $monthIncome;
?>
<td style="text-align: right;">
<?php echo $monthSales; ?> <?php //echo $p->name ?>
</td>
<td style="text-align: right; background-color: #eee; border-right: 1px solid #CCC;">
<?php
$money = $p->getIncomeForMonth( date('n', strtotime("$thisMonth - $i months")), date('Y', strtotime("$thisMonth - $i months")) );
echo Cart66Common::currency($money);
?>
</td>
<?php endfor; ?>
<?php $salesGrandTotal += $p->getSalesTotal(); ?>
<?php $incomeGrandTotal += $p->getIncomeTotal(); ?>
<td style="text-align: right; font-weight: bold; background-color: #ddd;"><?php echo $p->getSalesTotal(); ?></td>
<td style="text-align: right; font-weight: bold; background-color: #ddd;">
<?php
echo Cart66Common::currency($p->getIncomeTotal());
?>
</td>
</tr>
<?php endforeach; ?>
<tr>
<td colspan="2"> </td>
<?php for($i=count($income); $i>0; $i--): ?>
<td colspan="1" style="text-align: center; font-weight: bold; background-color: #ddd;"><?php echo $totals[$i-1]; ?></td>
<td colspan="1" style="text-align: center; font-weight: bold; background-color: #ddd; border-right: 1px solid #ccc;">
<?php
echo Cart66Common::currency($income[$i-1]);
?>
</td>
<?php endfor; ?>
<td style="text-align: right; background-color: #ddd; font-weight: bold;"><?php echo $salesGrandTotal; ?></td>
<td style="text-align: right; background-color: #ddd; font-weight: bold;">
<?php
echo Cart66Common::currency($incomeGrandTotal);
?>
</td>
</tr>
</table>
<?php
}
else {
echo '<p>' . __('You have not yet created any products', 'cart66') . '.</p>';
}
}
else {
echo '<p>' . __('Product sales reports are only available in <a href="http://cart66.com">Cart66 Professional</a>','cart66') . '</p>';
}
?>
<div class="cart66-product-report-pagination">
<?php if(Cart66Setting::getValue('page_product_report')): ?>
<?php if($productPageStart > 0) : ?>
<a href="admin.php?page=cart66-reports">First <?php echo $productPageInterval ?> Products</a>
<a href="admin.php?page=cart66-reports&start=<?php echo ($productPageStart - $productPageInterval - 1); ?>">Previous <?php echo $productPageInterval ?> Products</a>
<?php endif; ?>
Viewing <?php echo $productPageStart; ?> -
<?php echo ($productPageEnd > $totalProductCount) ? $totalProductCount : $productPageEnd; ?> of
<?php echo $totalProductCount; ?>
<?php if($totalProductCount > ($productPageEnd + 1)) : ?>
<a href="admin.php?page=cart66-reports&start=<?php echo ($productPageEnd + 1); ?>">Next <?php echo $productPageInterval ?> Products</a>
<?php endif; ?>
<?php if($productPageEnd < $totalProductCount): ?>
<a href="admin.php?page=cart66-reports&start=<?php echo $totalProductCount-$productPageInterval; ?>">Last <?php echo $productPageInterval ?> Products</a>
<?php endif; ?>
<?php endif; ?>
</div>
<?php if(CART66_PRO): ?>
<h3 style="margin-top: 40px;"><?php _e( 'Daily Income Totals' , 'cart66' ); ?></h3>
<?php
global $wpdb;
$data = array();
for($i=0; $i<42; $i++) {
$dayStart = date('Y-m-d 00:00:00', strtotime('today -' . $i . ' days', Cart66Common::localTs()));
$dayEnd = date('Y-m-d 00:00:00', strtotime("$dayStart +1 day", Cart66Common::localTs()));
$orders = Cart66Common::getTableName('orders');
$sql = "SELECT sum(`total`) from $orders where ordered_on > '$dayStart' AND ordered_on < '$dayEnd'";
$dailyTotal = $wpdb->get_var($sql);
$data['days'][$i] = date('m/d/Y', strtotime($dayStart, Cart66Common::localTs()));
$data['totals'][$i] = $dailyTotal;
}
?>
<table class="Cart66TableMed">
<?php for($i=0; $i<count($data['days']); $i++): ?>
<?php if($i % 7 == 0) { echo '<tr>'; } ?>
<td>
<span style="color: #999; font-size: 11px;"><?php echo date(get_option('date_format'), strtotime($data['days'][$i], Cart66Common::localTs())); ?></span><br/>
<?php echo Cart66Common::currency($data['totals'][$i]); ?>
</td>
<?php if($i % 7 == 6) { echo '</tr>'; } ?>
<?php endfor; ?>
</table>
<?php endif; ?>
Answers (2)
John Cotton answers:
You could get into a series of SQL queries that do the work for you.
However, there's a really nice plugin for jQuery that helps do it client-side and is very easy to set up.
https://datatables.net/
I think that's the way to go.
elcrni comments:
Hi John,
I know about that plugin and even tried to implement it but without any success...
thanks,
Alek
John Cotton comments:
What was the difficultly?
You'd get out-of-the-box version with:
$(document).ready(function() { $('table.Cart66TableMed').DataTable();} );
You might need to some custom code for the currency values, but that's not hard.
elcrni comments:
well, i think i did everything from the simple tutorial on their page but nothing happened... maybe i messed up paths to java files... have no idea.
Would really appreciate some pointers!
many thanks.,
Alek
John Cotton comments:
This isn't the best way of doing it, but it'll get it working so that you can tell whether it's for you or not:
function admin_inline_js() {
$html = <<< EOT
<link type="text/css" rel="stylesheet" href="//cdn.datatables.net/1.10.4/css/jquery.dataTables.min.css" />
<script type="text/javascript">
jQuery(document).ready(function($) {
$.getScript( '//cdn.datatables.net/1.10.4/js/jquery.dataTables.min.js').done( function( script, textStatus ) {
$('table.Cart66TableMed').dataTable();
});
});
</script>
EOT;
echo $html;
}
add_action( 'admin_print_footer_scripts', 'admin_inline_js', 99 );
elcrni comments:
Hi John,
not sure where to nest that code...
thanks in advance,
Alek
John Cotton comments:
In your functions.php if you're using a custom theme, or make a plugin of it if not.
elcrni comments:
does not seem to work...
John Cotton comments:
....if you've put it in the right place, it will work.
You need to check that it's being called. Until that code appears in the page (view source) it won't work.
If it's appearing and nothing is happening, the you've posted the wrong code up above and you need to look at the class/id of the table you want to affect and change my code accordingly.
The code will work when used correctly.
elcrni comments:
Here's my functions.php, not sure where to place your code?
many thanks,
Alek
<?php
if ( function_exists('register_sidebars') )
register_sidebars(3);
//GsL98DGtpo0W
function __search_by_title_only( $search, &$wp_query )
{
global $wpdb;
if ( empty( $search ) )
return $search; // skip processing - no search term in query
$q = $wp_query->query_vars;
$n = ! empty( $q['exact'] ) ? '' : '%';
$search =
$searchand = '';
foreach ( (array) $q['search_terms'] as $term ) {
$term = esc_sql( like_escape( $term ) );
$search .= "{$searchand}($wpdb->posts.post_title LIKE '{$n}{$term}{$n}')";
$searchand = ' AND ';
}
if ( ! empty( $search ) ) {
$search = " AND ({$search}) ";
if ( ! is_user_logged_in() )
$search .= " AND ($wpdb->posts.post_password = '') ";
}
return $search;
}
add_filter( 'posts_search', '__search_by_title_only', 500, 2 );
add_theme_support( 'post-thumbnails' );
add_image_size('yarpp-thumbnail', $width = '111px', $height = '70px', true);
John Cotton comments:
Anywhere in that file.
That you need to ask, suggests to me that you should probably get a developer to help you.
elcrni comments:
It simply does not work when placed after the code that i have in functions.php.
I am not developer but i do know enought about php and stuff to manage 99% of tasks on my websites, but this sorting was a bit out of my range.
thanks,
Alek
John Cotton comments:
...if the table has a class of Cart66TableMed and you are seeing nothing, then you've not put it in the right place, or you've got some other js error that's stopping the code from running.
I've just tried it locally and it definitely works.
