I have a problem with mysqli_prepare function, it doesn't work properly calling a stored procedure which contains prepare itself.
PHP example code: <? $db = mysqli_connect("localhost","user","password","real"); $stmt = mysqli_prepare($db, "call testproc_safe2_prep(?,?)"); mysqli_stmt_bind_param($stmt,'ss', $_GET['param1'],$_GET['param2']); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $name); while (mysqli_stmt_fetch($stmt)) { echo $name; echo " "; } mysqli_stmt_close($stmt); ?> My procedure: PROCEDURE `real`.testproc_safe2_prep(IN param1 VARCHAR(255), IN param2 VARCHAR(255)) BEGIN SET @query = 'SELECT field1 FROM test_table WHERE 1=1'; IF param1 IS NOT NULL Then SET @query = CONCAT(@query, ' AND criteria1 = ', param1); END IF; IF param2 IS NOT NULL Then SET @query = CONCAT(@query, ' AND criteria2 = ', param2); END IF; PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; END When i open php script i get smth like this: http://i013.radikal.ru/1101/c3/362690f7380d.jpg I have another procedure, that does all the same as the first one: PROCEDURE `real`.testproc_safe2_case(IN param1 VARCHAR(255), IN param2 VARCHAR(255)) BEGIN SELECT field1 FROM test_table WHERE 1 = 1 AND CASE WHEN param1 IS NULL THEN 1 ELSE CASE WHEN criteria1 = param1 THEN 1 ELSE 0 END END = 1 AND CASE WHEN param2 IS NULL THEN 1 ELSE CASE WHEN criteria2 = param2 THEN 1 ELSE 0 END END = 1; END So, when i change this way: $stmt = mysqli_prepare($db, "call testproc_safe2_case(?,?)"); everything works fine: http://s52.radikal.ru/i138/1101/26/2e29daf0daa4.jpg The procedures do the same thing, first using prepare and the second using case. But why first doesn't work with mysqli_prepare? Example of launching using console: mysql> call testproc_safe2_prep(1,1); +-----------+ | field1 | +-----------+ | Kuznetsov | +-----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> call testproc_safe2_case(1,1); +-----------+ | field1 | +-----------+ | Kuznetsov | +-----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select * from test_table; +----+-----------+-----------+-----------+ | id | field1 | criteria1 | criteria2 | +----+-----------+-----------+-----------+ | 1 | Ivanov | 0 | 0 | | 2 | Petrov | 0 | 1 | | 3 | Sidorov | 1 | 0 | | 4 | Kuznetsov | 1 | 1 | +----+-----------+-----------+-----------+ 4 rows in set (0.00 sec) mysql> select version(); +---------------------+ | version() | +---------------------+ | 5.0.45-community-nt | +---------------------+ 1 row in set (0.00 sec) How i can use mysqli_prepare to work properly with the first procedure? Maybe it was fixed in new versions or it's not a bug?