Mickey Mickey - 7 months ago 22
SQL Question

Database query failing

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.

Answer

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.