How to Handle a Many-to-Many Relationship with PHP and MySQL
By Tony Marston2005-04-06
Form design
|
|
Having designed the database we must now turn our attentions to designing the forms that will manipulate the data. For this I will suggest two alternatives although experienced developers may be able to suggest more.
Simple
In this design I have 4 simple forms which are interconnected as shown in the following diagram:
We start off with the form <Browse 'A'> which enables the user to browse through all occurrences of table 'A'. During the processing of this form the user will be able to select an occurrence of 'A', then press a control (a button or a hyperlink) which will pass the identity of the selected occurrence to a second form which I have identified as <Browse 'X' for selected 'A'>.
This second form will show which occurrence of table 'A' has been selected, then list the current contents of table 'X' for that occurrence. For each occurrence of table 'X' it will include the relevant description from table 'B'. The MySQL query to achieve this is as follows:
SELECT x.a_id, x.b_id, b.b_descA similar result may also be achieved using the following:
FROM x
LEFT JOIN b ON (b.b_id = x.b_id)
WHERE (x.a_id = 'whatever')
SELECT x.a_id, x.b_id, b.b_desc
FROM x, b
WHERE (x.a_id = 'whatever') AND (b.b_id = x.b_id)
The difference between these two SQL queries will only become apparent if an occurrence of table 'B' is missing for an occurrence of table 'X'. With the first query all columns selected from table 'B' will be returned as empty. With the second query the effect will be that the occurrence of 'X' will be dropped from the selection.
The second form can only show existing occurrences of table 'X'. In my design this form would have controls to invoke a third from to add new occurrences to 'X' or a fourth form to delete occurrences from 'X'.
Complex
An alternative to this combines the facilities of forms (2), (3) and (4) into a single more complex form, as shown in the following diagram:
This modified form will still show which occurrence of table 'A' has been selected, but it will then list all occurrences of table 'B'. Against each occurrence of table 'B' will be a checkbox to indicate if an associated occurrence exists on table 'X' or not. The user will be able to toggle the checkbox in order to create or delete the entry on table 'X'. The MySQL query to select the data is as follows:
SELECT a.a_id, b.b_id, b.b_desc,Notice here that I am selecting from table 'A' and 'B', then doing a LEFT JOIN on table 'X'. The CASE..WHEN..THEN..END statement checks the existence of an associated occurrence on 'X' and sets a column named 'selected' to 'T' if one exists or 'F' if one does not.
CASE WHEN x.b_id IS NULL THEN 'F' ELSE 'T' END AS selected
FROM a, b
LEFT JOIN x ON (a.a_id = x.a_id AND b.b_id_id = x.b_id)
WHERE (a.a_id = 'whatever')
As an added refinement I use a third form to allow the entry of selection criteria so that the contents of the second form can be refined. This is usually more relevant in those cases where there are more columns to be displayed, but it does provide the ability to select only those occurrences of 'B' where associated occurrences of 'X' either do or do not exist. This is accomplished by altering the WHERE clause to be one of the following:
WHERE (a.a_id = 'whatever') AND x.a_id IS NULLor
WHERE (a.a_id = 'whatever') AND x.a_id IS NOT NULL
Complex update
If you thought that the code to retrieve the current data from the database was tricky then what will you think about the code needed to update the database after the user has made his changes? For this I have a standard procedure which has as its input two arrays:
1. $fieldarray which has an entry for each row displayed in the form, and for each row it contains the names and values for the primary key required for table 'X'.
2. $selectarray which has an entry for the checkbox on each of the rows. Note that this will only contain entries where the checkbox is ON. If it has been checked OFF then the $_POST array will not contain an entry for that row.
I shall now describe the code to process these two arrays.
I begin by looping through each row that was displayed in the form and initialise two string variables:
foreach ($fieldarray as $rownum => $rowdata) {
$insert = NULL;
$delete = NULL;Each row provides me with the names and values for the primary key, so I can move their details into the two string variables.
foreach ($rowdata as $fieldname => $fieldvalue) {
$insert .= "$fieldname='$fieldvalue',";
$delete .= "$fieldname='$fieldvalue' AND ";
} // foreachWhen there are no more fields left I can trim the unwanted ',' and ' AND '.
$insert = rtrim($insert, ',');Now I examine the contents of the checkbox in $selectarray and construct the SQL query to either create the entry if the checkbox is ON or delete the entry if the checkbox is OFF:
$delete = rtrim($delete, ' AND ');
if (isset($selectarray[$rownum])) {
$query = "INSERT INTO $tablename SET $insert";
} else {
$query = "DELETE FROM $tablename WHERE $delete";
} // ifFinally I execute the query and check for errors. Note that I ignore errors concerning duplicate entries. This is caused by a checkbox being ON originally and not being changed to OFF by the user:
$result = @mysql_query($query, $dbconnect);
if (mysql_errno() <> 0) {
if (mysql_errno() == 1062) {
// ignore duplicate entry
} else {
trigger_error("SQL", E_USER_ERROR);
} // if
} // if
} // foreach
Tutorial Pages:
» Intended Audience
» Introduction
» Database schema
» Form design
» Summary
|
