Ask a Question Register Login

Unable to add user to MySQL database using PHP, error code #1064

69

UPDATE:

My initial question was regarding an issue with adding a user to the database. It was solved but now I am having trouble logging in (login.php). My database has a current email entry of [email protected] , but when I try to login with this email, it says "Login failed".

I tried to incorporate the initial question's solution into login.php but it seems the code was already present. So I'm stuck... here is my register.php page and my login.php page.

I have a feeling it has to do with:email somehow. What is wrong with my login.php file?

register.php

<?php 

    // First we execute our common code to connection to the database and start the session 
    require("common.php"); 

    // This if statement checks to determine whether the registration form has been submitted 
    // If it has, then the registration code is run, otherwise the form is displayed 
    if(!empty($_POST)) 
    { 
        // Ensure that the user has entered a non-empty username 
        if(empty($_POST['email'])) 
        { 
            // Note that die() is generally a terrible way of handling user errors 
            // like this.  It is much better to display the error with the form 
            // and allow the user to correct their mistake.  However, that is an 
            // exercise for you to implement yourself. 
            die("Please enter an email."); 
        } 

        // Make sure the user entered a valid E-Mail address 
        // filter_var is a useful PHP function for validating form input, see: 
        // http://us.php.net/manual/en/function.filter-var.php 
        // http://us.php.net/manual/en/filter.filters.php 
        if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) 
        { 
            die("Invalid E-Mail Address"); 
        } 

      // Now we perform the same type of check for the email address, in order 
        // to ensure that it is unique. 
        $query = " 
            SELECT 
                1 
            FROM users 
            WHERE 
                email = :email 
        "; 

        $query_params = array( 
            ':email' => $_POST['email'] 
        ); 

        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: "); 
        } 

        $row = $stmt->fetch(); 

        if($row) 
        { 
            die("This email address is already registered"); 
        } 

        // An INSERT query is used to add new rows to a database table. 
        // Again, we are using special tokens (technically called parameters) to 
        // protect against SQL injection attacks. 
        $query = " 
            INSERT INTO users (  
                email 
            ) VALUES ( 
             :email 
            ) 
        "; 

        $query_params = array( 
        ':email' => $_POST['email'] 
    ); 

    try 
    { 
        $stmt = $db->prepare($query); 
        $result = $stmt->execute($query_params); 
    } 
    catch(PDOException $ex) 
    { 
        die("Failed to run query: "); 
    }  

        // This redirects the user back to the login page after they register 
        header("Location: login.php"); 

        // Calling die or exit after performing a redirect using the header function 
        // is critical.  The rest of your PHP script will continue to execute and 
        // will be sent to the user if you do not die or exit. 
        die("Redirecting to login.php"); 
    } 

?> 
<h1>Register</h1> 
<form action="register.php" method="post"> 
    E-Mail:<br /> 
    <input type="text" name="email" value="" /> 
    <br /><br /> 
    <input type="submit" value="Register" /> 
</form>

login.php

<?php 

    // First we execute our common code to connection to the database and start the session 
    require("common.php"); 

    // This if statement checks to determine whether the login form has been submitted 
    // If it has, then the login code is run, otherwise the form is displayed 
    if(!empty($_POST)) 
    { 
        // This query retreives the user's information from the database using 
        // their email. 
        $query = " 
            SELECT 
                email 
            FROM users 
            WHERE 
                email = :email 
        "; 

        // The parameter values 
        $query_params = array( 
            ':email' => $_POST['email'] 
        ); 

        try 
        { 
            // Execute the query against the database 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            // Note: On a production website, you should not output $ex->getMessage(). 
            // It may provide an attacker with helpful information about your code.  
            die("Failed to run query: " . $ex->getMessage()); 
        } 

        // This variable tells us whether the user has successfully logged in or not. 
        // We initialize it to false, assuming they have not. 
        // If we determine that they have entered the right details, then we switch it to true. 
        $login_ok = false; 

        // Retrieve the user data from the database.  If $row is false, then the email
        // they entered is not registered. 
        $row = $stmt->fetch(); 

        // If the user logged in successfully, then we send them to the private members-only page 
        // Otherwise, we display a login failed message and show the login form again 
        if($login_ok) 
        { 

            // This stores the user's data into the session at the index 'user'. 
            // We will check this index on the private members-only page to determine whether 
            // or not the user is logged in.  We can also use it to retrieve 
            // the user's details. 
            $_SESSION['user'] = $row; 

            // Redirect the user to the private members-only page. 
            header("Location: private.php"); 
            die("Redirecting to: private.php"); 
        } 
        else 
        { 
            // Tell the user they failed 
            print("Login Failed.");   
        } 
    } 

?> 
<h1>Login</h1> 
<form action="login.php" method="post"> 
    Email:<br /> 
    <input type="text" name="email" value="My Email" /> 
    <br /><br /> 
    <input type="submit" value="Login" /> 
</form> 
<a href="register.php">Register</a>

Thank you!

You can disregard the text below.

OLD QUESTION (for register.php):

I'm trying to create a simple login system for my site, where only an email address is needed for registration/login. The problem arises when I try to add a user to the database using phpMyAdmin, I get an error (more below).

I've successfully created a MySQL database using:

CREATE TABLE `users` (
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

and then on the register.php page I have this code.

<?php 

    // First we execute our common code to connection to the database and start the session 
    require("common.php"); 

    // This if statement checks to determine whether the registration form has been submitted 
    // If it has, then the registration code is run, otherwise the form is displayed 
    if(!empty($_POST)) 
    { 
        // Ensure that the user has entered a non-empty username 
        if(empty($_POST['email'])) 
        { 
            // Note that die() is generally a terrible way of handling user errors 
            // like this.  It is much better to display the error with the form 
            // and allow the user to correct their mistake.  However, that is an 
            // exercise for you to implement yourself. 
            die("Please enter an email."); 
        } 

        // Make sure the user entered a valid E-Mail address 
        // filter_var is a useful PHP function for validating form input, see: 
        // http://us.php.net/manual/en/function.filter-var.php 
        // http://us.php.net/manual/en/filter.filters.php 
        if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) 
        { 
            die("Invalid E-Mail Address"); 
        } 

      // Now we perform the same type of check for the email address, in order 
        // to ensure that it is unique. 
        $query = " 
            SELECT 
                1 
            FROM users 
            WHERE 
                email = :email 
        "; 

        $query_params = array( 
            ':email' => $_POST['email'] 
        ); 

        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: "); 
        } 

        $row = $stmt->fetch(); 

        if($row) 
        { 
            die("This email address is already registered"); 
        } 

        // An INSERT query is used to add new rows to a database table. 
        // Again, we are using special tokens (technically called parameters) to 
        // protect against SQL injection attacks. 
        $query = " 
            INSERT INTO users (  
                email 
            ) VALUES ( 
             :email 
            ) 
        "; 



        // This redirects the user back to the login page after they register 
        header("Location: login.php"); 

        // Calling die or exit after performing a redirect using the header function 
        // is critical.  The rest of your PHP script will continue to execute and 
        // will be sent to the user if you do not die or exit. 
        die("Redirecting to login.php"); 
    } 

?> 
<h1>Register</h1> 
<form action="register.php" method="post"> 
    E-Mail:<br /> 
    <input type="text" name="email" value="" /> 
    <br /><br /> 
    <input type="submit" value="Register" /> 
</form>

The problem might be due to this code:

INSERT INTO users (  
                email 
            ) VALUES ( 
             :email 
            )

which is the code causing me problems in phpMyAdmin. It gives me this error: 

#1064 - 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 ':email 
            )' at line 4

The register.php page is successfully connecting to my DB and is also redirecting me as it should, so I feel the problem is adding the user.

992

Answer

Solution:

It doesn't look like your INSERT statement is executing the query or assigning the email parameter.

Under

$query = " 
    INSERT INTO users (  
        email 
    ) VALUES ( 
     :email
    ) 
";

Add

 $query_params = array( 
        ':email' => $_POST['email'] 
    ); 

    try 
    { 
        $stmt = $db->prepare($query); 
        $result = $stmt->execute($query_params); 
    } 
    catch(PDOException $ex) 
    { 
        die("Failed to run query: "); 
    }
945

Answer

Solution:

I've added/adjusted your code. I've used different placeholder names to make debugging easier

<?php 

// First we execute our common code to connection to the database and start the session 
require("common.php"); 

// This if statement checks to determine whether the registration form has been submitted 
// If it has, then the registration code is run, otherwise the form is displayed 
if(!empty($_POST)) 
{ 
    // Ensure that the user has entered a non-empty username 
    if(empty($_POST['email'])) 
    { 
        // Note that die() is generally a terrible way of handling user errors 
        // like this.  It is much better to display the error with the form 
        // and allow the user to correct their mistake.  However, that is an 
        // exercise for you to implement yourself. 
        die("Please enter an email."); 
    } 

    // Make sure the user entered a valid E-Mail address 
    // filter_var is a useful PHP function for validating form input, see: 
    // http://us.php.net/manual/en/function.filter-var.php 
    // http://us.php.net/manual/en/filter.filters.php 
    if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) 
    { 
        die("Invalid E-Mail Address"); 
    } 

  // Now we perform the same type of check for the email address, in order 
    // to ensure that it is unique. 
    $checkSQL = <<<SQL1 
        SELECT 
            1 
        FROM users 
        WHERE 
            email = :checkemail 
    SQL1;

    $insertSQL = <<<SQL2 
        INSERT INTO users (  
            email 
        ) VALUES ( 
         :insertemail 
        ) 
    SQL2;

    try 
    { 
        $s_ps = $db->prepare($checkSQL); 
        $s_ps -> bindValue(':checkemail',$_POST['email'],PDO::PARAM_STR);
        $s_ps -> execute(); 

        $checkRow = $s_ps->fetch(); 

        if($checkRow) 
        { 
            die("This email address is already registered"); 
        } 

        // An INSERT query is used to add new rows to a database table. 
        // Again, we are using special tokens (technically called parameters) to 
        // protect against SQL injection attacks. 

        $i_ps=$db -> prepare($insertSQL);
        $i_ps -> bindValue(':insertemail',$_POST['email'],PDO::PARAM_STR);
        $i_ps -> execute();

    } 
    catch(PDOException $ex) 
    { 
        die("Failed to run query: ".$ex->getMessage()); 
    } 

    // This redirects the user back to the login page after they register 
    header("Location: login.php"); 

    // Calling die or exit after performing a redirect using the header function 
    // is critical.  The rest of your PHP script will continue to execute and 
    // will be sent to the user if you do not die or exit. 
    die("Redirecting to login.php"); 
} 

?> 
<h1>Register</h1> 
<form action="register.php" method="post"> 
E-Mail:<br /> 
<input type="text" name="email" value="" /> 
<br /><br /> 
<input type="submit" value="Register" /> 
</form>
195

Answer

Solution:

use

":email"

This might work...

92

Answer

Solution:

The issue is mysql doens't know what :email is... Try...

$email = mysql_real_escape_string($_POST['email']);
$query = " 
        INSERT INTO users (  
            email 
        ) VALUES ( 
         '$email' 
        ) 
    ";

People are also looking for solutions to the problem: javascript - Can't pass PHP variables through JQUERY and insert them into MYSQL

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.