Write data to database, based on selected checkboxes

#1

Hi. I don’t write PHP and have created the following by staggering around Google for a few days, and trying to piece together the whole thing from various sources.

I have a database (tutorial) which contains a users table. The fields are: id (primary key), whenDo, name, done, and taskName. The done field contains 0 or 1 - 1 to tick the checkbox, 0 to leave it empty.

I can populate the form from the database.

BUT - I can’t get it to write changes back to the database. I don’t get any error - it simply empties any checkboxes I’ve ticked.

  <!-- Form -->
  <form method='post' action=''>
    <input type='submit' value='Submit' name='but_submit'><br><br>

    <!-- Record list -->
    <table border='1' id='recordsTable' style='border-collapse: collapse;' >
      <tr>
        <th>Task</th>
        <th>When</th>
        <th>Cath</th>
        <th>Ben</th>
        <th>Alex</th>
        <th>Emily</th>
        <th>Connor</th>
     </tr>

     <?php 
     $host = "localhost"; /* Host name */
     $user = "root"; /* User */
     $password = ""; /* Password */
     $dbname = "tutorial"; /* Database name */

     $con = mysqli_connect($host, $user, $password,$dbname);
     // Check connection
     if (!$con) {
        die("Connection failed: " . mysqli_connect_error());
     }
     $query = "SELECT * FROM users";
     $result = mysqli_query($con,$query);
     
     while($row = mysqli_fetch_array($result) ){
        $id = $row['id'];
        $whenDo = $row['whenDo'];
        $name = $row['name'];
        $done = $row['done'];
        $taskName = $row['taskName'];
     ?>
     <tr>
        
        <td id='tr_<?= $id ?>'>
          <span><?= $taskName ?></span>
        </td>
        <td>
          <span><?= $whenDo ?></span>
        </td>
        <td>
          <span><input type='checkbox' name='update[]' value="1" class='<?= $name ?>' <?php if ($done == 1) echo 'checked'; ?> ></span>
        </td>
        <td>
          <span><input type='checkbox' name='update[]' value="1" class='<?= $name ?>' <?php if ($done == 1) echo 'checked'; ?>></span>
        </td>
        <td>
          <span><input type='checkbox' name='update[]' value="1" class='<?= $name ?>' <?php if ($done == 1) echo 'checked'; ?>></span>
        </td>
        <td>
          <span><input type='checkbox' name='update[]' value="1" class='<?= $name ?>' <?php if ($done == 1) echo 'checked'; ?>></span>
        </td>
        <td>
          <span><input type='checkbox' name='update[]' value="1" class='<?= $name ?>' <?php if ($done == 1) echo 'checked'; ?>></span>
        </td>
 
        </tr>
    <?php
    }
    ?>
    <?php 
    if(isset($_POST['but_submit'])){ 

      if(isset($_POST['update'])){
        foreach($_POST['update'] as $updateid){

          $updateUser = "REPLACE INTO users
          VALUES 
          ('$id',
          '$name',
          '$done',
          '$whenDo',
          '$taskName')";
        }
      }
     
    }
    ?>

   </table>
 </form>

Any advice would be greatly appreciated.

#2

My opinion piecing together causes more headaches and wastes your time.

My first advice if find a good current tutorial on how to populate a form and write back using PHP.

Second have most of the PHP code on top as much as possible and the HTML at the bottom.

Here’s an example -

<?php
require_once "../assets/config/config.php";
require_once "../vendor/autoload.php";

use PhotoTech\Register;
use PhotoTech\sendMail;


if (($_SERVER['REQUEST_METHOD'] === 'POST') && isset($_POST['submit'])) {

    $send = new sendMail();
    $data = [];

    $data['name'] = trim($_POST['user']['first_name'] . ' ' . $_POST['user']['last_name']);
    $data['validation'] = $send->validationFCN(20);
    $_POST['user']['validation'] = $data['validation'];

    $data['email'] = $_POST['user']['email'];
    $data['phone'] = $_POST['user']['phone'];
    $data['birthday'] = $_POST['user']['birthday'];

    $data['message'] =
        '<html lang="en">' .
        '<body style=\'background: #eee;\'>' .
        '<p style="font-size: 1.8em; line-height: 1.5;">Full Name : ' . $data['name'] .
        '<br>Email Address : ' . $data['email'] .
        '<br>Phone : ' . $data['phone'] .
        '<br>Birthday : ' . $data['birthday'] . '</p>' .
        '<p style="font-size: 1.4em; line-height: 1.5;">Please click on link: https://www.phototechguru.com/admin/activate.php?confirmation=' . $data['validation'] . ' in order to have access to The Photo Tech Guru Website.</p>' .
        '<p style="font-size: 1.4em; line-height: 1.5;">In addition please answer the question "Meet Me Under the [blank] Clock" with the name of the clock in the image that was sent.</p>' .
        '</body>' .
        '</html>';



    $register = new Register($_POST['user']);
    $result = $register->create();
    if ($result) {
        $send->verificationEmail($data);
        header("Location: success.php");
        exit();
    }

    header("Location: register.php");
    exit();
}

?>

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=yes, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Registration</title>
    <link rel="stylesheet" media="all" href="../assets/css/game.css">
</head>
<body class="site">
<div id="skip"><a href="#content">Skip to Main Content</a></div>
<header class="masthead">

</header>

<div class="nav">
    <input type="checkbox" id="nav-check">

    <h3 class="nav-title">
        The Photo Tech Guru
    </h3>

    <div class="nav-btn">
        <label for="nav-check">
            <span></span>
            <span></span>
            <span></span>
        </label>
    </div>

    <div class="nav-links">
        <a href="../gallery.php">Home</a>
        <a href="login.php">login</a>
    </div>
</div>

<div class="sidebar">
    <div class="info">
        <h2>Registration System</h2>
        <p>The Registration is up a running and is no longer in development after a long delay of me not concentrating on this website.</p>
    </div>
</div>
<main id="content" class="checkStyle">
    <form class="registerStyle" action="register.php" method="post">

        <div class="first">
            <label for="first_name">First Name</label>
            <input id="first_name" type="text" name="user[first_name]" value="" tabindex="2" required>
        </div>

        <div class="last">
            <label for="last_name">Last Name</label>
            <input id="last_name" type="text" name="user[last_name]" value="" tabindex="3" required>
        </div>

        <div class="screenName">
            <label class="text_username" for="username">Username <span class="error"> is unavailable!</span> </label>
            <input id="username" class="io_username" type="text" name="user[username]" value="" tabindex="4" required>
        </div>

        <div class="telephone">
            <label for="phone">Phone (Optional)</label>
            <input id="phone" type="tel" name="user[phone]" value="" placeholder="555-555-5555" pattern="[0-9]{3}-[0-9]{3}-[0-9]{4}" tabindex="5">

        </div>

        <div class="emailStyle">
            <label class="emailLabel" for="email">Email <span class="emailError"> is invalid</span> </label>
            <input id="email" type="email" name="user[email]" value="" tabindex="1" autofocus required>
        </div>

        <div class="password1">
            <label for="passwordBox">Password</label>
            <input class="passwordBox1" id="passwordBox" type="password" name="user[hashed_password]" value=""
                   tabindex="6" required>
            <label for="passwordVisibility">Show Passwords (private computer)</label>
            <input class="passwordBtn1" id="passwordVisibility" type="checkbox" tabindex="7">
        </div>

        <div class="password2">
            <label for="redoPassword">ReEnter Password</label>
            <input class="passwordBox2" id="redoPassword" type="password" name="user[redo]" value="" tabindex="8"
                   required>
        </div>

        <div class="birthday">
            <label for="birthday">Birthday (optional)</label>
            <input id="birthday" type="date" name="user[birthday]" value="1970-01-01" tabindex="9">
        </div>


        <div class="submitForm">
            <button class="submitBtn" id="submitForm" type="submit" name="submit" value="enter" tabindex="10">Submit</button>
        </div>


    </form>
</main>

<footer class="colophon">
    <p>&copy; <?php echo date("Y") ?> The Photo Tech Guru</p>
</footer>


<script>
    function passwordVisibility() {

        let passwordBox1 = document.querySelector(".passwordBox1");
        let passwordBox2 = document.querySelector(".passwordBox2");
        const fields = [passwordBox1, passwordBox2];
        fields.forEach(x => {
            if (x.type === "password") {
                x.type = "text";
            } else {
                x.type = "password";
            }

        });
    }

    document.querySelector(".passwordBtn1").addEventListener('click', passwordVisibility, false);

</script>
<script src="/assets/js/validation.js"></script>

</body>
</html>

Second use PDO instead of mysqli as it’s easier (my opinion) and more robust as you can use different databases.

or you can save yourself a bunch of time using a 3rd party app to do the dirty work for you, by that I mean read, write, update and delete from database tables if you don’t feel like coding.

#3

Your data definition and the output you are producing do not make sense. To record the task status per user per task, there must be one row of data for each each user for each task.

Your database should have a user(s) table, that holds the unique/one-time information about each user - id, first name, last name, username, email, hashed password, … This defines the user ids. You need a task table, that holds the unique/one-time information about each task - id, name, … This defines the the task ids. The table holding the user/task information would have columns for - id, user_id, task_id, date assigned, date due, and status. You would use a JOIN query to get the various related information from these tables, such as the user’s name (first, last), the task name, task due date, task status.

Next, the code for any page should be laid out in this general order -

  1. initialization
  2. post method form processing
  3. get method business logic - get/produce data needed to display the page
  4. html document

Since you are editing existing data, you would query for that data in item #3 on this list. However, after the form has been submitted, if there are any user/validation errors, you would want to instead use the data submitted from the form, rather than the existing data, to populate the form fields. The way to do this is to use an internal ‘working’ array variable to hold the data. You would initialize this variable to an empty array. Inside the post method form processing code, you would assign a trimmed copy of the form data to this variable. In the get method business logic, you would test if the variable is empty. If it is empty, it means that the form has never been submitted and you would query for the existing data, fetch it, and assign it to the array variable. You would use the contents of this array variable throughout the rest of the code.

#5

Thanks for that. I’m working my way through this tutorial - https://www.taniarascia.com/create-a-simple-crud-database-app-php-update-delete/ - but since this is a one-off project (a family holiday) I’m hoping to spend as little time on it as possible!

#6

Thanks, phdr. I understand what you’re saying about having multiple related tables, but this is a one-off task.

I do have one record of data for each user for each task.

image
image920×361 46.1 KB

#8

You can have more than one record in a different table for the user(s) and doesn’t mean there would be more than 2 tables. The user id would point to the user in the task table with its separate id. It is like using the Dewey system in a library (I’m probably dating myself :rofl:)

#9

Your next task would be to define how you are going to display the data. Are you only going to display the incomplete tasks? Are you going to display everything, but with the complete tasks at the top? Are you going to display the tasks grouped together by name, then with the completed task first and the incomplete tasks next? Are you only going to allow incomplete tasks to be checked, making them complete, or are you going to allow previously completed tasks to be unchecked?

In your existing code, you are not associating the checkbox with the row of data it goes with. You would want to use the id as the checkbox field name’s array index. Only checked checkboxes will be set in the submitted form data. You can then test/get the ids for the checked checkboxes to use in the rest of the form processing code.

Sponsor our Newsletter | Privacy Policy | Terms of Service