0

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';
        }

    }
Ana
  • 167
  • 3
  • 17
  • 3
    [`mysqli_error`](http://php.net/manual/en/mysqli.error.php) give you anything? Also, your code is very exposed to SQL-injection: you should start using prepared statements with placeholders to avoid this exposure. – Qirel Mar 16 '16 at 10:54
  • 2
    Because you are inserting space into primary key `VALUES (' ',` – Saty Mar 16 '16 at 10:54
  • Hi thank you for the quick reply and thank you for the advice. I am just starting to learn sql and php so this is more of a start excercise for me but will consider to look up what you said. No errors are showing. I have looked for pother similar posts to this and most of them relate to the use of the quotes...so here is what my $sql variable prints as an example: INSERT INTO users (ID, name, lastname, e-mail, username, password) VALUES (' ','Bill' ,'Gates' ,'bill.gates@microsoft.com' ,'bill' ,'bill01'); – Ana Mar 16 '16 at 10:56
  • 1
    As Saty have pointed out, you are inserting a space as your primary ID. Your ID field is most likely set to "auto increment" which means that you can simply omit it from the insert completely, `INSERT .... (name, ....) VALUES('JOHN', ....` Like so. – Epodax Mar 16 '16 at 10:58
  • Thank you, i removed the space and is still not working. I have also tried to type NULL for the value instead of using an empty sting for the ID column but still no success. Also tried to remove the ID from the insert and still not working (both column and value) – Ana Mar 16 '16 at 10:59
  • Did you remove it from both parts? I mean both from the column list and the values list? – Epodax Mar 16 '16 at 11:01
  • Yes. Not working yet – Ana Mar 16 '16 at 11:05
  • is your ID field set to auto increment? If not, then you might want to consider doing so. – Epodax Mar 16 '16 at 11:06
  • it is. Not working yet --> IDPrimary int(255) No None AUTO_INCREMENT – Ana Mar 16 '16 at 11:09
  • Echo the query and copy it, then try and execute it directly in phpmyadmin, see if it throws any errors there. – Epodax Mar 16 '16 at 11:14
  • Thank you so much! It works now, the error was at 'e-mail' field it didn't like the dash -. Thank you! – Ana Mar 16 '16 at 11:20

2 Answers2

3

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:

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:

Other links:

Important sidenote about column length:

If and when you do decide to use password_hash() or crypt, it is important to note that if your present password column's length is anything lower than 60, it will need to be changed to that (or higher). The manual suggests a length of 255.

You will need to ALTER your column's length and start over with a new hash in order for it to take effect. Otherwise, MySQL will fail silently.

Community
  • 1
  • 1
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
0

you use ' ' to insert id. the id should be autoincrement and unique. you cannot insert a white space to all. try to remove id from insert into query. turn your query into this

$sql="INSERT INTO users (name, lastname, e-mail, username, password) VALUES ('" .$_POST['name'] . "' ,'" . $_POST['lastname']. "' ,'" . $_POST['email']. "' ,'" . $_POST['username']. "' ,'" . $_POST['password']. "');";
Andrea Panetta
  • 1
  • 4