0

I am trying to create a user registration form. However, when testing it on local machine, I get an error:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'ID' cannot be null.

I'm running it on AMPPS Apache, PHP 7.1 / MySQL. My OS is macOS Mojave 10.4. I've tried basically everything that was suggested on 'Integrity constraint violation: 1048' in previous answers, but still no clue. I hope somebody can help. Thanks for checking!

Here is the php: 

$event_owner_id=$_POST['event-owner-id'];
$event_owner_familyname=$_POST['event-owner-familyname'];
$event_owner_firstname=$_POST['event-owner-firstname'];
$event_owner_familyname_yomi=$_POST['event-owner-familyname-yomi'];
$event_owner_firstname_yomi=$_POST['event-owner-firstname-yomi'];
$event_owner_creditname=$_POST['event-owner-creditname'];
$event_owner_post=$_POST['event-owner-post']; 
$event_owner_address=$_POST['event-owner-address'];
$event_owner_phone=$_POST['event-owner-phone'];
$event_owner_mail=$_POST['event-owner-mail'];
$event_owner_pass=$_POST['event-owner-pass'];
$event_owner_pass_kaku=$_POST['event-owner-pass-kaku'];

try {
  $dsn = 'mysql:host=localhost;dbname=xxx;charset=utf8';
  $pdo = new PDO($dsn, $db['user'], $db['pass'], array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
  $sql = 'INSERT INTO User (ID, Type, LastName, FirstName, LastNameKana, FirstNameKana, Credit, PostCode, Address, PhoneNumber, eMailAddress, Password) VALUES (:ID, 002, :LastName, :FirstName, :LastNameKana, :FirstNameKana, :Credit, :PostCode, :Address, :PhoneNumber, :eMailAddress, :Password)';
  $stmt=$pdo->prepare($sql);
  $stmt -> bindParam(":ID", $event_owner_id, PDO::PARAM_STR);
  $stmt -> bindParam(":LastName", $event_owner_familyname, PDO::PARAM_STR);
  $stmt -> bindParam(":FirstName", $event_owner_firstname, PDO::PARAM_STR);
  $stmt -> bindParam(":LastNameKana", $event_owner_familyname_yomi, PDO::PARAM_STR);
  $stmt -> bindParam(":FirstNameKana", $event_owner_firstname_yomi, PDO::PARAM_STR);
  $stmt -> bindParam(":Credit", $event_owner_creditname, PDO::PARAM_STR);
  $stmt -> bindParam(":PostCode", $event_owner_post, PDO::PARAM_INT);
  $stmt -> bindParam(":Address", $event_owner_address, PDO::PARAM_STR);
  $stmt -> bindParam(":PhoneNumber", $event_owner_phone, PDO::PARAM_INT);
  $stmt -> bindParam(":eMailAddress", $event_owner_mail, PDO::PARAM_STR);
  $stmt -> bindParam(":Password", $event_owner_pass, PDO::PARAM_STR);
  $stmt->execute();
 } 
 catch (PDOException $e) {
 echo $e->getMessage();
 }

$pdo=null;
Alice Rivers
  • 1
  • 1
  • 3
  • 1
    Well, is `$event_owner_id` null? – aynber May 20 '19 at 13:35
  • 2
    What is this `:ID, 002,` – Masivuye Cokile May 20 '19 at 13:35
  • add `var_dump($_POST)` and see what value is there for `event-owner-id` – Masivuye Cokile May 20 '19 at 13:38
  • 1
    **Never store plain text passwords!** Please use ***PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html)*** to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). ***It is not necessary to [escape passwords](http://stackoverflow.com/q/36628418/1011527)*** or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. – Jay Blanchard May 20 '19 at 13:41

1 Answers1

0

As aynber suggested, the ID variable was actually null.
My registration form was supposed to include 3 pages:
1. HTML form
2. confirmation page
3. database insert and success message page.
The variables were passed only to confirmation page, and when it came to actual database insert, they indeed were all null. I was able to solve this with sessions: 

//form page HTML:
<form method="post" action="confirmation.php">
<input type="text" name="user-id" >

//confirmation page PHP:
session_start();
$_SESSION['user-id']=$_POST['user-id'];

//database insert and success message page PHP:
session_start();
$user_id = $_SESSION['user-id'];

Thanks everyone for your help.
Thanks Jay Blanchard for your note on password security. I will definitely work on that issue.

Alice Rivers
  • 1
  • 1
  • 3