SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lists from tables - arrays?

    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. #2
    SitePoint Addict Kokos's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    // 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/>';



    Taking over the web one pixel at a time.
    Currently working @ CodeCreators

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Kokos View Post
    Your code would look something like this:
    A query inside a loop? Better avoid that:
    Code PHP:
    // 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. #4
    SitePoint Enthusiast webdesignhouston's Avatar
    Join Date
    Dec 2010
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll assume that Fred #1 is different from Fred #5. Your query will become:

    PHP Code:
    <?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 Code:
    <?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. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by webdesignhouston View Post
    I'll assume that Fred #1 is different from Fred #5. Your query will become:
    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. #6
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    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

    PHP Code:
    $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. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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. #9
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes they are the same party - so a document will always have multiple parties and a party might link to multiple documents.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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. #11
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks for all of this help. It all works fine.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •