Need assistance with link to Delete record in MySQL DB

[webguync]

I still haven't figured this out. I need help with adding a delete link to this script which Deletes a record in the database, using the login info to determine which record to delete.

 

the code for the login page is

 

 

 

<?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","uname","pw") or die('Could not connect: ' . mysql_error());
mysql_select_db("nestle_exam", $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 = 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,user_id FROM TableName 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' => $_SERVER['HTTP_USER_AGENT']);
        session_store($user + $meta_data);
        return true;
    }

    throw new InvalidCredentialsException();
}

function user_update($data, $username, $db = null) {
    $query = 'UPDATE TableName 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) {
print_r($_SESSION);
    $_SESSION = array_merge($_SESSION, $data);
print_r($data);
}

if (isset($_POST['submit'])) {
    try {
        login($_POST['username'], $_POST['pwid'],$con);
    } 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>";

 

I want to add a <a href="Delete.php">Delete Link</a>

 

where the code for Delete.php is:

<?php
session_start();
//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 trigger_error('Query failed: ' .mysql_error());
if ($result_delete)
{
echo "Delete Successful"
}// end if
else
{
echo "Failed"
} //end else
?>

there are two tables involved here. the roster table which included the name and password and the log table. The unique field they have in common is $user_id, but with my current code, I don't believe that value is being set so I need to add it to my data array, but having trouble w/ that part.

 

Let me know if I need to supply any more info.

[ChemicalBliss]

Use print_r($_SESSION) to show you what session variables are available (The login script saves those session variables, with the username).

 

I would image to get the username you would need to use something similar to:

$_SESSION['user']['username'];

 

-cb-

[webguync]

yea, I tried doing that.

 

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

 

but when I login and get to the secure page nothing displays except the code I am pulling in from the database. I guess using print_r is supposed to display the array on the page?

 

the field the two tables share in common is user_id. Thanks for the assistance!

[webguync]

I am trying some different code that I used in the past and that I understand a lot better. The delete isn't working though.

 

Here is the login 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['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 username,pwid,name,user_id
           FROM   roster_April2010
           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 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['user_id'] = $user_id;
     $_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 roster_April2010
          SET login_timestamp = DATE_ADD(NOW(), INTERVAL 3 HOUR)
          WHERE username = '$username'
           AND pwid = '$pwid' ";
//echo $query; //for debugging test 
$result = mysql_query($query) 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 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><td><a href='delete.php'>Delete.php</td></tr></table>";

?>

 

and in my delete.php file

 

<?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());
$_SESSION['user_id'] = $user_id;
//DELETE QUERY TO SELECT RECORD TO DELETE BASED ON LOGIN INFO.
$query_delete = "DELETE FROM Caris_log_April2010 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 trigger_error('Query failed: ' .mysql_error());
if ($result_delete)
{
echo "Delete Successful"
}// end if
else
{
echo "Failed"
} //end else
?>

[webguync]

I am getting a lot closer with this. Using 

 print_r($_SESSION);

 

I can see that the Session for user_id is holding and displaying correctly on the delete.php page. My SQL query was giving me an error though, so I figured out that I needed to do this:

DELETE FROM log_April2010
USING log_April2010 INNER JOIN roster_April2010
WHERE log_April2010.user_id = roster_April2010.user_id
AND roster_April2010.user_id = 1;

 

I tested in the PHPMyAdmin SQL window and the SQL works.

 

except I get a SQL error with this in PHP. Here is the code for delete.php

<?php
ini_set("display_errors","1");
error_reporting(E_ALL);
session_start();
$con = mysql_connect("localhost","nestle","nutrition") or die('Could not connect: ' . mysql_error());
mysql_select_db("nestle_exam") or die(mysql_error());
$_SESSION['user_id']; 
$user_id=user_id; 
print_r($_SESSION);
//DELETE QUERY TO SELECT RECORD TO DELETE BASED ON LOGIN INFO.
$query_delete = "DELETE FROM log_April2010
USING log_April2010 INNER JOIN roster_April2010
WHERE log_April2010.user_id = roster_April2010.user_id
AND roster_April2010.user_id = $user_id;";
echo $query_delete; //for debugging test
$result_delete = mysql_query($query_delete) or trigger_error('Query failed: ' .mysql_error());
if ($result_delete)
{
echo "Delete Successful";
}// end if
else
{
echo "Failed";
} //end else
?>

 

again print_r shows that user_id does have a value, but the error I get with the SQL Query is:

 

Notice: Use of undefined constant user_id - assumed 'user_id' in Delete.php  on line 7

 

Notice: Query failed: Column 'user_id' in where clause is ambiguous in Delete.php on line 16

 

Failed

[ChemicalBliss]

$_SESSION['user_id']; 
$user_id=user_id;  

 

should be:

 

$user_id = $_SESSION['user_id']; 

 

-cb-

[webguync]

got it, thanks.