-1

i'm currently fetching a joined query to get this output:

Array
(
    [gp_id] => 103
    [pid] => 0
    [author_gp] => aboutthecreator
    [gname] => MEP news
    [ty1] => 0
    [tit1] => 2
    [dat1] => <div>Etiam iaculis nunc ac metus. Praesent egestas tristique nibh.</div>
<div>&nbsp;</div>
<div>Fusce ac felis sit amet ligula !qwerty pharetra condimentum. Integer ante arcu, accumsan a, consectetuer eget, posuere ut, mauris.</div>
<div>&nbsp;</div>
<div>Proin viverra, ligula sit amet ultrices semper, ligula arcu tristique sapien !bart, a accumsan nisi mauris ac eros. Sed in libero ut nibh placerat accumsan.</div>
<div>&nbsp;</div>
<div>Phasellus leo dolor, tempus non, auctor et, hendrerit quis, nisi. Sed a libero. !qwerty</div>
    [pdate] => 2019-12-19 22:28:04
    [group_id] => 25
    [author_id] => 142
    [up1] => 381
    [update_body] => Lorem ipsum dolor sit amet,consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Lorem ipsum dolor sit amet, <a href="home.php?u=shan2batman">@shan2batman</a><br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam voluptua. !qwerty Lorem ipsum dolor sit amet, consetetur sadipscing elitr, Lorem ipsum dolor sit amet, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, consetetur sadipscing elitr, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. no sea takimata sanctus est Lorem ipsum dolor sit amet. <br /><br />Lorem ipsum dolor sit amet,sed diam voluptua. Lorem ipsum dolor sit amet, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, consetetur sadipscing elitr, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam voluptua. Stet clita kasd gubergren, Lorem ipsum dolor sit amet, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, <br /><br />Lorem ipsum dolor sit amet,At vero eos et accusam et justo duo dolores et ea rebum. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, sed diam voluptua. Stet clita kasd gubergren, <br /><br />
    [time] => 2018-11-10 22:58:36
    [tit2] => qwertyu
    [account_name] => aboutthecreator
    [author] => aboutthecreator
    [ty2] => a
    [dat2] => 
)

how to change this in to this:

Array
(
    [gp_id] => 103
    [pid] => 0
    [author_gp] => aboutthecreator
    [gname] => MEP news
    [ty1] => 0
    [tit1] => 2
    [dat1] => <div>Etiam iaculis nunc ac metus. Praesent egestas tristique nibh.</div>
<div>&nbsp;</div>
<div>Fusce ac felis sit amet ligula !qwerty pharetra condimentum. Integer ante arcu, accumsan a, consectetuer eget, posuere ut, mauris.</div>
<div>&nbsp;</div>
<div>Proin viverra, ligula sit amet ultrices semper, ligula arcu tristique sapien !bart, a accumsan nisi mauris ac eros. Sed in libero ut nibh placerat accumsan.</div>
<div>&nbsp;</div>
<div>Phasellus leo dolor, tempus non, auctor et, hendrerit quis, nisi. Sed a libero. !qwerty</div>
    [pdate] => 2019-12-19 22:28:04
    [group_id] => 25
    [author_id] => 142
 )
array(   [up1] => 381
    [update_body] => Lorem ipsum dolor sit amet,consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Lorem ipsum dolor sit amet, <a href="home.php?u=shan2batman">@shan2batman</a><br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam voluptua. !qwerty Lorem ipsum dolor sit amet, consetetur sadipscing elitr, Lorem ipsum dolor sit amet, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, consetetur sadipscing elitr, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. no sea takimata sanctus est Lorem ipsum dolor sit amet. <br /><br />Lorem ipsum dolor sit amet,sed diam voluptua. Lorem ipsum dolor sit amet, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, consetetur sadipscing elitr, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam voluptua. Stet clita kasd gubergren, Lorem ipsum dolor sit amet, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, <br /><br />Lorem ipsum dolor sit amet,At vero eos et accusam et justo duo dolores et ea rebum. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, sed diam voluptua. Stet clita kasd gubergren, <br /><br />
    [time] => 2018-11-10 22:58:36
    [tit2] => qwertyu
    [account_name] => aboutthecreator
    [author] => aboutthecreator
    [ty2] => a
    [dat2] => 
)

in PHP using PDO currently i have the following class to get the above output how can i change it to get the below output using PHP PDO:

public function totalUpdates($friend,$session,$var_id, $g, $load) 
{
    try{
 $sql2="select distinct g.gp_id ,g.pid,g.author_gp,g.gname,g.type as ty1,g.title as tit1,g.data as dat1,g.pdate,g.group_id,g.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 g "
            . "join updates as up on g.author_id=up.user_id_u"
            . " where "
            . "g.gname=:g "
            . "and up.update_id not in(:update_id_all)  and up.author in(:friend, :session) order by time,pdate desc limit $load,5";
$stmth=  $this->_db->prepare($sql2);//Check here syntax of $db 
      
    $stmth->bindValue(":friend",$friend);
    $stmth->bindValue(":session",$session);
    $stmth->bindValue(":update_id_all",$var_id);
   $stmth->bindValue(":g",$g);
    $stmth->execute();
   return $stmth->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $ei){
        echo $ei->getMessage();
    }
}

if anyone knows hows how to do it pl let me know.(i'm not an expert in PHP or MYSQL)

UPDATE:-

following @adams advice i created a stored procedure but the problem is now i don't know how to fetch values through php class function.

i get the following result if i run this in phpmyadmin(click on the pic to enlarge):

enter image description here

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

now this is the php class function i wrote to fetch results but it isn't while following this example

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 var_dump( $stmt_sp_2->fetch(PDO::FETCH_ASSOC));
 
} 
catch (PDOException $ei)
{
  echo $ei->getMessage();
    
}    
    
}

instantiating the class:

$totalProcedures=$project->totalProcedures($imp_id,$session_uname);

UPDATE2:- After troubleshooting things with help i'm finally able to get the first tables result but not the second ones result. I know i'm missing something but can't figure it out. Any help is appreciated

here is the class function that fetches only updates values need to get group_posts value how to do it in pdo(phpmyadmin produces both results)

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

UPDATE2:- 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 = $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 soimeone point out where the mistake is. Ajax comes with a message of 200 but no results now.

Community
  • 1
  • 1
wolverine
  • 48
  • 5

1 Answers1

-1

You could use a stored procedure and run 2 select queries inside the stored procedure to return multiple arrays. Read more here

stored procedures return multiple values

Adam
  • 131
  • 2
  • 10
  • how to fetch the values through a php class @Adam – wolverine Jan 12 '20 at 04:28
  • @wolverine this should help - https://stackoverflow.com/questions/11271595/pdo-multiple-queries then in PHP you'd have $class_object = new Class; $rows = $class_object->getRowsWithPDOQueries($param1,$param2); – Adam Jan 12 '20 at 12:07
  • the link wasn't helpful or i couldn't understand it @Adam – wolverine Jan 12 '20 at 14:30
  • i get the updates result and not group_posts result what to do. i'll update the results – wolverine Jan 12 '20 at 19:03