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 99988Code: Select all
Name Address Phone
John Avenue,25 (+1)444337799988And 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;
}
}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]