Page 1 of 1

difficulty using mysqli with bind variables

Posted: Wed Aug 02, 2006 10:22 pm
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]

Posted: Thu Aug 03, 2006 4:11 am
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.

Posted: Thu Aug 03, 2006 4:35 am
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?

Posted: Thu Aug 03, 2006 5:21 am
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

Posted: Thu Aug 03, 2006 10:47 am
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?

Posted: Thu Aug 03, 2006 11:46 am
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 :)

Posted: Thu Aug 03, 2006 4:38 pm
by volka
jmut wrote:yes there is!!!
your quote is from pdo which is has nothing to do with mysqli
:oops: sorry

Posted: Sat Aug 05, 2006 1:59 pm
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();
?>

Posted: Sun Aug 06, 2006 3:18 pm
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

"finally" solved

Posted: Sun Aug 06, 2006 10:53 pm
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.