Re: [PHP] Prepared statements
Curt Zirzow wrote: On Mon, Mar 06, 2006 at 10:03:10PM +0100, Julius Hacker wrote: Curt Zirzow wrote: I assume your loop is something like: while(condition) { $auction_parts['id'] = 'some value'; $auction_parts['name'] = 'some value'; ... $insert-execute(); } Yes, thats it. My first guess would be, if not aleady done, initialize $auction_parts before you do your bind_param() with something like: $auction_parts = array('id' = null, 'name' = null, ...); $insert-bind_param(issdsis, $auction_house[id], ...); Unfortunately it helps nothing :-( Ok, i didn't really think it would but might have shown a obvious bug with mysqli. According to your original post it was odd that the update prepared statment didn't complain about a NULL value, but the insert did, and it does seem to be related to bind_param and prepare. What are your versions of: php? mysql_client php is built with? mysql server you are connecting to? I've alread a solution, just forgot to post here :) Anthony gave me the hint as he sent me a list of the datatypes accepted by bind_param and so I checked the datatypes of the variables and have seen that $auction_house[id] was a string and not an integer. After I converted it to an integer, it works properly. I don't know exactly why it worked if I did bind_param in the loop but I think it's because it saw there that I gave him a string and converted it automatically to integer. -- Regards Julius Hacker http://www.julius-hacker.de [EMAIL PROTECTED] OpenPGP-Key-ID: 0x4B4A486E -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
Anthony Ettinger wrote: On 3/6/06, Anthony Ettinger [EMAIL PROTECTED] wrote: On 3/6/06, Julius Hacker [EMAIL PROTECTED] wrote: Curt Zirzow wrote: I assume your loop is something like: while(condition) { $auction_parts['id'] = 'some value'; $auction_parts['name'] = 'some value'; ... $insert-execute(); } Yes, thats it. My first guess would be, if not aleady done, initialize $auction_parts before you do your bind_param() with something like: $auction_parts = array('id' = null, 'name' = null, ...); $insert-bind_param(issdsis, $auction_house[id], ...); Unfortunately it helps nothing :-( But I think also that it's something because of the declaration and initalization of the variables because it works if I have the bind_param in the loop after I gave the variables their values. But to have the bind_param in the loop isn't the best solution I think. -- Regards Julius Hacker http://www.julius-hacker.de [EMAIL PROTECTED] OpenPGP-Key-ID: 0x4B4A486E -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php $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(); http://us2.php.net/manual/en/function.mysqli-stmt-bind-param.php Table 1. Type specification chars Character Description i corresponding variable has type integer d corresponding variable has type double s corresponding variable has type string b corresponding variable is a blob and will be send in packets 'sssd' defines the datatypes of the 4 arguments you're passing in. I know the example ;-) I checked the statement now without the loop, but defined the variables also after the bind_param. Very interesting is that it works if I write $auction_parts[...] = ...; but that it doesn't if I write $auction_parts = array(... = ..., ...); -- Regards Julius Hacker http://www.julius-hacker.de [EMAIL PROTECTED] OpenPGP-Key-ID: 0x4B4A486E -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
On Mon, Mar 06, 2006 at 10:03:10PM +0100, Julius Hacker wrote: Curt Zirzow wrote: I assume your loop is something like: while(condition) { $auction_parts['id'] = 'some value'; $auction_parts['name'] = 'some value'; ... $insert-execute(); } Yes, thats it. My first guess would be, if not aleady done, initialize $auction_parts before you do your bind_param() with something like: $auction_parts = array('id' = null, 'name' = null, ...); $insert-bind_param(issdsis, $auction_house[id], ...); Unfortunately it helps nothing :-( Ok, i didn't really think it would but might have shown a obvious bug with mysqli. According to your original post it was odd that the update prepared statment didn't complain about a NULL value, but the insert did, and it does seem to be related to bind_param and prepare. What are your versions of: php? mysql_client php is built with? mysql server you are connecting to? But I think also that it's something because of the declaration and initalization of the variables because it works if I have the bind_param in the loop after I gave the variables their values. But to have the bind_param in the loop isn't the best solution I think. agreed. Curt. -- cat .signature: No such file or directory -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
Curt Zirzow wrote: I assume your loop is something like: while(condition) { $auction_parts['id'] = 'some value'; $auction_parts['name'] = 'some value'; ... $insert-execute(); } Yes, thats it. My first guess would be, if not aleady done, initialize $auction_parts before you do your bind_param() with something like: $auction_parts = array('id' = null, 'name' = null, ...); $insert-bind_param(issdsis, $auction_house[id], ...); Unfortunately it helps nothing :-( But I think also that it's something because of the declaration and initalization of the variables because it works if I have the bind_param in the loop after I gave the variables their values. But to have the bind_param in the loop isn't the best solution I think. -- Regards Julius Hacker http://www.julius-hacker.de [EMAIL PROTECTED] OpenPGP-Key-ID: 0x4B4A486E -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
MySQL returns Column 'auction_house' cannot be null. Here're some parts of my code: --- code --- $update = $this-sql-stmt_init(); $update-prepare(UPDATE auctions SET name=?, auction_house=?, link=?, prize=?, runtime=?, bids=?, picture=? WHERE link=?); $update-bind_param(sisdsiss, $auction_parts[name], $auction_house[id], $auction_parts[link], $auction_parts[prize], $auction_parts[runtime], $auction_parts[bids], $auction_parts[picture], $auction_parts[link]); $insert = $this-sql-stmt_init(); $insert-prepare(INSERT INTO auctions (auction_house, name, link, prize, runtime, bids, picture) VALUES (?, ?, ?, ?, ?, ?, ?)); $insert-bind_param(issdsis, $auction_house[id], $auction_parts[name], $auction_parts[link], $auction_parts[prize], $auction_parts[runtime], $auction_parts[bids], $auction_parts[picture]); --- /code --- after this, there's the loop, in which I do either $update-execute(); or $insert-execute(); - the $update-execute(); runs without problems but the $insert-execute runs into the described problem. So at some point the $auction_parts['id'] is empty. As you go into the loop, print out the $auction_house[id], then work backwards... -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
chris smith wrote: So at some point the $auction_parts['id'] is empty. As you go into the loop, print out the $auction_house[id], then work backwards... I output already some variables in the loop and all are set with correct values. As I said, if I do the bind_param inside the loop it works without problems. So there's a difference in doing it before the loop and inside the loop causing this problem. -- Regards Julius Hacker http://www.julius-hacker.de [EMAIL PROTECTED] OpenPGP-Key-ID: 0x4B4A486E -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
On Sun, Mar 05, 2006 at 04:03:17AM +0100, Julius Hacker wrote: On 3/4/06, Julius Hacker [EMAIL PROTECTED] wrote: Before that foreach, I use mysqli_stmt_init, mysql_stmt_prepare and mysql_stmt_bind_param. In the foreach-loop I give the variables, which I bound with bind_param, their values and want to execute the statement. But now MySQL returns always an error. It seems that the values I gave the variables in the loop aren't used because I used bind_param before that. In the example for mysql_bind_param they do it like me. Is the example also wrong or do I have to consider something special? ... MySQL returns Column 'auction_house' cannot be null. Here're some parts of my code: --- code --- ... $insert = $this-sql-stmt_init(); $insert-prepare(INSERT INTO auctions (auction_house, name, link, prize, runtime, bids, picture) VALUES (?, ?, ?, ?, ?, ?, ?)); $insert-bind_param(issdsis, $auction_house[id], $auction_parts[name], $auction_parts[link], $auction_parts[prize], $auction_parts[runtime], $auction_parts[bids], $auction_parts[picture]); --- /code --- I assume your loop is something like: while(condition) { $auction_parts['id'] = 'some value'; $auction_parts['name'] = 'some value'; ... $insert-execute(); } My first guess would be, if not aleady done, initialize $auction_parts before you do your bind_param() with something like: $auction_parts = array('id' = null, 'name' = null, ...); $insert-bind_param(issdsis, $auction_house[id], ...); Curt. -- cat .signature: No such file or directory -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
On 3/5/06, Curt Zirzow [EMAIL PROTECTED] wrote: On Sun, Mar 05, 2006 at 04:03:17AM +0100, Julius Hacker wrote: On 3/4/06, Julius Hacker [EMAIL PROTECTED] wrote: Before that foreach, I use mysqli_stmt_init, mysql_stmt_prepare and mysql_stmt_bind_param. In the foreach-loop I give the variables, which I bound with bind_param, their values and want to execute the statement. But now MySQL returns always an error. It seems that the values I gave the variables in the loop aren't used because I used bind_param before that. In the example for mysql_bind_param they do it like me. Is the example also wrong or do I have to consider something special? ... MySQL returns Column 'auction_house' cannot be null. Here're some parts of my code: --- code --- ... $insert = $this-sql-stmt_init(); $insert-prepare(INSERT INTO auctions (auction_house, name, link, prize, runtime, bids, picture) VALUES (?, ?, ?, ?, ?, ?, ?)); $insert-bind_param(issdsis, $auction_house[id], $auction_parts[name], $auction_parts[link], $auction_parts[prize], $auction_parts[runtime], $auction_parts[bids], $auction_parts[picture]); --- /code --- I assume your loop is something like: while(condition) { $auction_parts['id'] = 'some value'; $auction_parts['name'] = 'some value'; ... $insert-execute(); } My first guess would be, if not aleady done, initialize $auction_parts before you do your bind_param() with something like: $auction_parts = array('id' = null, 'name' = null, ...); $insert-bind_param(issdsis, $auction_house[id], ...); Curt. -- cat .signature: No such file or directory -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php might want to read up on bind_param. I'ts been awhile since I did this in Perl. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
One other thing: If I do the bind_param within the loop, it just works. The curious is that I have to prepared statements for this loop (one for inserting data and one for updating data) and the one for updating data works and that for inserting don't. Both statements are 100% valid. Julius Hacker wrote: Hi, so I need help again: I want to use prepared statements to insert lots of data in my MySQL-database. For that I use foreach because I have an array containing all necessary information. Before that foreach, I use mysqli_stmt_init, mysql_stmt_prepare and mysql_stmt_bind_param. In the foreach-loop I give the variables, which I bound with bind_param, their values and want to execute the statement. But now MySQL returns always an error. It seems that the values I gave the variables in the loop aren't used because I used bind_param before that. In the example for mysql_bind_param they do it like me. Is the example also wrong or do I have to consider something special? -- Regards Julius Hacker http://www.julius-hacker.de [EMAIL PROTECTED] OpenPGP-Key-ID: 0x4B4A486E -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
On 3/4/06, Julius Hacker [EMAIL PROTECTED] wrote: One other thing: If I do the bind_param within the loop, it just works. The curious is that I have to prepared statements for this loop (one for inserting data and one for updating data) and the one for updating data works and that for inserting don't. Both statements are 100% valid. Julius Hacker wrote: Hi, so I need help again: I want to use prepared statements to insert lots of data in my MySQL-database. For that I use foreach because I have an array containing all necessary information. Before that foreach, I use mysqli_stmt_init, mysql_stmt_prepare and mysql_stmt_bind_param. In the foreach-loop I give the variables, which I bound with bind_param, their values and want to execute the statement. But now MySQL returns always an error. It seems that the values I gave the variables in the loop aren't used because I used bind_param before that. In the example for mysql_bind_param they do it like me. Is the example also wrong or do I have to consider something special? -- Regards Julius Hacker http://www.julius-hacker.de [EMAIL PROTECTED] OpenPGP-Key-ID: 0x4B4A486E -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Can you dump the error string reported back from the mysql database connection? Could provide some insight as to why your INSERT fails, and the UPDATE works. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
Anthony Ettinger wrote: On 3/4/06, Julius Hacker [EMAIL PROTECTED] wrote: One other thing: If I do the bind_param within the loop, it just works. The curious is that I have to prepared statements for this loop (one for inserting data and one for updating data) and the one for updating data works and that for inserting don't. Both statements are 100% valid. Julius Hacker wrote: Hi, so I need help again: I want to use prepared statements to insert lots of data in my MySQL-database. For that I use foreach because I have an array containing all necessary information. Before that foreach, I use mysqli_stmt_init, mysql_stmt_prepare and mysql_stmt_bind_param. In the foreach-loop I give the variables, which I bound with bind_param, their values and want to execute the statement. But now MySQL returns always an error. It seems that the values I gave the variables in the loop aren't used because I used bind_param before that. In the example for mysql_bind_param they do it like me. Is the example also wrong or do I have to consider something special? -- Regards Julius Hacker http://www.julius-hacker.de [EMAIL PROTECTED] OpenPGP-Key-ID: 0x4B4A486E -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Can you dump the error string reported back from the mysql database connection? Could provide some insight as to why your INSERT fails, and the UPDATE works. MySQL returns Column 'auction_house' cannot be null. Here're some parts of my code: --- code --- $update = $this-sql-stmt_init(); $update-prepare(UPDATE auctions SET name=?, auction_house=?, link=?, prize=?, runtime=?, bids=?, picture=? WHERE link=?); $update-bind_param(sisdsiss, $auction_parts[name], $auction_house[id], $auction_parts[link], $auction_parts[prize], $auction_parts[runtime], $auction_parts[bids], $auction_parts[picture], $auction_parts[link]); $insert = $this-sql-stmt_init(); $insert-prepare(INSERT INTO auctions (auction_house, name, link, prize, runtime, bids, picture) VALUES (?, ?, ?, ?, ?, ?, ?)); $insert-bind_param(issdsis, $auction_house[id], $auction_parts[name], $auction_parts[link], $auction_parts[prize], $auction_parts[runtime], $auction_parts[bids], $auction_parts[picture]); --- /code --- after this, there's the loop, in which I do either $update-execute(); or $insert-execute(); - the $update-execute(); runs without problems but the $insert-execute runs into the described problem. -- Regards Julius Hacker -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Prepared statements
are you executing the statement in your loop too? On 3/3/06, Julius Hacker [EMAIL PROTECTED] wrote: Hi, so I need help again: I want to use prepared statements to insert lots of data in my MySQL-database. For that I use foreach because I have an array containing all necessary information. Before that foreach, I use mysqli_stmt_init, mysql_stmt_prepare and mysql_stmt_bind_param. In the foreach-loop I give the variables, which I bound with bind_param, their values and want to execute the statement. But now MySQL returns always an error. It seems that the values I gave the variables in the loop aren't used because I used bind_param before that. In the example for mysql_bind_param they do it like me. Is the example also wrong or do I have to consider something special? -- Regards Julius Hacker http://www.julius-hacker.de [EMAIL PROTECTED] OpenPGP-Key-ID: 0x4B4A486E -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php