| Problem with mysqli_stmt_bind_param() [message #179572] |
Thu, 08 November 2012 21:38  |
rayven
Messages: 3
Registered: November 2012
Karma: 0
|
Junior Member |
|
|
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] |
Fri, 09 November 2012 00:12  |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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 08:51 |
M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
|
Senior Member |
|
|
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 17:51 |
rayven
Messages: 3
Registered: November 2012
Karma: 0
|
Junior Member |
|
|
> 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 17:56 |
rayven
Messages: 3
Registered: November 2012
Karma: 0
|
Junior Member |
|
|
> 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 19:42 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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
==================
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]