mardi 30 avril 2019

Store checkboxes data from php to database

I have the following tables in my database:

CREATE TABLE subjects (
  subject_id int(11) NOT NULL AUTO_INCREMENT,
  subject text,
  PRIMARY KEY (subject_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE users_subjects (
  users_subjects_id int(11) NOT NULL AUTO_INCREMENT,
  user_id_fk int(11),
  subject_id_fk int(11),
  FOREIGN KEY(user_id_fk) REFERENCES users(id),
  FOREIGN KEY(subject_id_fk) REFERENCES subjects(subject_id),
  PRIMARY KEY (users_subjects_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

In the table 'users_subjects' I’m trying to relate the 'subjects' and 'users' tables. All the data in the tables are entered from my index.php.

I introduce the subject_name from my index.php and every time I enter a new one, checkboxes like these are created in the part where the user is added:

enter image description here

This is the code to enter the user, where checkboxes are formed every time a subject is introduced (index.php):

<form method="post" action="register.php">
    <?php include('errors.php'); ?>
    <div class="input-group">
        <label>User</label>
        <input type="text" name="username" value="">
    </div>
    <div class="input-group">
        <label>Subjects</label>
    </div>
    <div>
        <?php
        $sql = "SELECT subject FROM subjects"; /*Select from table name: subjects*/
        $result = $conn->query($sql); /*Check connection*/
        if($result)
        {
            foreach($result as $row)
            {
                echo "<input type='checkbox' name='subject' value='" . htmlspecialchars($row['subject']) . "' /> <label>" . $row['subject'] . " </label><br>";
            }
        }
        ?>
    </div>

    <div class="input-group">
        <button type="submit" class="btn" name="reg_user">Add new user</button>
    </div>
</form>

I have managed to enter the user name in the 'users' table.

The problem I have is that I don’t know how to shore the checkboxes data in the 'users_subjects' table. I'm stuck and I can’t get it solved. Can somebody help me?

This is the code I’ve done for ‘register.php’:

<?php
$username = "";
$subject = "";
$errors = array();

include('Conexion.php');

if (isset($_POST['reg_user'])) {
    // receive all input values from the form
    $username = mysqli_real_escape_string($conn, $_POST['username']);
    $subject = mysqli_real_escape_string($conn, $_POST['subject']);


    if (empty($username)) { array_push($errors, "Username is required"); }
    if (empty($subject)) { array_push($errors, "Subject is required"); }

    $user_check_query = "SELECT * FROM users WHERE username='$username' LIMIT 1";
    $result = mysqli_query($conn, $user_check_query);
    $user = mysqli_fetch_assoc($result);

    if ($user) { // if user exists
        if ($user['username'] === $username) {
            array_push($errors, "Username already exists");
        }
    }
    // Register user if there are no errors in the form
    if (count($errors) == 0) {
        $query = "INSERT INTO users (username) 
           VALUES('$username')";
        mysqli_query($conn, $query);


        $insert_id = mysqli_insert_id($conn);
        $subject=implode(',',$_POST['subject']);
        //Count subjects and checks if the subject exists
        for($i=0; $i<count($subject); $i++) {

            $query = "SELECT subject_id FROM subject where subject='$subject[$i]'";
            $result = $conn->query($query); /*Check connection*/

            if ($result->num_rows > 0) {
                $row = $result->fetch_assoc();
                $subject_id = $row["subject_id"];
                $query = "INSERT INTO users_subjects (user_id_fk, subject_id_fk)
           VALUES('$insert_id', '$subject_id')";
                mysqli_query($conn, $query);
            } else {
                /*???*/
            }
        }



        header('location: indexAdmin.php');
    }
}
?>




Aucun commentaire:

Enregistrer un commentaire