Jump to content

EXECUTE and CONCAT in stored procedure conflict with fetch_array()


J-Pro

Recommended Posts

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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.