Jump to content

Why won't this connect to my database?


slotegraafd
 Share

Recommended Posts

HI, Sorry the question title is really bad. But I'm currently making a simple php login page for school(well it should be simple anyway) and I'm having trouble getting the user to actually login. So Everything else on my page works fine. I have the login form and then I have error messages that displays an error if the user didnt enter any information or if the information is incorrect. So when I try to login with information from the database that should let me login and take me to the dashboard page it still says it is incorrect. Now at the top of my page I am getting an error that says: syntax error at or near "'lastAccess'" LINE 1: UPDATE users SET 'lastAccess' = $1 WHERE 'id' = $2 ^ in C:\XAMPP\htdocs\Webd3201\includes\db.php on line 13. I don't understand why I a,m getting that error. I don't see any syntax errors in my database.
 

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;

-- DROP'ping tables clear out any existing data
DROP TABLE IF EXISTS users;

-- CREATE the table
CREATE TABLE users(
userId VARCHAR(50) PRIMARY KEY NOT NULL,
email VARCHAR(255) UNIQUE,
password VARCHAR(255) NOT NULL,
firstName VARCHAR(128),
lastName VARCHAR(128),
enrolDate TIMESTAMP,
lastAccess TIMESTAMP,
enable BOOLEAN,
type VARCHAR(2) NOT NULL
);

ALTER TABLE users OWNER TO postgres;

INSERT INTO users (userId, email, password, firstName, lastName, enrolDate, lastAccess, enable, type) VALUES (
'DoeJ', 'jdoe@dcmail.ca', crypt('password', gen_salt('bf')), --NOTE: bf stands for blowfish
'John', 'Doe', '2020-09-14 19:10:25', '2020-09-15 11:11:11', true, 'a');

INSERT INTO users (userId, email, password, firstName, lastName, enrolDate, lastAccess, enable, type) VALUES (
'slotegraafd', 'deanna.slotegraaf@dcmail.ca', crypt('deanna1999', gen_salt('bf')),
'Deanna', 'Slotegraaf', '2020-09-15 23:18:27', '2020-09-16 13:40:02', true, 'a');

INSERT INTO users (userId, email, password, firstName, lastName, enrolDate, lastAccess, enable, type) VALUES (
'smithj', 'smithj@dcmail.ca', crypt('smith', gen_salt('bf')),
'Jane', 'Smith', '2020-08-22 09:14:56', '2020-09-01 20:34:14', true, 'a');

This is the database code ^^^^^^^

 

<?php
function db_connect()
{
	$conn = pg_connect("host=" .DB_HOST. " port=" .DB_PORT. " dbname=" .DB_NAME. " user=" .DB_USER. " password=" .DB_PASSWORD);
	return $conn;
} 

$conn = db_connect(); 

//Retrieve the user
$stmt1 = pg_prepare($conn, "user_select", 'SELECT * FROM users WHERE userId = $1 AND password = $2');
//Update the login time
$stmt2 = pg_prepare($conn, 'user_update_login_time', "UPDATE users SET 'lastAccess' = $1 WHERE 'id' = $2");
$stmt3 = pg_prepare($conn, 'user_authenticate', "SELECT 'userId', 'password', 'email', 'lastAccess', 'type' FROM users WHERE 'id' = $1 AND 'password' = $2");
$stmt4 = pg_prepare($conn, 'get_info', "SELECT userId, firstName, lastName FROM users WHERE userId = $1;");
?>

This is the file thats giving the error^^^^

<?php
    /*
    Name: Deanna Slotegraaf
    Course Code: WEBD3201 
    Date: 2020-09-22
    */

    $file = "login.php";
    $date = "2020-09-22";
    $title = "Login Page";
    $coursecode = "WEBD3201";
    $description = "This is a login page created for a real estate website for WEBD3201";
    $banner = "Login Page";
    $heading = "DS REAL ESTATE";
    require 'header.php';

    $error = ""; //Displays the error message to the screen

    //Get method to get  the info from the server
    if($_SERVER["REQUEST_METHOD"] == "GET")
    {
        $username = "";  //Holds the username
        $password = "";  //Holds the password
        $lastaccess = ""; //Displays the last access time       
        $error = "";     //Displays the error message on the screen
        $result = "";   //Gets the result of the query
        $validUser = ""; //Determines if the user is valid

    }
    //Posts to the server
    else if($_SERVER["REQUEST_METHOD"] == "POST")
    {
      //Connects to the database from db.php
        $conn;
        $username = trim($_POST["username"]); //removes trailing white space for username input box
        $password = trim($_POST["password"]);  //Removes trailing white space for password input box

        //Determines if the username input box is blank
        if(!isset($username) || $username == "")
        {
          //Display error message
            $error .= "<br/>You must enter a username!";

        }
        //Determines if the password input box is blank
        if(!isset($password) || $password == "")
        {
          //Display error message
            $error .= "<br/>You must enter a password!";
        }

        //If the error message is blank
        if ($error == "")
        {
          //Hash the password
            $password = hash("md5", $password);
            //Gets the user from the database
            $result = pg_execute($conn, "user_select", array($username, $password));
           
           //IF the query results were successful
            if(pg_num_rows($result))
            {
                
                $user = pg_fetch_assoc($result, 0);
                $_SESSION['user'] = $user;

                //Update the login time
                $result = pg_execute($conn, "user_update_login_time", array(date("Y-m-d"), $login));
                
                //If there is a user on the session
                if($_SESSION['user'])
                {
                    
                    header("Location: ./dashboard.php");
                    ob_flush();
                }
            }

            else
            {
              //Validates if the username and password are correct
              $sql = "SELECT userId FROM users
              WHERE userId = '".$username."'";
              $result = pg_query($conn, $sql);
              $validUser = pg_num_rows($result);
              if($validUser > 0)
              {
                //Display error message
                  $error = "Incorrect Password. Please try again!";
              }
              else
              {
                //Displays error message
                  $error = "Incorrect Username / Password. Please try again!";

              }
            }
        }
    }

?>

<div class = "form-signin">
    <?php
             echo "<h2 style='color:red; font-size: 20px'>".$error."</h2>";
    ?>
    <br/>
<form action = "<?php echo $_SERVER['PHP_SELF'];  ?>" method="post">
      <label for="username"><b>Username:</b></label>
      <input type="text" name="username" value="<?php echo $username; ?>"/>
      <br/>
      <label for="password"><b>Password:</b></label>
      <input type="password" name="password" value="<?php echo $password; ?>"/>
      <br/>
      <button type="submit">Login</button>
      <button type="reset">Reset</button>
</form>
</div>

<?php require "footer.php"; ?>

And this is the login page(don't know if this one will be neededf.

 

Anyway any help would be amazing!

Thanks in advance!

Link to comment
Share on other sites

Single ' quotes are for strings and only for strings. They will not work for table or column names.

You don't normally need quotes for table or column names, and you should try to avoid putting yourself into a situation where you do need them, but if you must then for PostgreSQL you should use double " quotes.

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.