php - 多次使用另一个表的值更新表

Question

对我来说，这是一个简单的问题，但我已经搜索了几天但没有找到合适的解决方案。

我有三张 table 。一种用于联系人，一种用于状态，一种用于是/否问题。联系人表是主表，它引用其他两个表来获取某些字段的值。是/否表在联系人表中被引用了三次。状态表被引用一次。

我已经能够使用适当的选择框对插入 php 文件进行编码，该选择框显示状态和是/否表中的值并将这些值插入到联系人表中，没有任何问题。

我遇到的问题是更新 php 文件。我无法找到可多次使用是/否表来更新联系人表中字段的代码示例。

我正在使用当前版本的 xampp 以及当前版本的 php 和 mysql。我正在使用 PDO 作为与 mysql 的连接进行编码。

这是当前更新mysql代码(我知道需要与states和yesno表建立连接，我只是不知道它应该是什么。)

<?php

require '../app/start.php';

if (!empty($_POST)) {
    $id = $_POST['id'];
    $deceased = $_POST['deceased'];
    $active = $_POST['active'];
    $realname = $_POST['realname'];
    $slug = $_POST['slug'];
    $nickname = $_POST['nickname'];
    $prefernick = $_POST['prefernick'];
    $dob = $_POST['dob'];
    $address = $_POST['address'];
    $city = $_POST['city'];
    $statelookup = $_POST['statelookup'];
    $zipcode = $_POST['zipcode'];
    $phone = $_POST['phone'];
    $email = $_POST['email'];
    $emailtwo = $_POST['emailtwo'];
    $facebook = $_POST['facebook'];
    $twitter = $_POST['twitter'];

    $updatePage = $db ->prepare("
        UPDATE contacts
        SET
            deceased = :deceased,
            active = :active,
            realname = :realname,
            slug = :slug,
            nickname = :nickname,
            prefernick = :prefernick,
            dob = :dob,
            address = :address,
            city = :city,
            statelookup = :statelookup,
            zipcode = :zipcode,
            phone = :phone,
            email = :email,
            emailtwo = :emailtwo,
            facebook = :facebook,
            twitter = :twitter,
            updated = NOW()
        WHERE id = :id
    ");

    $updatePage->execute([
        'id' => $id,
        'deceased' => $deceased,
        'active' => $active,
        'realname' => $realname,
        'slug' => $slug,
        'nickname' => $nickname,
        'prefernick' => $prefernick,
        'dob' => $dob,
        'address' => $address,
        'city' => $city,
        'statelookup' => $statelookup,
        'zipcode' => $zipcode,
        'phone' => $phone,
        'email' => $email,
        'emailtwo' => $emailtwo,
        'facebook' => $facebook,
        'twitter' => $twitter,
    ]);

    header('Location: ' . BASE_URL . '/adminix/list-contacts.php');
}

if (!isset($_GET['id'])) {
    header('Location: ' . BASE_URL . '/adminix/list-contacts.php');
        die();
}

$page = $db->prepare("
    SELECT *
    FROM contacts
    WHERE id = :id
");

$page->execute(['id' => $_GET['id']]);

$page = $page->fetch(PDO::FETCH_ASSOC);

require VIEW_ROOT . '/adminix/edit-contacts.php';

这是当前更新的 php 代码和表单(我知道需要更改选择选项，我只是不知道从联系人表中提取现有值应该是什么，但也显示可用的值)来自 states 表或 yesno 表的选项。)

<?php require VIEW_ROOT . '/templates/header.php'; ?>

<?php
// Require SQL Look-ups

$yesno1 = $db->query("
    SELECT *
    FROM yesno
    ORDER BY yesno_id DESC
")->fetchAll(PDO::FETCH_ASSOC);

$yesno2 = $db->query("
    SELECT *
    FROM yesno
    ORDER BY yesno_id ASC
")->fetchAll(PDO::FETCH_ASSOC);

$states = $db->query("
    SELECT *
    FROM states
    ORDER BY states_id DESC
")->fetchAll(PDO::FETCH_ASSOC);

?>

    <h2>EDIT CONTACTS PAGE</h2>

    <form action="<?php echo BASE_URL; ?>/adminix/edit-contacts.php" method="POST" autocomplete="off">

        <table width="100%" border="1">
            <tr>
                <td>
                    <label for="deceased">
                        Deceased:
                    </label>
                </td>
                <td>
                    <select name="deceased" id="deceased">
                        <?php
                        foreach ($yesno1 as $rowone) {
                            print '<option selected="'.$page['deceased'].'">'.$rowone['yesno'].'</option>';
                        }
                        ?>
                    </select>
                </td>
            </tr>
            <tr>
                <td>
                    <label for="active">
                        Active:
                    </label>
                </td>
                <td>
                    <select name="active" id="active">
                        <?php
                        foreach ($yesno2 as $rowtwo) {
                            print '<option value="'.$rowtwo['yesno_id'].'">'.$rowtwo['yesno'].'</option>';
                        }
                        ?>
                    </select>
                </td>
            </tr>
            <tr>
                <td>
                    <label for="realname">
                        Real Name:
                    </label>
                </td>
                <td>
                    <input type="text" name="realname" id="realname" value="<?php echo escape($page['realname']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="slug">
                        Slug:
                    </label>
                </td>
                <td>
                    <input type="text" name="slug" id="slug" value="<?php echo escape($page['slug']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="nickname">
                        Nick Name:
                    </label>
                </td>
                <td>
                    <input type="text" name="nickname" id="nickname" value="<?php echo escape($page['nickname']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="prefernick">
                        Prefer Nick Name:
                    </label>
                </td>
                <td>
                    <select name="prefernick" id="prefernick">
                        <?php
                        foreach ($yesno2 as $rowthree) {
                            print '<option value="'.$rowthree['yesno_id'].'">'.$rowthree['yesno'].'</option>';
                        }
                        ?>
                    </select>
                </td>
            </tr>
            <tr>
                <td>
                    <label for="dob">
                        Date of Birth:
                    </label>
                </td>
                <td>
                    <input type="date" name="dob" id="dob"  value="<?php echo escape($page['dob']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="address">
                        Address:
                    </label>
                </td>
                <td>
                    <input type="text" name="address" id="address"  value="<?php echo escape($page['address']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="city">
                        City:
                    </label>
                </td>
                <td>
                    <input type="text" name="city" id="city"  value="<?php echo escape($page['city']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="statelookup">
                        State:
                    </label>
                </td>
                <td>
                    <select name="statelookup" id="statelookup">
                        <?php
                        foreach ($states as $rowfour) {
                            print '<option value="'.$rowfour['states_id'].'">'.$rowfour['state_lookup'].'</option>';
                        }
                        ?>
                    </select>
                </td>
            </tr>
            <tr>
                <td>
                    <label for="zipcode">
                        Zip Code:
                    </label>
                </td>
                <td>
                    <input type="text" name="zipcode" id="zipcode"  value="<?php echo escape($page['zipcode']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="phone">
                        Phone:
                    </label>
                </td>
                <td>
                    <input type="text" name="phone" id="phone"  value="<?php echo escape($page['phone']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="email">
                        Email:
                    </label>
                </td>
                <td>
                    <input type="text" name="email" id="email"  value="<?php echo escape($page['email']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="emailtwo">
                        Email Two
                    </label>
                </td>
                <td>
                    <input type="text" name="emailtwo" id="emailtwo"  value="<?php echo escape($page['emailtwo']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="facebook">
                        Facebook:
                    </label>
                </td>
                <td>
                    <input type="text" name="facebook" id="facebook"  value="<?php echo escape($page['facebook']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="twitter">
                        Twitter:
                    </label>
                </td>
                <td>
                    <input type="text" name="twitter" id="twitter"  value="<?php echo escape($page['twitter']); ?>">
                </td>
            </tr>
            <tr>
                <td>
                    <input type="hidden" name="id" value="<?php echo escape($page['id']) ?>">
                    <input type="submit" value="Submit Edited Contact">
                </td>
            </tr>
        </table>

    </form>

<?php require VIEW_ROOT . '/templates/footer.php'; ?>

yesno 表有两个字段 yesno_id 和 yesno，yesno 字段的值为 Yes 和 No。 states 表有两个字段 states_id 和 state_lookup 以及美国 50 个州的列表。

如果您能提供任何帮助，我们将不胜感激。

Answer 1

好吧，我想我明白了。 yesno 表的结构不足以满足您的需求。事实上，我建议您根本不要使用该表。相反，将“已故”和“首选”的列类型更改为 enum .

deceased ENUM('Yes', 'No')
prefernick ENUM('Yes', 'NO')

然后在你的html中

<select name="deceased" id="deceased">
<option <?php echo $page['deceased'] === 'Yes' ? 'selected' : ''; ?> value="Yes">Yes</option>
<option <?php echo $page['deceased'] === 'No' ? 'selected' : ''; ?> value="No">No</option>
</select>

请务必对“prefernick”选择执行相同的操作。

在你的 PHP 中:

   //You forgot to prefix the keys with colons.
   $updatePage->execute([
        ':id' => $id,
        ':deceased' => $deceased,
        ':active' => $active,
        ':realname' => $realname,
        ':slug' => $slug,
        ':nickname' => $nickname,
        ':prefernick' => $prefernick,
        ':dob' => $dob,
        ':address' => $address,
        ':city' => $city,
        ':statelookup' => $statelookup,
        ':zipcode' => $zipcode,
        ':phone' => $phone,
        ':email' => $email,
        ':emailtwo' => $emailtwo,
        ':facebook' => $facebook,
        ':twitter' => $twitter,
    ]);

希望这有帮助