$objConn = setDbConn();
$strSQL = "CALL mp_proc_getHomePageProjNews('Chennai')";
if($objConn->multi_query($strSQL))
{
do
{
$l = 0;
/* store first result set */
if ($result = $objConn->use_result())
{
while($row = $result->fetch_row())
{
$arrTempResult[] = $row;
}
$arrResult[] = $arrTempResult;
unset($arrTempResult);
$result->close();
}
if($objConn->more_results())
{
}
} while ($objConn->next_result());
}
Daily Archives: September 27, 2012
Mysql Procedure Taking parameter
DROP PROCEDURE IF EXISTS proc_sample1;
CREATE PROCEDURE proc_sample1(IN pFruitId VARCHAR(255))
BEGIN
SET @strSQL = 'SELECT fruit_name
FROM fruits
WHERE fruit_id = ?';
SET @pFruitId = pFruitId;
PREPARE stmt FROM @strSQL;
EXECUTE stmt USING @pFruitId;
DEALLOCATE PREPARE stmt;
END
Alternate to Inner Join which prevents repetation of Records
create table fruits(fruit_id int, fruit_name VARCHAR(255));
create table type(fruit_id int, status VARCHAR(255));
INSERT INTO fruits(fruit_id, fruit_name)
values(101, 'Apple'),
(102, 'Mango'),
(103, 'Lemon'),
(104, 'Grape'),
(105, 'Orange');
INSERT INTO type(fruit_id, status)
values(101, 'Edible'),
(101, 'Sweet'),
(102, 'Edible'),
(103, 'Edible'),
(103, 'Salty'),
(103, 'Sour'),
(104, 'Sour');
When i Use Inner Join as Below the Output would be
To prevent the repetition of fruit name the queries are as follows
SELECT f.fruit_id, f.fruit_name
FROM fruits f
WHERE f.fruit_id in(SELECT DISTINCT fruit_id
FROM type);
SELECT DISTINCT t.Fruit_id, fruit_name
FROM fruits f RIGHT OUTER JOIN
type t ON F.Fruit_id = t.Fruit_id;
SELECT fruits.*
FROM fruits inner join
type on type.fruit_id = fruits.fruit_id
GROUP BY type.fruit_id;