i 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. The other examples from the site was not helpful to me.
here is what i wrote to get results:
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 bool(false) when i try to var_dump the second fetch st.
There is a suggestion here ( https://www.mysqltutorial.org/mysql-stored-procedures-return-multiple-values/ ) that you need to define each column that you are extracting as a separate OUT parameter, and you need to SELECT INTO that name. That does seem quite code-heavy, I’ve no idea if there’s a better way to do it.
@droopsnoot
do i have to just do like this : select gp_id, data,title,pdate e.tc from group_posts where ....
or like this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `timeline`(IN `friend` VARCHAR(255), IN `session_id` VARCHAR(255), OUT `updates` VARCHAR(62555), OUT `group_posts` VARCHAR(62555), OUT gp_id int (255),OUT data varchar(63655) ....e.tc)
BEGIN
select gp_id, data,title,pdate e.tc from group_posts where ...
and now i get the following output without any changes to the original function: array(2) { ["@updates"]=> NULL ["@group_posts"]=> NULL }
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 = $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();
}
}
still not able to get results now even updates results is not coming up can you point out where i’m going wroing.
Ajax comes with a message of 200 but no results now.
BEGIN
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 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 FROM group_posts as gp where author_gp in (friend,session_id) order by pdate desc limit 5 ;
END
And yet, that page I pointed to earlier seems to always use “INTO” to direct the output into the “OUT” parameters that are defined, so I think that might be important. Otherwise I can’t see how anything knows which of your OUT parameters would contain which value.
Is there much point in this being a stored procedure? I don’t know a lot about them, but it doesn’t seem like it’s saving you much compared to just calling the two queries directly.
What happens if you do this:
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 INTO group_posts FROM group_posts as gp where author_gp in (friend,session_id) order by pdate desc limit 5 ;
I don’t know whether naming your OUT parameters the same as the tables you are selecting from is enough to retrieve the results, but it seems not.
And what about if you try it with just one of the queries, and try to populate one of the output parameters? The error message suggests that it is trying to put both result sets into the same output variable, and can’t do it because it would need to have the same number of columns in each for that to work.
Try it this way, look at the parts of the error message:
SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns
SQLSTATE[21000]: I have no idea what that is, but I could try googling it I suppose.
Cardinality violation: 1222 I know “violation” can’t be a good thing. I know “cardinality” has to do with numbers, something to do with position. And I have no idea what “1222” might signify. OK, another I could try googling.
used SELECT statements have a different number of columns
This is something I can do without googling, I can look at the code. I’m not sure about the “user” of the SELECT statements, but I understand two things
“SELECT statements” is in plural - is there more than one SELECT in the code?
what “different number of columns” means is obvious to me, in any case, here’s a contrived example
SELECT
name
, age
, gender
FROM existing_clientele
UNION
SELECT
name
, age
FROM potential_clientele
MySQL wants to return results with both three and two columns. MySQL is not happy. MySQL confused. MySQL can not do colspan. MySQL does not compute. Error Will Robinson, Error.
Please excuse my attempt at humor. The point is, if you look at the SELECTs can you see a miscount? eg.
SELECT col_one, col_two, col_three ... FROM ...
SELECT col_two, col_three ... FROM ...
before i try the output by changing it to equal no of columns i took a look at the error SQLSTATE[2100] in this stack overflow page and found a new thing like setting views to get multiple rows, @Mittineague can you tell me what thats about and can i solve my problem by doing it.If so how can i do it.
I’m not certain, but I suspect that even though the “SQLSTATE[21000]” is in common, the “unequal rows” and “unequal columns” are different errors. If you looked and know there are an unequal number of columns being SELECTed from each table and the message is suggesting that is the problem, my bet would be that is what should be addressed. At least as a start. Don’t let other possible unrelated errors distract you until you’ve ruled out the more likely causes.
@Mittineague i’m sorry i have droppped my plans to using stored procedure to populate my test sites’s timeline. i have made changes and created a new function like this:
function totalprocedures($conn,$v,$implode1,$implode2)
{
try {
$sqlArray= array(
array(
"query"=> "select * FROM updates where `author` =:v and update_id not in (0,:implode1) order by time desc limit 1 ",
"bindvalue"=> array($v)
),
array(
"query"=>"select * FROM group_posts where `author_gp` =:v gp_id not in(0,:implode2) order by pdate desc limit 1",
"bindvalue"=>array($v)
)
);
do
{
$stmt=$conn->prepare($sqlArray[0]["query"]);
$stmt->bindValue(":v",$sqlArray[0]['bindvalue']);
$stmt->bindValue(":implode1",$implode1);
$stmt->execute();
$rows=$stmt->fetchAll();
} while($stmt->nextRowset());
/* while( $rows1=$stmt1->fetchAll()){
return array($rows, $rows1);
}*/
do
{
$stmt1=$conn->prepare($sqlArray[1]["query"]);
$stmt1->bindValue(":v",$sqlArray[1]['bindvalue']);
$stmt1->bindValue(":implode2",$implode2);
$stmt1->execute();
$rows1=$stmt1->fetchAll();
return array($rows,$rows1);
}while($stmt1->nextRowset());
} catch (Exception $exc) {
echo $exc->getMessage();
}
}
now i face a new problem i don’t know how to bind comma separated value inside a prepared statement can you help me with this.
do
{
$stmt=$conn->prepare($sqlArray[0]["query"]);
$stmt->bindValue(":v",$sqlArray[0]['bindvalue']);
$stmt->bindValue(":implode1",$implode1);
$stmt->execute();
$rows=$stmt->fetchAll();
} while($stmt->nextRowset());
you can do the “prepare” before the loop. Though, as you are no longer using a stored procedure, I’m not sure that using nextRowSet() is appropriate here. I admit I’d never heard of it before, but a quick look at the doc suggests it is for retrieving multiple sets of results from stored procedures.
