Well, I am running a SQL command that is very large. I am getting error 1064 (Syntax error or access violation).
Is there any way to get the prepared statement, to see where the fault can be? I'm only seeing 50 characters and I honestly don't know what the bug could be.
This is how I create and prepare the statement:
function insertIntoMultiple($tablename, $columns, $data, $datetime_column_name = 'created_at') {
global $db;
$data = json_decode(base64_decode($data), true);
$exp = explode(", ", $columns);
$params = array_diff($exp, ["created_at"]);
$real_columns = join(",", $params);
// $join = join(',', json_decode($columns, true));
$query = "INSERT INTO $tablename ($real_columns, $datetime_column_name) ";
$i = 0;
$c = count($data);
foreach ($data as $param) {
$marks = getMarks($params);
// $query = storeQuery("INSERT INTO $tablename ($join, $datetime_column_name) VALUES ($marks, NOW())");
$query .= "VALUES ($marks, NOW())";
$query .= $i < $c - 1 ? "," : ";";
$query .= "\n";
++$i;
}
$stmt = $db->prepare($query);
$pars = getParams($data, $params);
$r = $stmt->execute($pars);
return $r;
}
Where there are 24 columns and 1000 values to insert. So there is 24,000 parameters on the query.
These are the methods which I use in order to create the string for the query:
function getMarks($params, $for_update = false) {
$arr = array();
foreach ($params as $param) {
if($param == "created_at") continue;
$arr[] = $for_update ? '$param = ?' : '?';
}
return join(',', $arr);
}
function getParams($params, $columns) {
$arr = array();
foreach ($params as $param) {
foreach ($columns as $column) {
if($column == "created_at") continue;
$arr[] = $param[$column];
}
}
return $arr;
}
For some reason, this isn't working and this is the unique output that I'm getting:
Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'VALUES ('1','xxx','xxxx7010','','https://xxxx/xxx/5...' at line 2 in /root/project/php/api.php:126
As you can see I can't manage to make it to work with this unique information.
I already saw this question:
And I did the following code to output the entire command to a string:
function pdo_debugStrParams($stmt) {
ob_start();
$stmt->debugDumpParams();
$r = ob_get_contents();
ob_end_clean();
return $r;
}
Which gives me the same query (with the marks), because I suppose that this needs to be called after the ->execute() method which is giving me an exception, so I can't see anything.
