Custom block error on saving
Permalink
Hi,
I have created a custom block based on the basic test. In the add/edit form I have a text box which is expecting a numeric value. If I put in a number and click save, all is well. However, if I leave this input empty and submit it I get:
An unexpected error occurred. mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE bID=108' at line 1] in EXECUTE("UPDATE btJdmBackstretch SET fID=11,fade= WHERE bID=108")
I have no doubt it's because the database is expecting a value for the "fade" variable.
What I wondered is, how do I set a default value for the field and accept a blank input without the error being generated?
Cheers,
John
I have created a custom block based on the basic test. In the add/edit form I have a text box which is expecting a numeric value. If I put in a number and click save, all is well. However, if I leave this input empty and submit it I get:
An unexpected error occurred. mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE bID=108' at line 1] in EXECUTE("UPDATE btJdmBackstretch SET fID=11,fade= WHERE bID=108")
I have no doubt it's because the database is expecting a value for the "fade" variable.
What I wondered is, how do I set a default value for the field and accept a blank input without the error being generated?
Cheers,
John
I think I've got that bit - here is my db.xml:
<?xml version="1.0"?>
<schema version="0.3">
<table name="btJdmAddontest">
<field name="bID" type="I">
<key />
<unsigned />
</field>
<field name="fID" type="I">
<unsigned />
</field>
<field name="fade" type="I" >
<unsigned />
<default value="0" />
</field>
</table>
</schema>
<?xml version="1.0"?>
<schema version="0.3">
<table name="btJdmAddontest">
<field name="bID" type="I">
<key />
<unsigned />
</field>
<field name="fID" type="I">
<unsigned />
</field>
<field name="fade" type="I" >
<unsigned />
<default value="0" />
</field>
</table>
</schema>
I think the syntax error could be the lack of value for "fade" when the form gets submitted. i.e. SET fID=11,fade= WHERE bID=108
Is there any way to have that value populated with something even if the user doesn't enter a value in the form? Or for the php that receives the form submission to append a value before submitting it to the database?
Is there any way to have that value populated with something even if the user doesn't enter a value in the form? Or for the php that receives the form submission to append a value before submitting it to the database?
Yeah, I think it's because the database field is defined as an Integer (not a string/varchar), so the query is expecting to get a value of some kind. To work around this, override the "save" function in your block controller and set the value to zero if it's empty:
public function save($args) { $args['fade'] = intval($args['fade']); //convert empty values to 0 return parent::save($args); }
That is exactly what I needed - thanks!
Here is the xml
Rony