Joining two mysql tables and populate an array in php

Question

I have two tables

First table name is "Library"

ID Name
1  A
2  B

Second table name is "Books"

BID Book_Creater Book_Name
1   Variation1   Book1
1   Variation2   Book2
1   Variation3   Book3
2   Variation1   Book4
2   Variation2   Book5

The sql is

$sql = mysql_query("select library.ID,books.Book_Creater,books.Book_name from library,books where library.ID = books.BID;

Result is

ID   Book_Creater   Book_name
1    Variation1     Book1
1    Variation2     Book2
1    Variation3     Book3
2    Variation1     Book4
2    Variation2     Book5

Now I would like to create an array which should look like and this is where I am stuck.

array(
      [1] => array(
                   [Variation1] => Book1
                   [Variation2] => Book2
                   [Variation3] => Book3
                  )
      [2] => array(
                   [Variation1] => Book4
                   [Variation2] => Book5

                  )
)

Any suggestion will do great.

Flion · Accepted Answer · 2014-06-04 11:39:31Z

3

$res = array();
//for each result row
while($row = mysql_fetch_array($sql)) 
{
    //add a row to the multi dimensional result array
    //where the first key = id and the second key = Book_Creater
    $res[$row['id']][$row['Book_Creater']] = $row['Book_name'];
}
print_r($res);

edited Jun 4, 2014 at 11:39

answered Jun 3, 2014 at 13:54

Flion

11.1k14 gold badges55 silver badges78 bronze badges

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

5 Comments

mareckmareck Over a year ago

You missed the semicolon in block.

Kickstart Over a year ago

Don't think you can use foreach(mysql_fetch_array . mysql_fetch_array will return and array. I think php will try to loop around that array (which is one returned row) hence $row would contain the first column rather than the entire row. Presume next iteration it will grab the next rows first column.

user3671491 Over a year ago

Awesome!!There is one more issue. I have another field containing some content and that content has got a image source.So I am doing like preg_match_all('/(src)=("[^"]*")/i',$row['post_content'],$imgdetails);which is returning an array.Actually I think I should edit my post and show you the array structure I am getting now.

user3671491 Over a year ago

Actually it is getting more complicated here. I will create another question.However many thanks for your help.

Flion Over a year ago

ah yes, it should be while fetch_array.. updated the answer. @user3671491: yourwelcome, goodluck!

Kickstart · Accepted Answer · 2014-06-03 14:05:14Z

Simple way is to do a join as you currently do, and push them into an array as you loop around (possibly for neatness creating a new sub array explicitly when the ID as a key doesn't already exist).

Something like this:-

<?php

$store_array = array();

$sql = mysql_query("SELECT library.ID,books.Book_Creater,books.Book_name 
                FROM library
                INNER JOIN books 
                ON library.ID = books.BID");

$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) 
{
    if (!array_key_exists($row['ID'], $store_array))
    {
        $store_array[$row['ID']] = array();
    }
    $store_array[$row['ID']][$row['Book_Creater']] = $row['Book_name']
}

print_r($store_array);
?>

You could also shuffle some effort onto the database and bring things back concatenated together. Then just split the results to put into the array:-

<?php

$store_array = array();

$sql = mysql_query("SELECT library.ID, GROUP_CONCAT(CONCAT_WS('#~#', books.Book_Creater,books.Book_name)) AS details
                FROM library
                INNER JOIN books 
                ON library.ID = books.BID
                GROUP BY library.ID");

$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) 
{
    $store_array[$row['ID']] = array();
    $details = explode(',', $row['details']);
    foreach($details as $detail)
    {
        $detail_line = explode('#~#', $row['detail']);
        $store_array[$row['ID']][$detail_line[0]] = $detail_line[1];
    }
}

print_r($store_array);
?>

Note that if you are doing this with real data you probably want to chose delimiters which are not going to appear in your data, and also that by default GROUP_CONCAT has a fairly limited max length.

Note also I have used mysql_* functions as that is what you seem to be using, but these are deprecated and probably shouldn't be used now.

Collectives™ on Stack Overflow

Joining two mysql tables and populate an array in php

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related