PHP code not inserting into MySQL database

Question

I double checked my code compared to the book and I'm sure I typed the code in correctly. The page loads, the errors display if I leave a field blank or if the password and confirm password fields don't match. However, if I complete the form correctly and submit, the MySQL database doesn't get the information passed to it. The "Registered!" and login page doesn't show up either. I checked the MySQL database through Ubuntu directly and the users table is still empty (Empty set (0.00 sec)). phpMyAdmin also returns empty, "MySQL returned an empty result set (i.e. zero rows). (Query took 0.0011 seconds)", for the users table as well.

<!DOCTYPE HTML>                                   
<html lang="en">                                  
    <head>                                         
        <metacharset="UTF-8">                       
        <title>Register Page</title>             
    </head>                                        
    <body>                                         
        <?php
            $page_title = 'Register';
            include('/var/www/html/learn/php/header.html');

            if($_SERVER['REQUEST_METHOD'] == 'POST')
            {

                require('/var/www/connect_db.php');
                $errors = array();

                if(empty($_POST['first_name']))
                {
                    $errors[] = 'Enter your first name.';
                }
                else
                {
                    $fn = mysqli_real_escape_string($dbc, trim($_POST['first_name']));
                }

                if(empty($_POST['last_name']))
                {
                    $errors[] = 'Enter your last name.';
                }
                else
                {
                    $ln = mysqli_real_escape_string($dbc, trim($_POST['last_name']));
                }

                if(empty($_POST['email']))
                {
                    $errors[] = 'Enter your email address.';
                }
                else
                {
                    $e = mysqli_real_escape_string($dbc, trim($_POST['email']));
                }

                if(!empty($_POST['pass1']))
                {
                    if($_POST['pass1'] != $_POST['pass2'])
                    {
                        $errors[] = 'Passwords do not match.';
                    }
                    else
                    {
                        $p = mysqli_real_escape_string($dbc, trim($_POST['pass1']));
                    }
                }
                else
                {
                    $errors[] = 'Enter your password.';
                }

                if(empty($errors))
                {
                    $q = "SELECT user_id FROM users WHERE email='$e'";
                    $r = mysqli_query($dbc, $q);

                    if(mysqli_num_rows($r) != 0)
                    {
                        $errors[] = 'Email address already registered. <a href="login.php">Login</a>';
                    }
                }

                if(empty($errors))
                {
                    $q = "INSERT INTO users
                        (first_name, last_name, email, pass, reg_date)
                        VALUES ('$fn', '$ln', '$e', SHA1('$p'), NOW())";
                    $r = mysqli_query($dbc, $q);

                    if($r)
                    {
                        echo '<h1>Registered!</h1>
                            <p>You are now registered.</p>
                            <p><a href="login.php">Login</a></p>';
                    }

                    mysqli_close($dbc);
                    include('/var/www/html/learn/php/footer.html');
                    exit();
                }
                else
                {
                    echo '<h1>Error!</h1>
                    <p id="err_msg">The following error(s) occurred:<br>';
                    foreach($errors as $msg)
                    {
                        echo "- $msg<br>";
                    }
                    echo 'Please try again.</p>';
                    mysqli_close($dbc);
                }
            }
        ?>

        <h1>Register</h1>
        <form action="register.php" method="POST">
            <p>
                First Name: <input type="text" name="first_name" value="<?php if(isset($_POST['first_name'])) echo $_POST['first_name'];?>">
                Last Name: <input type="text" name="last_name" value="<?php if(isset($_POST['last_name'])) echo $_POST['last_name'];?>">
            </p>
            <p>
                Email Address: <input type="text" name="email" value="<?php if(isset($_POST['email'])) echo $_POST['email'];?>">
            </p>
            <p>
                Password: <input type="password" name="pass1" value="<?php if(isset($_POST['pass1'])) echo $_POST['pass1'];?>">
                Confirm Password: <input type="password" name="pass2" value="<?php if(isset($_POST['pass2'])) echo $_POST['pass2'];?>">
            </p>
            <p>
                <input type="submit" value="Register">
            </p>
        </form>

        <?php include('/var/www/html/learn/php/footer.html');?>
    </body>                                        
</html>

This is where I believe my problem is, but I've tried various alternatives and still cannot get the data to insert users into the MySQL database:

if(empty($errors))
{
    $q = "INSERT INTO users
        (first_name, last_name, email, pass, reg_date)
        VALUES ('$fn', '$ln', '$e', SHA1('$p'), NOW())";
    $r = mysqli_query($dbc, $q);

    if($r)
    {
        echo '<h1>Registered!</h1>
            <p>You are now registered.</p>
            <p><a href="login.php">Login</a></p>';
    }

    mysqli_close($dbc);
    include('/var/www/html/learn/php/footer.html');
    exit();
}
else
{
    echo '<h1>Error!</h1>
    <p id="err_msg">The following error(s) occurred:<br>';
    foreach($errors as $msg)
    {
        echo "- $msg<br>";
    }
    echo 'Please try again.</p>';
    mysqli_close($dbc);
}

I'm not sure why the book is making me type "if($r)", but please tell me what it means and show where I messed up.

Regarding the last part: [mysqli_query](http://www.php.net/mysqli_query) will return `TRUE` or an object on success (both those evaluate to true), so `if ($r)` checks if the query was successful. — ccKep, Jun 06 '17 at 16:59
*The "Registered!" and login page does show up either. * - it **does** or it **doesn't** ? The "does...either" is a bit misleading there. — ccKep, Jun 06 '17 at 17:01
**Please do not use SHA1 to hash your passwords!** PHP provides [`password_hash()`](https://php.net/manual/en/function.password-hash.php) and [`password_verify()`](https://php.net/manual/en/function.password-verify.php) please use them. If you are using a PHP version prior to 5.5 [there is a compatibility pack available here](https://github.com/ircmaxell/password_compat). Make sure you [**don't escape passwords**](https://stackoverflow.com/q/36628418/5914775) or use any other cleansing mechanism on them before hashing. Doing so changes the password and causes unnecessary additional coding. — Tom Udding, Jun 06 '17 at 17:04
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — John Conde, Jun 06 '17 at 17:50
You don't know what's wrong because you don't check for errors in your code. Never assume the code is always going to work flawlessly. Use [`mysqli_error()`](http://php.net/manual/en/mysqli.error.php) to get a detailed error message from the database. — John Conde, Jun 06 '17 at 17:50

score 0 · Answer 1 · answered Jun 06 '17 at 17:58

Your problem:

You are duplicating variable $q & $r. Change here

     if(empty($errors))
        {
       // changing the variable name here to $s and $m

            $s = "SELECT user_id FROM users WHERE  email='$e'"; 
            $m = mysqli_query($dbc, $s);

            if(mysqli_num_rows($m) != 0)
            {
                $errors[] = 'Email address already registered. <a href="login.php">Login</a>';
            }
        }

            $q = "INSERT INTO users
                (first_name, last_name, email, pass, reg_date)
                VALUES ('$fn', '$ln', '$e', SHA1('$p'), NOW())";
            $r = mysqli_query($dbc, $q);

Also remove the additional if statement as it is not doing any purpose. Additionaly please use parametrized query to prevent sql injections. Hope you understand.

This is not the problem, also: The if statement has a purpose and is required. — ccKep, Jun 07 '17 at 01:40

PHP code not inserting into MySQL database

1 Answers1