Asked  7 Months ago    Answers:  5   Viewed   58 times

I have a PHP form which enters data into my MySQL database. My primary key is one of the user-entered values. When the user enters a value that already exists in the table, the MySQL error "Duplicate entry 'entered value' for key 1" is returned. Instead of that error, I would like to alert the user that they need to enter a different value. Just an echoed message or something. I guess my question comes down to: how to turn a specific MySQL error into a PHP message Thanks

edit: nickf's answer below is nice, but is there any way to discern between specific errors?

 Answers

19

To check for this specific error, you need to find the error code. It is 1062 for duplicate key. Then use the result from errno() to compare with:

mysqli_query('INSERT INTO ...');
if (mysqli_errno() == 1062) {
    print 'no way!';
}

A note on programming style
You should always seek to avoid the use of magic numbers (I know, I was the one to introduce it in this answer). Instead, you could assign the known error code (1062) to a constant (e.g. MYSQLI_CODE_DUPLICATE_KEY). This will make your code easier to maintain as the condition in the if statement is still readable in a few months when the meaning of 1062 has faded from memory :)

Wednesday, March 31, 2021
 
jenny
answered 7 Months ago
24

You can use magic constants, or debug_backtrace, or write your own error handler.

Wednesday, March 31, 2021
 
TecHunter
answered 7 Months ago
48

I got a bug in resetpass.php

You'l first have to use $_GET['code'] to get your activation code and store in a hidden field of

here is modified code, that should work.

<?php

if(isset($_POST['pass'])){
$pass = $_POST['pass'];
$acode=$_POST['code'];

$con=mysqli_connect("xxx","xxx","xxx","xxx");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$query = mysqli_query($con,"select * from login where activation_code='$acode'")
or die(mysqli_error($con)); 

if (mysqli_num_rows ($query)==1) 
{
$query3 = mysqli_query($con,"update login set Password='$pass' where activation_code='$acode'")
or die(mysqli_error($con)); 

echo 'Password Changed';
}
else
{
echo 'Wrong CODE';
}
}
?>

<form action="resetpass.php" method="POST">
<p>New Password:</p><input type="password" name="pass" />
<input type="submit"  name="submit" value="Signup!" />
<input type="hidden" name="code" value="<?php echo $_GET['code'];?>" />
</form>
Saturday, May 29, 2021
 
turik
answered 5 Months ago
21

All you need to do is to click on the WAMP server icon in the system tray and go to mySQL and my.ini. Open the file in a text editor like notepad. At the top of the page there should be a line

socket = /tmp/mysql.sock

change that to

socket = c:/tmp/mysql.sock

Save the file and try running phpmyAdmin. Restart wamp. It should work perfectly.

If this does not fix the error for you let me know and I will try and help.

Saturday, May 29, 2021
 
Eugenie
answered 5 Months ago
35

Found problems:

  • First code line is wrong: <? php. The php file presents then the code like a text file. Correct: <?php.
  • Before the second !preg_match... in the if statement is a wrong character (. Correct: to be removed.
  • In php code, when writing comment lines without starting with //, or outside /* ... */, an error occurres.
  • Wrong: $POST['submit']. The correct global variable for the HTTP POST method is named $_POST. So, the correct code is: $_POST['submit'].
  • If you define <button>Sign Up</button>, then you have to give it a name (like <button name="submit">Sign Up</button>) in order to be able to reference it by it with if (isset($_POST['submit'])) {...}.
  • Wrong: .../abc/xyz.php. Correct: ../abc/xyz.php.
  • Wrong: if (... || empty($email) || ($password)) {...}. Correct: if (... || empty($email) || empty($password)) {...}.

Suggestions:

  • Put the php processing code in signup.php, instead of in signup.func.php and change it correspondingly. The form's action attribute will be signup.php, or "" for HTML5, then. That way you are able to directly (e.g. on-site) display any message to the user and also to perform only a single redirect in the end, when the sign-up processing is successful (for example to a login.php page).
  • Apply a good error reporting system, in order to see and correspondingly handle the errors and exceptions that the php engine might throw. For mysqli specifically: here. For PDO here.
  • Start using the so called prepared statements, in order to prevent SQL injection. Read this little, but effective article.
  • Start using the object-oriented mysqli extension instead of the procedural one - each mysqli function have the php.net documentation presented in both forms. Even better: start using the PDO extension instead of mysqli. Here the (same as above) tutorial.

The alternative code (with object-oriented mysqli), as promised

It includes my above suggestions. Run it as it is to test it. But change the table structure first (see title Used table structure below), change the db credentials in connection.php (see title includes/connection.php below) and create a filesystem structure as suggested by the titles of the code parts below.

To toggle from a production environment (when the errors are not shown on screen) to a development environment (when all raised errors are displayed on screen), just change the value of the constant APP_ENV (in handlers.php) from 'prod' to 'dev' and back (see title includes/handlers.php). And then, to test it, rename the table name "users" to another wrong name, for example. Or set the first of the two sql statements to NULL: $sql = NULL;.

signup.php

<?php
require 'includes/handlers.php';
require 'includes/connection.php';

// Signalize if a new account could be created, or not.
$accountCreated = FALSE;

/*
 * ====================================
 * Operations upon form submission.
 * ====================================
 */
if (isset($_POST['submit'])) {
    /*
     * ====================================
     * Read the posted values.
     * ====================================
     */
    $firstName = isset($_POST['firstName']) ? $_POST['firstName'] : '';
    $lastName = isset($_POST['lastName']) ? $_POST['lastName'] : '';
    $email = isset($_POST['email']) ? $_POST['email'] : '';
    $username = isset($_POST['username']) ? $_POST['username'] : '';
    $password = isset($_POST['password']) ? $_POST['password'] : '';

    /*
     * ====================================
     * Validate all posted values together.
     * ====================================
     */
    if (empty($firstName) && empty($lastName) && empty($email) && empty($username) && empty($password)) {
        $errors[] = 'All values are mandatory. Please provide them.';
    }

    /*
     * ====================================
     * Validate each value separately.
     * ====================================
     */
    if (!isset($errors)) {
        // Validate the first name.
        if (empty($firstName)) {
            $errors[] = 'Please provide a first name.';
        } elseif (!preg_match('/^[a-zA-Z]*$/', $firstName)) {
            $errors[] = 'The first name contains invalid characters.';
        } /* Other validations here using elseif statements */

        // Validate the last name.
        if (empty($lastName)) {
            $errors[] = 'Please provide a last name.';
        } elseif (!preg_match('/^[a-zA-Z]*$/', $lastName)) {
            $errors[] = 'The last name contains invalid characters.';
        } /* Other validations here using elseif statements */

        // Validate the email.
        if (empty($email)) {
            $errors[] = 'Please provide an email address.';
        } elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $errors[] = 'The email address is not in a valid format.';
        } /* Other validations here using elseif statements */

        // Validate the username.
        if (empty($username)) {
            $errors[] = 'Please provide a username.';
        } /* Other validations here using elseif statements */

        // Validate the password.
        if (empty($password)) {
            $errors[] = 'Please provide a password.';
        } /* Other validations here using elseif statements */
    }

    /*
     * ====================================
     * Check if user exists. Save if not.
     * ====================================
     */
    if (!isset($errors)) {
        /*
         * ====================================
         * Check if user already exists.
         * ====================================
         */

        /*
         * The SQL statement to be prepared. Notice the so-called markers,
         * e.g. the "?" signs. They will be replaced later with the
         * corresponding values when using mysqli_stmt::bind_param.
         *
         * @link http://php.net/manual/en/mysqli.prepare.php
         */
        $sql = 'SELECT COUNT(*)
                FROM users
                WHERE username = ?';

        /*
         * Prepare the SQL statement for execution - ONLY ONCE.
         *
         * @link http://php.net/manual/en/mysqli.prepare.php
         */
        $statement = $connection->prepare($sql);

        /*
         * Bind variables for the parameter markers (?) in the
         * SQL statement that was passed to prepare(). The first
         * argument of bind_param() is a string that contains one
         * or more characters which specify the types for the
         * corresponding bind variables.
         *
         * @link http://php.net/manual/en/mysqli-stmt.bind-param.php
         */
        $statement->bind_param('s', $username);

        /*
         * Execute the prepared SQL statement.
         * When executed any parameter markers which exist will
         * automatically be replaced with the appropriate data.
         *
         * @link http://php.net/manual/en/mysqli-stmt.execute.php
         */
        $statement->execute();

        /*
         * Transfer the result set resulted from executing the prepared statement.
         * E.g. store, e.g. buffer the result set into the (same) prepared statement.
         *
         * @link http://php.net/manual/en/mysqli-stmt.store-result.php
         * @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
         */
        $statement->store_result();

        /*
         * Bind the result set columns to corresponding variables.
         * E.g. these variables will hold the column values after fetching.
         *
         * @link http://php.net/manual/en/mysqli-stmt.bind-result.php
         */
        $statement->bind_result($numberOfFoundUsers);

        /*
         * Fetch the results from the result set (of the prepared statement) into the bound variables.
         *
         * @link http://php.net/manual/en/mysqli-stmt.fetch.php
         */
        $statement->fetch();

        /*
         * Free the stored result memory associated with the statement,
         * which was allocated by mysqli_stmt::store_result.
         *
         * @link http://php.net/manual/en/mysqli-result.free.php
         */
        $statement->free_result();

        /*
         * Close the prepared statement. It also deallocates the statement handle.
         * If the statement has pending or unread results, it cancels them
         * so that the next query can be executed.
         *
         * @link http://php.net/manual/en/mysqli-stmt.close.php
         */
        $statement->close();

        if ($numberOfFoundUsers > 0) {
            $errors[] = 'The given username already exists. Please choose another one.';
        } else {
            /*
             * ====================================
             * Save a new user account.
             * ====================================
             */
            // Create a password hash.
            $passwordHash = password_hash($password, PASSWORD_BCRYPT);

            $sql = 'INSERT INTO users (
                        first_name,
                        last_name,
                        email,
                        username,
                        password
                    ) VALUES (
                        ?, ?, ?, ?, ?
                    )';

            $statement = $connection->prepare($sql);
            $statement->bind_param('sssss', $firstName, $lastName, $email, $username, $passwordHash);
            $statement->execute();

            // Signalize that a new account was successfully created.
            $accountCreated = TRUE;

            // Reset all values so that they are not shown in the form anymore.
            $firstName = $lastName = $email = $username = $password = NULL;
        }
    }
}
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
        <meta charset="UTF-8" />
        <!-- The above 3 meta tags must come first in the head -->

        <title>Demo - Sign Up </title>

        <!--<link href="assets/images/favicon.ico" rel="icon" type="image/png" />-->

        <!-- CSS assets -->
        <link href="https://fonts.googleapis.com/css?family=Open+Sans:300,400,600,700,800" rel="stylesheet">
        <link href="https://fonts.googleapis.com/css?family=Roboto+Condensed:300,400,700" rel="stylesheet">
        <link href="assets/css/app.css" type="text/css" rel="stylesheet">
        <link href="assets/css/signup.css" type="text/css" rel="stylesheet">

        <!-- JS assets -->
        <script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript"></script>
    </head>
    <body>

        <div class="page-container">

            <nav class="navbar">
                <ul class="navbar-nav">
                    <li>
                        <a href="#">Home</a>
                    </li>
                    <li>
                        <a href="#">About Us</a>
                    </li>
                    <li>
                        <a href="#">Login</a>
                    </li>
                    <li>
                        <a href="signup.php" class="active">Sign Up</a>
                    </li>
                </ul>
            </nav>

            <header class="page-header">
                <h2 class="page-title">
                    Sign Up
                </h2>
                <div class="page-subtitle">
                    Hello. We are happy to see you here. Please fill in the form to register.
                </div>
            </header>

            <section class="page-content">

                <section class="form-container-outer">
                    <section class="form-container-inner">
                        <?php
                        if (isset($errors)) {
                            ?>
                            <div class="messages danger">
                                <?php echo implode('<br/>', $errors); ?>
                            </div>
                            <?php
                        } elseif ($accountCreated) {
                            ?>
                            <div class="messages success">
                                You have successfully created your account.
                                <br/>Would you like to <a href="#">login</a> now?
                            </div>
                            <?php
                        }
                        ?>

                        <form id="signup-form" action="" method="post">

                            <div class="form-group">
                                <label for="firstName">First Name <span class="mandatory">*</span></label>
                                <input type="text" id="firstName" name="firstName" value="<?php echo isset($firstName) ? $firstName : ''; ?>" placeholder="First Name" required>
                            </div>

                            <div class="form-group">
                                <label for="lastName">Last Name <span class="mandatory">*</span></label>
                                <input type="text" id="lastName" name="lastName" value="<?php echo isset($lastName) ? $lastName : ''; ?>" placeholder="Last Name" required>
                            </div>

                            <div class="form-group">
                                <label for="email">Email <span class="mandatory">*</span></label>
                                <input type="email" id="email" name="email" value="<?php echo isset($email) ? $email : ''; ?>" placeholder="Email" required>
                            </div>

                            <div class="form-group">
                                <label for="username">Username <span class="mandatory">*</span></label>
                                <input type="text" id="username" name="username" value="<?php echo isset($username) ? $username : ''; ?>" placeholder="Username" required>
                            </div>

                            <div class="form-group">
                                <label for="password">Password <span class="mandatory">*</span></label>
                                <input type="password" id="password" name="password" value="<?php echo isset($password) ? $password : ''; ?>" placeholder="Password" required>
                            </div>

                            <button type="submit" id="signupButton" name="submit" value="signup">
                                Create account
                            </button>
                        </form>
                    </section>
                </section>

            </section>

            <footer class="footer">
                &copy; <?php echo date('Y'); ?> <a href="#" title="Demo">Demo</a>. All rights reserved.
            </footer>

        </div>
    </body>
</html>

includes/connection.php

<?php

/*
 * This page contains the code for creating a mysqli connection instance.
 */

// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');

/*
 * Enable internal report functions. This enables the exception handling,
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
 * (mysqli_sql_exception).
 *
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
 *
 * @link http://php.net/manual/en/class.mysqli-driver.php
 * @link http://php.net/manual/en/mysqli-driver.report-mode.php
 * @link http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

/*
 * Create a new db connection.
 *
 * @see http://php.net/manual/en/mysqli.construct.php
 */
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);

includes/handlers.php

<?php

/*
 * Include this page in all PHP pages of the application.
 *
 * This page contains:
 *  - The APP_ENV constant, used to decide in which environment this application runs.
 *  - The functions for handling all the errors, or exceptions, raised by the application.
 *  - The code for setting them as error/exception handlers.
 *  - The code deciding if the errors should be displayed on the screen. The errors
 *    display MUST be activated ONLY in the development stage of the application. When
 *    the website goes live, ALL ERRORS must be written in a/the log file and NO ERRORS
 *    should be displayed on screen, but only a general, user-friendly message, or a
 *    custom error page.
 */

/*
 * Decide in which environment this application runs. Possible values:
 *  - 'prod' (app in production, e.g. live). The errors are not displayed, but only logged.
 *  - 'dev' (app in development). The errors are displayed on screen and logged.
 *  - 'test' (app in tests). Same as 'dev'.
 *  - etc.
 */
define('APP_ENV', 'dev');

// Activate the errors/exceptions logging.
ini_set('log_errors', 1);

// Set the error reporting level: report all errors.
error_reporting(E_ALL);

// Decide how to handle the errors/exceptions.
if (APP_ENV === 'prod') { // App in production, e.g. live.
    // DON'T display the errors/exceptions on the screen.
    ini_set('display_errors', 0);

    // Set the handler functions.
    set_error_handler('errorHandler');
    set_exception_handler('exceptionHandler');
} else { // App in development, tests, etc.
    // Display the errors/exceptions on the screen.
    ini_set('display_errors', 1);
}

/**
 * Error handler:
 *  - Print a user-friendly message, or show a custom error page.
 *  - Log the error.
 *
 * @link http://php.net/manual/en/function.set-error-handler.php set_error_handler.
 * @param int $errno
 * @param string $errstr
 * @param string $errfile
 * @param int $errline
 */
function errorHandler($errno, $errstr, $errfile, $errline) {
    echo 'An error occurred during your request. Please try again, or contact us.';
    error_log('Error ' . $errno . ' - ' . $errstr . ' in file ' . $errfile . ' on line ' . $errline);
    exit();
}

/**
 * Exception handler:
 *  - Print a user-friendly message, or show a custom error page.
 *  - Log the error.
 *
 * @link http://php.net/manual/en/function.set-exception-handler.php set_exception_handler.
 * @param Exception $exception
 */
function exceptionHandler($exception) {
    echo 'An error occurred during your request. Please try again, or contact us.';
    error_log('Exception ' . $exception->getCode() . ' - ' . $exception->getMessage() . ' in file ' . $exception->getFile() . ' on line ' . $exception->getLine());
    exit();
}

assets/css/app.css

/***************************************/
/* Base settings */
/***************************************/

html {
    -moz-box-sizing: border-box;
    -webkit-box-sizing: border-box;
    box-sizing: border-box;
}

*, *:before, *:after {
    -moz-box-sizing: inherit;
    -webkit-box-sizing: inherit;
    box-sizing: inherit;
}

/* Font size: 100% = 16px (in almost all web browsers) */
html, body {
    width: 100%;
    height: 100%;
    min-height: 100%;
    margin: 0;
    padding: 0;
    font-size: 100%;
    font-weight: 400;
    line-height: 1.8;
    color: #000;
    font-family: "Open Sans", Verdana, Arial, sans-serif !important;
    background-color: #fff;
}

/*
    A font size of 1rem means 16px. E.g. 100% of the font size of the "html" tag, which is 16px.
    A font size of 0.9375rem means: 0.9375 * 16px = 15px.
    From this point on, for font sizes, work with "rem", or "em" units, not anymore with px.
    The "rem" units are always relative to the font size of the "html" tag (here 16px, because is set as 100%).
    The "em" units are always relative to the font size of the parent tag.
*/
body {
    font-size: 0.9375rem;
    position: relative;
}

a {
    text-decoration: none;
    outline: none;
    color: #DF9237;
}

a:hover {
    text-decoration: none;
    outline: none;
    color: #000;
}

h1, h2, h3, h4, h5, h6 {
    margin: 0;
    padding: 0;
    line-height: 1;
    font-weight: 300;
}

/* A font size of 2.5rem means: 2.5 * 16px = 40px */
h2 {
    font-weight: 300;
    font-size: 2.5rem;
}

/***************************************/
/* Fonts settings */
/***************************************/

html, body {
    font-family: "Open Sans", Verdana, Arial, sans-serif !important;
}

h1, h2, h3, h4, h5, h6,
.navbar-nav li a,
.page-title,
.page-subtitle {
    font-family: "Roboto Condensed", Verdana, Arial, sans-serif !important;
}

/***************************************/
/* Layout settings */
/***************************************/

/* Page container */

/*
    The top-padding is the navbar's height (70px) + some additional pixels (30px).
    The bottom-padding is the footer's height (60px) + some additional pixels (30px).
*/
.page-container {
    /* Making relative position so, that we can absolute position the footer on the bottom */
    position: relative;
    padding: 100px 30px 90px 30px;
    width: 100%;
    min-height: 100%;
    overflow: hidden;
    margin: 0;
    background-color: #fff;
}

/* Navigation bar */

/*
    Navbar must have a fixed height. Example here: 70px (padding is included because of
    box-sizing: border-box in html). Then make the top-padding of the .page-container
    the same height (70px) + some additional pixels, in order to avoid overlapping!
*/
.navbar {
    height: 70px;
    padding: 22px 0 0 0;
    margin: 0;
    position: absolute;
    top: 0;
    left: 0;
    right: 0;
    border-bottom: 1px solid #f3f3f3;
    background-color: #fff;
}

.navbar-nav {
    margin: 0;
    padding: 0 60px;
    float: right;
    list-style: none;
    text-transform: uppercase;
}

.navbar-nav li {
    display: block;
    float: left;
    position: relative;
}

.navbar-nav li a {
    padding: 7px;
    margin-left: 5px;
    color: #000;
    font-size: 1rem;
    font-weight: 300;
    border-bottom: 0px solid transparent;
}

.navbar-nav li a:hover {
    color: #DF9237;
}

.navbar-nav li a.active {
    color: #DF9237;
}

.navbar-nav li a.active:hover {
    color: #000;
}

/* Page header */

.page-header {
    margin: 0 0 30px 0;
    padding: 0;
    text-align: center;
}

.page-title {
    margin: 0;
    padding: 10px;
    color: #DF9237;
    text-transform: uppercase;
}

.page-subtitle {
    /*margin-top: 10px;*/
    padding: 0;
    text-align: center;
    font-weight: 300;
    font-size: 1.0625rem;
    font-size: 1.1rem;
}

.page-content {

}

/* Messages */

.messages {
    padding: 10px;
    margin: 0;
    border-radius: 4px;
}

.success {
    color: #3c763d;
    border-color: #d6e9c6;
    background-color: #dff0d8;
}

.danger {
    color: #a94442;
    border-color: #ebccd1;
    background-color: #f2dede;
}

.warning {
    color: #8a6d3b;
    border-color: #faebcc;
    background-color: #fcf8e3;
}

/* Mandatory fields in forms */

.mandatory {
    font-size: 0.75rem;
    color: #DF9237;
}

/* Footer */

/*
    Footer must have a fixed height. Example here: 60px (padding is included because of
    box-sizing: border-box in html). Then make the bottom-padding of the .page-container
    the same height (60px) + some additional pixels, in order to avoid overlapping!
*/
.footer {
    height: 60px;
    padding-top: 20px;
    position: absolute;
    bottom: 0;
    left: 0;
    right: 0;
    margin: 0;
    font-weight: 300;
    text-align: center;
    background-color: #fff;
}

assets/css/signup.css

/* Form */

.form-container-outer {
    padding: 30px;
    position: relative;
    text-align: center;
    border-radius: 4px;
    background-color: #f4f4f4;
}

.form-container-inner {
    display: inline-block;
    margin: 0 auto;
    padding: 0;
}

.messages {
    text-align: left;
}

.messages a {
    text-transform: uppercase;
    font-weight: 600;
}

.messages.success {
    text-align: center;
}

#signup-form {
    padding: 20px;
    display: inline-block;
    text-align: left;
}

.form-group {
    margin-bottom: 20px;
}

.form-group label {
    display: inline-block;
    min-width: 100px;
}

input {
    padding: 5px;
    width: 250px;
    font-size: 0.9375rem;
}

button {
    padding: 7px 10px;
    display: block;
    float: right;
    color: #fff;
    font-size: 0.9375rem;
    cursor: pointer;
    border: none;
    border-radius: 4px;
    background-color: #5cb85c;
}

button:hover {
    background-color: #449d44;
}

Used table structure

Since you are using the proper hashing function (password_hash), let the password column's length be at least 255 characters long.

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Saturday, May 29, 2021
 
ammezie
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :