Lists from tables - arrays?

PHP
1

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

2

Are you talking about database tables?
If you are, when you retrieve the information you should always have it in an array or object.

Your code would look something like this:


// Database connection goes here
$result = mysql_query("SELECT * FROM `documents` ORDER BY `doctitle` ASC");

while($row = mysql_fetch_array($result)){
    
    echo $row['doctitle'].':<br/>';
    
    $result = mysql_query("SELECT * FROM `parties` WHERE `docid` = '".$row['id']."'");

    while($row = mysql_fetch_array($result))
        echo $row['party'].'<br/>';

    echo '<br/>';

}
3

A query inside a loop? Better avoid that:


// Database connection goes here
$query = "
  SELECT
      doctitle
    , party
  FROM documents
  INNER JOIN parties
  ON documents.docid = parties.docid
";
$result = mysql_query($query) or die("mysql error : " . mysql_error() . " in query $query");

// load the query result in a bidimensional array
$list = array();
while ($row = mysql_fetch_array($result)) {        
  $list[$row['doctitle']][] = $row['party'];  
}  

// let's display the list to see the result 
// (it's just for demonstration purposes, you can eliminate it after you've checked it)
print_r($list);

// loop through the array to create the list
foreach ($list as $document => $parties) {
  echo $document . "<br />";
  foreach ($parties as $party) {
    echo $party . "<br />";
  }
  echo "<br />";  
}
4

I’ll assume that Fred #1 is different from Fred #5. Your query will become:


<?php
	$query = "
	SELECT doctitle, documents.docid, party, partyid
	FROM documents
	INNER JOIN parties ON documents.docid = parties.docid
	ORDER BY doctitle, docid, party, partyid
	";
?>

Notice how ORDER clause is used. Now you can display the results, grouped, as follows:


<?php
	$docid = -1;                                // initialize with -1 (see reason below)
	$result = mysql_query($query) or die(mysql_error());
	while ($row = mysql_fetch_array($result)) { // group and display rows in one go
		if($docid != $row["docid"]) {           // if we have not yet displayed the header for current docid
			$docid = $row["docid"];             // display and make a note that it has been displayed
			?>
			<h1><?php echo $row["doctitle"]; ?> (id #<?php echo $row["docid"]; ?>)</h1>
			<?php
		}
		?>
		<p><?php echo $row["party"]; ?> (id #<?php echo $row["partyid"]; ?>)</p>
		<?php
	}
?>
5

Assumptions galore.

Let me make my own assumption, since we’re all doing it…
The key on the table is (partyid) PRIMARY auto_increment, natural key is (docid,party) UNIQUE

Which would mean:

The table is redundant (You should make a users table and link the id into the join table, rather than saving each person’s name over and over)

Sorting by Doctitle and then docid is redundant unless the user has allowed two documents to have the same name; sorting by doctitle is assuming the OP’s intent, as it is purely cosmetic. Sorting by party and then partyid is redundant because of the natural key.

The queries also offered above are operating on the assumption that you want to pull ALL documents on a single page. Is that the intent, or are you trying to select a singular document?

6

Many thanks for the suggestions - I am just about to start trying them.

I have moved on a little bit and both the documents and parties tables contain a field ‘idcode’ which also links to other tables which are not used for this example. However, a single idcode could be linked to several documents.

For example:
the documents table entries might be
|docid |doctitle |idcode
|1 |first document |1234
|2 |second document|1234
|3 |third document |3456

and the parties table entries

|partyid |docid|party |idcode
|1 |1 |Fred |1234
|2 |1 |Jim |1234
|3 |1 |Jane |1234
|4 |2 |Peter |1234
|5 |2 |Fred |1234
|5 |3 |Fred |3456
|5 |3 |Fred |3456

So, for a particular idcode (which is carried with the url) I can list the document title and its parties in the format (for example when idcode=1234):

First document:
Fred
Jim
Jane

Second document:
Peter
Fred

I do want to pull all of the documents (with the same idcode) on a single page but some parties will be the same for different documents. In my example Fred is the same party - he is linked to 2 separate documents.

7

Okay. Understanding it better now. You’ve got some redundancy, so lets try and clear that up first.

Schema:
Party

partyid
name

Documents

docid
idcode
doctitle

party_doc

docid
partyid


$query = "SELECT document.doctitle,GROUP_CONCAT(party.name ORDER BY party.name SEPARATOR '<br>') AS names 
FROM document
LEFT JOIN party_doc ON document.docid = party_doc.docid
INNER JOIN party ON party_doc.partyid = partyid
WHERE document.idcode = ".$idcode;

$res = $sql->query($query);
while($row = $res->fetch_array()) {
  echo $row['doctitle'].":<br>".$row['names'];
}
8

A document can have more parties. But can a party have more documents?

Looking at your parties table example data it seems that way (multiple Fred’s), but are they really the same party?

9

Yes they are the same party - so a document will always have multiple parties and a party might link to multiple documents.

10

Ok, in that case the database layout posted by StarLion is the way to go.

As far as the query is concerned, you can use his GROUP_CONCAT solution, or use mine (see answer #3) with some modification (you’ll have to join 3 tables).

11

Many thanks for all of this help. It all works fine.