Auto Hide Choices that are Full and Insert all the data to other Table

Question

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)

Answer 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.

Answer 2

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>

Answer 3

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.