PHP/Mysql: read data field value from lookup tables (split array)

Question

I have 1 Mysql database with 2 tables:

DOCUMENTS ... - staffID .....

STAFF - ID - Name

The DOCUMENTS table assigns each document to a single or multiple users from the STAFF table therefore the staffID in the DOCUMENTS table consists of a comma separated array of staff ID's for example (2, 14).

I managed to split the array into individual values:

• 2
• 14

but rather than having the ID numbers I would like to have the actual names from the STAFF table - how can I achieve this. Any help would be greatly appreciated - please see my current code below.

$result = mysql_query("SELECT 
  organizations.orgName,
  documents.docName,
  documents.docEntry,
  documents.staffID,
  staff.Name,
  staff.ID
FROM
  documents
  INNER JOIN organizations ON (documents.IDorg = organizations.IDorg)
  INNER JOIN staff ON (documents.staffID = staff.ID)
")
or die(mysql_error());  


while($row = mysql_fetch_array($result)){
    $splitA = $row['staffID'];
    $resultName = explode(',', $splitA );
    $i=0;

    for($i=0;$i<count($resultName);$i++)
    {
        echo "<a href='staffview.php?ID=".$row['docName'].
            "'>". $resultName[$i]."</a><br>";
    }

    echo '<hr>';

}

Answer 1

It looks like your existing code might work where documents.staffID = staff.ID - that is where there is just a single staffID associated with the document?

You'd be better off adding a table to model the relationships between documents and staff separately from either, and removing or deprecating the staffID field in the documents table. You'd need something like

CREATE TABLE document_staff (
    document_id <type>,
    staff_id    <type>
)

You can include compound indexes with ( document_id, staff_id ) and ( staff_id, document_id ) if you have lots of data and/or you want to traverse the relationship efficiently in both directions.

(You don't mention data types for your identity fields, but documents.staffID appears to be some sort of varchar based on what you say - perhaps you could use an integer type for these instead?)

But you can probably achieve what you want using the existing schema and the MySQL FIND_IN_SET function:

SELECT 
    organizations.orgName,
    documents.docName,
    documents.docEntry,
    documents.staffID,
    staff.Name,
    staff.ID
FROM
    documents
INNER JOIN organizations ON (documents.IDorg = organizations.IDorg)
INNER JOIN staff ON ( FIND_IN_SET( staff.ID, documents.staffID ) > 0 )

MySQL set types have limitations - maximum membership size of 64 for example - but may be sufficient for your needs.

If it was me though, I'd change the model rather than use FIND_IN_SET.

Answer 2

Thank you so much for you answer - greatly appreciated! My table setup is:

DOCUMENTS:

CREATE TABLE documents (
  docID     int NOT NULL,
  docTitle  mediumblob NOT NULL,
  staffID   varchar(120) NOT NULL,
  Author2   int,
  IDorg     int,
  docName   varchar(150) NOT NULL,
  docEntry  int AUTO_INCREMENT NOT NULL,
  /* Keys */
  PRIMARY KEY (docEntry)
) ENGINE = MyISAM;

STAFF:

CREATE TABLE staff (
  ID            int AUTO_INCREMENT NOT NULL,
  Name          varchar(60) NOT NULL,
  Organization  varchar(20),
  documents     varchar(150),
  Photo         mediumblob,
  /* Keys */
  PRIMARY KEY (ID)
) ENGINE = MyISAM;

The DOCUMENTS table reads via a lookup table (dropdown) from the STAFF table so that I can assign multiple staff members to a document. So I can access the staffID array in the DOCUMENTS table and split that and I wonder if there is a way to then associate the staffID with the staff.Name and print out the staff Name rather than the ID in the results of the query. Thanks again!