Jump to content

Don’t know how to get values from stored procedure


narutofan

Recommended Posts

 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:

574044732_Screenshotfrom2020-01-1209-38-06.thumb.png.74ac4c848fa78416f2857c7509122b35.png

 how do i do this inside a php class function.

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

 

Link to comment
Share on other sites

@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();
        }
    }

 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ;

 

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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

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.

 

Link to comment
Share on other sites

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. ☹️

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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.

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.