how to insert elements to mysql database

Question

am learning mySQL.Here i have a databale called "practice" and it has a table called "users".This table has four column.

username,password,firstname,lastname;

i have a form field to get user input which will be inserted into my mysql table.But whenever i want to insert a new user to the table it gives an error.What might be the reason behind this problem??

ERROR: INSERT INTO users (username,password,firstname,lastname,) VALUES (Nelson,101a6ec9f938885df0a44f20458d2eb4,Nelson,Mandela) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VALUES (Nelson,101a6ec9f938885df0a44f20458d2eb4,Nelson,Mandela)' at line 1

<?php
if(isset($_POST['name']) &&isset($_POST['password'])){
   if(!empty($_POST['name']) && !empty($_POST['password'])){
$servername = $_SERVER['SERVER_NAME'];
$username = "root";
$password = "";
$dbname = "practice";

$user=htmlentities($_POST['name']);
$firstname=htmlentities($_POST['firstname']);
$lastname=htmlentities($_POST['lastname']);
$pass=md5(htmlentities(filter_var($_POST['password'],FILTER_SANITIZE_STRING)));

echo $password;
// Create connection
$conn = new mysqli($servername, $username, $password,$dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


$sql = "INSERT INTO users (username,password,firstname,lastname)
VALUES ($user,$pass,$firstname,$lastname)";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "ERROR: " . $sql . "<br>" . $conn->error;
}

$conn->close();

}
}
?> 

<form action='foreach.php' method='post'>

      name      :<input type='text' name='name' placeholder='name'></br>
      firstname : <input type='text' name='firstname' placeholder='firstname'></br>
      lastname  : <input type='text' name='lastname' placeholder='lastname'></br>
      password  :<input type='text' name='password' placeholder='password'></br>
      <input type='submit'>

</form>

score 6 · Accepted Answer · edited May 23 '17 at 11:53

Edit: Since you removed the trailing comma in an edit and without marking it as an edit:

~~Remove the trailing comma in lastname, <=~~

Quote your values, since they're strings

VALUES ('$user','$pass','$firstname','$lastname')

On an added note:

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

You also seem to be storing passwords using MD5, which is highly discouraged, it is old and considered broken.

For password storage, use CRYPT_BLOWFISH or PHP 5.5's password_hash() function. For PHP < 5.5 use the password_hash() compatibility pack.

Read these articles regarding MD5:

another **+1**for The best answer – Amit Verma Feb 12 '15 at 17:52 — Amit Verma, Feb 12 '15 at 17:52
@CodingHorror Thank you, I do my best, *cheers* – Funk Forty Niner Feb 12 '15 at 17:53 — Funk Forty Niner, Feb 12 '15 at 17:53

score 2 · Answer 2 · answered Feb 12 '15 at 17:24

You have an extra comma at the end of your insert firstname,lastname,). Also, you need to quotes your value as they are String.

$sql = "INSERT INTO users (username,password,firstname,lastname,)
VALUES ($user,$pass,$firstname,$lastname)";

Should be changed to

$sql = "INSERT INTO users (username,password,firstname,lastname)
VALUES ('$user','$pass','$firstname','$lastname')";

how to insert elements to mysql database

2 Answers2

Linked