1

I have the following database consisted from the following fields.

**table_choice**

field_choiceid(auto increment) (1, 2, 3, 4, 5)

field_choicename (C1, C2, C3, C4, C5)

field_choicemaximumslot (10, 12, 15, 18, 20)


**table_select**

field_selectedid(auto increment)

field_selectedchoice

field_selectedby


**table_full_choice**

field_fullid(auto increment)

field_choiceid_full

field_choicename_full

field_choicemaximumslot_full


**table_full_select**

field_selectedid_full(auto increment)

field_selectedchoice_full

field_selectedby_full

And I'm using this code to auto-populate my dropdown list.

<label>CHOICE</label>

<select value="" name="choice" id="choice" required>

<option>SELECT</option>

<?php

$result=mysqli_query($connection,"SELECT * FROM table_choice")or die(mysqli_error,());

while($row=mysqli_fetch_array($result)){

$choiceid=$row['field_choiceid'];
$choicename=$row['field_choicename'];
$choiceslot=$row['field_choiceslot'];
?>

<option value="<?php echo $choicename;?>">

<?php echo $choicename;?>

</option>

<?php } ?>

</select>

If C1, C3, C5 reached their maximum slot. So I would like to hide C1, C3, C5 in the dropdown list that is auto-populated and insert the data of C1, C3, C5 from table_choice to table_full_choice and insert the data of C1, C3, C5 from table_select to table_full_select then delete the data of C1, C2, C3 in the table_choice and table_select. How to do that? Please help. I'm a noob. I'm a newbie. The this will be the data in the table

table_choice

field_choiceid(auto increment) (2, 4)

field_choicename (C2, C4)

field_choicemaximumslot (12, 18)


table_select

field_selectedid(auto increment)

field_selectedchoice

field_selectedby


table_full_choice

field_fullid(auto increment) (1, 2, 3)

field_choiceid_full (1, 3, 5)

field_choicename_full (C1, C3, C5)

field_choicemaximumslot_full (10, 15, 20)



table_full_select

field_selectedid_full (1 to 45)

field_selectedchoice_full (C1(10 times), C3(15 times), C5(20 times))

field_selectedby_full (Person1 to Person45)
BadSly
  • 23
  • 4
  • 2
    can please show us your expected output? this question is still unclear – M.Hemant Apr 30 '19 at 04:15
  • The expected output in the dropdown list is the ones that are not yet full are visible in dropdown list and the ones that are full are not visible in the dropdown list. Example: the original list in dropdown list is C1 C2 C3 C4 C5 because they are not yet full but then C1 is full 10/10 and C3 is full 15/15 and C5 is full 20/20 then the dropdown list must have C2 C4 only. – BadSly Apr 30 '19 at 04:24
  • Do you have the quantity of people signed up and how many can sign up in the database? – Comet Apr 30 '19 at 09:16
  • Each data stored in field_choicename can be set how many can select that choice(minimum 10, maximum 20), it is stored in field_choicemaximumslot. The admin can put how many he/she wants 10 to 20 slots. – BadSly Apr 30 '19 at 10:54
  • In your question there are 3 answers already. It is adcvisable to press the up arrow and the tick if any of them are usefull and solved your problem. – Dimitrios Desyllas Apr 30 '19 at 11:05

3 Answers3

1

You can use SQL JOIN to select.

If you haven't done it you should add a foreign Key for the fields table_choice.field_choiceid and table_select.field_selectedchoice

I hope I don't have a mistake but this Code should work. But try the select at first.

<form>  
    <select name = "CourseList">
<?php

    $result=mysqli_query($connection,"SELECT tc.*, count(ts.field_selectedchoice) AS c_selected FROM table_choice tc LEFT JOIN table_select ts ON ts.field_selectedchoice = tc.field.choiceid GROUP BY tc.field_choiceid")or die(mysqli_error,());

    while($row=mysqli_fetch_array($result)){

        $choiceid=$row['field_choiceid'];
        $choicename=$row['field_choicename'];
        $choiceslot=$row['field_choiceslot'];
        $maxchoice=$row['field_choicemaximumslot'];
        $selectcount=$row['c_selected'];

        if ($maxchoice > $selectcount){
            echo '<option value='.$choiceid.'>'.$choicename.'</option>';
        }
    ?>
    </select>

If there is a mistake in the SQL-Statment, I'll try and correct it.

Spirit
  • 631
  • 7
  • 11
0

If you can add how many people have signed up and the maximum of people who can sign up to the database, you can do it this way.

<form>  
     <select name = "CourseList">

<?php

    $result = mysqli_query($connection,"SELECT * FROM table_choice")or   die(mysqli_error,());

    while($row = mysqli_fetch_array($result)){

        $choiceid = $row['field_choiceid'];
        $choicename = $row['field_choicename'];
        $choiceslot = $row['field_choiceslot'];
        $HowMany = $row['How_Many'];
        $Maximum = $row['Maximum'];

        if(HowMany != Maximum){
            echo "<option value = "$choiceid" selected>$choicename</option>\n";
    }
?>
     </select>
</form>
Comet
  • 260
  • 2
  • 8
  • What if I can only add the number(maximum) of people who can select a specific choice. The admin can set the field_choicemaximumslot to 10 or 11 or 12 or 13 or 14 or 15 or 16 or 17 or 18 or 19 or 20, then if it set to 10 then 10 is the maximum slot. Then if 10 out of 15 people who signed up selected the C1 (has field_choicemaximumslot set to 10), C1 will disappear/hide in the dropdown list so i can avoid 11/10, all choice in the dropdown list that reaches maximum slot will disappear/hide. – BadSly Apr 30 '19 at 11:14
  • You will need to keep track of how many people to be able to stop it. Best way to do this is update the database to say how many people have signed up. – Comet Apr 30 '19 at 11:31
  • @Comet but on the other hand the approach that @BadSly is using allows to keep record who joined as well if the `table_select` is modified accordingly. – Dimitrios Desyllas May 02 '19 at 00:46
0

BadSly, I am humbly welcoming you to stackoverflow!

According to your problem as @Spirit Says you can LEFT JOIN them, after that you can filter it out using the HAVING reserved word. With that I filter out the lessons that are already full (as far as I understood in your question) after having joined it all together.

For more info regarding having reference at this piece of documentation. Also more information focusing on the differences between HAVING and WHERE is located in this piece of information: https://stackoverflow.com/a/2905312/4706711

I build a sample of your database in sqlfiddle http://sqlfiddle.com/#!9/df211f/4/0 for completeness reasons the sample database is build like that:

CREATE TABLE table_choice(
  field_choiceid INT UNSIGNED NOT NULL PRIMARY KEY,
  field_choicename VARCHAR(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  field_choicemaximumslot INT UNSIGNED NULL
);

CREATE TABLE table_select(
  field_selectedid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  field_selectedchoice INT UNSIGNED NOT NULL,
  FOREIGN KEY fk_select(field_selectedchoice) REFERENCES table_choice(field_choiceid) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO table_choice VALUES (1,'C1',10),(2,'C2',12),(3,'C3',15),(4,'C4',18),(5,'C5',20);
insert into table_select(field_selectedchoice) VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(4),(5),(3),(2),(2),(2),(5),(4);

As you can see the query:

SELECT table_choice.*, count(table_select.field_selectedchoice) 
AS c_selected FROM table_choice LEFT JOIN table_select
ON table_select.field_selectedchoice = table_choice.field_choiceid
GROUP BY table_choice.field_choiceid HAVING c_selected < table_choice.field_choicemaximumslot;

Will filter out the unwanted options. Thus you can use your code with a twist:

<label>CHOICE</label>

<select value="" name="choice" id="choice" required>

<option>SELECT</option>

<?php

// Is good idea to fetch the required fields and no more.
$query="SELECT table_choice.field_choicename as field_choicename, count(table_select.field_selectedchoice) AS c_selected FROM table_choice LEFT JOIN table_select ON table_select.field_selectedchoice = table_choice.field_choiceid GROUP BY table_choice.field_choiceid HAVING c_selected < table_choice.field_choicemaximumslot";

$result=mysqli_query($connection, $query)or die(mysqli_error,());

while($row=mysqli_fetch_array($result)){
$choicename=$row['field_choicename'];
?>
<option value="<?php echo $choicename;?>">
<?php echo $choicename;?>
</option>
<?php } ?>

Furthermore to keep things neat in your code you can create a view based on the query you want. As an example in your case you can create the vie like that:

CREATE VIEW view_table_choice AS SELECT table_choice.*, count(table_select.field_selectedchoice) 
AS c_selected FROM table_choice LEFT JOIN table_select
ON table_select.field_selectedchoice = table_choice.field_choiceid
GROUP BY table_choice.field_choiceid;

You can reference about views in this piece of mysql documentation.

So using the view the code will be:

<label>CHOICE</label>

<select value="" name="choice" id="choice" required>

<option>SELECT</option>

<?php

$result=mysqli_query($connection,"SELECT * FROM view_table_choice WHERE view_table_choice.c_selected < view_table_choice.field_choicemaximumslot")or die(mysqli_error,());

while($row=mysqli_fetch_array($result)){

$choiceid=$row['field_choiceid'];
$choicename=$row['field_choicename'];
$choiceslot=$row['field_choiceslot'];
?>

<option value="<?php echo $choicename;?>">

<?php echo $choicename;?>

</option>

<?php } ?>

</select>

And you can enjoy on how neater looks like. In order to update your current database you can create a php migration script to do that where will run the CREATE VIEW query.

Dimitrios Desyllas
  • 9,082
  • 15
  • 74
  • 164