Data is not inserting into MySQL DB

Question

I am using PHP and PDO, the problem is when I click on the submit button the data is not inserted into the Database. There are no errors at all, I am not sure what's causing it. I've tried a lot of things and still can't manage to find a solution.

This is my code:

<?php                           

                       function cl($info){
                        return preg_replace("|[^\w]|", "", $info);
                        }
                        function cl2($info){
                            return preg_replace("|[^\w]|", "", $info);
                        }
                        function check_email($email){
                            $exit = FALSE;
                            if(filter_var($email, FILTER_VALIDATE_EMAIL)){
                                return $exit = TRUE;
                            }
                            else {
                                return $exit;
                            }
                        }

                        if (isset($_POST['register'])) {

                            $errors = [];
                            $username = cl($_POST['username'] ?? '');
                            $password = cl2($_POST['password'] ?? '');
                            $email = $_POST['email'] ?? '';

                            try {
                                $conn = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
                            } catch (PDOException $e) {
                                echo "Failed to get DB handle: " . $e->getMessage() . "\n";
                                exit;
                            }



                            $check_username = $conn->prepare("SELECT * FROM accounts WHERE name = :username");
                            $check_username->bindParam(':username', $username, PDO::PARAM_STR);
                            $check_username->execute();                              

                            if($check_username->rowCount()) {
                                array_push($errors, 'Username already in use, please select a new one.');
                            } else if(empty($username) || strlen($username) < 4 || strlen($username) > 13) {
                                array_push($errors, 'Invalid username, please select another one.');
                            } else if(empty($password) || strlen($password) < 4 || strlen($password) > 20) {
                                array_push($errors, 'Invalid password, please select another one.');
                            } else if(empty($email) || !check_email($_POST['email'])) {
                                array_push($errors, 'Invalid password, please select another one.');
                            }

                            if(empty($errors)) {

                                $query = $conn->prepare("INSERT INTO accounts (name,password,email) VALUES ($username,$password,$email)");
                                $query->bindParam(':username', $username, PDO::PARAM_STR);
                                $query->bindParam(':password', $password, PDO::PARAM_STR);
                                $query->bindParam(':email', $email, PDO::PARAM_STR);
                                $query->execute();

                                echo '<br />';
                                echo ' <div class="alert alert-success text-center" role="alert">
                                        Account created succesfully.
                                        </div>';
                            } else {
                                foreach($errors as $error) {
                                    echo '<br />';
                                    echo '<div class="alert alert-danger text-center" role="alert">';
                                    echo $error;
                                    echo '</div>';
                                }
                            }

                        }


                    ?>

And the form:

<form method="POST">
                        <div class="form-group">
                            <label for="InputUsername">Username</label>
                            <input type="text" class="form-control" id="InputUsername" placeholder="Enter username" name="username">                               
                        </div>
                        <div class="form-group">
                            <label for="inputPassword">Password</label>
                            <input type="password" class="form-control" id="inputPassword" placeholder="Password" name="password">
                        </div>
                        <div class="form-group">
                            <label for="inputEmail">Email address</label>
                            <input type="email" class="form-control" id="inputEmail" aria-describedby="emailHelp" placeholder="Enter email" name="email">
                            <small id="emailHelp" class="form-text text-muted">We'll never share your email with anyone else.</small>
                        </div>                           
                        <div class="form-check">
                            <input type="checkbox" class="form-check-input" id="rules">
                            <label class="form-check-label" for="rules">I have read the rules before creating a new account.</label>
                        </div>
                        <br />
                        <button type="submit" class="btn btn-primary" name="register">Submit</button>
                    </form>

I am trying this using Wamp, in a local development. If anyone could help me I would really appreciate it.

Okay now after adding this to my code:

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I am getting the following error:

 Fatal error: Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'test.accounts' doesn't exist

But the table exists in my db. This is acting weird.

It should not matter because I am not adding the value into the PHP script, so it is currently not used. Or I am wrong? — Alessandro, May 10 '20 at 21:24
ok but you always use id, so you will not retrieve these values. when you access your database SELECT * FROM accounts WHERE name = :username you will retrieve nothing because username is empty — Antonio Abrantes, May 10 '20 at 21:56
It is supposed to work without the action, as far as I know. Since I am using the isset thing — Alessandro, May 10 '20 at 22:06
you are right again ! when you dont`t define action the form will submit to itself, I didn't know about that Great ! Thank you! — Antonio Abrantes, May 10 '20 at 22:13

score 1 · Answer 1 · answered May 10 '20 at 21:31

1

From what I can see this line is wrong

 $query = $conn->prepare("INSERT INTO accounts (name,password,email) VALUES ($username,$password,$email)");

It should be

 $query = $conn->prepare("INSERT INTO accounts (name,password,email) VALUES (:username,:password,:email)");

I THINK it would've worked had you put quotes around the variables but you look like you want to prevent sql injection properly.

answered May 10 '20 at 21:31

Forbs

1,256
1
7
9

I tried both, with the bindParam and also as it is right now. None of them were working. No clue what’s causing it. – Alessandro May 10 '20 at 21:40
when I tried using $query = $conn->prepare("INSERT INTO accounts (name,password,email) VALUES (:username,:password,:email)"); everythink worked for me !! – Antonio Abrantes May 10 '20 at 22:35
if there is not table accounts your code don't give any message, It gives empty($errors) also and show the message Account created succesfullys. but the insert is not made because table accounts doesnt exist. Are you sure that this table exist ? – Antonio Abrantes May 10 '20 at 22:37
@Alessandro If you invoke [this method](https://www.php.net/manual/en/pdo.errorinfo.php), it may give you more information about whatever is happening with your query, e.g. `var_dump($conn->errorInfo());` – Matthew Turland May 10 '20 at 23:02

Data is not inserting into MySQL DB

1 Answers1