need to create a link which deletes a record based on login info.

[webguync]

Hello, I have a login script where certain info is displayed based on login credentials. I have a log table which I want to be able to have a delete link or button which deletes the log record. The login I am using selects a username/password from a table and stores the login values into a secure page using SESSION ID's. It is on this secure page that I want the delete record link from the log table.

 

The username/password and log ID to delete are stored in two different tables, so was wondering if a SQL JOIN would be needed to accomplish this?

 

 

The two tables do share a common field which is user_id. Let me know if I need to clarify anything.

 

 

[ignace]

Do you want to perform a remote-logoff on a certain user? If so then you may be more interested in session_set_save_handler and create a session's table which will hold all session data.

 

sessions (id (PK), username (ID), lifetime, modified, data)

 

Deleting a record with a specified username now remotely logs off a user

[webguync]

no, not a remote logoff, just need to delete a log record and filter by user_id, so that the person who is logged in is only deleting their record and no one elses. The log entry isn't actually created by this login, it occurs previously when a user takes an online test.

 

A bit confusing, I know...

[Bio]

So you just need to delete a record from a database?

 

$sql = "DELETE FROM `table_name` WHERE `table_name`.`field_name` = " . $RecordToDelete . " LIMIT 1";

 

set $RecordToDelete to the record you want to delete.

[ignace]

so that the person who is logged in is only deleting their record and no one elses.

 

easy enough

 

DELETE FROM table WHERE table.field = $fieldvalue AND table.user_id = $uid

 

Mind the bold text

[webguync]

I think that is what I want. I would need to wrap it around an HTML link or button though.

 

<a href="">Delete Record</a> or <img src="img/delete.png">Delete Record</a>

[ignace]

I'm guessing you would mean:

 

<a href="delete.php?uid=1" class="delete-button">Delete Record</a>

<!-- OR -->

<form action="delete.php" method="POST">
<div>
    <button id="uid" name="uid" type="submit" value="1">Delete Record</button>
</div>
</form>

[webguync]

I like this one better

<a href="delete.php?uid=1" class="delete-button">Delete Record</a>

 

I have some question though. The user ID isn't currently being captured in the login, only username and password. So, how would the UID get determined? Would

 

$_SESSION['user_id'] = $uid;

 

do the trick?

 

[ignace]

No as you would delete your own record. Most likely you read this information from a database and if you include the uid in the select you can easily spit it out.

[webguync]

I think I am still missing a few things.

 

Here is my code as I have it now, It's a lot of code, and most of it has nothing to do with what I am trying to accomplish w/ the delete, but I want to make sure all the ode is displayed so I can be better assisted.

<?php
//ini_set("display_errors","1");
//ERROR_REPORTING(E_ALL);
session_start();

$con = mysql_connect("localhost","username","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['pwid']) ) {
     echo "<h2 class='fail'>Please fill in both your username and password to access your exam results.<br /><br >You will be redirected back to the login screen in five seconds.</h2>";
  echo "<meta http-equiv='refresh' content='5; url=StudentLogin.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 = "SELECT username,pwid,name,user_id
           FROM   Caris_roster_March2010
           WHERE
           pwid = '$pwid'
           AND
           username='$username'";

   $result_select = mysql_query($query_select) 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 class='fail'>You have entered a username or password that does not match our database records. please try again.<br><br>You will be redirected back to the login screen in five seconds.</h2> " . mysql_error();
echo "<meta http-equiv='refresh' content='5; url=StudentLogin.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


$dat = time() + 3600;
$query_update = "UPDATE Caris_roster_March2010
          SET login_timestamp = DATE_ADD(NOW(), INTERVAL 3 HOUR)
          WHERE username = '$username'
           AND pwid = '$pwid' ";
//echo $query; //for debugging test 
$result_update = mysql_query($query_update) or die(mysql_error()); 

//Check if query ran successfully     
   }
}

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

if(!isset($_SESSION['username'])){
header("Location:StudentLogin.php");
exit;
}
echo "<table id='header'><tr><td><img src='Caris-Life-Sciences-Logo_small.png' /></td><td align='middle'><div id='welcome'><h3>Welcome! You are now logged in " . $_SESSION['name'] . "</h3></td></tr>";

echo "<tr><td><a class='logout' href='LogoutStudent.php'>Logout</a></td></tr></table>";

//DELETE QUERY TO SELECT RECORD TO DELTE BASED ON LOGIN INFO.
$query_delete = "DELETE FROM Caris_log_March2010 WHERE user_id = $user_id AND Caris_roster_March2010.user_id = $user_id";
//echo $query; //for debugging test 
$result_delete = mysql_query($query_delete) or die(mysql_error()); 
?>

 

and with the code below...

<a href="delete.php?user_id=1" class="delete-button">Delete Record</a>

 

still not sure how the user_id is set. Do I need to create a hidden field in the login form?

 

[ignace]

I think you forgot to add $user_id = $_GET['user_id']; in the delete.php file

 

I also took the liberty to cleanup your code and give you an example how clean code will help you in your endeavors

 

<?php
//ini_set("display_errors","1");
//ERROR_REPORTING(E_ALL);
function my_error_handler($errno, $errstr, $errfile, $errline, array $errcontext = array()) {
    die($errstr);
}
set_error_handler('my_error_handler');

session_start();
$con = mysql_connect("localhost", "username", "pw") or trigger_error('Could not connect: ' . mysql_error());
mysql_select_db("DBName", $con) or trigger_error(mysql_error());

class EmptyCredentialsException extends Exception {}
class InvalidCredentialsException extends Exception {}

// Same checking stuff all over again.
function clean($value, $db = null) {
    $value = strip_tags($value);
    $value = htmlentities($value);
    $temp = @mysql_real_escape_string($value, $db)
        ? $value = $temp
        : $value = addslashes($value);
    return $value;
}

function login($username, $password, $db = null) {
    if (empty($username) || empty($password)) {
        throw new EmptyCredentialsException();
    }

    $username = clean($username, $db);
    $pwid = clean($password, $db);

    $pwid = intval($pwid);
    $query = "SELECT name, username FROM Caris_roster_March2010 WHERE pwid = MD5('$pwid') AND username = '$username'";
    $result = mysql_query($query, $db);
    if ($result && mysql_num_rows($result)) {
        $user = mysql_fetch_assoc($result);
        user_update(array('login_timestamp' => time()), $username, $db);

        session_regenerate_id();

        $meta_data = array('ip' => $_SERVER['REMOTE_ADDR'], 'browser' => $_SEVER['HTTP_USER_AGENT']);
        session_store($user + $meta_data);
        return true;
    }

    throw new InvalidCredentialsException();
}

function user_update($data, $username, $db = null) {
    $query = 'UPDATE Caris_roster_March2010 SET ';
    $data = array_map('user_update_callback', $data, array_keys($data));
    $query = $query . implode(', ', $data);
    $query = "$query WHERE username = '$username'";
    $result = mysql_query($query, $db) or trigger_error(mysql_error());
    return $result && mysql_affected_rows($result);
}

function user_update_callback($value, $key) {
    return "$key = '{clean($value)}'";
}

function session_is_auth() {
    return (isset($_SESSION['ip']) && isset($_SESSION['browser'])) &&
           (($_SESSION['ip'] === $_SERVER['REMOTE_ADDR']) && ($_SESSION['browser'] === $_SERVER['HTTP_USER_AGENT']));
}

function session_store($data) {
    $_SESSION = array_merge($_SESSION, $user);
}

if (isset($_POST['submit'])) {
    try {
        login($_POST['username'], $_POST['pwid']);
    } catch (EmptyCredentialsException $e) {
        echo "<h2 class='fail'>Please fill in both your username and password to access your exam results.<br />",
             "<br >You will be redirected back to the login screen in five seconds.</h2>";
        echo "<meta http-equiv='refresh' content='5; url=StudentLogin.php'>";
        exit;
    } catch (InvalidCredentialsException $e) {
        echo "<h2 class='fail'>You have entered a username or password that does not match our database records.",
             " please try again.<br><br>You will be redirected back to the login screen in five seconds.</h2> ";
        echo "<meta http-equiv='refresh' content='5; url=StudentLogin.php'>";
        exit();
    }
}

// Start a session. If not logged in will be redirected back to login screen.
if (!session_is_auth()) {
    header("Location:StudentLogin.php");
    exit;
}

echo "<table id='header'><tr><td><img src='Caris-Life-Sciences-Logo_small.png' /></td><td align='middle'><div id='welcome'><h3>Welcome! You are now logged in " . $_SESSION['name'] . "</h3></td></tr>";

echo "<tr><td><a class='logout' href='LogoutStudent.php'>Logout</a></td></tr></table>";

$user_id = $_GET['user_id'];
//DELETE QUERY TO SELECT RECORD TO DELTE BASED ON LOGIN INFO.
$query_delete = "DELETE FROM Caris_log_March2010 WHERE user_id = $user_id AND Caris_roster_March2010.user_id = $user_id";
//echo $query; //for debugging test
$result_delete = mysql_query($query_delete) or die(mysql_error());
?>

[webguync]

thanks for doing that! I will give it a try. One quick question, for the delete link, how would I code? meaning how to I determine user_id to delete?

 

<a href="delete.php?user_id=1" class="delete-button">Delete Record</a> 

[ignace]

Simple:

 

if !empty($_GET['user_id']) && is_numeric($_GET['user_id']) then

    delete from table where user_id = $_GET['user_id'];

[webguync]

sorry you lost me a little bit there. I thought we were already selecting the record to delete here?

$user_id = $_GET['user_id'];
//DELETE QUERY TO SELECT RECORD TO DELTE BASED ON LOGIN INFO.
$query_delete = "DELETE FROM Caris_log_March2010 WHERE user_id = $user_id AND Caris_roster_March2010.user_id = $user_id";
//echo $query; //for debugging test
$result_delete = mysql_query($query_delete) or die(mysql_error());

 

so, I would just need to trigger the delete with a link and preferably echo a result. echo "Delete Sucessfull!"

 

Sorry if I misunderstood.

[webguync]

Hey Ignace,

 

when I login to the form with the cleaned up code you provided I get the following error.

 

mysql_real_escape_string() expects parameter 2 to be resource, null given

 

I double checked and the database login info. seems correct. What could cause this error?

[ignace]

You get this error because of the function clean() remove $db as a second parameter for mysql_real_escape_string and you should be fine.

 

[ot]What is the default value for a resource?[/ot]

[webguync]

are you referring to this:

$temp = @mysql_real_escape_string($value, $db)

 

i changed to this:

$temp = @mysql_real_escape_string($value)

 

but then i get an error "Undefined variable: temp"

[ignace]

I think you deleted a little to many post your code

[webguync]

<?php
//ini_set("display_errors","1");
//ERROR_REPORTING(E_ALL);
function my_error_handler($errno, $errstr, $errfile, $errline, array $errcontext = array()) {
    die($errstr);
}
set_error_handler('my_error_handler');

session_start();
$con = mysql_connect("localhost", "username", "pw") or trigger_error('Could not connect: ' . mysql_error());
mysql_select_db("DB_name", $con) or trigger_error(mysql_error());

class EmptyCredentialsException extends Exception {}
class InvalidCredentialsException extends Exception {}

// Same checking stuff all over again.
function clean($value, $db = null) {
    $value = strip_tags($value);
    $value = htmlentities($value);
    $temp = @mysql_real_escape_string($value, $db)
        ? $value = $temp
        : $value = addslashes($value);
    return $value;
}

function login($username, $password, $db = null) {
    if (empty($username) || empty($password)) {
        throw new EmptyCredentialsException();
    }

    $username = clean($username, $db);
    $pwid = clean($password, $db);

    $pwid = intval($pwid);
    $query = "SELECT name, username FROM roster_March2010 WHERE pwid = MD5('$pwid') AND username = '$username'";
    $result = mysql_query($query, $db);
    if ($result && mysql_num_rows($result)) {
        $user = mysql_fetch_assoc($result);
        user_update(array('login_timestamp' => time()), $username, $db);

        session_regenerate_id();

        $meta_data = array('ip' => $_SERVER['REMOTE_ADDR'], 'browser' => $_SEVER['HTTP_USER_AGENT']);
        session_store($user + $meta_data);
        return true;
    }

    throw new InvalidCredentialsException();
}

function user_update($data, $username, $db = null) {
    $query = 'UPDATE roster_March2010 SET ';
    $data = array_map('user_update_callback', $data, array_keys($data));
    $query = $query . implode(', ', $data);
    $query = "$query WHERE username = '$username'";
    $result = mysql_query($query, $db) or trigger_error(mysql_error());
    return $result && mysql_affected_rows($result);
}

function user_update_callback($value, $key) {
    return "$key = '{clean($value)}'";
}

function session_is_auth() {
    return (isset($_SESSION['ip']) && isset($_SESSION['browser'])) &&
           (($_SESSION['ip'] === $_SERVER['REMOTE_ADDR']) && ($_SESSION['browser'] === $_SERVER['HTTP_USER_AGENT']));
}

function session_store($data) {
    $_SESSION = array_merge($_SESSION, $user);
}

if (isset($_POST['submit'])) {
    try {
        login($_POST['username'], $_POST['pwid']);
    } catch (EmptyCredentialsException $e) {
        echo "<h2 class='fail'>Please fill in both your username and password to access your exam results.<br />",
             "<br >You will be redirected back to the login screen in five seconds.</h2>";
        echo "<meta http-equiv='refresh' content='5; url=StudentLogin.php'>";
        exit;
    } catch (InvalidCredentialsException $e) {
        echo "<h2 class='fail'>You have entered a username or password that does not match our database records.",
             " please try again.<br><br>You will be redirected back to the login screen in five seconds.</h2> ";
        echo "<meta http-equiv='refresh' content='5; url=StudentLogin.php'>";
        exit();
    }
}

// Start a session. If not logged in will be redirected back to login screen.
if (!session_is_auth()) {
    header("Location:StudentLogin.php");
    exit;
}

echo "<table id='header'><tr><td align='middle'><div id='welcome'><h3>Welcome! You are now logged in " . $_SESSION['name'] . "</h3></td></tr>";

echo "<tr><td><a class='logout' href='LogoutStudent.php'>Logout</a></td></tr></table>";

$user_id = $_GET['user_id'];
//DELETE QUERY TO SELECT RECORD TO DELETE BASED ON LOGIN INFO.
$query_delete = "DELETE FROM log_March2010 WHERE user_id = $user_id AND roster_March2010.user_id = $user_id";
//echo $query; //for debugging test
$result_delete = mysql_query($query_delete) or die(mysql_error());
?>
<body class="results">

<div>

<h1>Validation Exam Results</h1>



<table id="resultlist">
   
	<tr>
		<th scope="col">Employee Name</th>
                        
		<th scope="col">Number Correct</th>
		<th scope="col">Score</th>
		<th scope="col">Question Number Answered Incorrectly</th>
		<th scope="col">Date Completed</th>
                       
                       <th scope="col">Pass/Fail</th>
		<th scope="col">Material to review in the Oncology Backgrounder (Chapter, Section)</th>
	</tr>

	<?php if (!isset($name)) { ?>
	<tr><td colspan="7">There are no scores to display</td></tr>
	<?php
	} else {
	for ($i=0; $i<count($name); $i++) { ?>
	<tr class="<?php echo $i%2 ? 'hilite' : 'nohilite'; ?>">
		<td ><?php echo $name[$i];?></td>
		<td><?php echo $numCorr[$i];?></td>
            

		<td><?php echo (ROUND(($pcnt[$i]*100),0).'%'); ?></td>
		<td><?php echo $incorr[$i];?></td>
		<td><?php echo (date('F j, Y  g:i A',($date[$i])));?></td>
          <td><?php
   if(($pcnt[$i]*100) > 89)
   {
      echo "<div class='passed'>" .Passed."</div>";
   }
   else
   {
      echo "<div class='failed'>" .Failed. "</div>";
   }
?></td>


<td><?php echo $workon[$i];?></td>
</tr>
	<?php }
	} ?>

</table>
</div>


<?php if (!isset($name)) { ?>
<tr><td><p><strong>We don't have a record of you taking this exam</strong></p></td></tr>
<?php
} else {
?>



<?php } ?>

 

also, I still need to add in the link which actually triggers the delete.

 

thanks,

 

 

 

 

 

 

[ignace]

Weird do you get that error by using the clean() function? Because I ran this little test:

 

error_reporting(E_ALL);
ini_set('display_errors', 1);

function clean($value, $db = null) {
    $value = strip_tags($value);
    $value = htmlentities($value);
    $temp = @mysql_real_escape_string($value, $db)
        ? $value = $temp
        : $value = addslashes($value);
    return $value;
}

$var = 'hello world';
$var = clean($var);

 

And didn't return any errors.

[webguync]

when I ran that code, I just got a blank white page. No errors came up though.

[ignace]

when I ran that code, I just got a blank white page. No errors came up though.

 

Same here so what line did you get that error? and what is on that line and the line above and below?

[webguync]

that's the frustrating thing. The error is just "Undefined variable: temp". No line number given.

 

I assume is it in this block

function clean($value, $db = null) {
    $value = strip_tags($value);
    $value = htmlentities($value);
    $temp = @mysql_real_escape_string($value)
        ? $value = $temp
        : $value = addslashes($value);
    return $value;
}

[ignace]

No otherwise you would have gotten the same error in that previous script. Otherwise remove all those lines ($temp = .. to $value = $temp : and just keep $value = addslashes($value); and see if the error then still turns up.

[webguync]

I did that and

 

I get the error "mysql_query(): supplied argument is not a valid MySQL-Link resource"

 

function clean($value, $db = null) {
  
    $value = addslashes($value);
    return $value;
}