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
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
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

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:
On subsequent times when I run it, I get:
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....
$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.