Example of Wordpress datatables.net SQL Query using PHP Server side and Client side

cpshart · March 2019

Hi, I am struggling to find a working example of a datatable generation within Wordpress environment using MySQL query for server side or client based set-up.

I have successfully created multiple working PHP / MySQL tables/modals using the datatables editor, but I don't think my approach for building those tables where editing is not a requirement, is correct.

I can provide more detail and example of scripts I have set-up if required.

Sorry if above is a bit vague, but it maybe a starting point to point me in the right direction.

Many Thanks

Colin

colin · March 2019

Hi @cpshart ,

This blog post should help, it addresses how to install DataTables and Editor within WordPress.

Cheers,

Colin

cpshart · March 2019

Hi colin

Many Thanks for the link which I have read again, I have successfully installed the system on my server and created editable datatables within Wordpress using Woody PHP Snippets plugin.

I am however getting unusual results with one of my non editable tables as shown below (so I am not sure if I have done something fundamentally wrong in my approach).

I have removed all filtering, footer callbacks etc from the PHP snippet as they are not responsible for the error.

The error generated by the PHP Snippet displayed on the console is shown below

**Failed to load resource: the server responded with a status of 500 ()
**
If I run the SQL query through phpMyAdmin it works and generates the required output. If, however I simplify the quantity part of the SQL query in the PHP snippet from

SUM(IF(t.transaction_type="Buy",ANY_VALUE(t.quantity),ANY_VALUE(t.quantity) * -1)) AS quantity,

to

SUM(ANY_VALUE(t.quantity)) AS quantity,

Then it generates the datatable with no errors on the console, albeit the quantity is no longer correct !!

Failing PHP Snippet shown below
**Console ERROR Failed to load resource: the server responded with a status of 500 ()
**

<html>
<head>
<title>16520, latest Income By Portfolio</title>
<!-- Basic Datatables Editor Initilisation -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css" />
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.6/css/buttons.dataTables.min.css" />
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/1.3.0/css/select.dataTables.min.css" />
<link rel="stylesheet" type="text/css" href="https://www.ukincomeinvestor.co.uk/Editor-1.8.1/css/editor.dataTables.min.css" />

<!-- Semantic UI Styling -->    
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/semantic-ui/2.3.1/semantic.min.css" />
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/dataTables.semanticui.min.css" />   
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.6/css/buttons.semanticui.min.css" />    
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/1.3.0/css/select.semanticui.min.css" />  
</head>

<table id="income_by_portfolio" class="" style="width:100%">
<thead>
<tr>
<th>Portfolio</th>
<th>Symbol</th>
<th>Name</th>
<th>PayDate</th>
<th>Dividend</th>
<th>Quantity</th>
<th>Payment</th>
</tr>
</thead>

<tbody>     
<?php   
global $wpdb;    
global $current_user;
get_currentuserinfo();
$user_id = $current_user->ID;

$rows = $wpdb->get_results("
SELECT 
        s.symbol AS symbol,
        s.name AS name, 
        p.code AS portfolio, 
        d.pdate AS pdate, 
        ROUND(ANY_VALUE(d.dividend),2) AS dividend,
        SUM(IF(t.transaction_type="Buy",ANY_VALUE(t.quantity),ANY_VALUE(t.quantity) * -1)) AS quantity,
        ROUND((ANY_VALUE(t.quantity) * ANY_VALUE(d.dividend) / 100),2) AS payment
        
        FROM
        dm_transactions t
        INNER JOIN dm_dividends d ON (d.stock_id = t.stock_id)
        INNER JOIN dm_stocks s ON (s.id = t.stock_id) 
        INNER JOIN dm_portfolios p ON (t.portfolio_id = p.id)
        
        WHERE
        t.user_id = 2
        GROUP BY
        d.pdate, t.portfolio_id, s.symbol, s.name
");
    
foreach ($rows as $row ){
    echo "<tr>";
    echo "<td>$row->portfolio</td>";
    echo "<td>$row->symbol</td>";
    echo "<td>$row->name</td>";
    echo "<td>$row->pdate</td>";
    echo "<td>$row->dividend</td>";
    echo "<td>$row->quantity</td>";
    echo "<td>$row->payment</td>";
echo "</tr>";
}
echo "</table>";

<?php
>
?>


<input type='hidden' id='passuserid' value='<?php echo $current_user->ID; ?>'>

<!-- Basic Datatables Editor Initilisation -->
<script type="text/javascript"   src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script type="text/javascript"   src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script type="text/javascript"   src="https://cdn.datatables.net/buttons/1.5.4/js/dataTables.buttons.min.js"></script>
<script type="text/javascript"   src="https://cdn.datatables.net/select/1.2.7/js/dataTables.select.min.js"></script>
<script type="text/javascript"   src="https://www.ukincomeinvestor.co.uk/Editor-1.8.1/js/dataTables.editor.js"></script>    
<script type="text/javascript"   src="https://cdn.datatables.net/responsive/2.2.3/js/dataTables.responsive.min.js"></script>
<script type="text/javascript"   src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.2/js/select2.min.js"></script> 

<!-- Semantic UI Javascript --> 
<script type="text/javascript"   src="https://cdnjs.cloudflare.com/ajax/libs/semantic-ui/2.3.1/semantic.min.js"></script>
<script type="text/javascript"   src="https://cdn.datatables.net/1.10.19/js/dataTables.semanticui.min.js"></script> 
<script type="text/javascript"   src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.semanticui.min.js"></script>
<script type="text/javascript"   src="https://www.ukincomeinvestor.co.uk/Editor-1.8.1/js/editor.semanticui.min.js"></script>

Any help much appreciated.

Thanks Colin

allan · March 2019

You'll need to check the server's error log for this one. I don't immediately see a coding error in the above, but the error log will contain a message indicating what the problem is.

Allan

cpshart · March 2019

Hi allan

apologies for what is probably a silly question as I am relatively new to datatables, is this the file you are referring to public_html/Editor-PHP-1.8.1/controllers/error_log

and if so, do I need to turn error checking on, as it is not being updated when I refresh the webpage.

I am out for next couple of hours will check after that, thanks

Thanks Colin

cpshart · March 2019

Hi, fixed the problem

PHP Snippet SQL Query was enclosed by double quotes, (lines 37 to 57), and line 44 also has double quotes around "Buy"

```
$rows = $wpdb->get_results("
SELECT
etc...

SUM(IF(t.transaction_type="Buy",ANY_VALUE(t.quantity),ANY_VALUE(t.quantity) * -1)) AS quantity,
etc ending
GROUP BY
d.pdate, t.portfolio_id, s.symbol, s.name
");

```

the solution was to change "Buy" to 'Buy' single quotes.

Thanks for help.

Colin

colin · March 2019

Hi @cpshart ,

Excellent - glad that blog proved useful!

Cheers,

Colin

Example of Wordpress datatables.net SQL Query using PHP Server side and Client side

Example of Wordpress datatables.net SQL Query using PHP Server side and Client side

Answers

Howdy, Stranger!

Categories

DataTables

Example of Wordpress datatables.net SQL Query using PHP Server side and Client side

Example of Wordpress datatables.net SQL Query using PHP Server side and Client side

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables