Page 1 of 1

EXECUTE and CONCAT in stored procedure conflict with fetch()

Posted: Wed Nov 08, 2006 1:13 pm
by J-Pro
feyd | Please use

Code: Select all

,

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]


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: 

[syntax="sql"]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; 

Earlier, when I writed like this:

Code: Select all

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;



, 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:[/syntax]

Code: Select all

Name Address Phone 

JohnAven ue,25(+1)4443377 99988
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: Select all

Name Address Phone 

John Avenue,25 (+1)444337799988



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: Select all

$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; 
} 

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


feyd | Please use

Code: Select all

,

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]