current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have the following tables in MYSQL:

CommentTable with columns: comment, commenter, datecommented and postid PostTable with columns: postid, dateposted

I perform this query in php

Select commentTable.comment, commentTable.commenter, commentTable.datecommented, shareTable.postid, shareTable.dateshared  from shareTable Left Join commentTable on commentTable.postid = shareTable.postid where shareTable.postid IN ($postidarray) order by  shareTable.dateshared desc

where $postid array is an array of post ids.

The problem I have is when i'm trying to sort the query result into a multidimensional array.

I would want to have a multidimensional array called comment which would like this

Comment{

[0] {
       [0] => "Comment 1 from first key in $postidaray"
       [1] =>  "Comment 2 from first key in $postidarray"
    }

[1] {
       [0] => "Comment 1 from second key in $postidarray"
     } // assuming there is only one comment for the second key in $postidarray

[2]{

       [0] => "Comment 1 from third key in $postidarray"
       [1] =>  "Comment 2 from third key in $postidarray"
       [2] =>  "Comment 3 from third key in $postidarray"
       [3] =>  "Comment 4 from third key in $postidarray"
   } 
   // assuming there are 4 comments for the third key in $postidarray
    }
}

I'm doing this so that when I make a php echo I can loop out the comments relating to the specific post

For instance comment[0][1] would echo 'Comment 2 from first key in $postidarray'

Any help is appreciated.

share|improve this question
    
You can't get a multi-dimensional array. Use GROUP in your statement and create (if really nedded) your array from the resultset. –  djot Apr 17 '13 at 18:21
    
I'm sorry I do not follow –  Kwaasi Djin Apr 17 '13 at 18:22
    
You can get a multi dimensional array. Please look below for the solution. –  Kwaasi Djin Apr 17 '13 at 22:22

1 Answer 1

up vote 0 down vote accepted

You could do an ORDER BY shareTable.postId THEN BY shareTable.dateshared DESC

This would make all the rows with the same postId appear together so you could just check for a change of postId.

$i=-1;
$currentPostId=0;

$comment = array();
 while($assoc = mysql_fetch_assoc($res)){
     if($assoc['postId']!=$currentPostId){
              $currentPostId = $assoc['postId'];
              $i++;
     }
  $comment[$i][] = $res;
}

This should give you the result you want. Alternately, If you use PDO, Use a prepared statement instead of using a single query with IN( ... )

$stmt = $pdo->prepare("Select ... shareTable.postid=? order by ShareTable.dateshared desc");
 $comment = array();
$p = &$postIds[0];
$stmt->bindParam($p);
 foreach($postIds as $p){
      $stmt->execute();
      $comment[] = $stmt->fetchAll(PDO::FETCH_ASSOC);
 }
share|improve this answer
    
Okay the output is how i want it to be but it does not put the comment in the the second array @2bigpigs –  Kwaasi Djin Apr 17 '13 at 19:00
    
Okay so I tweaked it a little bit- I replaced $comment[$i] =$res with $comment[$i] = $res['comment']. The mysql code ORDER BY shareTable.postId THEN BY shareTable.dateshared DESC does not work. Can you help me with that so that i can accept your answers? Thanks @2bigpigs –  Kwaasi Djin Apr 17 '13 at 19:06
    
okay the correct syntax for anyone with this problem is order by shareTable.posid desc, shareTable.dateShared desc –  Kwaasi Djin Apr 17 '13 at 22:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.