HTML DropDown with Mysqli data and data selection

Question

I have an html dropdown list which gets the data from mysql table. I have a second table where I can see which equipment has already been selected for another user for the day.

My question is:

How do I not display that equipment (BowCode and its Information) and instead go to the next entry.
It's possible that multiple equipment have been already selected for the day for other users.

My code:

$BowDropDown = mysqli_query($mysqli, "SELECT * FROM equipment order by BowCode ASC");

while ($row = $BowDropDown->fetch_assoc()){

    if($row['BowCode'] != $BowDropDownDayCheck){}

    if($row['Recurve'] =='1'){
        $value = 'Recurve';
    } else if ($row['Compound'] == '1'){
        $value = 'Compound';
    } else if ($row['Longbow'] == '1'){
        $value ='Longbow';
    }
echo "<option value= " . $row['BowCode'] . ">" . $value . " - " . $row['BowCode'] . " - " . $row['Info'] . " - " . $row['Poundage'] . "</option>";
}

I don't know if it is possible or not but maybe the values that are displayed in the dropdown can be visualised like a table view.

table from where the Equipment is stored

tabele where the users are stored with the Equipment the use for the day

*"maybe the values that are displayed in the Dropdown can be visualised like a table view."* - as what type of table, an HTML table? — Funk Forty Niner, Jan 19 '18 at 13:36
no also and to check if BowCode has already been used for the day. If so then not to Display the bowCode — Mark, Jan 19 '18 at 13:39
Adjust your query to not select items that are in the other table. — Michel, Jan 19 '18 at 13:42
$BowDropDownDayCheck = mysqli_query($mysqli, "SELECT * FROM equipment Where Date='$date'"); @Michel thats what i have tried but it didnt do anything — Mark, Jan 19 '18 at 13:43
Take a look [here](https://stackoverflow.com/questions/5287246/mysql-join-and-exclude), you need a `join`. To help you, we need some tablenames, columnnames etc. — Michel, Jan 19 '18 at 13:45
Is `BowCode` from the first table identical to `BowID` from the second? — Michel, Jan 19 '18 at 13:52
Yes i know i made the mistake of not Nameing it the same tho i will do it later — Mark, Jan 19 '18 at 13:53
the first table is called Equipment and the secon one is called comeandtrydaysparticipant — Mark, Jan 19 '18 at 13:55

score 0 · Accepted Answer · answered Jan 19 '18 at 16:08

0

This one does the job

SELECT 
   b.id, b.BowCode, b.Info
FROM Equipment AS b
LEFT JOIN 
  (SELECT a.BowID
   FROM comeandtrydaysparticipant AS a
   WHERE a.`date`="2018-01-26"
   GROUP BY a.BowID
  ) AS c
ON c.BowID = b.BowCode
WHERE c.BowID IS NULL
;

It selects all items not in comeandtrydaysparticipant for a particular day.

And the proof is in the pudding

answered Jan 19 '18 at 16:08

Michel

4,076
4
34
52

cool and now when i want to echo the values out in php do i do b.BowCode ? – Mark Jan 19 '18 at 16:20
Just `BowCode`. The `b.` is an alias for the table Equipment (as `a.` is for that other, very fance tablename ;), it just saves typing. – Michel Jan 19 '18 at 16:25

HTML DropDown with Mysqli data and data selection

1 Answers1