EXECUTE and CONCAT in stored procedure conflict with fetch_array()

[J-Pro]

Good day dear MySQL and PHP gurus!

For my convenience I've decided to work with the DB through stored procedures. And for selection of different records from ONE table by some different criteria I've decided to make only one stored procedure, but with few arguments. It looks like:

[code]CREATE PROCEDURE `GetAllDiscounts`(IN activitytypeid BIGINT, IN languageid BIGINT)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE statement VARCHAR(1024);
DECLARE lang VARCHAR(500);
DECLARE activitytype VARCHAR(500);

IF (activitytypeid IS NULL) THEN SET activitytype := ' AND 1=1';
ELSE SET activitytype := CONCAT(' AND AT.`ActivityTypeID` = ', activitytypeid);
END IF;

IF (languageid IS NULL) THEN SET lang := ' AND 1=1';
ELSE SET lang := CONCAT(' AND D.`LanguageID` = ', languageid);
END IF;

SET @statement :=
CONCAT('SELECT D.`DiscountID`,
D.`ActivityTypeID`,
AT.Name,
AT.Description,
D.`ArticleName`,
D.`PathToPhoto`,
D.`PathToFirstPhoto`,
D.`Value`,
D.`OldPrice`,
D.`NewPrice`,
D.`StartDate`,
D.`EndDate`,
D.`Description`,
D.`RegisteredOn`,
D.`UpdatedBy`,
D.`UpdatedOn`,
D.`LanguageID`,
L.Name AS LanguageName,
L.Initials AS LanguageInitials

FROM Discounts D
JOIN ActivityTypes AT ON D.`ActivityTypeID` = AT.`ActivityTypeID`
JOIN Languages L ON D.LanguageID = L.LanguageID
WHERE 1 = 1 ', lang, activitytype);

PREPARE stmt FROM @statement;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END;[/code]


Earlier, when I writed like this:

[code]CREATE PROCEDURE `GetAllDiscounts`(IN activitytypeid BIGINT, IN languageid BIGINT)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN

SELECT D.`DiscountID`,
D.`ActivityTypeID`,
AT.Name,
AT.Description,
D.`ArticleName`,
D.`PathToPhoto`,
D.`PathToFirstPhoto`,
D.`Value`,
D.`OldPrice`,
D.`NewPrice`,
D.`StartDate`,
D.`EndDate`,
D.`Description`,
D.`RegisteredOn`,
D.`UpdatedBy`,
D.`UpdatedOn`,
D.`LanguageID`,
L.Name AS LanguageName,
L.Initials AS LanguageInitials

FROM Discounts D
JOIN ActivityTypes AT ON D.`ActivityTypeID` = AT.`ActivityTypeID`
JOIN Languages L ON D.LanguageID = L.LanguageID
LEFT OUTER JOIN DiscountsTradeCenters DTC ON D.`DiscountID` = DTC.`DiscountID`
LEFT OUTER JOIN `tradecenters` TC ON DTC.`TradeCenterID` = TC.`TradeCenterID`;

END;[/code]


, it was necessary to make conditions(IF THEN) few times in a function and copy the same SELECT block but with differend WHEN condition. Sure it's easier and better to use the first variant, with EXECUTE.

This procedure is called from PHP.

BUT, here is a problem: the first case can't fetch normally from PHP, it shows that in result set are only column names, but one time, when there was and data, it was messed up from different columns except column names. Like:

[code]Name Address Phone

JohnAven ue,25(+1)4443377 99988[/code]


After that, I've changed NOTHING in my PHP-code, but just changed stored procedure from the first case to the second one(without EXECUTE and CONCAT, but with multiple SELECT blocks, IF blocks and different WHEN conditions). And it helped, all data was fetched normally like:

[code]Name Address Phone

John Avenue,25 (+1)444337799988[/code]



And want to mention one more thing: from MySQL query window(console), using just CALL statement, these two cases work in the same way - result sets are both rights. Then why these result can't exist in stmt in the same way?


Maybe I should make something after EXECUTE statement in the stored procedure to solve the problem?

Here is my PHP code:
[code]$procedureCallStatement = "CALL GetAllDiscounts(?, ?)";

$stmt = $mysqli->prepare($procedureCallStatement);
$stmt->bind_param('ii', $activityTypeID, $languageID);
$res = $stmt->execute();

if($res)
{
$meta = $stmt->result_metadata();
if(!$meta) return false;

$retArr = array(array());
$colNames = array();
$tmpArray = array();

while ($field = $meta->fetch_field())
{ // array of column names

$colNames[] = $field->name;
}

$retArr[0] = $colNames; // the first line of matrix

// making associative array
$j = 1;
while($assocArr = mps_fetch_assoc($stmt))
{

$namesQuan = count($colNames);

for($i = 0; $i < $namesQuan; $i++)
{
$tmpArray[$i] = $assocArr[$colNames[$i]];
}

$retArr[$j] = $tmpArray;

$j++;
}


}

// #######################################################################################################
public function mps_fetch_assoc(&$stmt)
{
/* Sample test code (take the database schema for granted here):

$db = new mysqli('hostname', 'user', 'pass', 'dbname');
$statement = $db->prepare("SELECT * FROM products WHERE company_id=?");
$statement->bind_param('s', $id);
$id='bol';
$statement->execute();

while($x = mps_fetch_assoc($statement))
{
echo $x["prod_id"] . "<br>";
}
*/

$meta = $stmt->result_metadata();
$retval[] = &$stmt;
$tmp;
$names;

while ($field = $meta->fetch_field())
{ // column names array
$names[] = $field->name;
}

$tmp = $names;

for ($c = 0; $c < count($names); $c++)
{ // copy column names into another array ("Id", "Name", .....)
$retval[] = &$tmp[$c];
}

call_user_func_array("mysqli_stmt_bind_result", $retval);

if ($stmt->fetch())
{
$assoc_array;
for ($c = 0; $c < count($names); $c++)
{
$assoc_array[$names[$c]] = $retval[$c + 1];
}

return $assoc_array;
}
else
{
return FALSE;
}

}[/code]

So, after that I get $retArr - matrix which contain column names as the first line and the rest lines - all result set.
But why I get such error - can't imagine... :(


Very strange.... I need help, please.


Thanks VERY MUCH in advance!