Skip to main content
Editing Smileys is causing errors Started by Ruth · · Read 10162 times 0 Members and 1 Guest are viewing this topic. previous topic - next topic

Editing Smileys is causing errors

Hi!

Editing Smileys (by clicking on them) or changing their order (if drag&drop is used) is causing database errors and does not work. This is on test forum, with new installed ElkArte 1.1.9. No smiley addon there yet.

Error log says: "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 'row FROM elkarte_smileys"

The errors are in /sources/subs/Smileys.subs.php on line 200.

Re: Editing Smileys is causing errors

Reply #1

1. Drag and drop works for me.
2. I've tested editing the smileys and had no issues, so I think this may be a server issue for you.

In closing: Unable to reproduce issue on a fresh clean install of 1.1.9.

Re: Editing Smileys is causing errors

Reply #2

I can edit smileys without any error, if i use the checkboxes and the scroll down menu.
I also can sort them without error, if i click on them, (just drag & drop is not working).

I think, it had been always like this on my forums....but not sure.
For the icons drag & drop is working fine.

Quoteso I think this may be a server issue for you

Yes....but I don't know, how to solve this....a php issue?

Re: Editing Smileys is causing errors

Reply #3

I suspect that is due to a new version of MySQL which added "row" as a reserved name.  Older versions of mysql will not have an issue, nor will any current version of mariadb.

The function getSmiley in Smileys.subs.php (around line 190) looks like this
Code: (find) [Select]
function getSmiley($id)
{
$db = database();

$request = $db->query('', '
SELECT id_smiley AS id, code, filename, description, hidden AS location, 0 AS is_new, smiley_row AS row
FROM {db_prefix}smileys
WHERE id_smiley = {int:current_smiley}',
array(
'current_smiley' => $id,
)
);
if ($db->num_rows($request) != 1)
throw new Elk_Exception('smiley_not_found');
$current_smiley = $db->fetch_assoc($request);
$db->free_result($request);

return $current_smiley;
}
We are going to drop that "AS row" part then add $current_smiley['row'] = $current_smiley['smiley_row']; just to keep it compatible with other existing functions.  When done it will look like this and should work with the latest MySQL
Code: (replace) [Select]
function getSmiley($id)
{
$db = database();

$request = $db->query('', '
SELECT id_smiley AS id, code, filename, description, hidden AS location, 0 AS is_new, smiley_row
FROM {db_prefix}smileys
WHERE id_smiley = {int:current_smiley}',
array(
'current_smiley' => $id,
)
);
if ($db->num_rows($request) != 1)
throw new Elk_Exception('smiley_not_found');
$current_smiley = $db->fetch_assoc($request);
$current_smiley['row'] = $current_smiley['smiley_row'];
$db->free_result($request);

return $current_smiley;
}

Re: Editing Smileys is causing errors

Reply #4

It works great now, Spuds!  :smiley: No more errors in the log.

Thank you!

Re: Editing Smileys is causing errors

Reply #5

It also works perfect together with the new version of the smiley addon.
I just tried sorting by drag & drop on elk11. Great. :smiley: