DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

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

cpshartcpshart Posts: 246Questions: 49Answers: 5
in Free community support

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

This question has accepted answers - jump to:

Answers

  • colincolin Posts: 15,146Questions: 1Answers: 2,586
    Answer ✓

    Hi @cpshart ,

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

    Cheers,

    Colin

  • cpshartcpshart Posts: 246Questions: 49Answers: 5

    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>";
?>

<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

  • allanallan Posts: 61,734Questions: 1Answers: 10,110 Site admin
    Answer ✓

    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

  • cpshartcpshart Posts: 246Questions: 49Answers: 5

    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

  • cpshartcpshart Posts: 246Questions: 49Answers: 5

    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

  • colincolin Posts: 15,146Questions: 1Answers: 2,586

    Hi @cpshart ,

    Excellent - glad that blog proved useful! :)

    Cheers,

    Colin

This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.