I am having a real problem trying to get this to work...hopefully someone can help!!!
I have 2 tables i am working with in this query: a PRINTERS table and a GROUPS table. GROUPS table has a GROUPS column and a GID column, PRINTERS table has a GID column also to associate GROUPS.GID with PRINTERS.GID. This is a one-to-many relationship. What I want to accomplish is to be able to display all the groups in a dropdown box, but have the group selected that the specific printer is in (on a page that you are able to update info about a printer), so essentially, I would like to have a table that looks like this:
(i changed the values to make it look like what i want)
+--------+------+
| GROUPS | GID |
+--------+------+
| BIMP | NULL |
| HCAP | NULL |
| HCBP | 3 |
| HDBP | NULL |
| HDWP | NULL |
| HSOP | NULL |
| LSAP | NULL |
| LSBP | NULL |
| RCMP | NULL |
| RCPP | NULL |
| RIMP | NULL |
+--------+------+
I cant figure out how to do this properly, or if its even possible at all, as the NULL values are not really present in the tables, the other PRINTERS.GID are just NULL because the specific printer that is associated with a group should not be NULL but all other values should be. If I had a query that looked like this, it would be very simple for me to have the correct group selected in the dropdown box using the query results, I could easily do this:
<?
$query=mysql_query("SELECT BLAH BLAH....I dont know what to put here");
while($result=mysql_fetch_array($query)){?>
<select name=group><option value="<? echo $result["GROUPS"]?>"<? if($result["GID"] == !NULL) {echo " SELECTED "} ?>><? echo $result["GROUPS"]?></option>
<?}?>
The only other way I can see it working is by adding another table that relates the GROUPS table to the PRINTERS table by having primary keys setup for each printer id and each group id and relate them appropriately...
I have already had this type of thing work well when a relations table was in the picture, i just have no need for one in this situation (besides if this is what i must do to make it work)
here is the type of query that would give me what i need if a relations table was added to keep track of what GROUPS.GID are associated with what PRINTERS.PID
This query works when there is a GROUPRELATIONS table present:
SELECT GROUPS.GROUPS, GROUPS.GID, GROUPRELATIONS.PID
FROM GROUPS
LEFT JOIN GROUPRELATIONS
ON GROUPRELATIONS.GID=GROUPS.GID
AND GROUPRELATIONS.PID=$ID
Looks like this:
+--------+-----+------+
| GROUPS | GID | PID |
+--------+-----+------+
| BIMP | 1 | NULL |
| HCAP | 2 | NULL |
| HCBP | 3 | 14 |
| HDBP | 4 | NULL |
| HDWP | 5 | NULL |
| HSOP | 6 | NULL |
| LSAP | 7 | NULL |
| LSBP | 8 | NULL |
| RCMP | 9 | NULL |
| RCPP | 10 | NULL |
| RIMP | 11 | NULL |
+--------+-----+------+
If anyone has any suggestions, please help, i dont want to have a whole bunch of relations tables just so i can make this work, but if i have to i have to....
Hopefully someone will have a suggestion...
