MySQL Insert query returning false

Question

This is probably the most asked question here. I have made a simple user registration form. The values are not inserted into the database. I used echo and the query is returning false. The form was working well before but then i separated the name into first and last name and dropped the previous table and made a new one. Here is the registration page code:

    <!DOCTYPE html>
    <?php 
        session_start();
        include('includes/connect.php');
    ?>

<html>
<head>
    <?php
        if(isset($_POST['register_ok'])){
                global $con;

                $user_first_name = $_POST['user_first_name'];
                $user_last_name = $_POST['user_last_name'];
                $email = $_POST['email'];
                $password = $_POST['password'];
                echo "<script type='text/javascript'>alert(\"$user_first_name\");</script>"; //This echo is returning username successfully!

                $query = "insert into user(user_first_name, user_last_name, user_email, password) values('$user_first_name', '$user_last_name', '$email', '$password')";

                if(mysqli_query($con, $query)){
                        $_SESSION['user'] = 'user';
                        $_SESSION['user_first_name'] = $user_first_name;
                        echo "header('Location:index.php')";

                } else {
                    echo "<script type='text/javascript'>alert(\"Values not inserted\");</script>"; //This is running which means query is not successfull.
                }
        } else {
            echo "<script type='text/javascript'>alert(\"Page didn't receive post values\");</script>";
        }
    ?>

    <link rel="stylesheet" type="text/css" href="styles/register_style.css">

    <title>New User Registration</title>
</head>
<body>
    <div class="wrapper">
        <header></header>
        <div class="form_div">
            <div class="form">
                <form id="register_form" method="post" action="" autocomplete="autocomplete">
                    <table>
                        <tr>
                            <td id="label">First Name: </td>
                            <td id="input"><input type="text" name="user_first_name" required="required" id="input_box"></td>
                        </tr>
                        <tr>
                            <td id="label">Last Name: </td>
                            <td id="input"><input type="text" name="user_last_name" required="required" id="input_box"></td>
                        </tr>
                        <tr>
                            <td id="label">Email: </td>
                            <td id="input"><input type="text" name="email" required="required" id="input_box"></td>
                        </tr>
                        <tr>
                            <td id="label">Password: </td>
                            <td id="input"><input type="password" name="password" id="input_box"></td>
                        </tr>
                        <tr>
                            <td id="label">Confirm Password: </td>
                            <td id="input"><input type="password" name="confirm_password" id="input_box"></td>
                        </tr>
                        <tr id="button_row">
                            <td colspan="2"><input type="reset" value="Reset" id="button">
                            <input type="submit" value="Register" id="button" name="register_ok"></td>
                        </tr>
                    </table>
                </form>
            </div>
        </div>
    </div>
</body>
</html>

And this is the table : The table is empty and the first alert is returning user first name and the second alert runs when the query returns false. It is returning false. I think it may be a typo but cannot narrow it down. Any help would be welcome.

Add `echo("Errormessage: %s\n", mysqli_error());` in your else statement to see what's going wrong. — eol, Oct 21 '16 at 10:54
The sessions are for storing information about the users which are logged in. — vilom, Oct 21 '16 at 10:54
echo ("Errormessage: %s\n", $mysqli->error); is returning syntax error in this line itself. — vilom, Oct 21 '16 at 10:57
i have checked without mysqli_query, it gives me `insert into user(user_first_name, user_last_name, user_email, password) values('test', 'test', 'test123654@gg.com', 'admin')` now run this query in phpmyadmin if its work it means, you need to check connection. — devpro, Oct 21 '16 at 10:58
and your mysql data types are incorrect, make them varchar except for user_id — Masivuye Cokile, Oct 21 '16 at 10:58
This is bad: Sql injection, plain-text password storage, output before starting the session and trying to redirect... You should just start over. — jeroen, Oct 21 '16 at 11:00
@MasivuyeCokile Even if i removed all session code, the main problem is that the data is not inserted into the table. Thanks for the response though. — vilom, Oct 21 '16 at 11:00
@MasivuyeCokile I suspected it would be a silly mistake. It was the datatypes as you suggested. Thanks for bearing with me. — vilom, Oct 21 '16 at 11:06

score 5 · Accepted Answer · answered Oct 21 '16 at 10:56

5

Change your column types from int to varchar. I'm talking about string columns (names and email). Mysql has an option to check for the data type you are trying to insert and fail if they don`t match.

answered Oct 21 '16 at 10:56

krasipenkov

2,031
1
11
13

Thanks. That was it. – vilom Oct 21 '16 at 11:13

score 2 · Answer 2 · answered Oct 21 '16 at 11:09

Firstly,

Change column name from INT to VARCHAR using this query.

"ALTER TABLE `user`
      MODIFY COLUMN `user_first_name` VARCHAR(225),
      MODIFY COLUMN `user_last_name` VARCHAR(225),
      MODIFY COLUMN `user_email` VARCHAR(225),
      MODIFY COLUMN `password` VARCHAR(225);";

Secondly,

You kept id for both input & <td> as same in each and every row. ID can't be same.

Change it to.

<table>
      <tr>
          <td id="label1">First Name: </td>
          <td id="input1"><input type="text" name="user_first_name" required="required" id="input_box1"></td>
      </tr>
      <tr>
          <td id="label2">Last Name: </td>
          <td id="input2"><input type="text" name="user_last_name" required="required" id="input_box2"></td>
      </tr>
      <tr>
          <td id="label3">Email: </td>
          <td id="input3"><input type="text" name="email" required="required" id="input_box3"></td>
      </tr>
      <tr>
          <td id="label4">Password: </td>
          <td id="input4"><input type="password" name="password" id="input_box4"></td>
      </tr>
      <tr>
          <td id="label5">Confirm Password: </td>
          <td id="input5"><input type="password" name="confirm_password" id="input_box5"></td>
      </tr>
      <tr id="button_row">
          <td colspan="2"><input type="reset" value="Reset" id="button">
          <input type="submit" value="Register" id="button" name="register_ok"></td>
      </tr>
  </table>

score 1 · Answer 3 · answered Oct 21 '16 at 11:08

You need to learn about sql injections and also about securing your passwords, use mysqli prepared statements / PDO what ever you find easy to learn

 <!DOCTYPE html>
    <?php 
        session_start();
        include('includes/connect.php');
    ?>

<html>
<head>
    <?php
        if(isset($_POST['register_ok'])){

                $user_first_name = $_POST['user_first_name'];
                $user_last_name = $_POST['user_last_name'];
                $email = $_POST['email'];
                $password = $_POST['password'];

                // Lets encrypt the password;

                $hash = pasword_hash($password,PASSWORD_DEFAULT);


                // lets insert then

                $query = $con->prepare("INSERT INTO user (user_id,user_first_name, user_last_name, user_email, password) VALUES(?,?,?,?)");
                $query->bind_param("ssss", $user_first_name, $user_last_name, $email,$hash);
                $query->execute();

               if ($query->execute()) { 

                    echo "<script type='text/javascript'>alert(\"$user_first_name\");</script>"; //This echo is returning username successfully!
                } else {
                    echo "<script type='text/javascript'>alert(\"Values not inserted\");</script>"; //This is running which means query is not successfull.
                } 




        }

    ?>

    <link rel="stylesheet" type="text/css" href="styles/register_style.css">

    <title>New User Registration</title>
</head>
<body>
    <div class="wrapper">
        <header></header>
        <div class="form_div">
            <div class="form">
                <form id="register_form" method="post" action="" autocomplete="autocomplete">
                    <table>
                        <tr>
                            <td id="label">First Name: </td>
                            <td id="input"><input type="text" name="user_first_name" required="required" id="input_box"></td>
                        </tr>
                        <tr>
                            <td id="label">Last Name: </td>
                            <td id="input"><input type="text" name="user_last_name" required="required" id="input_box"></td>
                        </tr>
                        <tr>
                            <td id="label">Email: </td>
                            <td id="input"><input type="text" name="email" required="required" id="input_box"></td>
                        </tr>
                        <tr>
                            <td id="label">Password: </td>
                            <td id="input"><input type="password" name="password" id="input_box"></td>
                        </tr>
                        <tr>
                            <td id="label">Confirm Password: </td>
                            <td id="input"><input type="password" name="confirm_password" id="input_box"></td>
                        </tr>
                        <tr id="button_row">
                            <td colspan="2"><input type="reset" value="Reset" id="button">
                            <input type="submit" value="Register" id="button" name="register_ok"></td>
                        </tr>
                    </table>
                </form>
            </div>
        </div>
    </div>
</body>
</html>

NB : You need to check if the username does not exist before inserting it.

score 1 · Answer 4 · answered Aug 07 '21 at 04:15

In my case, the strict mode setting was causing the issue. It requires values for all the columns to be specified in the INSERT command. Everything worked after the strict mode setting was disabled.

Check for strict mode (this should be blank):

SHOW VARIABLES LIKE 'sql_mode';

Turn off strict mode:

SET GLOBAL sql_mode = '';

Turn on strict mode:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

Credit: this post

MySQL Insert query returning false

4 Answers4

Check for strict mode (this should be blank):

Turn off strict mode:

Turn on strict mode: