narutofan

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

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

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

finally i got the output to separate both the tables results: sample output: Array ( [0] => Array ( [0] => Array ( [update_id] => 396 [0] => 396 [update_body] => <div>Praesent porttitor, nulla vitae posuere iaculis, arcu nisl dignissim dolor, a pretium mi sem ut ipsum. Morbi ac felis.</div> <div>&nbsp;</div> <div>Etiam sit amet orci eget eros faucibus tincidunt. Pellentesque commodo eros a enim.</div> <div>&nbsp;</div> <div>Cras ultricies mi eu turpis hendrerit fringilla. Cras id dui.<a class="btn btn-link" href="hash_sys.php?tag=bart"> !bart </a></div> [1] => <div>Praesent porttitor, nulla vitae posuere iaculis, arcu nisl dignissim dolor, a pretium mi sem ut ipsum. Morbi ac felis.</div> <div>&nbsp;</div> <div>Etiam sit amet orci eget eros faucibus tincidunt. Pellentesque commodo eros a enim.</div> <div>&nbsp;</div> <div>Cras ultricies mi eu turpis hendrerit fringilla. Cras id dui.<a class="btn btn-link" href="hash_sys.php?tag=bart"> !bart </a></div> [url] => [2] => [time] => 2019-12-23 13:20:47 [3] => 2019-12-23 13:20:47 [host] => [4] => [vote_up] => 0 [5] => 0 [vote_down] => 0 [6] => 0 [title] => 1```--- [7] => 1```--- [user_id_u] => 142 [8] => 142 [account_name] => aboutthecreator [9] => aboutthecreator [author] => aboutthecreator [10] => aboutthecreator [type] => a [11] => a [data] => [12] => ) ) [1] => Array ( [0] => Array ( [gp_id] => 104 [0] => 104 [pid] => 0 [1] => 0 [gname] => MEP news [2] => MEP news [author_gp] => aboutthecreator [3] => aboutthecreator [type] => 0 [4] => 0 [title] => 3 Etiam feugiat lorem non metusIn consectetuer turpis ut velit [5] => 3 Etiam feugiat lorem non metusIn consectetuer turpis ut velit [data] => <div>Etiam iaculis nunc ac metus. !qwerty bPraesent egestas tristique nibh.</div><div><br></div><div>Fusce ac felis sit amet ligula pharetra condimentum. Integer ante arcu, !bart accumsan a, consectetuer eget, posuere ut, mauris.</div><div><br></div><div>Proin viverra, ligula sit amet ultrices semper, ligula arcu tristique sapien, a accumsan nisi mauris ac eros. Sed in libero ut nibh placerat accumsan.</div><div><br></div><div>Phasellus leo dolor, tempus non, auctor et, hendrerit quis, nisi. Sed a libero.</div> [6] => <div>Etiam iaculis nunc ac metus. !qwerty bPraesent egestas tristique nibh.</div><div><br></div><div>Fusce ac felis sit amet ligula pharetra condimentum. Integer ante arcu, !bart accumsan a, consectetuer eget, posuere ut, mauris.</div><div><br></div><div>Proin viverra, ligula sit amet ultrices semper, ligula arcu tristique sapien, a accumsan nisi mauris ac eros. Sed in libero ut nibh placerat accumsan.</div><div><br></div><div>Phasellus leo dolor, tempus non, auctor et, hendrerit quis, nisi. Sed a libero.</div> [pdate] => 2019-12-19 22:29:42 [7] => 2019-12-19 22:29:42 [vote_up] => 0 [8] => 0 [vote_down] => 0 [9] => 0 [group_id] => 25 [10] => 25 [author_id] => 142 [11] => 142 ) ) ) 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

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.

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)

then how do i do it???

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.

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. as one of the eldest members you shouldn't be making fun when ppl are learning. ☹️

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

@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.

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

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 can you help me with this. can you point where i'm going wrong.

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.

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

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

yes it does check the original post carefullly @gw1500se

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);

@Psycho here is a execution of the query you asked to run with ids instead of strings. select distinct gp.gp_id as gp1,gp.pid,gp.author_gp,gp.gname,gp.type as ty1,gp.title as tit1,gp.data as dat1,gp.pdate,gp.group_id,gp.author_id, up.update_id as up1,up.update_body,up.time,up.title as tit2,up.account_name,up.author,up.type as ty2,up.data as dat2 from group_posts as gp inner join updates as up on gp.author_id=up.user_id_u where gp.group_id=25 and gp.author_id=127 and up.update_id not in(386) order by time,pdate desc limit 0,5 here is the output's screenshot you can notice up1 and the rest of the table coming up with duplicate values eg)288

@Psycho i'm calling the update table values as duplicate records. The group posts show unique values as you mentioned sir. i'll try to do the changes you asked and post the output here. yes the group_posts is between multiple people(but the site isn't live its my hand you see in the posts from different acc's i created to test the code and query sir) the pid you see in group posts is comment to a parent data. if you see number instead of zero in pid column then it is likely to be a comment for a parent post ,thats , how a conversation goes in groups you comment to a parent post.. (i'm also posting the sql dump to see anything odd in it) @barand let me know if you see anything odd. Group_posts sql DUMP:- CREATE TABLE `group_posts` ( `gp_id` int(255) NOT NULL, `pid` varchar(16) NOT NULL, `gname` varchar(100) NOT NULL, `author_gp` varchar(255) NOT NULL, `type` enum('0','1') NOT NULL, `title` varchar(500) NOT NULL, `data` varchar(10000) NOT NULL, `pdate` datetime NOT NULL, `vote_up` int(255) NOT NULL, `vote_down` int(255) NOT NULL, `group_id` int(255) NOT NULL, `author_id` int(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `group_posts` ADD PRIMARY KEY (`gp_id`), ADD KEY `group_id` (`group_id`), ADD KEY `author_id` (`author_id`), ADD KEY `group_id_2` (`group_id`,`author_id`), ADD KEY `pid` (`pid`), ADD KEY `gp_id` (`gp_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `group_posts` -- ALTER TABLE `group_posts` MODIFY `gp_id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=105; -- -- Constraints for dumped tables -- -- -- Constraints for table `group_posts` -- ALTER TABLE `group_posts` ADD CONSTRAINT `group_posts_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`g_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `group_posts_user_id` FOREIGN KEY (`author_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; updates table DUMP:- SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `project` -- -- -------------------------------------------------------- -- -- Table structure for table `updates` -- CREATE TABLE `updates` ( `update_id` int(255) NOT NULL, `update_body` varchar(10000) NOT NULL, `url` varchar(100) NOT NULL, `time` datetime NOT NULL, `host` varchar(100) NOT NULL, `vote_up` int(255) NOT NULL, `vote_down` int(255) NOT NULL, `title` varchar(1000) NOT NULL, `user_id_u` int(255) NOT NULL, `account_name` varchar(255) NOT NULL, `author` varchar(255) NOT NULL, `type` enum('a','b','c') NOT NULL, `data` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Indexes for table `updates` -- ALTER TABLE `updates` ADD PRIMARY KEY (`update_id`), ADD KEY `user_id_fk_upd` (`user_id_u`), ADD KEY `user_id_fk_upd_2` (`user_id_u`), ADD KEY `user_id_u` (`user_id_u`), ADD KEY `user_id_u_2` (`user_id_u`), ADD KEY `update_id` (`update_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `updates` -- ALTER TABLE `updates` MODIFY `update_id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=399; -- -- Constraints for dumped tables -- -- -- Constraints for table `updates` -- ALTER TABLE `updates` ADD CONSTRAINT `updates_ibfk_1` FOREIGN KEY (`user_id_u`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

@Barand tried a new union query and got this response which is weird. query: (select DISTINCT up.update_id, up.update_body, up.time, up.title as tit2, up.account_name, up.author, up.type as ty2, up.data as dat2 from updates as up where up.author in('shan2batman', 'aboutthecreator') and up.update_id not in(381,393) order by time desc limit 0,5) UNION ( select distinct gp.gp_id as gp1, gp.pid, gp.author_gp, gp.gname, gp.type as ty1, gp.title as tit1, gp.data as dat1, gp.pdate from group_posts as gp join user as u on u.uname=gp.author_gp where gp.gname='MEP news' and u.uname='aboutthecreator' order by pdate DESC limit 0, 5)

@Barand sir if u need further data to work on i can arrange exporting the individual tables from my side. the call is yours.

@Barand i tried that combination in mysql and goit the same duplicate entry earlier. so, tried a new one incase you don't believe me see the latest execution of that query here. i get the same duplicate posts problem.

@Barand thanks for the soln. now i face a new problem with the query. it produces only one update_id which is duplicating all posts. i'll attach a screen shot from the phpmyadmin below and advice me on how to approach and solve this problem. here is the query: select distinct gp.gp_id as gp1,gp.pid,gp.author_gp,gp.gname,gp.type,gp.title,gp.data,gp.pdate,gp.group_id,gp.author_id, u.avatar,u.user_id , up.update_id as up1,up.update_body,up.time,up.title,up.account_name,up.author,up.type,up.data from group_posts as gp join user as u on u.uname=gp.author_gp join updates as up on u.uname=up.author where gp.gname='MEP news' and up.update_id >391 and up.author in("shan2batman", "aboutthecreator") order by time,pdate desc limit 0,5 here is the screenshot: