Problem with mysqli_stmt_bind_param :: Variables are not Binding!

Here's my code:

$mysqli = new mysqli("localhost", "****", "********", "***********");

$idNum = "1030";

$sql = "select id,email from users where id > ?;";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s', $idNum);
$stmt->execute();

$stmt->bind_result($id, $email);

while ($stmt->fetch()) {
printf ("%s (%s)
\n", $id, $email);
}
?>

Obviously, what this should do is bind $idNum (value of 1030) to the
SQL statement and send this to the MySQL server -> select id,email
from users where id > '1030'; This should return 10 rows.

However, this is not what's happening. What's happening is that the
variable is not being binded and this is being sent -> select id,email
from users where id > ''; And because of this, it's returning 1040
rows.

I've tested this on many different scenarios. No matter what I do,
the variables are not binding.

Any idea why this could be happening? I'm assuming it's a server
config issue. Even if you can think of more than one reason, can I
hear some possibilities? I'm at a loss!

Re: Problem with mysqli_stmt_bind_param :: Variables are not Binding!

sugapablo wrote:
> Here's my code:
>
> > $mysqli = new mysqli("localhost", "****", "********", "***********");
>
> $idNum = "1030";
>
> $sql = "select id,email from users where id > ?;";
> $stmt = $mysqli->prepare($sql);
> $stmt->bind_param('s', $idNum);
> $stmt->execute();
>
> $stmt->bind_result($id, $email);
>
> while ($stmt->fetch()) {
> printf ("%s (%s)
\n", $id, $email);
> }
> ?>
>
> Obviously, what this should do is bind $idNum (value of 1030) to the
> SQL statement and send this to the MySQL server -> select id,email
> from users where id > '1030'; This should return 10 rows.
>
> However, this is not what's happening. What's happening is that the
> variable is not being binded and this is being sent -> select id,email
> from users where id > ''; And because of this, it's returning 1040
> rows.
>
> I've tested this on many different scenarios. No matter what I do,
> the variables are not binding.
>
> Any idea why this could be happening? I'm assuming it's a server
> config issue. Even if you can think of more than one reason, can I
> hear some possibilities? I'm at a loss!
>

What happens if you try

$idNum = 1030;

?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Problem with mysqli_stmt_bind_param :: Variables are not Binding!

..oO(Jerry Stuckle)

>sugapablo wrote:
>> Here's my code:
>>
>> >> $mysqli = new mysqli("localhost", "****", "********", "***********");
>>
>> $idNum = "1030";
>>
>> $sql = "select id,email from users where id > ?;";
>> $stmt = $mysqli->prepare($sql);
>> $stmt->bind_param('s', $idNum);
>> $stmt->execute();
>> [...]
>
>What happens if you try
>
>$idNum = 1030;
>
>?

And the first parameter of bind_param() should be 'i' then.

Micha

Re: Problem with mysqli_stmt_bind_param :: Variables are not Binding!

On Aug 29, 5:25 am, Michael Fesser wrote:

> >What happens if you try
>
> >$idNum = 1030;
>
> >?
>
> And the first parameter of bind_param() should be 'i' then.

Well then what's passed to the server is -> select id,email from users
where id > ; and it gives an error. By passing a string, the bind
command at least puts in the single quotes (as shown above in my first
post) and allows the statement to be executed without error.

Re: Problem with mysqli_stmt_bind_param :: Variables are not Binding!

..oO(sugapablo)

>Well then what's passed to the server is -> select id,email from users
>where id > ; and it gives an error. By passing a string, the bind
>command at least puts in the single quotes (as shown above in my first
>post) and allows the statement to be executed without error.

The ID is numeric, so it should not be treated as a string.

What does bind_param() return? If FALSE - is there an error message
(check the 'error' property of your statement)?

Micha

Re: Problem with mysqli_stmt_bind_param :: Variables are not Binding!

On Aug 29, 6:36 am, Michael Fesser wrote:

> The ID is numeric, so it should not be treated as a string.

That's not the point. Regardless of what parameters I'm trying to
bind to any SQL statement I make, the variables are not being binded.

If I try:


$email = "test@test.com";

$sql = "select id,email from users where email = ?;";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s', $email);
$stmt->execute();

?>

It still passes -> select id,email from users where email = ''; to the
server.


> What does bind_param() return? If FALSE - is there an error message
> (check the 'error' property of your statement)?

It returns 1. No error.

Re: Problem with mysqli_stmt_bind_param :: Variables are not Binding!

..oO(sugapablo)

>On Aug 29, 6:36 am, Michael Fesser wrote:
>
>> The ID is numeric, so it should not be treated as a string.
>
>That's not the point. Regardless of what parameters I'm trying to
>bind to any SQL statement I make, the variables are not being binded.

Yep, it was just a general comment. Parameters should always be passed
with the correct type.

>It still passes -> select id,email from users where email = ''; to the
>server.
>
>
>> What does bind_param() return? If FALSE - is there an error message
>> (check the 'error' property of your statement)?
>
>It returns 1. No error.

Hmm. What's the version of the MySQL server and the client library
(check phpinfo() for the client and "mysql -V" on command line for the
server)?

Would it be possible to use PDO instead of mysqli?

Micha

Re: Problem with mysqli_stmt_bind_param :: Variables are not Binding!

sugapablo wrote:
> On Aug 29, 6:36 am, Michael Fesser wrote:
>
>> The ID is numeric, so it should not be treated as a string.
>
> That's not the point. Regardless of what parameters I'm trying to
> bind to any SQL statement I make, the variables are not being binded.
>

No, that is the point. You must use strings with string fields and
numerics with numeric fields in an rdb.

> If I try:
>
> >
> $email = "test@test.com";
>
> $sql = "select id,email from users where email = ?;";
> $stmt = $mysqli->prepare($sql);
> $stmt->bind_param('s', $email);
> $stmt->execute();
>
> ?>
>
> It still passes -> select id,email from users where email = ''; to the
> server.
>

How do you know? Last I checked there was no way in PHP to see the
results of a prepared statement with or without the bound values. Or
are you checking your MySQL log files?

>
>> What does bind_param() return? If FALSE - is there an error message
>> (check the 'error' property of your statement)?
>
> It returns 1. No error.
>
>
>

It does work. If it's not working for you, there is another problem. We
just need to figure out what it is.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Problem with mysqli_stmt_bind_param :: Variables are not Binding!

On Wed, 29 Aug 2007 13:20:49 +0200, Jerry Stuckle
wrote:
>>> What does bind_param() return? If FALSE - is there an error message
>>> (check the 'error' property of your statement)?
>> It returns 1. No error.
>>
>
> It does work. If it's not working for you, there is another problem. We
> just need to figure out what it is.

I can verify the code works perfectly here with a proper database/mysqli
setup (both the string as the (preferred) integer variant). So, it's not
the code, it's the settings/environment somewhere... PHP version? MySQL
version? Settings for both?

--
Rik Wasmus

My new ISP's newsserver sucks. Anyone recommend a good one? Paying for
quality is certainly an option.

Re: Problem with mysqli_stmt_bind_param :: Variables are not Binding!

On Aug 29, 8:04 am, "Rik Wasmus" wrote:

> Settings for both?

Linux 2.6.18-1.2200.fc5smp

Apache/1.3.37 (Unix) PHP/5.1.6 mod_jk/1.2.14 mod_auth_passthrough/1.8
mod_log_bytes/1.2 mod_bwlimited/1.4 FrontPage/5.0.2.2635.SR1.2 mod_ssl/
2.8.28 OpenSSL/0.9.8a

'./configure' '--with-apxs=/usr/local/apache/bin/apxs' '--prefix=/usr/
local' '--with-xml' '--enable-bcmath' '--enable-calendar' '--enable-
ftp' '--with-gd' '--with-jpeg-dir=/usr/local' '--with-png-dir=/usr' '--
with-xpm-dir=/usr/X11R6' '--with-imap=/usr/local/imap-2004g' '--enable-
mbstring' '--enable-mbstr-enc-trans' '--enable-mbregex' '--enable-
magic-quotes' '--with-mysqli=/usr/bin/mysql_config' '--with-mysql=/
usr' '--enable-discard-path' '--with-pear' '--enable-sockets' '--
enable-track-vars' '--with-xmlrpc' '--with-zlib'

MySQL 4.1.22

Re: Problem with mysqli_stmt_bind_param :: Variables are not Binding!

On Aug 29, 10:59 am, sugapablo wrote:
> On Aug 29, 8:04 am, "Rik Wasmus" wrote:
>
> > Settings for both?
>
> Linux 2.6.18-1.2200.fc5smp
>
> Apache/1.3.37 (Unix) PHP/5.1.6

Bumped it up to PHP/5.2.3

No difference. Same problem persists.