I have written script which takes data from remote location, parses it, creates SQL INSERT INTO query and then well executes the query. The query generated by PHP that I wrote is this: http://pastebin.com/YkRUv7cj
I send it through simple $sqli->query(). But the table doesn’t get filled. I thought to myself, let’s dump $sqli->error. Which says, pretty straightforward: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘INSERT INTO champions (id, name, title, icon, image) VALUES (24, “Jax”, "Grandma’ at line 1
I decided to use phpMyAdmin to have better idea of what’s going on. So I echo’ed entire query, and I dumped it. What I had was TRUNCATE champions; THE_QUERY and then query above after it. But phpMyAdmin accepted the SQL and told me “XYZ rows affected”. I refreshed the table, and all the results from query above were there. phpMyAdmin has been written on PHP and runs on exactly same server (localhost) and permissions as my custom script. Yet my custom script fails and phpMyAdmin succeedes.
What am I doing wrong? What is it that I’m missing?
P.S.: My database:
Server: Localhost via UNIX socket
Server type: MariaDB
Server version: 10.1.16-MariaDB - Source distribution
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)
“TRUNCATE champions; THE_QUERY and then query above after it” is not a query. But a set of queries, while mysqli::query is supposed to run only a single query.
I put first truncate query, then my query. Table is still empty and the error changed slightly.
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘“Jax”, “Grandmaster at Arms”, "http://ddragon.leagueoflegends.com/cdn/6.24.1/img’ at line 1
To be specific:
object(mysqli)#1 (19) {
["affected_rows"]=>
int(-1)
["client_info"]=>
string(79) "mysqlnd 5.0.12-dev - 20150407 - $Id: [hex] $"
["client_version"]=>
int(50012)
["connect_errno"]=>
int(0)
["connect_error"]=>
NULL
["errno"]=>
int(1064)
["error"]=>
string(228) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"Jax", "Grandmaster at Arms", "http://ddragon.leagueoflegends.com/cdn/6.24.1/img' at line 1"
["error_list"]=>
array(1) {
[0]=>
array(3) {
["errno"]=>
int(1064)
["sqlstate"]=>
string(5) "42000"
["error"]=>
string(228) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"Jax", "Grandmaster at Arms", "http://ddragon.leagueoflegends.com/cdn/6.24.1/img' at line 1"
}
}
["field_count"]=>
int(0)
["host_info"]=>
string(20) "127.0.0.1 via TCP/IP"
["info"]=>
NULL
["insert_id"]=>
int(0)
["server_info"]=>
string(21) "5.5.5-10.1.16-MariaDB"
["server_version"]=>
int(50505)
["stat"]=>
string(133) "Uptime: 2262 Threads: 1 Questions: 940 Slow queries: 0 Opens: 38 Flush tables: 1 Open tables: 24 Queries per second avg: 0.415"
["sqlstate"]=>
string(5) "00000"
["protocol_version"]=>
int(10)
["thread_id"]=>
int(91)
["warning_count"]=>
int(0)
}
All you need to know that in mysql error messages the problem part is exactly before the cited part. So I was able to tell that your fist problem is before INSERT (another query) and the second one is before “jax” which is easy to spot
Scroll down that to example 3 which shows how to use a prepared statement which is used prepared once then executed many times. It’s also safer as well as you can’t trust any 3rd party source of data, no matter how well that you know them