PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'user_bonds
Ik ben hier nogal nieuw dus ik hoop dat ik het topic in de juiste categorie heb geplaatst.
Maar laat ik het kort houden en gelijk naar het probleem gaan.
Ik heb nogal moeite om data naar mijn database te schrijven door middel van pdo en een query. De variabelen uit mijn register file moeten zijn doorgegeven naar mijn loginmodel. Maar telkens krijg ik deze error waaruit blijkt dat mijn variabelen niet null mogen zijn.
Frank Nietbelangrijk op 11/01/2015 11:35:41:
of je past de tabelstructuur aan zodat ze wel NULL mogen zijn.
Ahum, om een integrity constraint violation te vermijden de constraint verwijderen????
Ik begin me af te vragen hoe jou database modellen er uit zien :-P
Gewijzigd op 11/01/2015 14:03:20 door Ger van Steenderen
Ger van Steenderen op 11/01/2015 14:03:04:
de constraint verwijderen????
Dat heb ik niet gezegd.
Die constraint staat er niet voor niets, dus weghalen is absoluut geen optie.
Hopelijk kan iemand mij helpen of heeft de gouden tip
Hier is de code van de controller:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
<?php
/**
* Class Application
* The heart of the app
*/
class Application
{
/** @var null The controller part of the URL */
private $url_controller;
/** @var null The method part (of the above controller) of the URL */
private $url_action;
/** @var null Parameter one of the URL */
private $url_parameter_1;
/** @var null Parameter two of the URL */
private $url_parameter_2;
/** @var null Parameter three of the URL */
private $url_parameter_3;
/**
* Starts the Application
* Takes the parts of the URL and loads the according controller & method and passes the parameter arguments to it
* TODO: get rid of deep if/else nesting
* TODO: make the hardcoded locations ("error/index", "index.php", new Index()) dynamic, maybe via config.php
*/
public function __construct()
{
$this->splitUrl();
// check for controller: is the url_controller NOT empty ?
if ($this->url_controller) {
// check for controller: does such a controller exist ?
if (file_exists(CONTROLLER_PATH . $this->url_controller . '.php')) {
// if so, then load this file and create this controller
// example: if controller would be "car", then this line would translate into: $this->car = new car();
require CONTROLLER_PATH . $this->url_controller . '.php';
$this->url_controller = new $this->url_controller();
// check for method: does such a method exist in the controller ?
if ($this->url_action) {
if (method_exists($this->url_controller, $this->url_action)) {
// call the method and pass the arguments to it
if (isset($this->url_parameter_3)) {
$this->url_controller->{$this->url_action}($this->url_parameter_1, $this->url_parameter_2, $this->url_parameter_3);
} elseif (isset($this->url_parameter_2)) {
$this->url_controller->{$this->url_action}($this->url_parameter_1, $this->url_parameter_2);
} elseif (isset($this->url_parameter_1)) {
$this->url_controller->{$this->url_action}($this->url_parameter_1);
} else {
// if no parameters given, just call the method without arguments
$this->url_controller->{$this->url_action}();
}
} else {
// redirect user to error page (there's a controller for that)
header('location: ' . URL . 'error/index');
}
} else {
// default/fallback: call the index() method of a selected controller
$this->url_controller->index();
}
// obviously mistyped controller name, therefore show 404
} else {
// redirect user to error page (there's a controller for that)
header('location: ' . URL . 'error/index');
}
// if url_controller is empty, simply show the main page (index/index)
} else {
// invalid URL, so simply show home/index
require CONTROLLER_PATH . 'index.php';
$controller = new Index();
$controller->index();
}
}
/**
* Gets and splits the URL
*/
private function splitUrl()
{
if (isset($_GET['url'])) {
// split URL
$url = rtrim($_GET['url'], '/');
$url = filter_var($url, FILTER_SANITIZE_URL);
$url = explode('/', $url);
// Put URL parts into according properties
// By the way, the syntax here if just a short form of if/else, called "Ternary Operators"
// http://davidwalsh.name/php-shorthand-if-else-ternary-operators
$this->url_controller = (isset($url[0]) ? $url[0] : null);
$this->url_action = (isset($url[1]) ? $url[1] : null);
$this->url_parameter_1 = (isset($url[2]) ? $url[2] : null);
$this->url_parameter_2 = (isset($url[3]) ? $url[3] : null);
$this->url_parameter_3 = (isset($url[4]) ? $url[4] : null);
}
}
}
en dit is mijn function uit mn model:
public function registerNewUser()
{
// perform all necessary form checks
if (!$this->checkCaptcha()) {
$_SESSION["feedback_negative"][] = FEEDBACK_CAPTCHA_WRONG;
} elseif (empty($_POST['user_name'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_USERNAME_FIELD_EMPTY;
} elseif (empty($_POST['user_password_new']) OR empty($_POST['user_password_repeat'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_PASSWORD_FIELD_EMPTY;
} elseif ($_POST['user_password_new'] !== $_POST['user_password_repeat']) {
$_SESSION["feedback_negative"][] = FEEDBACK_PASSWORD_REPEAT_WRONG;
} elseif (strlen($_POST['user_password_new']) < 6) {
$_SESSION["feedback_negative"][] = FEEDBACK_PASSWORD_TOO_SHORT;
} elseif (strlen($_POST['user_name']) > 64 OR strlen($_POST['user_name']) < 2) {
$_SESSION["feedback_negative"][] = FEEDBACK_USERNAME_TOO_SHORT_OR_TOO_LONG;
} elseif (!preg_match('/^[a-z\d]{2,64}$/i', $_POST['user_name'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_USERNAME_DOES_NOT_FIT_PATTERN;
} elseif (empty($_POST['user_email'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_EMAIL_FIELD_EMPTY;
} elseif (strlen($_POST['user_email']) > 64) {
$_SESSION["feedback_negative"][] = FEEDBACK_EMAIL_TOO_LONG;
} elseif (!filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL)) {
$_SESSION["feedback_negative"][] = FEEDBACK_EMAIL_DOES_NOT_FIT_PATTERN;
} elseif (empty($_POST['user_persnaam'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_PERSNAAM_FIELD_EMPTY;
} elseif (empty($_POST['user_telefoonnummer'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_TELEFOONNUMMER_FIELD_EMPTY;
} elseif (empty($_POST['user_enkelsterkte'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_ENKELSTERKTE_FIELD_EMPTY;
} elseif (empty($_POST['user_dubbelsterkte'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_DUBBELSTERKTE_FIELD_EMPTY;
} elseif (empty($_POST['user_geslacht'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_GESLACHT_FIELD_EMPTY;
} elseif (empty($_POST['user_leeftijd'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_LEEFTIJD_FIELD_EMPTY;
} elseif (!empty($_POST['user_name'])
AND strlen($_POST['user_name']) <= 64
AND strlen($_POST['user_name']) >= 2
AND preg_match('/^[a-z\d]{2,64}$/i', $_POST['user_name'])
AND !empty($_POST['user_enkelsterkte'])
AND !empty($_POST['user_dubbelsterkte'])
AND !empty($_POST['user_telefoonnummer'])
AND !empty($_POST['user_geslacht'])
AND !empty($_POST['user_bondsnummer'])
AND !empty($_POST['user_persnaam'])
AND !empty($_POST['user_leeftijd'])
AND !empty($_POST['user_email'])
AND strlen($_POST['user_email']) <= 64
AND filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL)
AND !empty($_POST['user_password_new'])
AND !empty($_POST['user_password_repeat'])
AND ($_POST['user_password_new'] === $_POST['user_password_repeat'])) {
// clean the input
$user_name = strip_tags($_POST['user_name']);
$user_email = strip_tags($_POST['user_email']);
// crypt the user's password with the PHP 5.5's password_hash() function, results in a 60 character
// hash string. the PASSWORD_DEFAULT constant is defined by the PHP 5.5, or if you are using PHP 5.3/5.4,
// by the password hashing compatibility library. the third parameter looks a little bit shitty, but that's
// how those PHP 5.5 functions want the parameter: as an array with, currently only used with 'cost' => XX
$hash_cost_factor = (defined('HASH_COST_FACTOR') ? HASH_COST_FACTOR : null);
$user_password_hash = password_hash($_POST['user_password_new'], PASSWORD_DEFAULT, array('cost' => $hash_cost_factor));
// check if username already exists
$query = $this->db->prepare("SELECT * FROM users WHERE user_name = :user_name");
$query->execute(array(':user_name' => $user_name));
$count = $query->rowCount();
if ($count == 1) {
$_SESSION["feedback_negative"][] = FEEDBACK_USERNAME_ALREADY_TAKEN;
return false;
}
// check if email already exists
$query = $this->db->prepare("SELECT user_id FROM users WHERE user_email = :user_email");
$query->execute(array(':user_email' => $user_email));
$count = $query->rowCount();
if ($count == 1) {
$_SESSION["feedback_negative"][] = FEEDBACK_USER_EMAIL_ALREADY_TAKEN;
return false;
}
// generate random hash for email verification (40 char string)
$user_activation_hash = sha1(uniqid(mt_rand(), true));
// generate integer-timestamp for saving of account-creating date
$user_creation_timestamp = time();
// write new users data into database
$sql = "INSERT INTO users (user_name, user_password_hash, user_email, user_bondsnummer, user_telefoonnummer, user_leeftijd, user_geslacht, user_persnaam, user_dubbelsterkte, user_enkelsterkte, user_creation_timestamp, user_activation_hash, user_provider_type)
VALUES (:user_name, :user_password_hash, :user_email, :user_bondsnummer, :user_telefoonnummer, :user_leeftijd, :user_geslacht, :user_persnaam, :user_dubbelsterkte, :user_enkelsterkte, :user_creation_timestamp, :user_activation_hash, :user_provider_type)";
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if ($query = $this->db->prepare($sql)) {
$query->execute(array(':user_name' => $user_name,
':user_password_hash' => $user_password_hash,
':user_email' => $user_email,
':user_bondsnummer' => $user_bondsnummer,
':user_telefoonnummer' => $user_telefoonnummer,
':user_leeftijd' => $user_leeftijd,
':user_geslacht' => $user_geslacht,
':user_persnaam' => $user_persnaam,
':user_dubbelsterkte' => $user_dubbelsterkte,
':user_enkelsterkte' => $user_enkelsterkte,
':user_creation_timestamp' => $user_creation_timestamp,
':user_activation_hash' => $user_activation_hash,
':user_provider_type' => 'DEFAULT'));
}else{
print_r($_POST);
echo "\nPDO::errorInfo():\n";
print_r($this->db->errorInfo());
}
/**
* Class Application
* The heart of the app
*/
class Application
{
/** @var null The controller part of the URL */
private $url_controller;
/** @var null The method part (of the above controller) of the URL */
private $url_action;
/** @var null Parameter one of the URL */
private $url_parameter_1;
/** @var null Parameter two of the URL */
private $url_parameter_2;
/** @var null Parameter three of the URL */
private $url_parameter_3;
/**
* Starts the Application
* Takes the parts of the URL and loads the according controller & method and passes the parameter arguments to it
* TODO: get rid of deep if/else nesting
* TODO: make the hardcoded locations ("error/index", "index.php", new Index()) dynamic, maybe via config.php
*/
public function __construct()
{
$this->splitUrl();
// check for controller: is the url_controller NOT empty ?
if ($this->url_controller) {
// check for controller: does such a controller exist ?
if (file_exists(CONTROLLER_PATH . $this->url_controller . '.php')) {
// if so, then load this file and create this controller
// example: if controller would be "car", then this line would translate into: $this->car = new car();
require CONTROLLER_PATH . $this->url_controller . '.php';
$this->url_controller = new $this->url_controller();
// check for method: does such a method exist in the controller ?
if ($this->url_action) {
if (method_exists($this->url_controller, $this->url_action)) {
// call the method and pass the arguments to it
if (isset($this->url_parameter_3)) {
$this->url_controller->{$this->url_action}($this->url_parameter_1, $this->url_parameter_2, $this->url_parameter_3);
} elseif (isset($this->url_parameter_2)) {
$this->url_controller->{$this->url_action}($this->url_parameter_1, $this->url_parameter_2);
} elseif (isset($this->url_parameter_1)) {
$this->url_controller->{$this->url_action}($this->url_parameter_1);
} else {
// if no parameters given, just call the method without arguments
$this->url_controller->{$this->url_action}();
}
} else {
// redirect user to error page (there's a controller for that)
header('location: ' . URL . 'error/index');
}
} else {
// default/fallback: call the index() method of a selected controller
$this->url_controller->index();
}
// obviously mistyped controller name, therefore show 404
} else {
// redirect user to error page (there's a controller for that)
header('location: ' . URL . 'error/index');
}
// if url_controller is empty, simply show the main page (index/index)
} else {
// invalid URL, so simply show home/index
require CONTROLLER_PATH . 'index.php';
$controller = new Index();
$controller->index();
}
}
/**
* Gets and splits the URL
*/
private function splitUrl()
{
if (isset($_GET['url'])) {
// split URL
$url = rtrim($_GET['url'], '/');
$url = filter_var($url, FILTER_SANITIZE_URL);
$url = explode('/', $url);
// Put URL parts into according properties
// By the way, the syntax here if just a short form of if/else, called "Ternary Operators"
// http://davidwalsh.name/php-shorthand-if-else-ternary-operators
$this->url_controller = (isset($url[0]) ? $url[0] : null);
$this->url_action = (isset($url[1]) ? $url[1] : null);
$this->url_parameter_1 = (isset($url[2]) ? $url[2] : null);
$this->url_parameter_2 = (isset($url[3]) ? $url[3] : null);
$this->url_parameter_3 = (isset($url[4]) ? $url[4] : null);
}
}
}
en dit is mijn function uit mn model:
public function registerNewUser()
{
// perform all necessary form checks
if (!$this->checkCaptcha()) {
$_SESSION["feedback_negative"][] = FEEDBACK_CAPTCHA_WRONG;
} elseif (empty($_POST['user_name'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_USERNAME_FIELD_EMPTY;
} elseif (empty($_POST['user_password_new']) OR empty($_POST['user_password_repeat'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_PASSWORD_FIELD_EMPTY;
} elseif ($_POST['user_password_new'] !== $_POST['user_password_repeat']) {
$_SESSION["feedback_negative"][] = FEEDBACK_PASSWORD_REPEAT_WRONG;
} elseif (strlen($_POST['user_password_new']) < 6) {
$_SESSION["feedback_negative"][] = FEEDBACK_PASSWORD_TOO_SHORT;
} elseif (strlen($_POST['user_name']) > 64 OR strlen($_POST['user_name']) < 2) {
$_SESSION["feedback_negative"][] = FEEDBACK_USERNAME_TOO_SHORT_OR_TOO_LONG;
} elseif (!preg_match('/^[a-z\d]{2,64}$/i', $_POST['user_name'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_USERNAME_DOES_NOT_FIT_PATTERN;
} elseif (empty($_POST['user_email'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_EMAIL_FIELD_EMPTY;
} elseif (strlen($_POST['user_email']) > 64) {
$_SESSION["feedback_negative"][] = FEEDBACK_EMAIL_TOO_LONG;
} elseif (!filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL)) {
$_SESSION["feedback_negative"][] = FEEDBACK_EMAIL_DOES_NOT_FIT_PATTERN;
} elseif (empty($_POST['user_persnaam'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_PERSNAAM_FIELD_EMPTY;
} elseif (empty($_POST['user_telefoonnummer'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_TELEFOONNUMMER_FIELD_EMPTY;
} elseif (empty($_POST['user_enkelsterkte'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_ENKELSTERKTE_FIELD_EMPTY;
} elseif (empty($_POST['user_dubbelsterkte'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_DUBBELSTERKTE_FIELD_EMPTY;
} elseif (empty($_POST['user_geslacht'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_GESLACHT_FIELD_EMPTY;
} elseif (empty($_POST['user_leeftijd'])) {
$_SESSION["feedback_negative"][] = FEEDBACK_LEEFTIJD_FIELD_EMPTY;
} elseif (!empty($_POST['user_name'])
AND strlen($_POST['user_name']) <= 64
AND strlen($_POST['user_name']) >= 2
AND preg_match('/^[a-z\d]{2,64}$/i', $_POST['user_name'])
AND !empty($_POST['user_enkelsterkte'])
AND !empty($_POST['user_dubbelsterkte'])
AND !empty($_POST['user_telefoonnummer'])
AND !empty($_POST['user_geslacht'])
AND !empty($_POST['user_bondsnummer'])
AND !empty($_POST['user_persnaam'])
AND !empty($_POST['user_leeftijd'])
AND !empty($_POST['user_email'])
AND strlen($_POST['user_email']) <= 64
AND filter_var($_POST['user_email'], FILTER_VALIDATE_EMAIL)
AND !empty($_POST['user_password_new'])
AND !empty($_POST['user_password_repeat'])
AND ($_POST['user_password_new'] === $_POST['user_password_repeat'])) {
// clean the input
$user_name = strip_tags($_POST['user_name']);
$user_email = strip_tags($_POST['user_email']);
// crypt the user's password with the PHP 5.5's password_hash() function, results in a 60 character
// hash string. the PASSWORD_DEFAULT constant is defined by the PHP 5.5, or if you are using PHP 5.3/5.4,
// by the password hashing compatibility library. the third parameter looks a little bit shitty, but that's
// how those PHP 5.5 functions want the parameter: as an array with, currently only used with 'cost' => XX
$hash_cost_factor = (defined('HASH_COST_FACTOR') ? HASH_COST_FACTOR : null);
$user_password_hash = password_hash($_POST['user_password_new'], PASSWORD_DEFAULT, array('cost' => $hash_cost_factor));
// check if username already exists
$query = $this->db->prepare("SELECT * FROM users WHERE user_name = :user_name");
$query->execute(array(':user_name' => $user_name));
$count = $query->rowCount();
if ($count == 1) {
$_SESSION["feedback_negative"][] = FEEDBACK_USERNAME_ALREADY_TAKEN;
return false;
}
// check if email already exists
$query = $this->db->prepare("SELECT user_id FROM users WHERE user_email = :user_email");
$query->execute(array(':user_email' => $user_email));
$count = $query->rowCount();
if ($count == 1) {
$_SESSION["feedback_negative"][] = FEEDBACK_USER_EMAIL_ALREADY_TAKEN;
return false;
}
// generate random hash for email verification (40 char string)
$user_activation_hash = sha1(uniqid(mt_rand(), true));
// generate integer-timestamp for saving of account-creating date
$user_creation_timestamp = time();
// write new users data into database
$sql = "INSERT INTO users (user_name, user_password_hash, user_email, user_bondsnummer, user_telefoonnummer, user_leeftijd, user_geslacht, user_persnaam, user_dubbelsterkte, user_enkelsterkte, user_creation_timestamp, user_activation_hash, user_provider_type)
VALUES (:user_name, :user_password_hash, :user_email, :user_bondsnummer, :user_telefoonnummer, :user_leeftijd, :user_geslacht, :user_persnaam, :user_dubbelsterkte, :user_enkelsterkte, :user_creation_timestamp, :user_activation_hash, :user_provider_type)";
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if ($query = $this->db->prepare($sql)) {
$query->execute(array(':user_name' => $user_name,
':user_password_hash' => $user_password_hash,
':user_email' => $user_email,
':user_bondsnummer' => $user_bondsnummer,
':user_telefoonnummer' => $user_telefoonnummer,
':user_leeftijd' => $user_leeftijd,
':user_geslacht' => $user_geslacht,
':user_persnaam' => $user_persnaam,
':user_dubbelsterkte' => $user_dubbelsterkte,
':user_enkelsterkte' => $user_enkelsterkte,
':user_creation_timestamp' => $user_creation_timestamp,
':user_activation_hash' => $user_activation_hash,
':user_provider_type' => 'DEFAULT'));
}else{
print_r($_POST);
echo "\nPDO::errorInfo():\n";
print_r($this->db->errorInfo());
}