Page 1 of 1

Lists from tables - arrays?

Posted: Wed Apr 27, 2011 1:31 am
by grahambucknall
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

Re: Lists from tables - arrays?

Posted: Wed Apr 27, 2011 7:51 am
by JustPlainJef
I just did something similar, and it involved multiple While loops.

I don't have the syntax in front of me, so I'll have to go general, and specifics can be gotten later...

Code: Select all

echo "<table>"; //Start the table
$docquery = 'SELECT * FROM Documents ORDER BY DocID'; //Pull your documents
$docresult = mysqli_query($docquery);
While ($docrow = mysqli_fetch_assoc($docresult))
     {
     extract $docrow;  //This will extract the info, so your first row will give $DocID = 1 and $DocTitle = First Title
     $partyquery = 'SELECT PartyName from Paries WHERE DocID = $DocID'; //Select all partynames where docID = 1.
     $partyresult = mysqli_query($partyquery);
     echo "<th>$DocTitle</th>"; //Make a table header with the doc title
     While ($partyrow = mysqli_fetch_assoc($partyresult))
          {
          extract $partyrow;
          echo "<td>$PartyName</td>"; //Make a table row with each name that matches the docID
          }
     }
echo "</table>";
Here's mine, you can change the look of the headings as you wish. If you went with just bold headings, a little padding on the TD, and no lines, it would look like an un-bulleted list.

http://mchenrypigtail.com/recteams.php