how to display mysql data on Modal after select of dropdown

cpshart · March 2019

Hi,

I am using the datatables and editor in wordpress using Mysql for my data. I need to display information on the modal triggered by selection of the stock from the dropdown list, such as stock price.

Extract of my code below


<html>
<head>
<title>Stock Transactions</title>
<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.4/css/buttons.dataTables.min.css" />    
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/1.2.7/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 style files -->    
<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" />   
<!-- Select2 style files -->        
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.2/css/select2.min.css" />   

</head>

<table id="transactions" class="row-border responsive nowrap" style="width:100%">

<thead id="dt-table-thead">
<tr>
<th>Code</th>
<th>Quantity</th>
<th>Price</th>
</tr>
</thead>
    
<tbody>     
<?php
global $wpdb;    
global $current_user;
get_currentuserinfo();
$user_id = $current_user->ID;

$rows = $wpdb->get_results("
SELECT 
t.user_id AS user_id,
t.transaction_type AS transaction_type,
s.symbol AS symbol,
s.name AS name,
... 
");
        
foreach ($rows as $row ){
    echo "<tr>";
    echo "<td>$row->symbol</td>";
    echo "<td>$row->quantity</td>";
    echo "<td>$row->price</td>";
    echo "</tr>";
}

Javascript extract

...
editor = new $.fn.dataTable.Editor( {
        ajax: {
            url: '../../Editor-PHP-1.8.1/controllers/transactions.php',
            type: 'POST',
            data: function ( d ) {
                d.userid = $('#passuserid').val();
            },
           formOptions : {
                   main:{
                            submit : 'changed'
                        }
                },
            },
        table: "#transactions",
        fields: [ {
                label: "type:",
                name: "dm_transactions.transaction_type",
                type:  "select",
                placeholder: "Select a type"
            }, {
                label: "stock:",
                name: "dm_transactions.stock_id",
                type: "select",
                placeholder: "Select a stock"
            }, {

PHP file extract


Editor::inst( $db, 'dm_transactions' )
    ->fields( 
        Field::inst( 'dm_transactions.user_id' )
        // default the value of the userid to $userid        
            ->setValue( $userid ),            
        Field::inst( 'dm_transactions.transaction_type' )
            ->options( Options::inst()
                ->table( 'dm_transactions' )
                ->value( 'transaction_type' )
                ->label( 'transaction_type' )
            )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Please select type ..' )
                ) ),
        Field::inst( 'dm_transactions.transaction_date' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
        
        Field::inst( 'dm_transactions.stock_id' )
            ->options( Options::inst()
                ->table( 'dm_stocks' )
                ->value( 'id' )
                ->label( array('symbol', 'name') )
            )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Please select stock ..' )
            ) ),    
        Field::inst( 'dm_stocks.symbol' ),
        Field::inst( 'dm_stocks.name' ),

On selection of the stock in the modal, I should like to display a message on the modal, say below the stock dropdown of the form AZN Astrazeneca Closing Price 6200 on <Yesterdays Weekday Date>, given by
dm_stocks.symbol, dm_stocks.name, dm_stocks.price, date variable.

Any ideas on how to achieve this much appreciated.

Thanks Colin

cpshart · March 2019

I have attached a sample of the Modal

So having selected the stock, I want the price showing below the stock dropdown on the modal given by the mySQL table dm_stocks.price WHERE dm_stocks.symbol = "AZN.L"

Thanks

Colin

colin · March 2019

Hi @cpshart ,

The message() can be used for that - see this example here, where it's being used to display the length of the name field in the edit/create form. You could put test and insert the message in open.

Cheers,

Colin

cpshart · March 2019

Hi colin

Thanks for your advice and quick response, I should have enough information now to work on my solution now.

Best regards

Colin

cpshart · March 2019

Hi colin

I have added the following code which writes the unique id of the stock selected below the stock_id field (shown as code + name in select list) in the modal as shown in the attachment.

    editor.dependent('dm_transactions.stock_id', function ( val, data, callback ){
    return { messages: { 'dm_transactions.stock_id': editor.field('dm_transactions.stock_id').val() } };
    },{
    event: 'keyup change'
    })

On selection of the stock from dropdown list, instead of displaying the dm_transactions.stock_id on the modal,
I need to display the price of the stock stored on the table.column dm_stocks.price where dm_transactions.stock_id = dm_stocks.id.

Extract of PHP file showing the joins between dm_stocks and dm_transactions tables in the PHP file

->leftJoin( 'dm_stocks', 'dm_stocks.id', '=', 'dm_transactions.stock_id' )
->where( function ( $q ) use ( $userid) {
$q->where( 'dm_transactions.user_id', $userid);
} )
->process($_POST)
->json();

Anymore help would be appreciated.

Many Thanks

Colin

allan · March 2019

Hi Colin,

I think you'll need to use dependent() to make an Ajax call to the server to get the information you want to display. It sounds like you could look it up from the local table, but I think an Ajax call submitting the selected value would give a lot more flexibility.

Allan

cpshart · March 2019

Hi colin

Thanks for the help, I will start working on a solution using the above method.

Best Regards

Colin

cpshart · March 2019

Hi allan

I am having success with the dependent() API for various examples which I have replicated on my system but I don't know the syntax of making an Ajax call to retrieve the information from the server.

Effectively I have 2 Mysql tables dm_stocks and dm_transactions
where dm_stocks.id = dm_transactions.stock_id, I need to display additional information on my modal such as

dm.stocks.symbol and dm_stocks.price, so I need to get these values from the server.

I assume you are referring to the examples shown here

https://editor.datatables.net/reference/api/dependent()

but I cannot locate the contents of the file /ajax/update-position to fully understand the example and thus establish the required syntax of my request.

If you can assist further that would be great !!

Many Thanks

Colin

cpshart · March 2019

Hi allan

I have found some examples on the forum providing example code, so hopefully I can work through a solution ..

Thanks

Colin

how to display mysql data on Modal after select of dropdown

how to display mysql data on Modal after select of dropdown

Answers

Howdy, Stranger!

Categories

DataTables

how to display mysql data on Modal after select of dropdown

how to display mysql data on Modal after select of dropdown

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables