stored procedure Don’t know how to get values from stored procedure

Barand · January 14, 2020

If you are still determined to use a single stored procedure then you need a single query, therefore use a union. Note that I had to rearrange the order of your column selections in each part so that like types were in the same columns

stored procedure...

CREATE PROCEDURE `timeline`(
		IN `friend` varchar(255), 
		IN `session_id` varchar(255)
		)
BEGIN
SELECT * 
FROM (
      SELECT
      'U' as source
      , update_id as up1              
      , account_name as acc           
      , user_id_u as uid              
      , author as auth                
      , type
      , time                          
      , title as tit1                 
      , update_body as u_body         
	  FROM updates as up                    
	  WHERE author IN (friend,session_id)
	  LIMIT 5
	  ) a   
UNION
SELECT * 
FROM (
      SELECT
      'GP' as source
      , gp_id
      , gname 
      , author_id
      , author_gp
      , type
      , pdate
      , title
      , data
	  FROM group_posts as gp 
	  WHERE author_gp IN (friend,session_id)
      LIMIT 5
      ) b
ORDER BY time DESC;
END

To use

$res = $db->query("CALL timeline($friend, $session_id)");
while ($row = $res->fetch_assoc()) {
    // process row
}

narutofan · January 14, 2020

following @Barand advice i wrote a new function to test in but now the new problem is it brings only updates table result and not group_posts result.

here is the new function:

function totalprocedures($conn,$friend)
{
    try{
  $sql=
"select * FROM updates where `author`  ='".$friend."'   order by time desc limit 1 ;";
    

$sql.="select * FROM group_posts where `author_gp` ='".$friend."' order by pdate desc limit 1";
 
   
  $stmt=$conn->query($sql);
 // $row=$stmt->nextRowset();
 //$rows= $row->fetchAll(PDO::FETCH_BOTH);
 while($rows=$stmt->fetch()){
  return $rows;
  }
 
    } 
    catch (PDOException $e)
    {
        echo $e->getMessage();
    }
  };

i get the following result:

array(26) {
  ["update_id"]=>
  string(3) "302"
  [0]=>
  string(3) "302"
  ["update_body"]=>
  string(36) "Just type and press the post button."
  [1]=>
  string(36) "Just type and press the post button."
  ["url"]=>
  string(0) ""
  [2]=>
  string(0) ""
  ["time"]=>
  string(19) "2016-12-05 07:21:04"
  [3]=>
  string(19) "2016-12-05 07:21:04"
  ["host"]=>
  string(0) ""
  [4]=>
  string(0) ""
  ["vote_up"]=>
  string(1) "1"
  [5]=>
  string(1) "1"
  ["vote_down"]=>
  string(1) "0"
  [6]=>
  string(1) "0"
  ["title"]=>
  string(22) "You know how it works."
  [7]=>
  string(22) "You know how it works."
  ["user_id_u"]=>
  string(3) "173"
  [8]=>
  string(3) "173"
  ["account_name"]=>
  string(9) "wolverine"
  [9]=>
  string(9) "wolverine"
  ["author"]=>
  string(9) "wolverine"
  [10]=>
  string(9) "wolverine"
  ["type"]=>
  string(1) "a"
  [11]=>
  string(1) "a"
  ["data"]=>
  string(0) ""
  [12]=>
  string(0) ""
}

(still don't know what he meant by normal queries)

Barand · January 14, 2020

On 1/12/2020 at 9:21 PM, Barand said:

That's because, by default, MySQL will not run multiple queries.

Barand · January 14, 2020

29 minutes ago, narutofan said:

normal queries

One that does not involve a stored procedure

narutofan · January 14, 2020

40 minutes ago, Barand said:

On 1/13/2020 at 2:51 AM, Barand said:

That's because, by default, MySQL will not run multiple queries.

then how come these ppl can run multiple normal queries and get results pl check this stackoverflow page for my confusion and advice on the same as a gut feeling i know that there is a way out there to produce both the tables result.

Edited January 14, 2020 by narutofan

Barand · January 14, 2020

On 1/12/2020 at 9:21 PM, Barand said:

That's because, by default, MySQL will not run multiple queries. You can turn on multiple client queries but that will probably give you even more problems.

Perhaps you should also have read the rest of that reply.

P.S.

+-----------------------------------------------------------------------------+
|                                Multiple queries                             |
+----------------------------------------+------------------------------------+
|              CONS                      |               PROS                 |
+----------------------------------------+------------------------------------+
|                                        |                                    |
|   1 - more complicated to use          |                                    |
|                                        |        Can't think of any          |
|   2 - they open wide the door to some  |      significant advantages        |
|       seriously catastrophic hacks     |                                    |
|                                        |                                    |
+----------------------------------------+------------------------------------+

Edited January 14, 2020 by Barand

narutofan · January 15, 2020

finally i got the output to separate both the tables results:

sample output:

code that i tried is to separate the queries in a array and call it inside a function and returned the results in an array like this, Hope it might help someone in the future:

function totalprocedures($conn,$v)
{
    
        try {
      $sqlArray= array(
 
     array(
         "query"=> "select * FROM updates where `author`  =?   order by time desc limit 1 ",
          "bindvalue"=> array($v)
         ),
        array(
         "query"=>"select * FROM group_posts where `author_gp` =? order by pdate desc limit 1",
         "bindvalue"=>array($v)
         
         
     )
     );
     
    

do
{
   $stmt=$conn->prepare($sqlArray[0]["query"]);
           $stmt->execute($sqlArray[0]['bindvalue']);
        
           $rows=$stmt->fetchAll();  

    
} while($stmt->nextRowset());        
            

   
        
        
           do
        {
           $stmt1=$conn->prepare($sqlArray[1]["query"]);
           $stmt1->execute($sqlArray[1]['bindvalue']);
           $rows1=$stmt1->fetchAll();
           return array($rows,$rows1);
        }while($stmt1->nextRowset());
       
       }   catch (Exception $exc) {
            echo $exc->getMessage();
        }      
        }

@Barand and @all thanks for your help , pl forgive me if i looked ignorant

Barand · January 15, 2020

As I said, you can not execute queries like this ...

$sql =  "select * FROM updates where `author`  ='".$friend."'   order by time desc limit 1 ;";
$sql .= "select * FROM group_posts where `author_gp` ='".$friend."' order by pdate desc limit 1";

… where you are attempting to put multiple queries in a single string.

However I was wrong regarding the stored procedure, for which I apologise. After further experimentation, I have found that several queries inside a procedure will work...

CREATE PROCEDURE `timeline`(
            IN theauthor VARCHAR(255)
            )
BEGIN
    SELECT
        update_id              
      , account_name           
      , user_id_u            
      , author           
      , type
      , time                          
      , title               
      , update_body  
    FROM updates                    
    WHERE author = theauthor
    LIMIT 5;

    SELECT
        gp_id
      , gname 
      , author_id
      , author_gp
      , type
      , pdate
      , title
      , data
      FROM group_posts 
      WHERE author_gp = theauthor
      LIMIT 5;
END

To process (PDO) ...

$results = $db->query("CALL timeline($friend)");
do {
    $timeline_data[] = $results->fetchAll();
} while ($results->nextRowset());

//view results
echo '<pre>', print_r($timeline_data, 1), '</pre>';

Alternatively, there is the function you last posted (rewritten)

function totalprocedures($conn,$v)
{
    $results = [];
    
    $sqlArray = [

        [
             "query"     => "SELECT * FROM updates WHERE `author` = ? ORDER BY time DESC LIMIT 1 ",
             "bindvalue" => [$v]
        ],
        [
             "query"     =>"SELECT * FROM group_posts WHERE `author_gp` = ? ORDER BY pdate DESC LIMIT 1",
             "bindvalue" => [$v]
        ]
    ];

    foreach ($sqlArray as $qry) {
        $stmt=$conn->prepare($qry["query"]);
        $stmt->execute($qry['bindvalue']);
        $results[] = $stmt->fetchAll();
    }
    return $results;   
}

narutofan · January 16, 2020

On 1/15/2020 at 3:46 PM, Barand said:

I apologise.

@Barandno need to apologise ,everybody make mistakes.

i have tweaked the query & code to prevent repeated values from populating the timeline for some silly mistake that i made its not working and i couldn't figure it out.

here is the code:



function totalprocedures($conn, $v, $update_id1, $gp_id1) {
    foreach ($update_id1 as $v1) {


        $unique_up = array_unique($v1);
        $implode1 = implode(",", $unique_up);
    }

    foreach ($gp_id1 as $j => $v2) {



        $unique_gp = array_unique($v2);
        $implode2 = implode(",", $unique_gp);
    }

    foreach ($v as $i) {

        $implode_v = "'" . implode("','", $v) . "'";
    }
//return print_r($implode_v);echo '<implode1:-> ';print_r($implode1);echo 'implode2:-';print_r($implode2);
    $imp1 = implode(',', array_fill(1, count($v), '?'));
    $imp2 = implode(',', array_fill(1, count($v), '?'));
    $imp_u = implode(',', array_fill(1, count($v1), '?'));
    $imp_g = implode(',', array_fill(1, count($v2), '?'));
    $sqlArray = array(
        array(
            "query" => "select * FROM updates where `author`  in({$imp1})  and update_id not in ({$imp_u}) order by time desc limit 1 ",
            "bindvalue" => $implode_v,
            "stopvalue" => $implode1
        ),
        array(
            "query" => "select * FROM group_posts where `author_gp` in({$imp2}) and gp_id not in({$imp_g}) order by pdate desc limit 1",
            "bindvalue" => $implode_v,
            "stopvalue" => $implode2
        )
    );



    do {
        try {
            $stmt = $conn->prepare($sqlArray[0]["query"]);
            $stmt1 = $conn->prepare($sqlArray[1]["query"]);

            $stmt->execute([$sqlArray[0]['bindvalue'], $sqlArray[0]['stopvalue']]);
            $rows = $stmt->fetchAll();

            $stmt1->execute([$sqlArray[0]['bindvalue'], $sqlArray[0]['stopvalue']]);
            $rows1 = $stmt1->fetchAll();
             return print_r($sqlArray);

            return print_r([$rows, $rows1]);
        } catch (Exception $exc) {
            echo $exc->getMessage();
        }
    } while ($stmt->nextRowset() && $stmt1->nextRowset());
}

The $sqlArray prints like this:

Array ( 
[0] => Array (
[query] => select * FROM updates where `author` in(?,?,?,?) and update_id not in (?,?,?,?,?) order by time desc limit 1
[bindvalue] => 'wolverine','aboutthecreator','jayson','shan2batman'
[stopvalue] => 302,396,307,386, ) 
[1] => Array ( 
[query] => select * FROM group_posts where `author_gp` in(?,?,?,?) and gp_id not in(?,?,?) order by pdate desc limit 1 
[bindvalue] => 'wolverine','aboutthecreator','jayson','shan2batman'
[stopvalue] => 104,91, )

And i get an empty array like this if i run the function:

Array ( [0] => Array ( ) [1] => Array ( ) )

Barand · January 16, 2020

The array you pass to the execute should contain an element for each placeholder in the query.

ie 9 elements for the first query , 7 for the second.

It looks like yours contain two string elements, the bindvalue and the stopvalue.

narutofan · January 16, 2020

@Barand so, i should pass arrays is what you suggest sir ?.

Barand · January 16, 2020

You need to end up with something like

$stmt->execute( [ 'wolverine', 'aboutthecreator', 'jayson', 'shan2batman', 104, 91, 123 ] );

so if $bindvalue and $stopvalue are both arrays then you can merge them.

$stmt->execute( array_merge($bindvalue, $stopvalue) );

Make sure the number of values in the array matches the number of "?" in the query.

narutofan · January 16, 2020

thanks @Barand and others who helped me to make it work. it now works now flawlessley.

Sign In

stored procedure Don’t know how to get values from stored procedure

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information