Skip to content
Advertisement

Insert data in a different table sql/ php

Could you please help me i have two different tables

table tbl_users:

CREATE TABLE `tbl_users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `prenom` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `adresse` varchar(100)  DEFAULT NULL,
  `gender` varchar(10)  DEFAULT NULL,
  `roleid` tinyint(4) DEFAULT NULL,
  `isActive` tinyint(4) DEFAULT 0,
  `idjob`int(11),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

table travail:

create table `travail`(
`id`int(11) NOT NULL,
`job` varchar(200) Default ' ' NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I put idjob as a foreign key .*

And I want to insert the value of job in the user table and i don’ know how

the following code of “job” section

register.php:

<div class="form-group">
    <ul>
        <li> 
            <label >
                <input type="radio" name="job" value="traiteur" checked/>
                <img  class="imag" src="../img/traite.png" />
            </label>
        </li>
        <li>
            <label>
                <input type="radio" name="job" value="cuisinier"  />
                <img src="../img/cui.png" />
            </label>
        </li>
        <li>
            <label>
                <input type="radio" name="job" value="patissier" />
                <img src="../img/patis.png" />
            </label>
        </li>
        <li>
            <label>
                <input type="radio" name="job" value="stylistcheveux"  />
                <img src="../img/ha.webp" />
            </label>
        </li>
        <li>
            <label>
                <input type="radio" name="job" value="makeupartist"  />
                <img src="../img/make.png" />
            </label>
        </li>
        <li>

            <label>
                <input type="radio" name="job" value="stylistvetements" />
                <img src="../img/stv.png" />
            </label>
        </li>

        <li>
            <label >
                <input type="radio" name="job" value="photographe" />
                <img src="../img/ph.png" />
            </label>
        </li>
        <li>
            <label >
                <input type="radio"  name="job" value="musicien" />
                <img src="../img/d.png" />
            </label>
        </li>
        <li>
            <label >
                <input type="radio" name="job" value="dj"  />
                <img src="../img/dj.png" />
            </label>
        </li>
    </ul>
</div>
<div class="form-group">
    <button type="submit" name="register" class="btn btn-success">Register</button>
</div>

Users.php:

// User Registration Method
public function userRegistration($data){
    $name = $data['name'];
    $username = $data['username'];
    $email = $data['email'];
    $mobile = $data['mobile'];
    $prenom = $data['prenom'];
    $roleid = $data['roleid'];
    $password = $data['password'];

    $checkEmail = $this->checkExistEmail($email);

    if ($name == "" || $username == "" || $email == "" || $mobile == "" || $password == "" ) {
      $msg = '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg">
<a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
<strong>Error !</strong> Please, User Registration field must not be Empty !</div>';
        return $msg;
    }elseif (strlen($username) < 3) {
      $msg = '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg">
<a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
<strong>Error !</strong> Username is too short, at least 3 Characters !</div>';
        return $msg;
    }elseif (filter_var($mobile,FILTER_SANITIZE_NUMBER_INT) == FALSE) {
      $msg = '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg">
<a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
<strong>Error !</strong> Enter only Number Characters for Mobile number field !</div>';
        return $msg;

    }elseif(strlen($password) < 5) {
      $msg = '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg">
<a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
<strong>Error !</strong> Password at least 6 Characters !</div>';
        return $msg;
    }elseif(!preg_match("#[0-9]+#",$password)) {
      $msg = '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg">
<a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
<strong>Error !</strong> Your Password Must Contain At Least 1 Number !</div>';
        return $msg;
    }elseif(!preg_match("#[a-z]+#",$password)) {
      $msg = '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg">
<a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
<strong>Error !</strong> Your Password Must Contain At Least 1 Number !</div>';
        return $msg;
    }elseif (filter_var($email, FILTER_VALIDATE_EMAIL === FALSE)) {
      $msg = '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg">
<a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
<strong>Error !</strong> Invalid email address !</div>';
        return $msg;
    }elseif ($checkEmail == TRUE) {
      $msg = '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg">
<a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
<strong>Error !</strong> Email already Exists, please try another Email... !</div>';
        return $msg;
    }else{

      $sql = "INSERT INTO tbl_users(name, username, email, password, mobile,prenom,roleid) VALUES(:name, :username, :email, :password, :mobile,:prenom, :roleid)";
      $stmt = $this->db->pdo->prepare($sql);
      $stmt->bindValue(':name', $name);
      $stmt->bindValue(':username', $username);
      $stmt->bindValue(':email', $email);
      $stmt->bindValue(':password', SHA1($password));
      $stmt->bindValue(':mobile', $mobile);
      $stmt->bindValue(':prenom', $prenom);

      $stmt->bindValue(':roleid', $roleid);
      $result = $stmt->execute();
      if ($result) {
        $msg = '<div class="alert alert-success alert-dismissible mt-3" id="flash-msg">
            <a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
            <strong>Success !</strong> Wow, you have Registered Successfully !</div>';
        return $msg;
    }else{
        $msg = '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg">
            <a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
            <strong>Error !</strong> Something went Wrong !</div>';
        return $msg;
    }

Advertisement

Answer

In your register.php, it would be way more practical to have the ids of the jobs as values instead of having the names of the jobs. Then you can just plug that number in the insert just like you’re plugging in name and everything else. You can surround your input with labels instead of putting the names of the jobs in the value, like this:

<label><input type=radio name=job value=0> Cook</label>
<label><input type=radio name=job value=1> Driver</label>
<label><input type=radio name=job value=2> Superhero</label>

Then you do the validation of the field on the php side, like you’re doing to all other fields and add it to the query. The FK is just a regular field.

$sql = "INSERT INTO tbl_users(name, username, email, password, mobile,prenom,roleid) VALUES(:name, :username, :email, :password, :mobile,:prenom, :roleid, :jobid)";
$stmt = $this->db->pdo->prepare($sql);
$stmt->bindValue(':name', $name);
$stmt->bindValue(':username', $username);
$stmt->bindValue(':email', $email);
$stmt->bindValue(':password', SHA1($password));
$stmt->bindValue(':mobile', $mobile);
$stmt->bindValue(':prenom', $prenom);

$stmt->bindValue(':roleid', $roleid);
$stmt->bindValue(':jobid', $jobid);
$result = $stmt->execute();
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement