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

webguync · March 22, 2010

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 · March 22, 2010

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 · March 22, 2010

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 · March 22, 2010

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 · March 22, 2010

Quote
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 · March 23, 2010

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 · March 23, 2010

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 · March 23, 2010

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 · March 23, 2010

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 · March 23, 2010

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 · March 24, 2010

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 · March 24, 2010

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 · March 24, 2010

Simple:

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

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

webguync · March 24, 2010

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 · March 25, 2010

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 · March 25, 2010

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 · March 25, 2010

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 · March 25, 2010

I think you deleted a little to many post your code

webguync · March 25, 2010

<?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 · March 26, 2010

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 · March 26, 2010

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

ignace · March 26, 2010

Quote

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 · March 26, 2010

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 · March 26, 2010

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 · March 26, 2010

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;
}

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information