Can't insert values in database using form

Question

I have a form where users can register other accounts. It was working fine until I changed the data type of the column date to data type date (I was using varchar so I changed it to date). After changing the datatype, the registration stopped working. I don't get an error but I can't see the new account when I try to view the records.

Here's my form:

<div class="main">
  <div class="one">
    <div class="register">
      <center><h3>Add Account</h3></center>
      <form name="reg" action="code_exec.php" onsubmit="return validateForm()" method="post">
  <div>
          <label>ID</label>
          <input type="text" name="id" required>
        </div>
  <div>
          <label>First Name</label>
          <input type="text" name="firstname" required>
        </div>
  <div>
          <label>Last Name</label>
          <input type="text" name="lastname" required>
        </div>
        <div>
          <label>Email</label>
          <input type="text" name="email" placeholder="user@teamspan.com" required>
        </div>
        <div>
          <label>Username</label>
          <input type="text" name="username" required>
        </div>
        <div>
          <label>Password</label>
          <input type="password" name="password" required>
        </div>
  <div>
          <label>Street Address</label>
          <input type="text" name="street" required>
        </div>
  <div>
          <label>Town/Suburb</label>
          <input type="text" name="town" required>
        </div>
  <div>
          <label>City</label>
          <input type="text" name="city" required>
        </div>
  <div>
          <label>Contact</label>
          <input type="text" name="contact" required>
        </div>
  <div>
          <label>Gender</label>
   <select name="gender" required>
    <option disabled selected hidden>Select Gender</option>
    <option value="Male">Male</option>
    <option value="Female">Female</option>
   </select>
        </div>
  <div>
          <label>User Levels</label>
   <select name="user_levels" required>
    <option disabled selected hidden>Select Access Level</option>
    <option value="0">Employee</option>
    <option value="1">Administrator</option>
    <option value="2">Manager</option>
    <option value="1">HR</option>
   </select>
        </div>
  <div>
          <label>Date</label>
          <input type="text" readonly="readonly" name="date" value="<?php echo date("m/j/Y");?>" required>
        </div>
  <div>
          <label>Sick Leave</label>
          <input type="text" name="sickleave" required>
        </div>
  <div>
          <label>Vacation Leave</label>
          <input type="text" name="vacationleave" required>
        </div>
  <div>
          <label>Picture (Link)</label>
          <input type="text" name="picture" value="img/emp/" required>
        </div>
        <div>
          <label></label>
    <input type="submit" name="submit" value="Add Account" class="button" style="color: white;" />
    <a href="hr_panel.php"><input type="button" value="Back" class="button" style="color: white;" />
        </div>
      </form>
    </div>
  </div>

And here's code_exec.php

<?php

 session_start();
  
 include('connection.php');

 $id=$_POST['id']; 
 $username=$_POST['username'];
 $firstname=$_POST['firstname'];
 $lastname=$_POST['lastname'];
 $email=$_POST['email'];
 $street=$_POST['street'];
 $town=$_POST['town'];
 $city=$_POST['city'];
 $contact=$_POST['contact'];
 $gender=$_POST['gender'];
 $password=$_POST['password'];
 $user_levels=$_POST['user_levels'];
 $date=$_POST['date'];
 $picture=$_POST['picture'];
 $sickleave=$_POST['sickleave'];
 $vacationleave=$_POST['vacationleave'];
  
 mysqli_query($bd, "INSERT INTO employee(id, firstname, lastname, username, email, street, town, city, contact, gender, password, user_levels, date, picture, sickleave, vacationleave) 
    VALUES ('$id', '$firstname', '$lastname', '$username', '$email', '$street', '$town', '$city', '$contact', '$gender', '$password', '$user_levels', '$date', '$picture', '$sickleave', '$vacationleave')");
  
 echo "<script>alert('Successfully Added!'); window.location='register.php'</script>";
  
 mysqli_close($con);
 
?>

Database Schema:

DB Schema

You might have to convert the date, since you changed it to date type date the way the date is formatted is important. Imagine your database being ( y/m/d ) and your form being ( d/m/y ). You should check if they are both in the same format — Tomm, Mar 24 '18 at 08:57
Warning, your code is wide open to SQL injections. Please have a look to https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php and use parameterized queries as soon as possible. — Syscall, Mar 24 '18 at 08:58
@Tomm I'm not sure what you meant, I added my db schema above. Also, here's the format I used `` — Andre F, Mar 24 '18 at 09:00
@Syscall I will change it eventually, for now I'd have to figure this out. Thank you! — Andre F, Mar 24 '18 at 09:00
Unrelated to your question, but you are using `<label>` wrong. Either you add the 'for' attribute and give it the id of the corresponding `<input>` or wrap the label text and the input in the same label — Sebastian Speitel, Mar 24 '18 at 09:08
You might want to get the actual error output. Enable PHP error reporting, and add `echo mysqli_error($conn)` after the query. Also, you have mixed variables here, is it `$conn` or `$bd` that is the connection handle? What's inside your connection file? You attempt to query with one of them, and close the other. — Qirel, Mar 24 '18 at 09:10

score 1 · Accepted Answer · answered Mar 24 '18 at 09:03

1

As others have already stated, your date format may not be correct. And you need to look at securing your queries against sql injection.

In order to get you date issue fixed try replacing:

$date=$_POST['date'];

With:

$date=date('Y-m-d', strtotime($_POST['date']));

The Date format for sql is described as YYYY-MM-DD meaning a four digit year-two digit month - two digit day.

answered Mar 24 '18 at 09:03

Joseph_J

3,654
2
13
22

Noted. Thank you! – Andre F Mar 24 '18 at 09:08

score 0 · Answer 2 · answered Mar 24 '18 at 09:02

You need to convert the received date from your input date :

$dt = \DateTime::createFromFormat('m/j/Y', $_POST['date']);

See this StackOverflow answer for more informations.

Moreover, as @Syscall said, you should also pay attention to your query which is open to SQL injections. To prevent that, you should use a PDO statement, for example :

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

Example taken from How can I prevent SQL injection in PHP?

Can't insert values in database using form

2 Answers2