php mysql database sql-insert insert-into

Insert data to database not working (PHP-SQL)

My insert data sql command in my PHP code is not working. Can anyone help please?

I have a registration form that takes the values from the input fields (name, lastname, email, username and password)

The values that the user inputs in this fields should be saved into my table "users" whith columns (ID [which is the primary key /INT], name [TEXT], lastname[TEXT], e-mail [VARCHAR], username[VARCHAR] and password [VARCHAR]) .

Here is my current code:

if (isset ($_POST['name'],$_POST['lastname'],$_POST['email'],$_POST['username'], $_POST['password']))
    {
        //connect to database
        $conn = mysqli_connect ('localhost', 'root', '', 'test_database');

        if ($conn)          
        {
            $sql="SELECT username FROM users WHERE username = '" . $_POST['username'] . "';";
            $query = mysqli_query ($conn, $sql);
            $result = mysqli_fetch_array ($query);

            if ($result ['username'])
            {
                header ('Location: ' . $_SERVER['PHP_SELF'] . '?errno=1');
            }
            else
            {
                $sql="INSERT INTO users (ID, name, lastname, e-mail, username, password) VALUES (' ','" .$_POST['name'] . "' ,'" . $_POST['lastname']. "' ,'" . $_POST['email']. "' ,'" . $_POST['username']. "' ,'" . $_POST['password']. "');";
                mysqli_query ($conn, $sql);
                mysqli_close ($conn);   
                //registration completed, redirect to index page
                //header ('Location:index.php?reg=1');

            }
        }

        else
        {
            echo 'connection error';
        }

    }

Solution

Besides what has already been outlined in comments for the space VALUES (' ', for the ID column etc., your email column name contains a hyphen and is interpreted as e MINUS mail and as a mathematical operation.

Either rename it to e_mail or place ticks around it.

`e-mail`

Read the following on Identifier Qualifiers:

http://dev.mysql.com/doc/refman/5.7/en/identifier-qualifiers.html

Having used mysqli_error($conn) on the query would have thrown you a syntax error.

Sidenote: You should be escaping your data for quite a few reasons, one is for protection against an SQL injection and if your data could contain characters that MySQL could complain about such as John's Bar & Grill as an example.

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements.

Passwords

I also noticed that you may be storing passwords in plain text. This is not recommended.

Use one of the following:

CRYPT_BLOWFISH
crypt()
bcrypt()
scrypt()
On OPENWALL
PBKDF2
PBKDF2 on PHP.net
PHP 5.5's password_hash() function.
Compatibility pack (if PHP < 5.5) https://github.com/ircmaxell/password_compat/