I have a form tag on my site that leads to the PHP page with email and/or/without description. Based on that the code generates a query, the query needs to update these credidentials. That part of the code works and has been tested. The problem is that the database is not updating the e-mail credidential, but if i put it to update the description it does so. The code has 3 checks, if the user puts only his email, if he puts only his description or puts both. Based on that the code works like this :
<?php
session_start();
include_once 'connection.php';
$id = $_SESSION['user_id'];
if(isset($_POST['emailChange']) || isset($_POST['descChange'])){
$desc = $_POST['descChange'];
$email = $_POST['emailChange'];
if(empty($email)){
$query = "UPDATE users SET description = :descr WHERE user_id= :id ;";
$stmt = $conn->prepare($query);
$stmt->bindParam(":descr", $desc);
} else if(empty($desc)){
$query = "UPDATE users SET user_email= :email WHERE user_id= :id ;";
$stmt = $conn->prepare($query);
$stmt->bindParam(":email", $email);
} else{
$query = "UPDATE users SET description = :descr AND user_email = :email WHERE user_id= :id;";
$stmt = $conn->prepare($query);
$stmt->bindParam(":email", $email);
$stmt->bindParam(":descr", $desc);
}
if(!filter_var($email, FILTER_VALIDATE_EMAIL)){
header("Location: ../profile.php?error=invalidEmail");
exit();
}
$stmt->bindParam(":id", $id);
$stmt->execute();
}
The form itself looks like this :
<form action="assets/upload.php" method="POST">
<input type="text" name="emailChange" class="inputs" id="changeEmail" placeholder = "Enter your new E-mail">
<input type="text" name="descChange" class="inputs" id="changeDesc" placeholder="Enter your description">
<button type="submit" id="btnconfirmCreds" name="changeCreds">Confirm Changes</button>
</form>
The names in the database looks like this :
[user_id][user_username][user_email][user_password][role_id][user_image][description][num_of_posts]
You should set up PDO error logging.
From Comments; paraphrased for clarity:
My
user_idcolumn isint(11) auto_increment
Your problem is you are trying to insert a string value into a numerical column in MySQL.
user_id / id in database parlance is usually a numerical value, but you have not set the value type in your SQL, so it defaults to string.
Because your :id value is a numeric value in PHP you need to do this:
$stmt->bindParam(":id", $id, 'i'); // i = integer type.
It is highly recommended to explicitly set the value of the data type supplied each and every time .
If the data given to the PDO does not match the value-type given, then the PDO transaction will void and will not complete. This is a security measure.
For example:
$id = 3;
$stmt->bindParam(":id", $id);
This is the same as saying:
$stmt->bindParam(":id", 3, 's'); // default type value is 's' for string.
Obviously the value 3 is not a string so this transacion ($stmt) is never performed.
i assume it's because it views the description as a special word, if that is true then i should change the name in my database. Thoughts?
"description" is neither a Keyword or a reserved word in MySQL 5.5-->5.7
(in MySQL 8.0.4 DESCRIPTION is a keyword but is not a reserved word)
You can view a list of MySQL Keywords and Reserved words .
Some notes about the logic:
if(isset($_POST['emailChange']) || isset($_POST['descChange']))
{
$desc = $_POST['descChange'];
$email = $_POST['emailChange'];
...
First you check, if at lease one parameter exists, but then you access both. You can argue, that the form send always both, but never believe user input: Manipulating data is so easy!
Either change your if(...) to:
if( isset($_POST['emailChange']) && isset($_POST['descChange']) )
The following line is a shorter form with identical semantics:
if( isset( $_POST['emailChange'], $_POST['descChange'] ) )
The other ways is to change the 2 other lines, for example by:
$desc = isset($_POST['descChange']) ? $_POST['descChange'] : '';
$email = isset($_POST['emailChange']) ? $_POST['emailChange'] : '';