Geek Answers Handbook

Php pdo insert in a column with a question mark in the name

I spent several hours inserting a query using PDO, and finally I realized that I couldn’t do this (I don’t know how). The problem is that the column name has ?? in that. One of the columns is called "If the HSM visa is in which year?". Because of this, every time I insert, I get either: - the wrong number of parameters passed in or - Can’t change the name and? in the request.

I have given up on this and I'm going to change the mysql table I need to work with (who calls columns with question marks anyway?), But I'm still interested.

INSERT INTO `tbl_maindetails` (`Id`,`Title`,`If HSM Visa to what year?`) VALUES (?, ?, ?)

Thanks Goran

+4
source share
3 answers

Here is the only (ugly) solution that I could come up with:

 $db->query("SET @column_name = 'question?'"); $db->query("SET @sql_text = CONCAT('INSERT INTO table1 SET `', @column_name, '` = ?')"); $db->query("PREPARE stmt FROM @sql_text;"); $db->query("SET @v = ?", 'something'); $db->query("EXECUTE stmt USING @v"); echo $db->insert_id();

This will add a new row to table1 with a question? = 'something'

This code uses the PDO shell, so you will need to configure it.

In the end, I think you made the right choice by renaming the column.

0
source 
 $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

will solve the problem, I think.

0
source

I have the same problem! I tried to get around the confusion between the character "?" in the names of the columns (fields) and the same "?" as a positional (unnamed) placeholder in PDO, switching to the NAMED parameter binding ... but the STILL problem persists: (

Now PDO sees "?" in the column names and thinks I'm mixing the named and positional parameters!

For instance:

 UPDATE myTable SET `title` = :title, `Underwater?` = :Underwater WHERE ID='123'

together with the following binding:

  Array ( [:title] => test [:Underwater] => 0)

gives the following error message:

 "SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters"

Please note that I avoided posting any "?" inside the placeholder itself (it's ": Underwater , not ": Underwater? " , but that didn't help ...)

Clearly, this is a mistake! PDO sees "?" in the column names, and going to the conclusion that this is a positional placeholder, even if we strictly use the NAME parameter binding!

I will see if I can report this error ...

Since MySQL allows question marks in column names, I see no good reason why we should avoid them! (Although, in the short term, I will do it, sigh ...)

0
source

More articles:


All Articles