Error adding users to simple database in php with mySql

Question

I'm new to php with mySql so sorry in advance if this is something obvious. When I try to add a new user using this code I always get my error message and never the success one. Can anyone see where I have gone wrong ?

Edit: "Adding echo mysqli_error($link)" gives me this error; "Field 'name' doesn't have a default value"

There are 4 columns in my database and name is the last one, can I add a user this way without specifying a 'name'

<?php
  if (array_key_exists('email', $_POST) OR array_key_exists('password', $_POST)) {
    $link = mysqli_connect("localhost", "root", "", "users");

    if (mysqli_connect_error()){
      die ("Error connecting");
    }


    if ($_POST['email'] == ''){
      echo "<p>Email address is required</p>";
    } else if ($_POST['password'] == ''){
      echo "<p>Password is required</p>";
    } else {
      $query = "SELECT `id` FROM `users` WHERE email = '".mysqli_real_escape_string($link, $_POST['email'])."'";
      $result = mysqli_query($link, $query);
      if (mysqli_num_rows($result) > 0) {
        echo "<p>That Email address already has an account<p>";
    } else {
      $query = "INSERT INTO `users` (`email`, `password`) VALUES ('".mysqli_real_escape_string($link, $_POST['email'])."',
       '".mysqli_real_escape_string($link, $_POST['password'])."')";

       if (mysqli_query($link, $query)) {
         echo "<p>You have been signed up!</p>";
       } else {
         echo "<p>There was a problem, please try again later</p>";
       }
    }
  }
}

?>

HTML form :

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>
      Form
    </title>
  </head>
  <body>
    <form method="post">
      email<br>
      <input type="text" name="email" id="email" value=""><br>
      password<br>
      <input type="password" name="password" id="email" value=""><br>
      <input type="submit" value="Sign Up">
    </form>
  </body>
</html>

The use of mysqli_real_escape_string is not as secure. You should switch to using "prepared statements" if this is new code you are creating. — IncredibleHat
– IncredibleHat, Commented Oct 30, 2017 at 21:01
When you get your 'There was a problem...' response... add this after to get the sql error (if one): echo mysqli_error($link); — IncredibleHat
– IncredibleHat, Commented Oct 30, 2017 at 21:04
echo $query is a great way to debug you can often just see the error or if not try the query in the likes of phpmyadmin — user6763587
– user6763587, Commented Oct 30, 2017 at 21:06

Piyin · Accepted Answer · 2017-10-30 22:25:09Z

1

You are trying to insert a new user, but you're only providing email and password. All other columns in your table users will take the default value set on the database (by the one who made it), or give you an error because there's no default value. So you can either:

Set a default value for the name column in the database running a query like this:
```
ALTER TABLE `users` ALTER COLUMN `name` SET DEFAULT 'No Name';
```

Change your code like this:

$query = "INSERT INTO `users` (`email`, `password`, `name`) VALUES (
  '".mysqli_real_escape_string($link, $_POST['email'])."',
  '".mysqli_real_escape_string($link, $_POST['password'])."',
  'No Name' //Or even ask for their name and put it here
)";

answered Oct 30, 2017 at 22:25

Piyin

1,8341 gold badge18 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

Error adding users to simple database in php with mySql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related