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

Home » Imported messages » comp.lang.php » Problem with mysqli_stmt_bind_param()
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
Problem with mysqli_stmt_bind_param() [message #179572] Thu, 08 November 2012 16:38 Go to next message
rayven is currently offline  rayven
Messages: 3
Registered: November 2012
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Can anyone help as I am at my wits end.
On my PHP page, the following code works perefectly:

<?php
$connection = new mysqli("localhost", "...", "...", "...");
$sql="SELECT field1, field2 FROM mytable";
if($stmt = mysqli_prepare($connection, $sql))
{
mysqli_stmt_bind_param($stmt, "s", $emailaddress);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
mysqli_stmt_fetch($stmt);
echo($clientid);
echo($siteadmin);
mysqli_stmt_close($stmt);
}
mysqli_close($connection);
?>

But the moment I introduce mysqli_stmt_bind_param() and a parameter on the SELECT statement, I get rows_affected = -1 and no error returned, and no data is returned either. But if I run the same statement with the same parameters (and I have checked the parameter variables are populated correctly) in the mysql administrator, it works!

<?php
$connection = new mysqli("localhost", "...", "...", "...");
$sql="SELECT field1, field2 FROM mytable WHERE field3=?";
if($stmt = mysqli_prepare($connection, $sql))
{
mysqli_stmt_bind_param($stmt, "s", $myparam);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
mysqli_stmt_fetch($stmt);
echo($clientid);
echo($siteadmin);
mysqli_stmt_close($stmt);
}
mysqli_close($connection);
?>

What on earth am I doing wrong?

Thanks in advance.
Any responses posted here, or emailed to me directly will be very much appreciated.
Re: Problem with mysqli_stmt_bind_param() [message #179573 is a reply to message #179572] Thu, 08 November 2012 19:12 Go to previous messageGo to next message
Jerry Stuckle is currently offline  Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On 11/8/2012 4:38 PM, rayven wrote:
> Can anyone help as I am at my wits end.
> On my PHP page, the following code works perefectly:
>
> <?php
> $connection = new mysqli("localhost", "...", "...", "...");
> $sql="SELECT field1, field2 FROM mytable";
> if($stmt = mysqli_prepare($connection, $sql))
> {
> mysqli_stmt_bind_param($stmt, "s", $emailaddress);
> mysqli_stmt_execute($stmt);
> mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
> mysqli_stmt_fetch($stmt);
> echo($clientid);
> echo($siteadmin);
> mysqli_stmt_close($stmt);
> }
> mysqli_close($connection);
> ?>
>
> But the moment I introduce mysqli_stmt_bind_param() and a parameter on the SELECT statement, I get rows_affected = -1 and no error returned, and no data is returned either. But if I run the same statement with the same parameters (and I have checked the parameter variables are populated correctly) in the mysql administrator, it works!
>
> <?php
> $connection = new mysqli("localhost", "...", "...", "...");
> $sql="SELECT field1, field2 FROM mytable WHERE field3=?";
> if($stmt = mysqli_prepare($connection, $sql))
> {
> mysqli_stmt_bind_param($stmt, "s", $myparam);
> mysqli_stmt_execute($stmt);
> mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
> mysqli_stmt_fetch($stmt);
> echo($clientid);
> echo($siteadmin);
> mysqli_stmt_close($stmt);
> }
> mysqli_close($connection);
> ?>
>
> What on earth am I doing wrong?
>
> Thanks in advance.
> Any responses posted here, or emailed to me directly will be very much appreciated.
>
>

What's in $myparam> echo it with delimiters around it (to ensure there
is no extra white space) just before binding it.

Does msqli_stmt_bind_param() return TRUE?
Do any of the rows in your table contain the value in $myparam?

You need to do a bit of debugging here.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
Re: Problem with mysqli_stmt_bind_param() [message #179576 is a reply to message #179572] Fri, 09 November 2012 03:51 Go to previous messageGo to next message
M. Strobel is currently offline  M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
Am 08.11.2012 22:38, schrieb rayven:
> Can anyone help as I am at my wits end.
> On my PHP page, the following code works perefectly:
>
> <?php
> $connection = new mysqli("localhost", "...", "...", "...");
> $sql="SELECT field1, field2 FROM mytable";
> if($stmt = mysqli_prepare($connection, $sql))
> {
> mysqli_stmt_bind_param($stmt, "s", $emailaddress);
> mysqli_stmt_execute($stmt);
> mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
> mysqli_stmt_fetch($stmt);
> echo($clientid);
> echo($siteadmin);
> mysqli_stmt_close($stmt);
> }
> mysqli_close($connection);
> ?>
>
> But the moment I introduce mysqli_stmt_bind_param() and a parameter on the SELECT statement, I get rows_affected = -1 and no error returned, and no data is returned either. But if I run the same statement with the same parameters (and I have checked the parameter variables are populated correctly) in the mysql administrator, it works!
>
> <?php
> $connection = new mysqli("localhost", "...", "...", "...");
> $sql="SELECT field1, field2 FROM mytable WHERE field3=?";
> if($stmt = mysqli_prepare($connection, $sql))
> {
> mysqli_stmt_bind_param($stmt, "s", $myparam);
> mysqli_stmt_execute($stmt);
> mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
> mysqli_stmt_fetch($stmt);
> echo($clientid);
> echo($siteadmin);
> mysqli_stmt_close($stmt);
> }
> mysqli_close($connection);
> ?>
>
> What on earth am I doing wrong?

On my system (not mysql), I would enable full statement logging in the database and
see what is sent.

Another observation: your code is not very stable, the only time you check a return
code is the prepare step. And you do not check the number of returned rows.

Then style: I wonder why the OO style is not used, it is shorter to write and read.
And you can extend mysqli and put your utility functions into it.

But your problem seems to depend only on the values of your variables.

/Str.
Re: Problem with mysqli_stmt_bind_param() [message #179578 is a reply to message #179572] Fri, 09 November 2012 12:51 Go to previous messageGo to next message
rayven is currently offline  rayven
Messages: 3
Registered: November 2012
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
> On 11/8/2012 4:38 PM, rayven wrote:
>> Can anyone help as I am at my wits end.
>> On my PHP page, the following code works perefectly:
>>
>> <?php
>> $connection = new mysqli("localhost", "...", "...", "...");
>> $sql="SELECT field1, field2 FROM mytable";
>> if($stmt = mysqli_prepare($connection, $sql))
>> {
>> mysqli_stmt_bind_param($stmt, "s", $emailaddress);
>> mysqli_stmt_execute($stmt);
>> mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
>> mysqli_stmt_fetch($stmt);
>> echo($clientid);
>> echo($siteadmin);
>> mysqli_stmt_close($stmt);
>> }
>> mysqli_close($connection);
>> ?>
>>
>> But the moment I introduce mysqli_stmt_bind_param() and a parameter on the SELECT statement, I get rows_affected = -1 and no error returned, and no data is returned either. But if I run the same statement with the same parameters (and I have checked the parameter variables are populated correctly) in the mysql administrator, it works!
>>
>> <?php
>> $connection = new mysqli("localhost", "...", "...", "...");
>> $sql="SELECT field1, field2 FROM mytable WHERE field3=?";
>> if($stmt = mysqli_prepare($connection, $sql))
>> {
>> mysqli_stmt_bind_param($stmt, "s", $myparam);
>> mysqli_stmt_execute($stmt);
>> mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
>> mysqli_stmt_fetch($stmt);
>> echo($clientid);
>> echo($siteadmin);
>> mysqli_stmt_close($stmt);
>> }
>> mysqli_close($connection);
>> ?>
>>
>> What on earth am I doing wrong?
>>
>> Thanks in advance.
>> Any responses posted here, or emailed to me directly will be very much appreciated.
>>
>>

> What's in $myparam> echo it with delimiters around it (to ensure there
i>s no extra white space) just before binding it.
>
> Does msqli_stmt_bind_param() return TRUE?
> Do any of the rows in your table contain the value in $myparam?
>
> You need to do a bit of debugging here.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex(at)attglobal(dot)net
> ==================

Way ahead of you I'm afraid.
I have already echoed the parameter out to check the contents, made sure there are no leading or trailing spaces, checked the case or the parameter and checked teh database.

I have even hard coded a $param='value'; into the page to make sure i knew it was correct, and like I said, I can search for the same data using mySQL Administrator and the record returns.

What is worse, mysqli_stmt_bind_param() returns FALSE indicating an error, but when I ask for the error, nothing is being returned.
Re: Problem with mysqli_stmt_bind_param() [message #179579 is a reply to message #179572] Fri, 09 November 2012 12:56 Go to previous messageGo to next message
rayven is currently offline  rayven
Messages: 3
Registered: November 2012
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
> Am 08.11.2012 22:38, schrieb rayven:
>> Can anyone help as I am at my wits end.
>> On my PHP page, the following code works perefectly:
>>
>> <?php
>> $connection = new mysqli("localhost", "...", "...", "...");
>> $sql="SELECT field1, field2 FROM mytable";
>> if($stmt = mysqli_prepare($connection, $sql))
>> {
>> mysqli_stmt_bind_param($stmt, "s", $emailaddress);
>> mysqli_stmt_execute($stmt);
>> mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
>> mysqli_stmt_fetch($stmt);
>> echo($clientid);
>> echo($siteadmin);
>> mysqli_stmt_close($stmt);
>> }
>> mysqli_close($connection);
>> ?>
>>
>> But the moment I introduce mysqli_stmt_bind_param() and a parameter on the SELECT statement, I get rows_affected = -1 and no error returned, and no data is returned either. But if I run the same statement with the same parameters (and I have checked the parameter variables are populated correctly) in the mysql administrator, it works!
>>
>> <?php
>> $connection = new mysqli("localhost", "...", "...", "...");
>> $sql="SELECT field1, field2 FROM mytable WHERE field3=?";
>> if($stmt = mysqli_prepare($connection, $sql))
>> {
>> mysqli_stmt_bind_param($stmt, "s", $myparam);
>> mysqli_stmt_execute($stmt);
>> mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
>> mysqli_stmt_fetch($stmt);
>> echo($clientid);
>> echo($siteadmin);
>> mysqli_stmt_close($stmt);
>> }
>> mysqli_close($connection);
>> ?>
>>
>> What on earth am I doing wrong?

> On my system (not mysql), I would enable full statement logging in the database and
> see what is sent.

> Another observation: your code is not very stable, the only time you check a return
> code is the prepare step. And you do not check the number of returned rows.

> Then style: I wonder why the OO style is not used, it is shorter to write and read.
> And you can extend mysqli and put your utility functions into it.

> But your problem seems to depend only on the values of your variables.

> /Str.

Sorry, I should have been a bit more precise.

The original code has full error checking and row count checking, I stripped all of that out just to show the statements I am using.

As mentioned to a previous poster, I have checked the values and parametyers carefully, that was the first thing I did. mysql_stmt_bind_params() is returning FALSE indicating that I have an error there, but when I ask for the error nothing is returned.

I can't enable logging as the database is on a hosted server that I have no control over.

And as for the OO style, that was my original approach but that wasn't working, which is why I swapped to the procedural style shown here, but the problem followed me.

I'll soldier on, I've had a night of sleep so hopefully I might spot something I have missed. I just hate it when something gets the better of me :)

Thanks for all your help anyway.
Re: Problem with mysqli_stmt_bind_param() [message #179581 is a reply to message #179578] Fri, 09 November 2012 14:42 Go to previous message
Jerry Stuckle is currently offline  Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On 11/9/2012 12:51 PM, rayven wrote:
>> On 11/8/2012 4:38 PM, rayven wrote:
>>> Can anyone help as I am at my wits end.
>>> On my PHP page, the following code works perefectly:
>>>
>>> <?php
>>> $connection = new mysqli("localhost", "...", "...", "...");
>>> $sql="SELECT field1, field2 FROM mytable";
>>> if($stmt = mysqli_prepare($connection, $sql))
>>> {
>>> mysqli_stmt_bind_param($stmt, "s", $emailaddress);
>>> mysqli_stmt_execute($stmt);
>>> mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
>>> mysqli_stmt_fetch($stmt);
>>> echo($clientid);
>>> echo($siteadmin);
>>> mysqli_stmt_close($stmt);
>>> }
>>> mysqli_close($connection);
>>> ?>
>>>
>>> But the moment I introduce mysqli_stmt_bind_param() and a parameter on the SELECT statement, I get rows_affected = -1 and no error returned, and no data is returned either. But if I run the same statement with the same parameters (and I have checked the parameter variables are populated correctly) in the mysql administrator, it works!
>>>
>>> <?php
>>> $connection = new mysqli("localhost", "...", "...", "...");
>>> $sql="SELECT field1, field2 FROM mytable WHERE field3=?";
>>> if($stmt = mysqli_prepare($connection, $sql))
>>> {
>>> mysqli_stmt_bind_param($stmt, "s", $myparam);
>>> mysqli_stmt_execute($stmt);
>>> mysqli_stmt_bind_result($stmt, $clientid, $siteadmin);
>>> mysqli_stmt_fetch($stmt);
>>> echo($clientid);
>>> echo($siteadmin);
>>> mysqli_stmt_close($stmt);
>>> }
>>> mysqli_close($connection);
>>> ?>
>>>
>>> What on earth am I doing wrong?
>>>
>>> Thanks in advance.
>>> Any responses posted here, or emailed to me directly will be very much appreciated.
>>>
>>>
>
>> What's in $myparam> echo it with delimiters around it (to ensure there
> i>s no extra white space) just before binding it.
>>
>> Does msqli_stmt_bind_param() return TRUE?
>> Do any of the rows in your table contain the value in $myparam?
>>
>> You need to do a bit of debugging here.
>>
>
> Way ahead of you I'm afraid.
> I have already echoed the parameter out to check the contents, made sure there are no leading or trailing spaces, checked the case or the parameter and checked teh database.
>
> I have even hard coded a $param='value'; into the page to make sure i knew it was correct, and like I said, I can search for the same data using mySQL Administrator and the record returns.
>
> What is worse, mysqli_stmt_bind_param() returns FALSE indicating an error, but when I ask for the error, nothing is being returned.
>

Please use the "Reply" button when responding to messages. That way the
thread will be maintained.

OK, so the bind is failing. Depending on the cause of the failure, it
may or may not show up as a MySQL error. On your test system ensure
that you have enabled all errors and are displaying them. Your php.ini
file should have:

error_reporting=E_ALL
display_errors=on

I suspect you'll get an error message with these settings.

Also - why are you mixing OO and non-OO methods?

$connection = new mysqli... is object oriented. The rest of your code
is non-OO. You need to decide on one or the other (I prefer OO, myself).


P.S. You do NOT want these set in a production environment.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Analista Programador PHP
Next Topic: Using += assignment recursively on an array w/o notice
Goto Forum:
  

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

Current Time: Tue Dec 12 18:52:31 EST 2017

Total time taken to generate the page: 0.00653 seconds