Reputation: 51
I just finished a login-system and added another table for the useres personal information. For this reason, I have som inputs which aren't required. Textfields are nor problem but there is a problem with date and numberfields:
Since the User isn't forced to give all the information fields that aren't filledin should be NULL. But in case of date it alway gives me 0000-00-00 instead and in case of number its always 0.
I give you the whole code because I#m not sure about what is most important but I think its line 9, 10, 14-21, 73, 76
<?php
$user_id = $_GET['id'];
if(isset($_POST['add-personal-data'])){
$geschlecht = $_POST['geschlecht'];
$vorname = $_POST['vorname'];
$nachname = $_POST['nachname'];
$alter = $_POST['alter'];
$input_date = $_POST['geburtsdatum'];
$lieblingsziel = $_POST['lieblingsziel'];
$beschreibung = $_POST['beschreibung'];
if($input_date != null){
$geburtsdatum = date("Y-m-d H:i:s",strtotime($input_date));
} else {
$geburtsdatum = NULL;
}
$beschreibung_statement = $pdo->prepare("INSERT INTO beschreibung (user_id, name, vorname, geschlecht, user_alter, geburtsdatum, lieblingsziel, beschreibung)
VALUES ('$user_id', '$nachname', '$vorname', '$geschlecht', '$alter', '$geburtsdatum', '$lieblingsziel', '$beschreibung')");
$user_control_statement = $pdo->prepare("SELECT * FROM user WHERE user_id = '$user_id'");
$user_control_statement->execute();
$user = $user_control_statement->fetch();
if($user != NULL){
$user_has_description_statement = $pdo->prepare("SELECT * FROM beschreibung WHERE user_id = '$user_id'");
$user_has_description_statement->execute();
$description = $user_has_description_statement->fetch();
if($description == NULL){
$beschreibung_statement->execute();
} else {
echo ('<div class="line fehler"><p> Dieser Benutzer besitzt bereits eine Beschreibung. </p></div>');
}
} else {
echo ('<div class="line fehler"><p> Dieser Benutzer existiert nicht. </p></div>');
}
}
echo $geschlecht . "<br>" . $vorname . "<br>" . $nachname . "<br>" . $alter . "<br>" . $geburtsdatum . "<br>" . $lieblingsziel . "<br>" . $beschreibung ;
?>
<main>
<div class="line">
<div class="register">
<form method="post" action="?p=add-personal-data&id=<?php echo $user_id; ?>">
<h2>Persönliche Daten</h2>
<hr />
<p>Daten, die du hier angibst, werden auf deinem Profil angezeigt.</p>
<h4>Geschlecht</h4>
<select name="geschlecht" class="datafield">
<option>männlich</option>
<option>weiblich</option>
<option>anderes</option>
<option selected>keine Angabe</option>
</select>
<h4>Vorname</h4>
<input type="text" name="vorname" placeholder="Vorname" class="datafield" value="">
<h4>Nachname</h4>
<input type="text" name="nachname" placeholder="Nachname" class="datafield" value="">
<h4>Alter</h4>
<input type="number" name="alter" placeholder="Alter" class="datafield" value="">
<h4>Geburtsdatum</h4>
<input type="date" name="geburtsdatum" placeholder="Geburtsdatum" class="datafield" value="">
<h4>Lieblingsziel</h4>
<input type="text" name="lieblingsziel" placeholder="Lieblingsziel" class="datafield" value="">
<h4>Beschreibung</h4>
<textarea name="beschreibung" wrap="soft" class="datafield" rows="5" placeholder="Gib hier persönliche information, wie zum Beispiel Hobbies an."></textarea>
<div>
<input type="submit" name="add-personal-data" value="Speichern" class="submitbutton">
</div>
</form>
</div>
</div>
</main>
How can I set NULL to "geburtsdatum" and "user_alter" if the user doesn't give any information?
Upvotes: 2
Views: 10977
Reputation: 3723
If your geburtsdatum column already set to accept NULL values and Default set to NULL, you should instead separate the db statement into 2.
$geburtsdatum = date("Y-m-d H:i:s",strtotime($input_date));
if($input_date != null){
$beschreibung_statement = $pdo->prepare("INSERT INTO beschreibung (user_id, name, vorname, geschlecht, user_alter, geburtsdatum, lieblingsziel, beschreibung)
VALUES ('$user_id', '$nachname', '$vorname', '$geschlecht', '$alter', '$geburtsdatum', '$lieblingsziel', '$beschreibung')");
} else {
//this statement will assign NULL into geburtsdatum field
$beschreibung_statement = $pdo->prepare("INSERT INTO beschreibung (user_id, name, vorname, geschlecht, user_alter, lieblingsziel, beschreibung)
VALUES ('$user_id', '$nachname', '$vorname', '$geschlecht', '$alter', '$lieblingsziel', '$beschreibung')");
}
Upvotes: 0
Reputation: 3615
This is a solution outside prepare:
You set either $geburtsdatum
to string NULL
, or to the quoted (not only escaped) date.
if( $input_date != null ){
$geburtsdatum = '"'.date("Y-m-d H:i:s",strtotime($input_date)).'"';
} else {
$geburtsdatum = "NULL";
}
Then in the VALUES() part you write it without quotes:
VALUES ( ..., '$alter', $geburtsdatum, '$lieblingsziel', ... )
Upvotes: 0
Reputation: 2964
Set default value to NULL
or you can simply insert NULL
value if the user doesn't give any information. Plus if you set DEFAULT
to NULL
then you don't even need to assign it any value. It will automatically assign it NULL
You can add you insert query for NULL
simply like this
INSERT INTO beschreibung (geburtsdatum, user_alter, ...) VALUES (NULL,NULL,...);
Still, if you face problems then you need to go to phpmyadmin and make it allow null by checking this checkbox
Update
As Mangesh Sathe defined in the comment section you need to change the type to DATETIME
or TIMESTAMP
instead of date
Upvotes: 2
Reputation: 178
First of all set the default value of geburtsdatum column NULL
in your database. Now I think it would be better if you use 2 query strings or both if and else for geburtsdatum. If date is null, then do not insert it in the query.
Like:
$insert_query = '';
if($input_date != null){
$geburtsdatum = date("Y-m-d H:i:s",strtotime($input_date));
$insert_query = "INSERT INTO beschreibung (user_id, name, vorname, geschlecht, user_alter, geburtsdatum, lieblingsziel, beschreibung)
VALUES ('$user_id', '$nachname', '$vorname', '$geschlecht', '$alter', '$geburtsdatum', '$lieblingsziel', '$beschreibung')";
} else {
// there is no geburtsdatum in the query.
$insert_query = "INSERT INTO beschreibung (user_id, name, vorname, geschlecht, user_alter, lieblingsziel, beschreibung)
VALUES ('$user_id', '$nachname', '$vorname', '$geschlecht', '$alter', '$lieblingsziel', '$beschreibung')"
}
$beschreibung_statement = $pdo->prepare($insert_query);
Upvotes: 0