Cannot Insert Into PHP to MYSQL database

Question

I can read from database, but when I insert data to database, It said "Successfully Registered!", then I check my database , it didn't insert any data.

Register.php

    <!DOCTYPE HTML>
    <html>
<head>
<title>Register page</title>
<br>
<font size="5" color="black"><b>&nbsp;Register Page<br><br></b></font>
</head>

<body background=" images/background.jpg">
<form action="index.php">
    <input type="submit" value="Home">
</form>
<br>
<form action="Register.php" method="POST">
 <fieldset>
  <legend>Enter your information</legend>
  <table>
      <tr>
          <td>Username:</td>
          <td><input type="text" name="username" required="required"/><br></td>
      </tr>
      <tr>
          <td>Password:</td>
          <td><input type="text" name="password" required="required"/><br></td>
      </tr>
      <tr>
          <td>Email:</td>
          <td><input type="text" name="email" required="required"/><br></td>
      </tr>
      <tr>
          <td>First Name:</td>
          <td><input type="text" name="firstname" required="required"/><br></td>
      </tr>
      <tr>
          <td>Last Name:</td>
          <td><input type="text" name="lastname" required="required"/><br></td>
      </tr>
      <tr>
          <td>Date of Birth:</td>
          <td><input type="date" name="dateofbirth" required="required" ><br></td>
      </tr>
      <tr>
          <td><input type="submit" value="Register"/></td>
    </tr>

  </table>
 </fieldset>
</form>


</body>
</html>
<?php
if($_SERVER["REQUEST_METHOD"] == "POST")
    {
        $username   = mysql_real_escape_string($_POST['username']);
        $password   = mysql_real_escape_string($_POST['password']);
        $firstname  = $_POST['firstname'];
        $lastname   = $_POST['lastname'];
        $email      = mysql_real_escape_string($_POST['email']);
        $dateofbirth= $_POST['dateofbirth'];
        echo "user is :".$username;
        $bool = true;
        mysql_connect("localhost", "root", "") or die(mysql_error());
        mysql_select_db("userdata") or die(mysql_error("Cannot Connect to Database"));
        $query = mysql_query("SELECT * FROM users");
        while($row = mysql_fetch_array($query))
                {
                    $table_users = $row['username'];
                    if($username == $table_users)
                        {
                            $bool = false;
                            Print '<script>alert("username is used!");</script>';
                            Print '<script>window.location.assign("Register.php");</script>';

                        }
                }
        if($bool)
         {
        mysql_query("INSERT INTO users ('username','password','email','firstname','lastname','dateofbirth') VALUES ('$username','$password','$email','$firstname','$lastname','$dateofbirth')");
        Print '<script>alert("Successfully Registered!");</script>';
        Print '<script>window.location.assign("Register.php");</script>';
         }


}
 ?>

SOLVED: I used PDO instead of mysql_ and mysqli_ .

You've got a missing single-quote before 'username' (and you don't actually need to encapsulate column names, and should use backticks anyway) in the insertion query. You're not checking if the insertion was successful. You're using deprecated mysql_ functions instead of mysqli_ — Geoff Atkins
– Geoff Atkins, Commented Oct 6, 2015 at 12:35
Why you are still using mysql_connect , It is already deprecated , please use PDO instead of this. — Prafulla Kumar Sahu
– Prafulla Kumar Sahu, Commented Oct 6, 2015 at 12:36
I don't know how to use PDO, I hope if you help me if it better — w3000cpu
– w3000cpu, Commented Oct 6, 2015 at 13:40
You should test to see what mysql_query is actually returning rather than just assuming it was successful. That isn't the cause of your failed insert, but would help with troubleshooting and give you an opportunity to trap the error and report on the problem rather than just silently discarding data. — Isaac Bennetch
– Isaac Bennetch, Commented Oct 7, 2015 at 15:17

Pupil · Accepted Answer · 2015-10-06 12:36:36Z

3

Correct the SQL:

mysql_query("INSERT INTO users (username','password','email','firstname','lastname','dateofbirth') VALUES ('$username','$password','$email','$firstname','$lastname','$dateofbirth')");

To

mysql_query("INSERT INTO users (username,password,email,firstname,lastname,dateofbirth) VALUES ('$username','$password','$email','$firstname','$lastname','$dateofbirth')");

Explanation:

In MySQL, field names are not enclosed with single quotes. Rather they can be enclosed with backticks (`) to avoid conflicts with reserved keywords.

Note: Don't use mysql_* functions. They are deprecated, you mysqli_* or PDO instead.

answered Oct 6, 2015 at 12:36

Pupil

24k6 gold badges49 silver badges71 bronze badges

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

Comments

seemc0 · Accepted Answer · 2015-10-06 12:36:33Z

0

You're missing an opening ' before username in your mysql query. Not sure if it's the problem, but it will cause issues.

answered Oct 6, 2015 at 12:36

seemc0

12 bronze badges

Comments

ljubomir · Accepted Answer · 2015-10-06 12:46:48Z

0

This is not related to the INSERT problem itself, but rather to the routine for checking if username exists in the database:

...
$bool = true;
$query = mysql_query("SELECT * FROM users");
while($row = mysql_fetch_array($query))
{
    $table_users = $row['username'];
    if($username == $table_users)
    {
        $bool = false;
        Print '<script>alert("username is used!");</script>';
   }
}
if($bool)
{
...

Rather than iterating in your code through all records from users table, you can use SQL to do the job for you:

...
$username_esc = mysql_real_escape_string($username);
$query = mysql_query("SELECT * FROM users where username = '$username_esc'");
...

and then only check if the query returns any row or not.

Hope this helps a bit.

answered Oct 6, 2015 at 12:46

ljubomir

1,5251 gold badge21 silver badges40 bronze badges

1 Comment

w3000cpu Over a year ago

I checked I can read rows from database, but I can't insert, It said"successfully register", but when I check database , no changes

hvs9 · Accepted Answer · 2015-10-06 13:24:15Z

0

I tested your code and found that connection was not proper and second you are using quote in fields name that is incorrect syntax so query was not executing. And my opinion is use mysqli_connect instead of mysql_connect it is old and deprecated. I have updated your code, hope it will help you :

    <?php 
//your values from post I just assign test for all
$username   = 'test';//mysql_real_escape_string($_POST['username']);
$password   = 'test';//mysql_real_escape_string($_POST['password']);
$firstname  = 'test';//mysql_real_escape_string($_POST['firstname']);
$lastname   = 'test';//mysql_real_escape_string($_POST['lastname']);
$email      = 'test';//mysql_real_escape_string($_POST['email']);
$dateofbirth= 'test';//mysql_real_escape_string($_POST['dateofbirth']);
$bool = true;

//database connection
$servername = "localhost";
$username1 = "root";
$password1 = "";
$db  = 'test_001';

// Create connection
$conn = new mysqli($servername, $username1, $password1, $db);

$result = $conn->query("SELECT * FROM users");

while($row = $result->fetch_assoc())
{
    $table_users = $row['username'];
    if($username == $table_users)
    {
        $bool = false;
        Print '<script>alert("username is used!");</script>';
        //Print '<script>window.location.assign("Register.php");</script>';

    }
}

if($bool)
{
    //mysql_query("INSERT INTO users ('username','password','email','firstname','lastname','dateofbirth') VALUES ('$username','$password','$email','$firstname','$lastname','$dateofbirth')");

    $query = "INSERT INTO users (username,password,email,firstname,lastname,dateofbirth) VALUES ('$username','$password','$email','$firstname','$lastname','$dateofbirth')";
    $x = $conn->query($query);
    if ($x)
        Print '<script>alert("Successfully Registered!");</script>';
    Print '<script>window.location.assign("Register.php");</script>';
}

answered Oct 6, 2015 at 13:24

hvs9

706 bronze badges

2 Comments

w3000cpu Over a year ago

thanks , but when I use this code it doesn't print "Successfully Registered" and same problem

hvs9 Over a year ago

first check mysql is connection status, is it connected or not by using connection_status() , if connection_status() is returning 1, means its connected properly, then echo your query and try to run it in sql or comment here (after echo copy and paste as comment )

Christopher Shaw · Accepted Answer · 2015-10-06 14:00:26Z

0

You need to remove the quotes round the field names, so your quest should be

INSERT INTO users (username,password,email,firstname,lastname,dateofbirth) VALUES ('$username','$password','$email','$firstname','$lastname','$dateofbirth')

I would also recommend using a DB wrapper to auto escape your variables.

http://www.chris-shaw.com/b/How-to-connect-to-a-database-using-a-class

answered Oct 6, 2015 at 14:00

Christopher Shaw

7451 gold badge7 silver badges19 bronze badges

Comments

w3000cpu · Accepted Answer · 2015-10-16 20:22:46Z

0

PDO solve this problem MYSQL made many problems but when I used PDO it solved the problem

answered Oct 16, 2015 at 20:22

w3000cpu

556 bronze badges

Comments

Maha Dev · Accepted Answer · 2015-10-06 12:36:05Z

-1

Update your bottom code

if($bool)
    {
        mysql_query("INSERT INTO users ('username','password','email','firstname','lastname','dateofbirth') VALUES ('$username','$password','$email','$firstname','$lastname','$dateofbirth')");
        Print '<script>alert("Successfully Registered!");</script>';
        Print '<script>window.location.assign("Register.php");</script>';
    }

answered Oct 6, 2015 at 12:36

Maha Dev

3,9653 gold badges34 silver badges53 bronze badges

3 Comments

Maha Dev Over a year ago

@WaleedWalaaAldeanAbuZaid edit your question and write what code you are running now

Maha Dev Over a year ago

@WaleedWalaaAldeanAbuZaid it should work now. well try once again by quoting table name like users

w3000cpu Over a year ago

I did that , still have problem..when I have row with username "admin", when I insert username admin, It say "username is used", that means the problem not in the query.

Chibuzo Emelike · Accepted Answer · 2018-06-20 17:40:31Z

-1

$sql = "INSERT INTO users (name,username,password,status,picture) VALUES ('$name','$uname','$pass',0,'$picture')";

edited Jun 20, 2018 at 17:40

user555121

answered Jun 20, 2018 at 14:32

Chibuzo Emelike

1

1 Comment

Derek Brown Over a year ago

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

Collectives™ on Stack Overflow

Cannot Insert Into PHP to MYSQL database

8 Answers 8

Comments

Comments

1 Comment

2 Comments

Comments

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

Comments

1 Comment

2 Comments

Comments

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related