difficulty using mysqli with bind variables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mountaindrummer
Forum Newbie
Posts: 4
Joined: Wed Aug 02, 2006 10:15 pm

difficulty using mysqli with bind variables

Post by mountaindrummer »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi,
I am having trouble using bind variables in prepared statements with mysqli.

I have:
PHP version: 5.1.4
Linux distro: Kubuntu dapper drake
Mysql: 4.1.12


This first set of code does not use bind variables and works as expected:

Code: Select all

$sql = "SELECT count(*) as TOTAL from users where id = 1";
$stmt=$mysqli->prepare($sql);
$stmt->bind_result($count);
$stmt->execute();
$stmt->fetch();
echo "Count is: ".$count."<br/>";
$stmt->close();
-> It displays Count is: 1 to the web page

The second set of statements, displays Count is: 0, when I thought
it is the same query as the first one:

Code: Select all

$sql_bind = "SELECT count(*) as TOTAL from users where id = ?";
$stmt=$mysqli->prepare($sql_bind);
$id = 1;
$stmt->bind_param('i', $id);
$stmt->bind_result($count);
$stmt->execute();
$stmt->fetch();
echo "Count is: ".$count."<br/>";
What am I doing wrong?


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

did you try this example.
be sure to set error_reporting(E_ALL) in php.
Try using mysqli_report() as well

Let us now what these return

Code: Select all

mysqli_get_server_info()
mysqli_get_server_version()
mysqli_get_client_info()
mysqli_get_client_version()
actually I think *_version are the important.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

$stmt->bind_param('i', $id);
What i?

btw: There is no bind_param / bind_result
http://de2.php.net/pdo wrote:bindParam - binds a PHP variable to a parameter in the prepared statement
bindColumn - binds a PHP variable to an output column in a result set
so this is obviously not the original code. Why?
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

volka wrote:
$stmt->bind_param('i', $id);
What i?

btw: There is no bind_param / bind_result
http://de2.php.net/pdo wrote:bindParam - binds a PHP variable to a parameter in the prepared statement
bindColumn - binds a PHP variable to an output column in a result set
so this is obviously not the original code. Why?

yes there is!!!
your quote is from pdo which is has nothing to do with mysqli
mountaindrummer
Forum Newbie
Posts: 4
Joined: Wed Aug 02, 2006 10:15 pm

Post by mountaindrummer »

jmut,

I ran code similar to what you described to get the version info
and display the following output:

Code: Select all

$mysqli->get_server_info: 4.1.12-Debian_1ubuntu3-log
$mysqli->server_version: 40112
$mysqli->get_client_info: 4.1.12
$mysqli->client_version: 40112
Any ideas?
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

mountaindrummer wrote:jmut,

I ran code similar to what you described to get the version info
and display the following output:

Code: Select all

$mysqli->get_server_info: 4.1.12-Debian_1ubuntu3-log
$mysqli->server_version: 40112
$mysqli->get_client_info: 4.1.12
$mysqli->client_version: 40112
Any ideas?
well, this looks ok I think. as long as client/server are the same.
Are you having 64bit PC - I have seen problems with that.
Other than that I would suggest you use the link I gave you and recreate the example given.
If then it does not work....will see :)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

jmut wrote:yes there is!!!
your quote is from pdo which is has nothing to do with mysqli
:oops: sorry
mountaindrummer
Forum Newbie
Posts: 4
Joined: Wed Aug 02, 2006 10:15 pm

Post by mountaindrummer »

I ran the example that you specified above in your link jmut and I seem to be getting strange results,
maybe I have a bad configuration of preloaded kubuntu packages vs. the compiled php5,apache,mysql
installations..

The first time I ran the php script example I got:

Code: Select all

1 Row inserted. 0 Row deleted.
On subsequent times when I run it, I get:

Code: Select all

0 Row inserted. 0 Row deleted.
I do a select in the database, and I can not find the record:

Code: Select all

mysql> select * from CountryLanguage where CountryCode = 'DEU';
+-------------+---------------------------+------------+------------+
| CountryCode | Language                  | IsOfficial | Percentage |
+-------------+---------------------------+------------+------------+
| DEU         | German                    | T          |       91.3 |
| DEU         | Greek                     | F          |        0.4 |
| DEU         | Italian                   | F          |        0.7 |
| DEU         | Polish                    | F          |        0.3 |
| DEU         | Southern Slavic Languages | F          |        1.4 |
| DEU         | Turkish                   | F          |        2.6 |
+-------------+---------------------------+------------+------------+
6 rows in set (0.04 sec)

mysql>
Just to confirm - here is the php code from the example:

Code: Select all

<?php
$mysqli = new mysqli('localhost', 'xxx, 'xxx', 'world');

/* check connection */
if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

/* execute prepared statement */
$stmt->execute();

printf("%d Row inserted.\n", $stmt->affected_rows);

/* close statement and connection */
$stmt->close();

/* Clean up table CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d Row deleted.\n", $mysqli->affected_rows);

/* close connection */
$mysqli->close();
?>
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

hm...well I guess you should try reinstall these mysqli modules...or search
mysqli prepared statemetns problems or something in google for the os.
I get correct result each time I run the script. (one insert, followed by delete).


Edit:
FYI this is my results....maybe here is the problem with your compilation. they should be same after all.... :roll:
$mysqli->get_server_info():5.0.22-log
$mysqli->server_version:50022
$mysqli->get_client_info():5.0.22
$mysqli->client_version:50019
mountaindrummer
Forum Newbie
Posts: 4
Joined: Wed Aug 02, 2006 10:15 pm

"finally" solved

Post by mountaindrummer »

I downloaded the latest 4.1.21 version of mysql and removed the ubuntu package for mysql on my server.
I compiled mysql from scratch, apache2 from scratch, and php5 from scratch.

After solving a variety of minor configuration problems to get everything linking correctly,
I have been able to get the correct results from all of my testing with php5 mysqli.

The reason why I didn't want to use mysql 5 is because my isp, hostgator, does not support it and I wanted to recreate that environment on my development box.

If you are going to use mysql 5, then you could add the mirror: dotdeb.org for apt-get package updates because
that mirror includes php5 packages that probably just work if you update with apt-get.

Thanks jmut for the advice, it encouraged me to keep trying to solve the problem.
Post Reply