current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
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

Some background. I have two MySQL tables derived from PubMed XML files. The pubauth table has all authors for a given reference and the pubmed table has all the unique pubmed info. There are n+1 authors for each pubmed record. What I think I need is this:

Array(
    [id]=> Array([id]=>1
        [PMID]=>123456
        Array(
            [pubauth]=>Array(
                [pubauth_id]=>1
                [LastName]=>Chen
            )
        )
    )
)

My model is:

$this->db->select( 'pubmed.id, PMID,  pubauth.id AS pubauth_id, LastName');
$this->db->from( 'pubmed' );
$this->db->join( 'pubauth', 'pubmed_id = pubmed.id', 'left outer' );        
$this->db->where( 'idecm',$keyword );
$query = $this->db->get();
$temp_result = array();
foreach ( $query->result_array() as $row ){
    $temp_result[] = array(
                        'pubmed_id' => $row['id'],
                        'PMID'      => $row['PMID'],
                        'LastName'  => $row['LastName'],
                        'pubauth_id'=> $row['pubauth_id']
                        );
}

This returns each row as an array in $temp_result. What I am shooting for is similar to this question: PHP MYSQL multidimensional array. replacing the foreach in my Model according to the answer, I have:

  foreach ( $query->result_array() as $row )
    {
        $r_array[$row["id"]]=array();

        $row_st = (string) $row["pubauth_id"];
        $r_array[$row["id"]][] = $row_st;
    }

foreach ($r_array as $id => & $data) {
// Start with just ID
$newarray = array(
  "id" => $id
);

if (count($data) == TRUE)
  $newarray["pubauth_id"] = & $data;

$finalarray[] = & $newarray;
print_r( $finalarray);
unset($newarray);
}

This array I get from this is

Array ( [0] => Array ( [id] => 1 [pubauth_id] => Array ( [0] => 7 ) ) ) Array ( [0] => Array ( [id] => 1 [pubauth_id] => Array ( [0] => 7 ) ) [1] => Array ( [id] => 2 [pubauth_id] => Array ( [0] => 17 ) ) )

Data it is only the last row and LastName is missing. Sorry for rambling but this has me flumoxed. I can work with 2D arrays but cannot seem to wrap my head around this one. The output I am shooting for in the View would look like a PubMed reference; Author1...Author_n, Title, Data, Journal, Volume, Issue, Page. All I can get now is a Title, Data, Journal, Volume, Issue, Page for each Author as a row. Any help will be greatly appreciated.

share|improve this question

2 Answers 2

up vote 0 down vote

I have solved my problem. This probably is not the most efficient way to do this since it requires two sequential MySQL quires to get the data. The solution was in part from this post: Nested foreach(). My new model:

//Retrieve pubmed record
$this->db->select( 'pubmed.id, PMID);
$this->db->from( 'ecmnote' );
$this->db->join( 'pubmed', 'pubmed.idecm = ecmnote.idecm', 'left' );
$this->db->where( 'ecmnote.idecm',$key );
$query = $this->db->get();

//Retrieve associated authors.
$this->db->select( 'pubmed.id AS pubmed_id, LastName);
$this->db->from( 'ecmnote' );
$this->db->join( 'pubmed', 'pubmed.idecm = ecmnote.idecm', 'left' );
$this->db->join( 'pubauth', 'pubmed_id = pubmed.id');
$this->db->where( 'pubmed.idecm',$key );
$auth_query = $this->db->get();

$pub = array();
$auth = array();

foreach ($auth_query->result_array() as $r){
     $auth[] = array (
               'pubmed_id'=> $r['pubmed_id'],
               'LastName' => $r['LastName']
                );
}

 foreach ( $query->result_array() as $row ){
      $pub[] = array(
               'PMID' => $row["PMID"],
               'authors' => $auth
               );
}
return $pub;

From the controller I pass this to a Smarty template to view it. My View is this:

 <table class="table table-striped"> 
 <tbody>
        {foreach $data as $apub}
            <tr>
                <td >
                    {assign var = "title" value = '. '|cat:$apub['ArticleTitle']|cat:' ('|cat:$apub['PubDate']|cat:') '|cat:$apub['Title']|cat:'. '|cat:$apub['Volume']|cat:'('|cat:$apub['Issue']|cat:')'|cat:$apub['MedlinePgn'] }

                    {foreach $apub as $svar=>$sval}
                        {if ($svar === "authors")}
                            {foreach $sval as $apeople}
                                {assign var = "sauthors" value = ' '|cat:$apeople['LastName']|cat:' '|cat:$apeople['Initials'] scope="global"}

                                {$sauthors}

                            {/foreach}
                        {/if}
                    {/foreach}

                {$title}

                </td>

Is there a way to make this more efficient? When I combine the MySQL query into one I get a multidimensional array that has each author plus the pubmed table as its own array. I could never get that to display correctly.

share|improve this answer
    
This actually still does not work correctly. The {foreach} on the authors loops through all authors for each PubMed article. –  Dennis Simpson Sep 8 '13 at 23:33
up vote 0 down vote accepted

I get the result I needed now. Still not sure this is the most efficient way since I cannot think of a method to use only one DB query. I had to change my Smarty template slightly. 

    <table class="table table-striped"> 
 <tbody>
        {foreach $data as $apub}
            <tr>
                <td >
                    {assign var = "title" value = '. '|cat:$apub['ArticleTitle']|cat:' ('|cat:$apub['PubDate']|cat:') '|cat:$apub['Title']|cat:'. '|cat:$apub['Volume']|cat:'('|cat:$apub['Issue']|cat:')'|cat:$apub['MedlinePgn'] }

                    {foreach $apub['authors'] as $apeople}
                        {assign var = "sauthors" value = ''}
                        {if $apub["id"] == $apeople["pubmed_id"]}
                            {assign var = "authors" value = ' '|cat:$apeople['LastName']|cat:' '|cat:$apeople['Initials'] scope="global"}
                            {$authors}
                        {/if}
                    {/foreach}
                    {$title}
                </td>
{/tr}

This displays Author1 ... AuthorN Title Year Journal Volume Issue Pages.

share|improve this answer

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.