how to display a share price on editor modal using dependent() from mysql database in wordpress
how to display a share price on editor modal using dependent() from mysql database in wordpress
cpshart
Posts: 246Questions: 49Answers: 5
Hi
I have spent days trying to get this process working so I would be very grateful for some help to enable me to complete the process
On the selection of a stock, dm_transactions.stock_id, I want to return the price given by dm_stocks.price from my PHP server script and write the value into the field dm_transactions.price on the editor modal.
and/or as an alternative, I should also like to know how to display the price in a non editable field with a description e.g. Closing Price: dm_stocks.price.
This is the most important part of the process above.
I also need the system to calculate the value of dm_transactions.quantity * dm_transactions.price (possibly overwritten by user i.e. not dm-stocks.price, default value) and default the value into dm_transactions.total field.
Any help on this would be much appreciated.
editor = new $.fn.dataTable.Editor( {
ajax: {
url: '../../Editor-PHP-1.8.1/controllers/stock_transactions.php',
type: 'POST',
data: function ( d ) {
d.userid = $('#passuserid').val();
},
formOptions : {
main:{
submit : 'changed'
}
},
},
table: "#stock_transactions",
fields: [ {
... some fields omitted from screen shot
label: "stock:",
name: "dm_transactions.stock_id",
type: "select",
placeholder: "Select a stock"
}, {
label: "quantity:",
name: "dm_transactions.quantity"
}, {
label: "price:",
name: "dm_transactions.price"
}, {
label: "total:",
name: "dm_transactions.total"
}
]
});
// display the stock symbol, name and price
editor.dependent(
'dm_transactions.stock_id',
function ( val, data, callback ) {
$.ajax( {
url: '../../Editor-PHP-1.8.1/controllers/ajax_transactions_stockinfo.php',
dataType: 'json',
data: function ( d ) {
d.stock_id = $('dm_transactions.stock_id').val();
},
success: function ( json ) {
callback( json );
}
} );
editor.field( 'dm_transactions.price').set( editor.field('dm_stocks.price').val() );
} );
......
ajax: {
url: '../../Editor-PHP-1.8.1/controllers/stock_transactions.php',
type: 'POST',
data: function ( d ) {
d.userid = $('#passuserid').val();
}
},
columns: [
{ data: "dm_stocks.symbol" },
{ data: null, // signedquantity
render: function(data, type, row) {
if (row.dm_transactions.transaction_type === 'Buy') {
return parseFloat(row.dm_transactions.quantity).toFixed(2); }
else {
return 0 - parseFloat(row.dm_transactions.quantity).toFixed(2); }
}
},
{ data: "dm_transactions.price" },
{ data: null, // total
render: function(data, type, row) {
if (row.dm_transactions.transaction_type === 'Buy') {
return parseFloat(row.dm_transactions.total).toFixed(2); }
else {
return 0 - parseFloat(row.dm_transactions.total).toFixed(2); }
},
}
],
....
``` php
<?php
/* Loads the WordPress environment and template */
require( '../../wp-blog-header.php' );
global $current_user;
wp_get_current_user();
// DataTables PHP library
include( "../lib/DataTables.php" );
$stock_id='1'; // manually setting variable for testing
//$stock_id = $_GET['stock_id']; // manually setting variable for testing
$stock_arr = array();
try {
$pdo = new PDO(strtolower($sql_details['type']) . ":host=" . $sql_details['host'] . ";dbname=" . $sql_details['db'], $sql_details['user'], $sql_details['pass']);
// set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully" . "\n\n";
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
= t.stock_id) WHERE t.stock_id = $stock_id");
$result = $pdo->query("SELECT t.stock_id AS stock_id, s.symbol AS symbol, s.name AS name, s.price AS price FROM dm_transactions t INNER JOIN dm_stocks s ON (s.id = t.stock_id) WHERE t.stock_id = $stock_id");
foreach ($result as $row) {
array_push( $stock_arr, array('label'=>$row['symbol'], 'value'=>$row['price']) );
}
$temp = array('symbol'=>$stock_arr);
$json = array('options'=>$temp);
echo json_encode($json);
the above script connects to the database and generates JSON data
Thanks in advance
Colin
This question has an accepted answers - jump to answer
Answers
Can you show me the JSON returned from the
stock_transactions.phpscript please?What does it currently do on the JSON return? I'm guessing nothing, but are there any error messages?
Allan
Hi allan
If I remove the WHERE condition on the SELECT statement, it returns the data on the table as follows
From
To
debug displays
stock_id:symbol:name:price
16:BP..L:BP:557.5
18 : BT.A.L : BT Group : 228.3
48 : HSBA.L : HSBC Holdings : 624.8
etc. as
Above is an extract of the JSON returned
Now hardcode stock_id to 18 for BP
On selecting any stock debug returned from PHP script is
There are no errors on the console, but the status code of the PHP file is 304
So how as a 1st step how do I pass the dm_transactions.stock_id using dependent() to the PHP server script then I disable hardcoding and enable the command on the PHP script
From
To
Many Thanks
Colin
Hi allan
If I enable the following lines
and the WHERE clause on the query WHERE t.stock_id = $stock_id
I get an error on the Network
<br />
<b>Notice</b>: Undefined index: stock_id in <b>/home/ukincome/public_html/Editor-PHP-1.8.1/controllers/ajax_transactions_stockinfo.php</b> on line <b>17</b><br />
the value of stock_id is ::
Connected successfully
so my issue is how to successfully pass the stock_id value to the server using the dependent() code and PHP code shown below
and PHP code extract, avoiding the network error above
Hope that helps to clarify issue.
Many Thanks
Colin
Hi allan
This change to my JS now passes stock_id to my PHP script data: { "stock_id": val },
see attached screen
so my next issue is how to display the price on the modal in either
1/ the field given by dm_transactions.price
and/or
2/ a separate non editable field including some text as discussed e.g."closing price:" <the value of price returned by JSON response>
Many Thanks
Colin
Hi allan
I am now writing a single row to the JSON response, as opposed to multiple rows shown above (my SQL query was incorrect), and I am writing a non editable box with data.
My only issue is how to write a JSON value from the server to an editable field in my case dm_transactions.price and/or to a non editable box on the modal.
Is there a working example of this process and/or can you point me in the right direction. I am in the meantime searching the forum for answers.
Many Thanks
Colin
Sounds like you've made good progress - nice one.
Similar to the
optionsproperty in the returned JSON you are currently using there is avaluesoption for the JSON returned todependent(). That can be used to set the value of the fields - e.g.:Likewise you can specify any other fields in the
valuesobject that you want.Allan
Hi allan
Many thanks for the response, I will look at using the values object.
Much appreciated
Colin
Hi
Sorry I must be nearly there ..
Just to clarify, I sent a single stock_id value to the server in this case stock_id=3 for ADM.L, Admiral Group, Price is 2037.
So I need to get the price from the server JSON and store its value in dm_transactions.price and/or a variable for the message box.
The following code updates the message dm-transactions.price editor field and message box respectively albeit with the wrong value stock_id, not stock price.
This is the JSON on the server, I can change the structure to whatever is required.
3 : ADM.L : Admiral Group : 2037
{"options":[{"price":"2037"}]}
So I assume I need to define the JSON structure on the client (to match what is returned by server) and if so where and how and also how do I store the JSON price in a variable for use in the message box and the editor field dm_transactions.price
I just need the context of where any code snippets needs to be placed in my file.
Many Thanks
Colin