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

    }
Improve this question
11
  • 3
    mysqli_error give you anything? Also, your code is very exposed to SQL-injection: you should start using prepared statements with placeholders to avoid this exposure. Commented Mar 16, 2016 at 10:54
  • 2
    Because you are inserting space into primary key VALUES (' ', Commented Mar 16, 2016 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' ,'[email protected]' ,'bill' ,'bill01'); Commented Mar 16, 2016 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. Commented Mar 16, 2016 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) Commented Mar 16, 2016 at 10:59

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Am on mobile right now so I was not able to show ticks. I will improve my answer in a few minutes
Ah, I was staring myself blind on that bloody query, nicely spotted.
@Epodax hoping that is all that is wrong. Let's see what the op has to say
"Thank you so much! It works now, the error was at 'e-mail' field it didn't like the dash -. Thank you! " - Spot on.
OMG it is hyphen. Nice catch @Fred-ii- !!
|
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']. "');";
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.