SQL code not working

[webguync]

Can anyone take a look at my code and determine why the code to UPDATE MySQL table with a timestamp isn't working and also the username/password isn't properly authenticated against the info in the MySQL DB (anything gets you through to the submit page).

 

<?php
ini_set("display_errors","1");
ERROR_REPORTING(E_ALL);
session_start();
$con = mysql_connect("localhost","uname","pw") or die('Could not connect: ' . mysql_error());

mysql_select_db("DBName") or die(mysql_error());


//Escape user input
$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);


//Update record with current time IF the account has never logged in before
$query = "UPDATE `MyTable`
          SET `login_timestamp` = NOW()
          WHERE `username` = '$username'
            AND `password` = '$password'
            AND login_timestamp = ''";
$result = mysql_query($query);
//Check if query ran succesfully
if(!$result)
{
    //Query failed, add error handling
    $response = "Query failed";
}
else
{
    //Set flag
    $error = false;
    if(mysql_affected_rows()!=1)
    {
        //Record doesn't exist OR credentials have been previously used
        //Run query to see when the initial login was
        $query = "SELECT `MyTable`
                  FROM `Editor_Candidates`
                  WHERE `username` = '$username'
                    AND `password` = '$password'";
        $result = mysql_query($query);

        if (mysql_num_rows($result)!=1)
        {
            //username/password doesn't exist
            $error = "That username/password is not valid.";
        }
        else
        {
            //Get record and check first login time
            $record = mysql_fetch_assoc($result);
            if ($record['login_timestamp']<strtotime("-60 minutes"))
            {
                //username/password was used more than 60 minutes ago
                $error = "That username/password has expired";
            }
        }
    }
    //Check if error occured
    if ($error == false)
    {
        // Same checking stuff all over again.
if(isset($_POST['submit'])) {
   if(empty($_POST['username']) || empty($_POST['pwid']) ) {
    echo "<h2 style='color:#039;font-size:14px;font-family:arial, helvetica,sans-serif'>Please fill in both your username and password to access the editor exam. You will be redirected back to the login screen in 5 seconds</h2>";
  echo "<meta http-equiv='refresh' content='5; url=EditorLogin.php'>";
                exit;
   }
   // Create the variables again.
   
   $username = mysql_real_escape_string($_POST['username']);
   $pwid = $_POST['pwid'];

   // Encrypt the password again with the md5 hash. 
   // This way the password is now the same as the password inside the database.
   //$pwid = md5($pwid);

   // Store the SQL query inside a variable. 
   // ONLY the username you have filled in is retrieved from the database.
   $query = "SELECT username,pwid,name
           FROM   'MyTable'
           WHERE
           pwid = '$pwid'
           AND
           username='$username'";

   $result = mysql_query($query) or die(mysql_error());
   if(mysql_num_rows($result) == 0) { 
      // Gives an error if the username/pw given does not exist.
      // or if something else is wrong.
     echo "<h2 style='color:#039;font-size:14px;font-family:arial, helvetica,sans-serif'>You have entered a username or password that does not match our database records. please try again. You will be directed back to the login screen in 5 seconds. </h2> " . mysql_error();
echo "<meta http-equiv='refresh' content='5; url=EditorLogin.php'>";
exit();
/*
this would benefit from a redirect to a page giving better information to
the user and maybe logging some errors.
*/
   } else {
      // Now create an object from the data you've retrieved.
      $row = mysql_fetch_object($result);
      // You've now created an object containing the data.
      // You can call data by using -> after $row.
      // For example now the password is checked if they're equal.

      // By storing data inside the $_SESSION superglobal,
      // you stay logged in until you close your browser.
  $_SESSION['name'] = $row->name;
     $_SESSION['username'] = $username;
      $_SESSION['sid'] = session_id(); 
      // Make it more secure by storing the user's IP address.
      $_SESSION['ip'] = $_SERVER['REMOTE_ADDR'];
      // Now give the success message.
      // $_SESSION['username'] should print out your username.

//move this to after your redirect further below..
      
   }
}

// Start a session. If not logged in will be redirected back to login screen.

if(!isset($_SESSION['username'])){
exit;
}
echo "<div id='welcome'><h3>Welcome! You are now logged in " . $_SESSION['name'] . "</h3>";

echo "<a class='logout' href='logout.php'>Logout</a></div>";
     }
}


?>

 

<form enctype="multipart/form-data" method="post" action="test.php">

<label for="username">Username (email address): </label><br />
<input type="text" name="username" id="username"><br />
<label for="password">Password<br /> (you should have been given this): </label><br />
<input type="password" name="password" id="password"><br />
   
<input class="submit" type="submit" name="submit" value="Log In" />
</form>

[jl5501]

Is the name of the db table MyTable?

 

If so, your select query is wrong - should be select stuff from MyTable

[piyush23424]

try this if nothing works.... Hope it will work..............

 

$query = "UPDATE `MyTable`

          SET `login_timestamp` = NOW()

          WHERE `username` = '".mysql_real_escape_string($_POST['username'])."'

            AND `password` = '".mysql_real_escape_string($_POST['username'])."'

            AND login_timestamp = ''";

 

 

 

[webguync]

Thanks for the replies. For the SELECT, I meant to change the table name. MyTable is just a dummy name. I tried the code suggested for the UPDATE and it still doesn't work. Putting any values into the username and password fields gets you through also, so it's not checking the DB properly.

 

My MYSQL code looks like this...

 

CREATE TABLE `MyTable` (
  `name` text,
  `username` text,
  `pwid` varchar(10) default NULL,
  `login_timestamp` date NOT NULL default '0000-00-00',
  `user_id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

 

anyways to check and see why the SQL isn't working?

[webguync]

forgot to post my current PHP code

 

<?php
ini_set("display_errors","1");
ERROR_REPORTING(E_ALL);
session_start();
$con = mysql_connect("localhost","uname","pw") or die('Could not connect: ' . mysql_error());

mysql_select_db("DBName") or die(mysql_error());


// Same checking stuff all over again.
if(isset($_POST['submit'])) {
   if(empty($_POST['username']) || empty($_POST['password']) ) {
    echo "<h2 style='color:#0080b2;font-weight:bold;font-family:arial, helvetica, sans-serif;font-size:'14px';>Please fill in both your username and password to access your exam results.</h2>";
  echo "<meta http-equiv='refresh' content='5; url=ExamLogin.php'>";
                exit;
   }
   // Create the variables again.
   
   $username = mysql_real_escape_string($_POST['username']);
   $password = $_POST['password'];

   // Encrypt the password again with the md5 hash. 
   // This way the password is now the same as the password inside the database.
   //$pwid = md5($pwid);

   // Store the SQL query inside a variable. 
   // ONLY the username you have filled in is retrieved from the database.
   $query = "SELECT username,password,name
           FROM   Editor_Candidates
           WHERE
           password = '$password'
           AND
           username='$username'";

   $result = mysql_query($query) or die(mysql_error());
   if(mysql_num_rows($result) == 0) { 
      // Gives an error if the username/pw given does not exist.
      // or if something else is wrong.
     echo "<h2 style='color:#0080b2;font-weight:bold;font-family:arial, helvetica, sans-serif;font-size:'14px';>You have entered a username or password that does not match our database records. please try again. You will be redirected back to the login screen in 5 seconds.</h2> " . mysql_error();
echo "<meta http-equiv='refresh' content='5; url=EditorLogin.php'>";
exit();
/*
this would benefit from a redirect to a page giving better information to
the user and maybe logging some errors.
*/
   } else {
      // Now create an object from the data you've retrieved.
      $row = mysql_fetch_object($result);
      // You've now created an object containing the data.
      // You can call data by using -> after $row.
      // For example now the password is checked if they're equal.

      // By storing data inside the $_SESSION superglobal,
      // you stay logged in until you close your browser.
   $_SESSION['name'] = $row->name;
     $_SESSION['username'] = $username;
      $_SESSION['sid'] = session_id(); 
      // Make it more secure by storing the user's IP address.
      $_SESSION['ip'] = $_SERVER['REMOTE_ADDR'];
      // Now give the success message.
      // $_SESSION['username'] should print out your username.

//move this to after your redirect further below..
//Update record with current time IF the account has never logged in before
$query = "UPDATE `Editor_Candidates`
          SET `login_timestamp` = NOW()
          WHERE `username` = '$username'
            AND `password` = '$password'
            AND login_timestamp = ''";
$result = mysql_query($query);
//Check if query ran succesfully
      
   }
}

// Start a session. If not logged in will be redirected back to login screen.

if(!isset($_SESSION['username'])){
header("Location:EditorLogin.php");
exit;
}
echo "<div id='welcome'><h3>Welcome! You are now logged in " . $_SESSION['name'] . "</h3>";


?>

[dashti]

read the massege of piyush23424

 

you forgot a dot before $username and after $username

[webguync]

thanks, but I changed the SQL per piyush23424 post and it still does not seem to be updating the MySQL field.

[dashti]

oke

and do u get a error?

[webguync]

no errors, I am thinking I need to change the MySQL default, but not sure to what, exactly.

 

CREATE TABLE `Editor_Candidates` (
  `name` text,
  `username` text,
  `password` varchar(10) default NULL,
  `login_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;