| Re: mysqli bind_param reports error with COLLATE [message #185994 is a reply to message #185985] |
Mon, 26 May 2014 23:12  |
Gregor Kofler
Messages: 69
Registered: September 2010
Karma:
|
Member |
|
|
Am 2014-05-21 22:28, Lew Pitcher meinte:
> On Wednesday 21 May 2014 15:50, in comp.lang.php, "Gregor Kofler"
> <usenet(at)gregorkofler(dot)com> wrote:
>
>> PHP 5.5.9 on Ubuntu 14.04 64bit. The database table/column in question
>> is on utf8/utf8_general_ci.
>>
>> The following code
>>
>> $stmt = $db->prepare('
>> SELECT f.filesID FROM files f WHERE f.File = ? COLLATE utf8_bin
>> ');
>> $file = 'foo';
>> $stmt->bind_param('s', $file);
>>
>> results in
>>
>> Warning: mysqli_stmt::bind_param(): Number of variables doesn't match
>> number of parameters in prepared statement in ...
>>
>> When removing the collation
>>
>> $stmt = $db->prepare('
>> SELECT f.filesID FROM files f WHERE f.File = ?
>> ');
>> $file = 'foo';
>> $stmt->bind_param('s', $file);
>>
>> the binding works as expected.
>>
>> Any ideas?
>
> It appears that prepare() doesn't like your COLLATE clause.
Thanks for the response - I cancelled my posting, since I found a
solution (or workaround) just immediately after posting. Therefore I'm
gonna leave the FQ.
> I note that you do not check the results from prepare(). I'm not an OO
> programmer, and I may have misread or misunderstood the mysqli
> documentation, but
> http://dev.mysql.com/doc/apis-php/en/apis-php-mysqli-stmt.prepare.html
> says that prepare() returns TRUE or FALSE, and shows examples of how to test
> it in OO code.
>
> My guess is that your version of the mysqli interface, or mysql, finds some
> problem with the statement you are trying to prepare (when you include the
> COLLATE clause), and builds it's results such that there are *no*
> parameters to be bound to the prepared statement (either, no statement at
> all, or a dummy statement, with FALSE returned). This, in turn, causes
> bind_param() to complain that the the number of parameters given (1) does
> not match the number of parameters expected (0).
Well, I *know* ther error (see above for the error message). However,
the actual error message is (IMO) misleading.
> You might want to discuss this problem of COLLATE with the
> comp.databases.mysql newsgroup; it sounds like it is right up their alley.
They might complain that this is supposedly a problem with the database
driver.
> As for your code, try putting some error-checking around the prepare() call,
> and see if it returns an error when you include COLLATE in the statement.
Anyway, the solution:
I replaced
SELECT f.filesID FROM files f WHERE f.File = ?
with
SELECT f.filesID FROM files f WHERE f.File COLLATE utf8_bin = ?
Gregor
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]