Datatables PHP library not updating MySQL Database.
Datatables PHP library not updating MySQL Database.
borconi
Posts: 56Questions: 19Answers: 1
Hi.
I do have a small problem where I cannot think of any reasonable why....
Basically Editor does not update the MySQL table, there is no error thrown either.
Here is the JS:
editor = new $.fn.dataTable.Editor( {
ajax: "general_data.php",
table: "#example",
fields: [ {
label: "Company Name",
name: "clients.name",
type: "readonly"
},
{
label: "Company Abbreviation",
name: "clients.abreviation",
type: "readonly"
},
{
label: "Company Homapge",
name: "clients.homepage"
},
{
label: "User Name",
name: "cms.user",
type: "readonly"
},
{
label: "Old Password",
name: "cms.pass",
type:"password",
},
{label: "New Password",
name: "cms.newpass",
type:"password"
},
{label: "Repeat New Password",
name: "cms.newpass2",
type:"password"
},
{
label: "Enable Prints Sale",
name: "clients.enablePrints",
type:"select",
options: [
{ label: "Enabled", value: 1 },
{ label: "Disabled", value: 2 }
]
},
{
label: "Price List footer",
name: "clients.pricelistFooter"
},
{label: "Printers Email address",
name: "clients.printerEmail"
},
{label: "Prints Postage Price",
name: "clients.postage"
},
{label: "PayPal Email address",
name: "clients.paypalemail"
},
{
label: "Default Currency",
name: "clients.defaultCurrency",
type:"select",
options: ["GBP","USD","EUR","CAD","AUD"]
},
{
label: "Enable Card Payment",
name: "clients.enableCard",
type:"select",
options: [
{ label: "Enabled", value: 1 },
{ label: "Disabled", value: 2 }
]
},
{
label: "CardCutters Merchant ID",
name: "clients.CardCuttersMerchant",
type:"readonly"
},
{
label: "CardCutters Secret",
name: "clients.CardCuttersSecret",
type:"password"
},
{
label: "SMTP Server",
name: "clients.smtpserver",
},
{
label: "SMTP User",
name: "clients.smtpuser",
},
{
label: "SMTP pass",
name: "clients.smtppass",
type:"password"
},
{
label: "SMTP Port",
name: "clients.smtpport"
},
{
label: "SMTP Encryption",
name: "clients.smtpEncryption",
type:"select",
options:["TLS","SSL","NONE"]
},
{
label: "Mail From (Name): ",
name: "clients.mailFromName"
},
{
label: "Mail From (Email): ",
name: "clients.mailFrom"
},
{
label: "Email Subject: ",
name: "clients.mailSubject"
},
{
label: "Email Subject: ",
name: "clients.mailText",
type:"textarea"
},
]
} );
Here is the PHP file:
// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Upload,
DataTables\Editor\Validate;
// Build our Editor instance and process the data coming from _POST
$editor=Editor::inst( $db, 'cms')
->fields(
Field::inst( 'clients.name' )->set(false),
Field::inst( 'clients.abreviation' )->set(false),
Field::inst( 'clients.enablePrints' ),
Field::inst( 'clients.postage' ),
Field::inst( 'clients.enableCard' )->set(false),
Field::inst( 'clients.CardCuttersMerchant' )->set(false),
Field::inst( 'clients.CardCuttersSecret' )->set(false),
Field::inst( 'clients.pricelistFooter' ),
Field::inst( 'clients.homepage' ),
Field::inst( 'clients.smtpserver' )->validator( 'Validate::notEmpty' ),
Field::inst( 'clients.smtpuser' )->validator( 'Validate::notEmpty' ),
Field::inst( 'clients.smtppass' )->validator( 'Validate::notEmpty' ),
Field::inst( 'clients.smtpport' )->validator( 'Validate::notEmpty' )->validator( 'Validate::numeric' ),
Field::inst( 'clients.mailSubject' )->validator( 'Validate::notEmpty' ),
Field::inst( 'clients.mailText' )->validator( 'Validate::notEmpty' ),
Field::inst( 'clients.smtpEncryption' )->validator( 'Validate::notEmpty' ),
Field::inst( 'clients.mailFrom' )->validator( 'Validate::notEmpty' )->validator( 'Validate::email',array(
'empty' => false,
'optional' => false
)),
Field::inst( 'clients.mailFromName' )->validator( 'Validate::notEmpty' ),
Field::inst( 'clients.paypalemail' )->validator( 'Validate::email',array(
'empty' => false,
'optional' => false
)),
Field::inst( 'cms.pass' )->get(false)
->validator( function ( $val, $data, $field, $host ) use ($db2) {
if ($data['cms']['newpass']!=$data['cms']['newpass2']) return "New password does not match";
if ($data['cms']['newpass']=='') return true;
$stmt=$db2->prepare("select * from `cms` where `user`=?");
$stmt->execute(array($data['cms']['user']));
$result=$stmt->fetch();
if (!password_verify($data['cms']['pass'], $result['pass']))
return "Incorect old password";
return true;
})
->setFormatter( function ( $val, $data ) {
return password_hash($data['cms']['newpass'], PASSWORD_DEFAULT);
}),
Field::inst( 'cms.user' )->set(false),
Field::inst( 'clients.defaultCurrency' ),
Field::inst( 'clients.printerEmail' )->validator( 'Validate::email',array(
'empty' => true,
'optional' => true
) ));
$editor->leftJoin("clients", "cms.owner", "=", "clients.id");
$editor->where('cms.user',$_SESSION['username']);
$editor ->on( 'preEdit', function ( $e, $id, $values ) {
if ( $values['cms']['newpass'] === '' )
$e->field( 'cms.pass' )->set( false );
});
$editor->process( $_POST );
$editor->json();
Here are the submitted headers:
.....
X-Requested-With: XMLHttpRequest
action: edit
data[row_1][clients][name]: NAME
data[row_1][clients][abreviation]: MBN
data[row_1][clients][homepage]: //www.......
data[row_1][clients][enablePrints]: 1
data[row_1][clients][pricelistFooter]: ALL PRICES INCLUDE VAT # 235 4087 14.
data[row_1][clients][printerEmail]: order@example.com
data[row_1][clients][postage]: 3.95
data[row_1][clients][paypalemail]: martyn@example.com
data[row_1][clients][defaultCurrency]: GBP
data[row_1][clients][enableCard]: 1
data[row_1][clients][CardCuttersMerchant]: 11111111
data[row_1][clients][CardCuttersSecret]: TheSecret
data[row_1][clients][smtpserver]: email-smtp.eu-west-1.amazonaws.com
data[row_1][clients][smtpuser]: SMTPUSER
data[row_1][clients][smtppass]: SMTPPASS
data[row_1][clients][smtpport]: 587
data[row_1][clients][smtpEncryption]: TLS
data[row_1][clients][mailFromName]: XYZ
data[row_1][clients][mailFrom]: do_not_reply@example.com
data[row_1][clients][mailSubject]: Your Personal PAGE
data[row_1][clients][mailText]: Hi #name.<br><br> Thank you for your order and payment.<br><br> blablabla<br><br> <a href='http://www.example.com/?id=#product_id'>http://www.example.com/?id=#product_id</a><br><br> #additional Kind regards<br> The Team "
data[row_1][cms][user]: martyn
data[row_1][cms][pass]:
data[row_1][cms][newpass]:
data[row_1][cms][newpass2]:
The field printerEmail value was changed compared to the original, and this is being submitted correctly to the server, however there is no UPDATE query triggered on the server side at all. I did enable the debugging in the Query.php so all the queries are saved to the temporary file but there is no trace of the UPDATE query.
I have tried to remove the pre-edit event in the PHP library but still no joy.
I saw similar question regarding this, but there the issue was MJoin which isn't the case here.
Also all other tables are updated correctly (all with Editor).
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
Hi,
Does it update the content in the
cmstable, but not theclientsone? It looks likeclientsis your main editing point here, so if you aren't planning on having thecmstable editable here, it would be worth makingclientsthe target table - that would make things a bit easier.If however, you want them both to be editable, then you need to make sure that you read the primary key of the
clientstable (clients.idin this case) and include it as ahiddenfield in the data Editor submits to the server. Otherwise the server-side doesn't know what row to update in the second table.Allan
So obvious when you say it like that....
Thanks Allan!