MySQL PDO server script for a MySQL view

cpshart

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

Hi

I am trying to construct a server script which complies with PDO security, the MySQL query has a GROUP BY clause so I have created a MySQL View shown below the screenshot (as not support by datatables ??)

My problem is how I add the GROUP BY MySQL View into the server script and preferably include PDO security, or if you can point me in the direction of an example. The MySQL query would be as follows

SELECT v_holdings_by_share WHERE dm_holdings.userid = $userid

CREATE VIEW v_holdings_by_share AS
SELECT 
        ANY_VALUE(s.symbol) AS symbol,
        ANY_VALUE(s.name) AS name, 
        SUM(ANY_VALUE(h.quantity)) AS quantity,
        ANY_VALUE(s.price) AS price,
        IF(ANY_VALUE(s.currency)='GBX',(SUM(ANY_VALUE(h.quantity)) * ANY_VALUE(s.price) / 100)
        ,(SUM(ANY_VALUE(h.quantity)) * ANY_VALUE(s.price))) AS value
        
        FROM
        dm_holdings h
        INNER JOIN dm_stocks s ON (s.id = h.stock_id) 
        INNER JOIN dm_portfolios p ON (p.id = h.portfolio_id)
        
        WHERE
        p.reporting_status = 'yes'
        GROUP BY
        s.symbol, s.name

Extract Of Client File

<table id="holdings-by-share" class="display" style="width:100%">
<thead>
<tr>
<th>Symbol</th>
<th>Name</th>
<th>Quantity</th>
<th>Price</th>
<th>Value</th>
</tr>
</thead>
    
<tbody>     
<?php
global $wpdb;    
global $current_user;
get_currentuserinfo();
$user_id = $current_user->ID;

<?php
>

        

?>


<input type='hidden' id='passuserid' value='<?php echo $current_user->ID; ?>'>
    
<script type="text/javascript">
(function($) {
/* 
* change when upgrading the Version of the datatables.net PHP files
*/  
var EDITOR_DIR = 'Editor-PHP-1.9.4';    
    
var newType = "column";
    
$(document).ready(function() {
    
$('<div class="loading">Loading</div>').appendTo('body');
    
var table = $('#holdings-by-share').DataTable( { 
        processing:     true,
        serverSide:     true,
        serverMethod:   'post',
        ordering:       true,
        dom: "lBfrtip",
        "scrollY":      true,
        "scrollX":      true,
        ajax: {
        url: "../../" + EDITOR_DIR + "/controllers/ukif-holdings_by_share_pdo.php",
        type: "post",
        data: function(d) {
            d.userid = $('#passuserid').val();
            }
        },
        columns: [
            { data: "dm_stocks.symbol" },
            { data: "dm_stocks.name" },
            { data: "dm_holdings.quantity" },
            { data: "dm_stocks.price" },
            { title: "Value", data: null,
                render: function ( data, type, row ) {
                    return (row.dm_stocks.currency == 'GBX')
                    ? (( Number(row.dm_stocks.price) * Number(row.dm_holdings.quantity) )  / 100 ).toFixed(2)
                    : ( Number(row.dm_stocks.price) * Number(row.dm_holdings.quantity) ).toFixed(2)
                }
            }
        ],
        columnDefs: [ // apply dt-nowrap to specific columns
          { className: "dt-nowrap", "targets": [ 1 ] }
    ],
        select: true,

Server File

<?php

// DataTables PHP library
include( "../lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;
    
$search = '%';
$userid = $_POST['userid'];

// Build our Editor instance and process the data coming from _POST

Editor::inst( $db, 'dm_holdings' )
    ->fields( 
        Field::inst( 'dm_stocks.symbol' ),
        Field::inst( 'dm_stocks.name' ),
        Field::inst( 'dm_stocks.price' ),
        Field::inst( 'dm_stocks.currency' ),
        Field::inst( 'dm_holdings.quantity' )
    )

->leftJoin( 'dm_portfolios', 'dm_portfolios.id', '=', 'dm_holdings.portfolio_id' )
->leftJoin( 'dm_stocks', 'dm_stocks.id', '=', 'dm_holdings.stock_id' )
->where( function ( $q ) use ( $userid) {
    $q
    ->where( 'dm_holdings.user_id', $userid )
    ->and_where('dm_portfolios.reporting_status', 'yes');
} )
->process($_POST)
->json();

<?php
>
```
?>


If you need access to my system the files are as follows, let me know if you need a PM of access details, with thanks.

Client File Snippet

https://www.ukincomefinder.com/wp-admin/post.php?post=26650&action=edit

Server File

public_html/Editor-PHP-1.9.4/controllers/ukif-holdings_by_share_pdo.php
```

Many Thanks

Colin

tangerine

There's a View example here:
https://editor.datatables.net/examples/advanced/sqlView
See the server script.

cpshart

Hi tangerine

Thanks for your very quick response and the example, I will integrate into my system and get back to you.

Best Regards

Colim

cpshart

Hi tangerine

I'm getting a server error status code 500, I am not sure how to express the rendered field Value in the server file, have a made a simple error.

extract of server file
public_html/Editor-PHP-1.9.4/controllers/ukif-holdings_by_share_pdo.php

$search = '%';
$userid = $_POST['userid'];

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dm_holdings' )
    ->readTable('v_holdings_by_share') // The VIEW to read data from
    ->field(
        Field::inst( 'dm_stocks.symbol' ),
        Field::inst( 'dm_stocks.name' ),
        Field::inst( 'dm_holdings.quantity' ),
        Field::inst( 'dm_stocks.price' ),
        Field::inst( 'dm_holdings.value' )
    )
    
->where( function ( $q ) use ( $userid) {
    $q
    ->where( 'dm_holdings.user_id', $userid );
} )

->process($_POST)
->json();

<?php
>
```
?>


extract of client file

    columns: [
        { data: "dm_stocks.symbol" },
        { data: "dm_stocks.name" },
        { data: "dm_holdings.quantity" },
        { data: "dm_stocks.price" },
        { title: "Value", data: null,
            render: function ( data, type, row ) {
                return (row.dm_stocks.currency == 'GBX')
                ? (( Number(row.dm_stocks.price) * Number(row.dm_holdings.quantity) )  / 100 ).toFixed(2)
                : ( Number(row.dm_stocks.price) * Number(row.dm_holdings.quantity) ).toFixed(2)
            }
        }
    ],

```

Best regards

Colin

cpshart

Hi tangerine

I have added debug to to the top of the server script
public_html/Editor-PHP-1.9.4/controllers/ukif-holdings_by_share_pdo.php

so I am working through the errors, making progress

/* error reporting uncomment next 2 lines, writes to error_log in PWD*/
error_reporting(E_ALL);
ini_set('display_errors', '1');

Thanks Colin

cpshart

Hi tangerine

I am getting an error on column dm_holdings.id which I cannot work out, as it happens if this line is commented out, or present in the server file, same error. Also if it present or not in the client file, I am getting the same error.

Any ideas much appreciated, thanks.

        Field::inst( 'dm_holdings.id' )->set( false ),

error

DataTables warning: table id=holdings-by-share - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dm_holdings.id' in 'field list'

server file

Editor::inst( $db, 'dm_holdings', 'id' )
    ->readTable('v_holdings_by_share') // The VIEW to read data from
    ->field(
        Field::inst( 'dm_holdings.id' )->set( false ),
        Field::inst( 'dm_holdings.user_id' )
            ->setValue( $userid ),            
        Field::inst( 'dm_stocks.symbol' ),
        Field::inst( 'dm_stocks.name' ),
        Field::inst( 'dm_stocks.price' ),
        Field::inst( 'dm_holdings.quantity' )
    )
    
->leftJoin( 'dm_stocks', 'dm_stocks.id', '=', 'dm_holdings.stock_id' )
->where( function ( $q ) use ( $userid) {
    $q
    ->where( 'dm_holdings.user_id', $userid );
} )

Best Regards

Colin

cpshart

.

cpshart

I have added debug to server file here, and I am getting same error for MySQL query shown in debug response running in phpMyAdmin, so working on solution ...

```
->debug(true)
->process($_POST)
->json();

<?php > ``` ?>

Thanks Colin

cpshart

All working now, I needed to match the aliases of the MySQL query exactly without table names to the server script and client Fields.

The exact query executed was shown in the debug section of Network Chrome Developer.

Thanks for your help.

Colin

cpshart

The only problem I have now is that the system only really works if the MySQL view restricts the selection to the current logged in Wordpress User ID, so matches

dm_holdings.user_id = CURRENT_LOGGED_IN_WORDPRESS_USER_ID

in the SQL query. I know how to pick up this value from a PHP script, but not directly in an MySQL View, so the View Query above needs to have a line of the form

        WHERE
        p.reporting_status = 'yes'
        AND
        h.user_id = CURRENT_LOGGED_IN_WORDPRESS_USER_ID

If I add the where clause on the user_id in the server script above the GROUP BY function does not pick up the required rows, it only works if part of the MySQL View, I proved it by hardcoding it to one of my user id's

i.e.

        WHERE
        p.reporting_status = 'yes'
        AND
        h.user_id = '1'

this produces the correct results for user_id = '1'

If anyone has an idea on how to achieve this it would be appreciated.

Many Thanks

Colin

cpshart

The problem is now resolved by performing a sub select in my SQL query and grouping again, so that the user filter could be done post running the MySQL View to provide the correct result.

Thanks for your help.

Regards Colin