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

[narutofan]

 wrote a stored procedure this morning and i don’t know how to get the values out of it through a class function in php or phpmyadmin.

here is what i wrote :

public function totalProcedures($friend_name,$session_id) 
{ /* *query to fetch stored procedure */ 
try { 
//executing the stored procedure 
$sql_sp="CALL timeline (:friend, :session,@updates, @group_posts)";
$stmt_sp= $this->_db->prepare($sql_sp); 
$stmt_sp->bindValue(":friend",$friend_name);
$stmt_sp->bindValue(":session",$session_id); 
$stmt_sp->execute();
$rows=$stmt_sp->fetch(PDO::FETCH_ASSOC); 
$stmt_sp->closeCursor(); // closing the stored procedure 
//trying to get values from OUT parameters.
$stmt_sp_2=$this->_db->prepare("select @updates,@group_posts");
$stmt_sp_2->execute();
return $stmt_sp_2->fetch(PDO::FETCH_ASSOC);
} catch (PDOException $ei)
{
echo $ei->getMessage(); } 
}

can someone helpme how to get results.

here is the storedprocedure:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `timeline`(IN `friend` VARCHAR(255), IN `session_id` VARCHAR(255), OUT `updates` VARCHAR(62555), OUT `group_posts` VARCHAR(62555))
BEGIN


select * FROM updates where author in (friend,session_id)  order by time desc limit 5;



select * FROM group_posts where author_gp in (friend,session_id) order by pdate desc limit 5;
END$$
DELIMITER ;

i get the result in php myadmin as follows:

 how do i do this inside a php class function.

CALL timeline('shan2batman','aboutthecreator', @updates, @group_posts);

 

[gw1500se]

This is really a MySQL question. Your stored procedure has no IN/OUT statements. Perhaps this will help. Search engines are your friend.

[narutofan]

yes it does check the original post carefullly @gw1500se

[NotionCommotion]

Are you using namespace?  If so, you will need to use \PDO::FETCH_ASSOC instead of PDO::FETCH_ASSOC unless you have a PDO alias.

Are you getting errors? Do you get any content or just with the stored procedure?  Try a simple query such as SELECT 123 or select just from some table.

[narutofan]

@NotionCommotion now i get results from table updates and not from group_updates what to do. i'll give the code can you troubleshoot it as it is frying my brain.

Updates:

 public function totalProcedures($friend_name, $session_id) {
        /*
         * query to fetch stored procedure 
         */
        try {
            //executing the stored procedure
            $sql_sp = "CALL timeline (:friend,:session)";
            $stmt_sp = $this->_db->prepare($sql_sp);
            $stmt_sp->bindValue(":friend",$friend_name);
            $stmt_sp->bindValue(":session",$session_id);
            $stmt_sp->execute();
          
do {
    try {
       $rows = $stmt_sp->fetchAll(PDO::FETCH_ASSOC);
       if ($rows) {
       return $rows;     
       } else {
           echo die().'no data try rows';   
       }
       
    } catch (PDOException $exc) {
        echo $exc->getMessage();
    }

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

        } catch (PDOException $ei) {
            echo $ei->getMessage();
        }
    }

 

[Barand]

Why are you preparing a query that is a call to a stored procedure?

The whole point of preparing queries is to isolate the data from the query code. Stored procedures already do that (the IN clauses are the same as binding parameters in a prepared query). Just use

$result = $this->db->query("CALL timeline($friend_name, $session_id)");

 

2 hours ago, narutofan said:

now i get results from table updates and not from group_updates

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.

Split it into two calls/ stored procedures (if you feel it really must be stored procedures)

[narutofan]

4 hours ago, Barand said:

Split it into two calls/ stored procedures (if you feel it really must be stored procedures)

how to do it in the same class function sir @Barand can you show me an example.

[narutofan]

after referring to this stackoverflow page i changed the code to:

    public function totalProcedures($friend_name, $session_id) {
        /*
         * query to fetch stored procedure 
         */
        try {
            //executing the stored procedure
            $sql_sp = 
                    "CALL timeline (:friend,:session)"
                    ;
            $stmt_sp = $this->_db->prepare($sql_sp);
            $stmt_sp->bindValue(":friend",$friend_name);
            $stmt_sp->bindValue(":session",$session_id);
            $stmt_sp->execute();
          $row=$stmt_sp->nextRowset();
do {
    try {
       $rows = $row->fetchAll(PDO::FETCH_ASSOC);
       if ($rows) {
       return $rows;     
       } else {
           echo die().'no data try rows';   
       }
       
    } catch (PDOException $exc) {
        echo $exc->getMessage();
    }

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

        } catch (PDOException $ei) {
            echo $ei->getMessage();
        }
    }

still not able to get results now even updates results is not coming up.

[narutofan]

@Barand can you help me with this. can you point where i'm going wrong.

[narutofan]

After changing the query to this i get the following error code:

SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns

sql query that was changed:

select update_id as up1,account_name as acc,user_id_u as uid,author as auth,time,title as tit1,update_body as u_body INTO updates FROM updates as up where author in (friend,session_id)  order by time desc limit 5 ;

select gp_id as gid,author_gp as auth2,type as ty2,title as tit2,data as dat2,pdate,author_id as authid INTO group_posts FROM group_posts as gp where author_gp in (friend,session_id) order by pdate desc limit 5 ;

 

[Barand]

8 hours ago, narutofan said:

@Barand can you help me with this. can you point where i'm going wrong.

How can I?

I have no idea what you are trying to do. I can see the method you are attempting to achieve your objective but I haven't clue about

• what that objective is, or
• why you have chosen this method.

[narutofan]

@Barandi want to retrieve the values of both the select statements and print it in my timeline thats the objective. 

Edited January 13, 2020 by narutofan

[Barand]

Why a stored procedure?

[narutofan]

@Barandbecause its easier and my tables have some problems for joined queries. plus i can get the values in two arrays which helps me a lot to separate them.

Edited January 13, 2020 by narutofan

[ginerjm]

Your tables have some problems for joined queries?  That sure sounds like a poor design.  Perhaps addressing that would solve not only this problem but a lot of future ones?  And then it would allow you to collect all of your data in one query results set and that would make your output algorithm easier.

And please don't say that you can't make time to fix this problem or that you only want to get this one thing working.  You have done something wrong and for people here to help you work around it would not only be a bad use of their time, it would encourage you to continue to do things in less than appropriate ways.  You have made a misstep here and you should step back and learn from it and gain the knowledge that comes with that.

[Barand]

9 minutes ago, narutofan said:

because its easier

LOL - yes, it's evident that you find it so much easier.

 

9 minutes ago, narutofan said:

my tables have some problems for joined queries

Seems like you really need to fix those problems - they won't go away on their own.

 

9 minutes ago, narutofan said:

plus i can get the values in two arrays

As you can with two queries, one for each table. But I guess you would find that so much more difficult!

[EDIT] If you putting them all in the one timeline, why would want to keep them separate? I would have thought you would want them all merged an sorted by date. 

Edited January 13, 2020 by Barand

[narutofan]

1 minute ago, Barand said:

Seems like you really need to fix those problems - they won't go away on their own.

i will try to fix them but the pressing problem at hand is executing the stored procedure. As it helps me to learn by the way sir @Barand

[Barand]

4 hours ago, narutofan said:

After changing the query to this i get the following error code:

SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns

 

Here's a simpler example to illustrate your problem

mysql> select id, name INTO @a FROM locations;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

The query is trying to select 2 columns into 1 variable so it throws that error.

Aha! so all we have to do is put the 2 columns into 2 variables?

mysql> select id, name INTO @a, @b FROM locations;
ERROR 1172 (42000): Result consisted of more than one row

Alas, you can't do that with a query returning many rows, so...

mysql> select id, name INTO @a, @b FROM locations LIMIT 1;
Query OK, 1 row affected (0.00 sec)

mysql> select @a, @b;
+------+-----------------+
| @a   | @b              |
+------+-----------------+
|    1 | Test Location 1 |
+------+-----------------+

Now we're getting somewhere - all you need to do is create a stored procedure and call it 10 times, each time selecting a different single row in to 7 variables.

Then after you've finished, put all those 70 variables into your timeline.

As you said, much easier than using two normal queries.

 

[narutofan]

so, @Barand basically i need to create more out parameters is what i understand from your examples and call them through the procedure like this:

begin

select id1, name1 INTO @a1, @b1 FROM location1 LIMIT 1; //not limit it to 5
select id2, name2 into @a2,@b2 FROM location2 limit 1;  //not limit it to 5
end //

then call it like:

call timeline('shan2batman','aboutthecreator',@a1,@b1,@a2,@b2);

is that correct sir.

26 minutes ago, Barand said:

As you said, much easier than using two normal queries.

as one of the eldest members you shouldn't be making fun when ppl are learning. ☹️

[Barand]

3 minutes ago, narutofan said:

as one of the eldest members you shouldn't be making fun when ppl are learning

No so much making fun of you but rather showing the folly of your approach.

Quote

Reductio Ad Absurdum

In logic, reductio ad absurdum (Latin for "reduction to absurdity"), also known as argumentum ad absurdum (Latin for "argument to absurdity"), apagogical arguments, negation introduction or the appeal to extremes, is a form of argument that attempts to establish a claim by showing that the opposite scenario would lead to absurdity or contradiction. It can be used to disprove a statement by showing that it would inevitably lead to a ridiculous, absurd, or impractical conclusion, or to prove a statement by showing that if it were false, then the result would be absurd or impossible. Traced back to classical Greek philosophy in Aristotle's Prior Analytics (Greek: ἡ εἰς τὸ ἀδύνατον ἀπόδειξις, lit. 'demonstration to the impossible', 62b), this technique has been used throughout history in both formal mathematical and philosophical reasoning, as well as in debate.

I've suggested a couple of times in this thread that you just use two normal queries. You could even use a single query with a union but you said you wanted the two results kept separate for some reason.

Yet despite any advice (which you always have a habit of ignoring) you insist on this "easier" stored procedure approach. 

[narutofan]

4 minutes ago, Barand said:

I've suggested a couple of times in this thread that you just use two normal queries.

since i'm still learning i just didn't want to sound stupid of asking the question how??? i'm sorry if i made you uneasy. 

so was my approach right when it comes to stored procedure.

how can i use two normal queries when ajax keeps on producing repeated values bcoz of loops in php while executing the script. its the reason i tried my hand in joins(but will reopen that thread once i finish with this stored procedure) result total failure becoz of poor design(dont know how to correct it).

these are the reasons why i might look ignorant sir @Barand as an elder i respec t you the most in this community thats why i'm pestering you don't get me wrong.

[Barand]

34 minutes ago, narutofan said:

so was my approach right when it comes to stored procedure.

No

[narutofan]

7 hours ago, Barand said:

No

then how do i do it???

[Barand]

13 hours ago, Barand said:

I've suggested a couple of times in this thread that you just use two normal queries. You could even use a single query with a union but you said you wanted the two results kept separate for some reason.

 

[ginerjm]

Barand - such patience....