FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » Re: mysqli bind_param reports error with COLLATE
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
Re: mysqli bind_param reports error with COLLATE [message #185985] Wed, 21 May 2014 16:28 Go to next message
Lew Pitcher is currently offline  Lew Pitcher
Messages: 60
Registered: April 2013
Karma: 0
Member
add to buddy list
ignore all messages by this user
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.

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).

You might want to discuss this problem of COLLATE with the
comp.databases.mysql newsgroup; it sounds like it is right up their alley.

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.

HTH
--
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request
Re: mysqli bind_param reports error with COLLATE [message #185994 is a reply to message #185985] Mon, 26 May 2014 19:12 Go to previous message
Gregor Kofler is currently offline  Gregor Kofler
Messages: 69
Registered: September 2010
Karma: 0
Member
add to buddy list
ignore all messages by this user
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
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: PHP-SOAP/5.4.21 SoapClient terminating POST without waiting for reply, but still complaining "error fetching http headers".
Next Topic: Add compiler to Page
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Oct 21 10:14:13 EDT 2017

Total time taken to generate the page: 0.00464 seconds