In my 'artists' table I have the fields USP and ABOUT as well as the icelandic versions USP_IS and ABOUT_IS.
When I debug the SQL generated by:
$this->model->update($data['id'], $data, 'artists');
//This is the line #126 in the Umsjon.php file that the error is pointing to.
I get this SQL:
As you can tell the contents of the usp_is field is the same as the English version (should be the Icelandic version), but right after the string I get "_is" added before the next field.
This would explain why the SQL statement is not running as it should.
Finally I get this error message:
Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\xampp\htdocs\artice\engine\Model.php:73 Stack trace: #0 C:\xampp\htdocs\artice\engine\Model.php(73): PDOStatement->execute() #1 C:\xampp\htdocs\artice\engine\Model.php(427): Model->prepare_and_execute('UPDATE `artists...', Array) #2 C:\xampp\htdocs\artice\modules\members\umsjon\controllers\Umsjon.php(126): Model->update(236, Array, 'artists') #3 C:\xampp\htdocs\artice\engine\Core.php(207): Umsjon->stadfesta('') #4 C:\xampp\htdocs\artice\engine\Core.php(13): Core->serve_controller() #5 C:\xampp\htdocs\artice\public\index.php(5): Core->__construct() #6 {main} thrown in C:\xampp\htdocs\artice\engine\Model.php on line 73
Could this be an error in the Trongate engine or the PDO?
Weird error in the 'Update' method
3 years ago
3 years ago
#1
3 years ago
#2
It's not an error in the Trongate engine or PDO, just a malformed SQL query.
This is your query formatted to be easily read:
You can't just tac '_is' to the end of your string like that, that's why you're getting 'number of bound variables does not match number of token'
The query should work if you enclose _is into the string, like this
and the same for `about_is` = 'The boats ... as a whole._is',
Cheers,
Si
This is your query formatted to be easily read:
You can't just tac '_is' to the end of your string like that, that's why you're getting 'number of bound variables does not match number of token'
The query should work if you enclose _is into the string, like this
and the same for `about_is` = 'The boats ... as a whole._is',
Cheers,
Si
3 years ago
#3
No - that is not what is happening. I am NOT creating the SQL statement. It is being generated by the Trongate engine or the PDO. Using this statement:
$this->model->update($data['id'], $data, 'artists');
This after collecting the $data through a form like this:
The "_is" is from the column name.
json echoing the $data shows that everything is ok with the content before submitting it to the engine.
I only saw the generated SQL by setting Debug on the Model.php.
$this->model->update($data['id'], $data, 'artists');
This after collecting the $data through a form like this:
The "_is" is from the column name.
json echoing the $data shows that everything is ok with the content before submitting it to the engine.
I only saw the generated SQL by setting Debug on the Model.php.
3 years ago
#4
hmm can you share what is in the array by json ($data, true); on line 126 of your controller 'Umsjon.php'?
or if the controller is not too big, share that too or atleast the methods leading up to the database update - need more info to diagnose what is going on here.
or if the controller is not too big, share that too or atleast the methods leading up to the database update - need more info to diagnose what is going on here.
3 years ago
#5
Here is $data
and the full code for the Umsjon.php
and the full code for the Umsjon.php
3 years ago
#6
Change the $usp and $about to something else and see if it works. If not, remove the _is from $usp_is and $about_is to something else. Dont change the input field names.
3 years ago
#7
Hi Thrandur,
I could not reproduce the PDO error with the $data array above. The Icelandic Unicode stored in 'nafn', 'usp_is', and 'about_is' all wrote a read fine for me from my laptop with Xampp installed on Windows 11 using Apache/2.4.54 (Win64) OpenSSL/1.1.1p PHP/8.2.0, 10.4.27-MariaDB.
Also, ensure your database is of charset utf8 and collation utf8_general_ci and your tables have the character set as follows:
There is also a setting you can add to the 'Model.php' in the try/catch to force the connection to a utf8 charset, but remeber this will be over written on framework updates, unless someone creates a pull request and DC adds it.
You also have a few strange things going on in your controller which I won't go into here but I do suggest you create a new controller with the desktop app, add all the fields in the 'artist' table and try a few writes and if all is good copy over some of the code into your sub-module 'umsjon' to start a fresh.
You can also improve your controller by doing the login check in the constructor which saves repeating those 2 lines in every method and getting the 'member_obj' ready for use and cutting out repeated code. Also, adding the destruct at the end of the class will free up memory.
Here is the sub-module class 'umsjson' created with the VS Code extension and a bit of what I was talking about above:
You can also decode the Unicode so it is ready to display on a HTML page when you get it from the database
which converts '\u00c1t\u00f6k vi\u00f0 hafi\u00f0' to 'Átök við hafið', etc..
I hope some of this helps ??
I could not reproduce the PDO error with the $data array above. The Icelandic Unicode stored in 'nafn', 'usp_is', and 'about_is' all wrote a read fine for me from my laptop with Xampp installed on Windows 11 using Apache/2.4.54 (Win64) OpenSSL/1.1.1p PHP/8.2.0, 10.4.27-MariaDB.
Also, ensure your database is of charset utf8 and collation utf8_general_ci and your tables have the character set as follows:
There is also a setting you can add to the 'Model.php' in the try/catch to force the connection to a utf8 charset, but remeber this will be over written on framework updates, unless someone creates a pull request and DC adds it.
You also have a few strange things going on in your controller which I won't go into here but I do suggest you create a new controller with the desktop app, add all the fields in the 'artist' table and try a few writes and if all is good copy over some of the code into your sub-module 'umsjon' to start a fresh.
You can also improve your controller by doing the login check in the constructor which saves repeating those 2 lines in every method and getting the 'member_obj' ready for use and cutting out repeated code. Also, adding the destruct at the end of the class will free up memory.
Here is the sub-module class 'umsjson' created with the VS Code extension and a bit of what I was talking about above:
You can also decode the Unicode so it is ready to display on a HTML page when you get it from the database
which converts '\u00c1t\u00f6k vi\u00f0 hafi\u00f0' to 'Átök við hafið', etc..
I hope some of this helps ??
3 years ago
#8
Thanks for all these hints DaFa!
Now I have changed the field names from usp_is to serstada and about_is to um_mig. After the change the SQL statement is without these mixup errors.
Also I am sure the DB language settings work just fine :)
I also figured out the error pointing to number of bound variables from PDO: That problem was in my code!
So all problems are fixed on my end.
I still suspect there is an error in the Trongate engine. I will create a simple test module to try out my theory...
Now I have changed the field names from usp_is to serstada and about_is to um_mig. After the change the SQL statement is without these mixup errors.
Also I am sure the DB language settings work just fine :)
I also figured out the error pointing to number of bound variables from PDO: That problem was in my code!
So all problems are fixed on my end.
I still suspect there is an error in the Trongate engine. I will create a simple test module to try out my theory...