Database query failing

Question

I am trying to build a user registration system but the code that i have isn't inserting any new records into the table. the following is the code:

<?php 
    require("../includes/database.php");
?>
<?php
    //mysql_
    $connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
    if(mysqli_connect_errno())
    {
        die("Database connection failed: " .
                    mysqli_connect_error() .
        " (" . mysqli_connect_errno() . ")");
    }
?>
<?php
    if(isset($_POST['register'])) {
        session_start();
        $f_name = $_POST['fname'];
        $l_name = $_POST['lname'];
        $email = $_POST['email'];
        $pword = $_POST['key'];

        $query ="INSERT INTO students (f_name, l_name, email, password) VALUES('{$f_name}', '{$l_name}', '{$email}', '{$pword}')";
    $result = mysqli_query($connection, $query);

    if(!$result){
        die("Database query failed.");
    }
    echo $f_name;
    //header('Location: login.php');
    }
?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <title>EdHoc - Sign Up</title>
    <!-- Latest compiled and minified CSS -->
</head>
<body>

    <!--login form-->
    <section id="signup">
    <div class="container">
        <div class="row">
            <div class="col-xs-12">
                <div class="form-wrap">
                <h1>Sign Up</h1>
                    <form role="form" action="Sign_up.php" method="post" id="register-form" name="register-form" autocomplete="off">
                        <div class="form-group">
                            <label for="first_name" class="sr-only">First Name</label>
                            <input type="fname" name="fname" id="fname" class="form-control"  required = "required" placeholder="Enter First Name " >
                        </div>
                        <div class="form-group">
                            <label for="last_name" class="sr-only">Last Name</label>
                            <input type="lname" name="lname" id="lname" class="form-control"  required = "required" placeholder="Enter Last Name ">
                        </div>
                        <div class="form-group">
                            <label for="email" class="sr-only">Email</label>
                            <input type="email" name="email" id="email" class="form-control"  required = "required" placeholder="Enter Email-Id ">
                        </div>
                        <div class="form-group">
                            <label for="key" class="sr-only">Password</label>
                            <input type="password" name="key" id="key" class="form-control"  required = "required" placeholder="Password">
                        </div>
                        <input type="submit" id="btn-login" class="btn btn-custom btn-lg btn-block" value="Sign Up" name="register">
                    </form>
                    <hr>
                </div>
            </div> <!-- /.col-xs-12 -->
        </div> <!-- /.row -->
    </div> <!-- /.container -->
</section>


</script>
</body>

The above code returns Database query failed.

My database table:

    CREATE TABLE students
(
    student_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(30),
    email VARCHAR(80),
    password VARCHAR(30),
    f_name VARCHAR(30),
    l_name VARCHAR(30),
    bio VARCHAR(350),
    dp VARCHAR(15),
    is_suspended CHAR(1) DEFAULT '0' NOT NULL,
    suspension_reason VARCHAR(150),
    role_id INT NOT NULL,
    created_on DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_on TIMESTAMP,
    is_active CHAR(1) DEFAULT '1' NOT NULL,
    city VARCHAR(15) NOT NULL,
    state VARCHAR(15) NOT NULL,
    zip VARCHAR(6) NOT NULL,
    b_day DATE,
    CONSTRAINT students_id_pk PRIMARY KEY(student_id),
    CONSTRAINT students_role_id_fk FOREIGN KEY(role_id) REFERENCES user_roles(role_id) ON DELETE CASCADE,
    CONSTRAINT students_username_uq UNIQUE(username),
    CONSTRAINT students_email_uq UNIQUE(email)
);

The form elements like the First Name is being echoed out correctly.

[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Apr 18 '16 at 19:52
Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure that you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Apr 18 '16 at 19:53
@JayBlanchard Valuable advise. I was actually going implement password hashing right after i have made this simple insert query work. — Mickey, Apr 18 '16 at 19:58
In your database table there are some columns which are defined as NOT NULL with out default value. Try to pass values to them. If possible try to run the query in PHPMyadmin and see the result/error. — Ravinder Reddy, Apr 18 '16 at 20:03
Rule #1 of debugging: return error messages that actually *mean* something, and can help you to debug the problem. "*Database query failed*" is *not* one of those valuable debugging messages. Instead use (in this case): `die( mysqli_error($connection) );` Would have saved you some time. — mferly, Apr 18 '16 at 20:08

score 1 · Accepted Answer · answered Apr 18 '16 at 20:02

1

Your problem is constraint issues.

INSERT INTO students (f_name, l_name, email, password) VALUES ....

Yet all of these columns are assumed null with not null constraints:

role_id INT NOT NULL,
city VARCHAR(15) NOT NULL,
state VARCHAR(15) NOT NULL,
zip VARCHAR(6) NOT NULL,

There could be other issues as well, but these will make that query fail.

answered Apr 18 '16 at 20:02

Pete B.

3,188
6
25
38

had just figured it out. Thanks – Mickey Apr 18 '16 at 20:06

Database query failing

1 Answers1