Lists from tables - arrays?

[tommy2shoes]

Hi

 

I am having problems trying to view some data and wondered if anyone could help.

 

I have a table called 'parties' which contains fields - partyid, docid and party. There is another table called 'documents' which has fields - docid and doctitle.

 

I have a number of documents and for each document there are a number of parties - ie people linked to that document.

 

What I would like to do is get a view on screen (and also printed out) that has the title of the document followed by a list of the parties associated with that document.

 

For example, if in the documents table there was:

 

|docid |doctitle         |

|1         |first document |

|2         |second document|

 

and in the parties table there was:

|partyid |docid |party |

|1 |1    |Fred |

|2 |1         |Jim  |

|3 |1         |Jane |

|4 |2    |Peter |

|5 |2    |Fred         |

 

I could get a view that looked like:

 

First document:

Fred

Jim

Jane

 

Second document:

Peter

Fred

 

I suspect it may involve arrays but I have never used them so any guidance would be very welcome.

 

Thanks

[matthewgregory]

So assuming that you are using mysql you could do something like this (code is untested  )

<?php
//select documents from the database
$query = 'SELECT docid, doctitle FROM documents';
$result = mysql_query($query);

//declare an array to store the data
$documents = array();

//if there are documents to display
if(mysql_num_rows($result) > 0) {
    while($row = mysql_fetch_assoc($result)) {
        //create an array to store the parties
        $parties = array();
        
        //fetch the parties for this document from the database
        $query = 'SELECT partyid, party FROM parties WHERE docid = '.$row['docid'];
        $party_result = mysql_query($query);
        
        if(mysql_num_rows($party_result) > 0) {
            while($party = mysql_fetch_assoc($party_result)) {
                 $parties[] = $party['party'];
            }
        }
         
        $documents[$row['doctitle']] = $parties;
    }
}

 

This will give you an array like so:

 

array('First document' => array(0 => 'Fred', 1 => 'Jim', 2 => 'Jane'),

        'Second document' => array(0 => 'Peter', 1 => 'Fred'))

 

and you can extract it to display it like so:

<?php
$output = array();

if(count($documents) > 0) {
    foreach($documents as $document => $parties) {
        $output[] = '<p>'.$document.'</p><br/>';
        
        if(count($parties) > 0) {
            foreach($parties as $party) {
                $output[] = '<p>'.$party.'</p><br />';
            }
        } else {
            print '<p>No parties for this document</p>';
        }
    }
} else {
    print '<p>No documents available</p>';
}

print implode($output, "\n");

[Psycho]

Oh, hell no! Never run queries in loops - it kills the server's resources. All you need to do is run a single query that uses a JOIN on the two tables. Each record in the result will have the document name. You just use logic in the PHP code to only display the document title once.

$query = "SELECT d.doctitle, p.party
          FROM documents as d
          JOIN parties as p USING(docid)";
$result = mysql_query($query) or die(mysql_error());

//Variable to track change in document title
$current_doc = false;

while($row = mysql_fetch_array($result))
{
    if($current_doc != $row['doctitle'])
    {
        //New document, show doc title
        echo "<br><b>{$row['doctitle']}:</b>\n";
        $current_doc = $row['doctitle'];
    }
    //Display party
    echo "<b>{$row['party']}:</b>\n";
}