[RESOLVED] Strange behaviour mysqli bind_param('is', $id, $id);

[knowj]

I have just come across a strange manner in which the bind_param method seems to be working within the mysqli stmt class.

Query contains:

$stmt = $db->prepare("SELECT `id` FROM `table` WHERE id=? or page=? LIMIT 1");

$id = 1;
//returns 0 rows even though id does equal 1
$stmt->bind_param('is', $id, $id);



$id = 1;
$page = $id;
//returns the correct row for `id`='1'
$stmt->bind_param('is', $id, $page);

My initial impression was that $id was being typecast by bind_param becuase I was passing a string to retrieve the row via the page field.

I then started using the int $id=1; I was still getting 0 results but a string typecast as an INT would return 1 which should still return id=1.

I removed the OR page=? and bind_param to 1 argument and it worked fine with just id=1. After adding it back in again 0 rows returned.

Strangely however adding $page = $id; to separate the variables got the query to return the desired results again.

Anyone any ideas/input onto what may be happening here?

[bradgrafelman]

Can you do a 'SHOW CREATE TABLE table' for us so we can see the exact table structure you're using? This does seem like strange behavior to me as well.

Also, show us what the row looks like that you're testing this with.

[knowj]

The code/structure is extremely simple which is why it has given me such a problem.

CREATE TABLE `gallery` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `intro` tinytext,
  `page` varchar(100) NOT NULL default '',
  `galleryImage` int(11) default NULL,
  `order` int(11) default NULL,
  `live` tinyint(4) default '1',
  `delete` tinyint(4) default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

INSERT INTO `gallery` (`id`,`title`,`intro`,`page`,`galleryImage`,`order`,`live`,`delete`)
VALUES
	(1, 'New gallery', 'A new gallery', 'new-gallery', 1, 1, 1, 0);

[halojoy]

I know that you can not use same
placeholder for two places is query:
like
SELECT * FROM table WHERE id=:id AND page=:id
bind_param(:id,...
But in your case I do not know why it do not work.
Because it looks like it should work.
I have no answer and will monitor this topic, until somebody finds the explanation.

MySQLi is a very good class for handling MySQL databases.
Only, in my opinion, [man]PDO[/man] is just a little bit better 🙂
http://php.net/manual/en/book.pdo.php
PDO is what I use now .... both for my MySQL and SQLite data.

🆒

[halojoy]

I found a bug report
reporting the same issue a couple of years ago:
http://bugs.php.net/44390

[QUOTE]Description:

Mysqli bind_param and bind_result functions are changing object member variables to be references with strange side affects.

a) I expect the object to keep the member variable types as is. Currently they change to reference variables with the result of strange side effects when you do not keep this in mind. We have to clone objects before using them for bindings, right now - this is a working workaround. I vote for a bug, at least it should be documented.

b) I expect binding on the same variable with different types working. Currently I can manage to get a memory access to arbitrary data, possibly leading to a segmentation fault or security violation. Again, I vote for a bug.[/QUOTE]
From attached example:

echo "Test 3: \n";

$stmt = $db->prepare("SELECT ? FOO, ? BAR");
var_dump($foo);
$stmt->bind_param("is", $foo->bar, $foo->bar);
var_dump($foo);
$stmt->bind_result($one, $two);
$stmt->execute();
$stmt->fetch();
$stmt->free_result();
echo("$one - $two\n\n");

[knowj]

Brilliant thanks. At least I know I'm not being a complete idiot.

I use mysqli over PDO as I know the environment that each system I build is going to run on. mysqli has a slight performance advantage over PDO as obviously it only has to/can work with one DBMS.

[knowj]

Update.

Just had a deeper look into the system and found if you bind the strings first then handle integers last (where possible). An int can be typecast as a string without loosing its integrity but a string looses its value when typecast as an integer.

$string='page1';

//works fine
$stmt->bind_param('si', $string, $string);

//doesn't work (string is typecast as an int by the 1st (i), 2nd argument ($string)
$stmt->bind_param('is', $string, $string);