PHP and MySQL, array associated with 2 keys

Question

I have this scenario.

I input $groupid="1";

main table

----------------------
| groupid  |  postid  |
|---------------------|
|       1  |       1  |
|       2  |       2  |
|       1  |       3  |

$query = "SELECT postid FROM `mainl` WHERE groupid='$groupid'";
$result = mysql_query($query);

// a group of postids belonging to that groupid which should hold [1, 3] for groupid=1
while($row = mysql_fetch_array($result)) {
    $postids[] = $row["postid"];
}

second table

-------------------------------------------
|  postid  |  commentid  |     comment    |
-------------------------------------------
|       1  |          1  |   testing 1    |
|       1  |          2  |   testing 2    |
|       1  |          3  |       what?    |
|       2  |          1  |       hello    |
|       2  |          2  | hello world    |
|       3  |          1  |      test 3    |
|       3  |          2  |       begin    |
|       3  |          3  |        why?    |
|       3  |          4  |       shows    |


$query = "SELECT * FROM `second`";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
    if (in_array($row["postid"], $postids)) {
        $comments[$row["postid"]] = $row["comment"];

But how should I take care of commented
I want the postid array to be [1,3] and my comment array to be [commentid: comment] [1:testing1, 2: testing2, 3: what?] for postid=1
and
[1:test3, 2:begin, 3: why? 4:shows] for postid=3

how should be arrange everything such comment are associated with commentid and postid?

The mysql_* functions should not be used anymore, php has a newer, faster and more secure version to access mysql databases. mysqli_* functions should be used. More info and syntax can be found here. php.net/manual/en/mysqli.query.php — Dave_Peachy
– Dave_Peachy, Commented Jun 12, 2012 at 20:20
What should he use? I tried finding info on it in the php manual but couldnt find anything — Chris James Champeau
– Chris James Champeau, Commented Jun 12, 2012 at 20:25
You asked for two different things, a) a personalised array from each table and b) more simpler array derived from SQL query using join. Which one would you prefer? — J A
– J A, Commented Jun 12, 2012 at 20:34

Kris · Accepted Answer · 2012-06-13 02:48:29Z

1

First I would follow rokdd suggestion and make 1 query

SELECT  m.groupid , s.postid, s.commentid, s.comment FROM `main1` m JOIN `second` s USING (postid) where m.groupid = 1

Then I would make a multi-dimensional array

while ($row = mysql_fetch_array($result))
    $groups[$row['groupid'][$row['postid']][$row['commentid']=$row['comment'];

then to iterate through the array

foreach($groups as $group)    
    foreach($group as $post)
        foreach($post as $comment)
           echo $comment;

This will keep track of groups also (if you ever want to select by more than 1 group. If you don't care about groups just drop off the first part of the array.

while ($row = mysql_fetch_array($result))
    $posts[$row['postid']][$row['commentid']=$row['comment'];


    foreach($posts as $post)
        foreach($post as $comment)
           echo $comment;

edited Jun 13, 2012 at 2:48

answered Jun 13, 2012 at 2:11

Kris

6,1222 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rokdd Over a year ago

well @php side i was not sure what he like to do. before iterating to many times you can also make a key with a seperator and have one array: $comments[$row['groupid'].'-'.$row['postid'].'-'.$row['commentid']]=$row['comment'];

rokdd · Accepted Answer · 2012-06-12 20:33:44Z

0

I guess to use the join in sql so that you will have one statement:

SELECT * FROM second as second_tab LEFT join main as main_table ON main_table.post_id=second_table.post_id WHERE main_table.group_id="3"

Well not tested now but thats a way to solve some of your problems!

answered Jun 12, 2012 at 20:33

rokdd

6526 silver badges19 bronze badges

Collectives™ on Stack Overflow

PHP and MySQL, array associated with 2 keys

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related