php - 自动隐藏完整的选择并将所有数据插入其他表

Question

我有以下数据库，由以下字段组成。

**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

我正在使用这段代码来自动填充我的下拉列表。

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

如果 C1、C3、C5 达到了它们的最大时隙。所以我想在自动填充的下拉列表中隐藏 C1、C3、C5 并将 C1、C3、C5 的数据从 table_choice 插入到 table_full_choice 并将 C1、C3、C5 的数据从 table_select 插入到 table_full_select 然后删除table_choice和table_select中C1、C2、C3的数据。怎么做？请帮忙。我是菜鸟。我是新手。这将是表中的数据

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

可以使用SQL JOIN来选择。

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

我希望我没有弄错，但这段代码应该有效。但首先尝试选择。

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

如果SQL语句有错误，我会尽量改正。