-3

Hello so this is the message I get when inserting duplicate key Fatal error: Uncaught mysqli_sql_exception: Duplicate entry 'admin' for key 'username_2' in C:\xampp\htdocs\WMSU\ILS\actions\update\updateUserName.php:29 Stack trace: #0 C:\xampp\htdocs\WMSU\ILS\actions\update\updateUserName.php(29): mysqli_stmt->execute() #1 {main} thrown in C:\xampp\htdocs\WMSU\ILS\actions\update\updateUserName.php on line 29 and I want to display an error message the " username already exist" like how my successful message is working

This is my update execute query and the else statement doesn't read and doesn't show error message. the username is unique so there should be a display of error message when I input a duplicate key from the database

<?php

session_start();
include("../../functions/connectDatabase.php"); 

if(isset($_POST['updateadviserdata']))
{
      //get input
    $id=$mysqli->real_escape_string($_POST['update_id']);
    $fn=$mysqli->real_escape_string($_POST['fname']);
    $mn=$mysqli->real_escape_string($_POST['mname']);
    $ln=$mysqli->real_escape_string($_POST['lname']);
    $un=$mysqli->real_escape_string($_POST['username']);

    //Prepared Statement
    $stmt = $mysqli->prepare("UPDATE users SET `firstname`= ?,`middlename`= ?, `lastname`= ?, `username` = ? WHERE `user_id` =?");

    //function for user activity
    $stmtUA = $mysqli->prepare("INSERT into history (activity,user_id,date_added) 
    VALUES ('update name',?,NOW() )");

  
    //bind parameter
    $stmt->bind_param("ssssi", $fn, $mn, $ln, $un, $id );
    $stmtUA->bind_param("i", $id);

   
    //execute query
    if($stmt->execute() && $stmtUA->execute()){
      $_SESSION['success']= "successfully change username"; 
      header('Location: ../../adviseraccount.php');
    } else {
        $_SESSION['error'] ="Username already exists";
        header('Location: ../../adviseraccount.php');
     
    }
   
 
 
      
    

   
 
    //close prepare statement
    $stmt->close();
    $stmtUA->close();
}

The success message is showing and working perfectly fine but the error message won't show and show me a fatal error instead what did I do wrong? I'm really new into prepared statement and i'm a beginner so I don't know if I'm doing it right, the username key from the table is set to unique and the error message is working fine before making it into prepared statement with this update execute query

I've tried the mysqli_errno == 1062 but it's not also working any help appreciated so much thank you I've been also trying everything I found on internet but still show the fatal error message

this is the connect database mysqli

<?php 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);


DEFINE('HOST', 'localhost');
DEFINE('USERNAME','root');
DEFINE('PASSWORD', '');
DEFINE('DATABASE', 'mafuyu');


//create conncection string
$mysqli = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);

//check connection

if ($mysqli->connect_error){
    die("Connection failed:" . $mysqli->connect_error );
}

$mysqli->set_charset("utf8mb4");

NOTE: the success message is working properly when i change the username if there is no duplicate entry

StoicHooman
  • 1
  • 2
  • Why are you executing the statement twice? `&` is bitwise AND, I think you want `&&` for logical AND. – Barmar Jan 23 '23 at 21:37
  • Could you also provide us with your MySQLi code? E.g. where you are preparing the SQL command. – David Jan 23 '23 at 21:40
  • Hello it's a different query I forgot to correct it – StoicHooman Jan 23 '23 at 21:40
  • Design note I: You get error message `"Username already exist"` even if db access, fails (connection broken, syntax error, ...) or the db doesn't accept the username (wrong chars, too long). – Wiimm Jan 23 '23 at 21:54
  • Design note II: What happens, if a user get first an error and second a success message. In this case both vars `success` and `error` are defined in the session environment. Better is to delete the other var, or to use status and message like: `errstatus=false/true' and `message="..."`. – Wiimm Jan 23 '23 at 21:57
  • hello thanks for replying I have updated my code and the description – StoicHooman Jan 23 '23 at 22:07
  • hello wiimm, i'm actually trying to show the error message because it was working fine before prepared statements and i'm practicing prepared statement as a beginner and the error message won't show anymore rathern than that it give me a fatal error from the updated description i have posted. – StoicHooman Jan 23 '23 at 22:19
  • You need to stop using `real_escape_string`. This will damage your data. – Dharman Jan 23 '23 at 22:21
  • Hello Dharman Thank you so much the error message is finally working after using thr try/catch from the link – StoicHooman Jan 23 '23 at 22:38

1 Answers1

-2

Use $stmt->errno to get the SQL error code.

if($stmt->execute()){
    $_SESSION['success']= "successfully change username";
} else{
    if ($stmt->errno == 1062) {
        $_SESSION['error'] ="Username already exists";
    else {
        $_SESSION['error'] = $stmt->error;
    }
}
header('Location: ../../adviser account.php');

Since you're redirecting to the same URL in all cases, there's no need for that to be in the if/else statements.

Barmar
  • 741,623
  • 53
  • 500
  • 612