PDA

View Full Version : How would you capture which user has done what to the process?


smitho
02-07-2008, 02:29 AM
I've got a screen that allows the user to checkbox 3 options that may have occurred to a file.

1. processed
2. accepted
3. rejected

To just enable them to tick a option and update the date base is straight forward. There is a possibility that different users may do/check different stages of the process. So if user1 checks off processed and later user2 checks off accepted how do I capture this. What I've put together will capture the user at the time but what is happening is that if user1 processed the file and then user2 accepted it user1's details are overwritten but user2.


<?php
//This is what I'm using to update the date base.

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
$updateSQL = sprintf("UPDATE $site SET proccessed=%s, proccessedby=%s, accepted=%s, acceptedby=%s, rejected=%s, rejectedby=%s WHERE id=%s",
GetSQLValueString(isset($_POST['proccessed']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString($_POST['proccessedby'], "text"),
GetSQLValueString(isset($_POST['accepted']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString($_POST['acceptedby'], "text"),
GetSQLValueString(isset($_POST['rejected']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString($_POST['rejectedby'], "text"),
GetSQLValueString($_POST['id'], "int"));

//Then I capture the name if its there to display it.

$processedby=$row_details['proccessedby'];
$acceptedby=$row_details['acceptedby'];
$rejectedby=$row_details['rejectedby'];
$approver=$_SESSION['Operator'];

//I used the code for the checkbox to capture the users name but not really working as the cheackbox need to the checked to capture the name.

<td><input type="checkbox" name="proccessed" value=""
<?php if (!(strcmp(htmlentities($row_details['proccessed'], ENT_COMPAT, 'UTF-8'),'Y')))
{echo "checked=\"checked\"";} ?> />
</td>
<td><?php echo $processedby;?><input type="hidden" name="proccessedby" id="proccessedby"
value="<?php if(!(strcmp(htmlentities($row_details['proccessed'], ENT_COMPAT, 'UTF-8'),'Y')))
{echo $approver;}?>" /></td>

Firstly I'd need to capture the name as the checkbox is ticked.
Where I'm having trouble is working out where/how would I check if a checkbox is ticked so that information is not changed?

davidj
02-07-2008, 05:56 AM
you need to treat each user as a separate entity

you need a db row dedicated to each user and their actions

you need to assign all actions to the users ID

//////////////

also i see your using dreamweaver to write your code? You are not hand coding. Why?

smitho
02-07-2008, 08:55 AM
Thanks davidj,

You're correct I've been using dreamweaver to do some of the code and the more I do the less I'm using dreamweaver's code. Thanks to your tutorials I've been getting more confident with writting the code myself. Using dreamweaver has helped by pulling apart the code and seeing what it's doing but man there's a lot of code it puts out when I don't think you need it. Take your dropmenu tut dreamweaver would produce about 3 to 4 time the same amount of code to do the same thing. Trying to understand it becomes difficult. Anyway just wanted to say thanks to the time you've taken to put together some great tutorials. Have to keep remembering watch them again as you pick up new things once you learn more.

In your responce I have the first 2 items covered. When you say " you need to assign all actions to the users ID" I've got the user details in a session is this a problem?. So while they are viewing the page to process, accept and reject the file I can tell who it is. I'm thinking I would need to use somesort of an if() stament to check if any of the options have a user assigned or have been checked and not change them. I was also thinking it may be good to keep a text file going with all the actions taken on the file. Not sure if this is a good or bad idea. I find check boxes very difficult compared to text fields.

Cheers.

davidj
02-07-2008, 09:20 AM
lets look at this more closely

you have a USER table which stores your users. Each user has an ID to identify them amd make each one unique. (This could be driven by an auto incrementing key in the table)

next we have a table of transactions. Things that the user has done to a file.

when a user is created in the USER table it also creates an entry for that user in the transaction table by inserting the users ID. Now we have a user in the USER table and a corresponding relationship ID in the transactions table.

ok .... is this making sense?

right now say a user performs an action on the system. The user logged in and you stored their ID in a session which they carry around from page to page. When you want to record an action they performed then just do an update on the transaction table using the ID in the WHERE clause. That row in the transactions table will just be relevant to that user as the ID is the unique identifyer

you can store any action you want to record in that transaction table.

//////

please note that i cant supply any coding examples as long as you use dreamweaver code to build this.

My help is limited as long as you code in this way

drop the shite and we will build this together

smitho
02-11-2008, 11:46 PM
Here is an example of what I'd like to be able to do.

Using a couple of the tutorials davidj has put together this is what I've got (see code below).

The user logs into the system (using login tut) then I've made a slight change to the car_make table adding service (Y/N), serviceby, testdrive (Y/N), testdriveby, cleaned (Y/N), cleanedby. I've got a dynamic table to show the cars and if they have been serviced, test driven, and cleaned. The user can select a car and update the record which will also capture the id of the user that is ticking off the option.

I can log in as dj select Alfa tick service and submit. The record is updated as serviced and by userid 1. Now if I log in as js01 and select Alfa and tick testdrive and submit it not only updates testdrive it also updates service and userid 2 which is js01.

I understand what you say about creating a transaction record/table but I can see how I would show which task had been ticked off by each user.


<?php
session_start();
require_once("Connections/dwc.php");//database connection
include("includes/security.php");

$id = $_SESSION['id'];
@$service=$_POST['service'];
@$testdrive=$_POST['testdrive'];
@$cleaned=$_POST['cleaned'];
@$submit=$_POST['submit'];
@$recordid=$_GET['recordid'];


//////////////////////////////////////////////////////
$query = sprintf("SELECT * FROM users WHERE user_id='$id'");
$result = @mysql_query($query);
$rowAccount = @mysql_fetch_array($result);
//////////////////////////////////////////////////////

$query2 = sprintf("SELECT * FROM car_make");
$result2 = mysql_query($query2);
$rowmake = mysql_fetch_array($result2);
$totalrowsmake = mysql_num_rows($result2);

$query3 = sprintf("SELECT * FROM car_make WHERE make_id=$recordid");
$result3 = mysql_query($query3);
$rowmake2 = @mysql_fetch_array($result3);
$totalrowsmake2 = @mysql_num_rows($result3);

if($submit && $service){
$query = sprintf("UPDATE car_make SET service='$service', serviceby='$id' WHERE make_id='$recordid'");
mysql_query($query)or die(mysql_error);
header("Location: welcome.php?recordid=$recordid");
}
if($submit && $testdrive){
$query = sprintf("UPDATE car_make SET testdrive='$testdrive', testdriveby='$id' WHERE make_id='$recordid'");
mysql_query($query)or die(mysql_error);
header("Location: welcome.php?recordid=$recordid");
}
if($submit && $cleaned){
$query = sprintf("UPDATE car_make SET cleaned='$cleaned', cleanedby='$id' WHERE make_id='$recordid'");
mysql_query($query)or die(mysql_error);
header("Location: welcome.php?recordid=$recordid");
}



?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<p>Welcome to the application <?php echo $rowAccount['user_name'];?></p>
<table id="table2" width="0" border="0">
<tr>
<td width="96"><div align="center">CAR MODEL</div></td>
<td width="48"><div align="center">SERVICE</div></td>
<td width="35"><div align="center">TESTDRIVE</div></td>
<td width="46"><div align="center">CLEANED</div></td>
</tr>
<?php do { ?>
<tr>
<td><div align="center"><a href="welcome.php?recordid=<?php echo $rowmake['make_id']; ?>"><?php echo $rowmake['make']; ?></a></div></td>
<td><div align="center">
<input type="checkbox" name="service" id="service" value=""<?php if (!(strcmp($rowmake['service'],'Y'))) {echo "checked";} ?> />
</div></td>
<td><div align="center">
<input type="checkbox" name="testdrive" id="testdrive" value=""<?php if (!(strcmp($rowmake['testdrive'],'Y'))) {echo "checked";} ?>/>
</div></td>
<td><div align="center">
<input type="checkbox" name="cleaned" id="cleaned" value=""<?php if (!(strcmp($rowmake['cleaned'],'Y'))) {echo "checked";} ?>/>
</div></td>
</tr>
<?php }while ($rowmake = mysql_fetch_array($result2)); ?>
</table>

<p>&nbsp;</p>
<form id="form1" name="form1" method="post" action="<?php $_SERVER['PHP_SELF'];?>">
<table width="0" border="1">
<tr>
<td>id</td>
<td><?php echo $rowmake2['make_id']; ?></td>
<td>User Name</td>
</tr>
<tr>
<td>Make</td>
<td><?php echo $rowmake2['make']; ?></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>SERVICE</td>
<td><input <?php if(!(strcmp($rowmake2['service'],'Y'))){echo "\"checked\"";}?> type="checkbox" name="service" id="service" value="Y" /></td>
<td><?php echo $rowmake2['serviceby']; ?></td>
</tr>
<tr>
<td>TESTDRIVE</td>
<td><input <?php if(!(strcmp($rowmake2['testdrive'],'Y'))){echo "\"checked\"";}?> type="checkbox" name="testdrive" id="testdrive" value="Y" /></td>
<td><?php echo $rowmake2['testdriveby']; ?></td>
</tr>
<tr>
<td>CLEANED</td>
<td><input <?php if(!(strcmp($rowmake2['cleaned'],'Y'))){echo "\"checked\"";}?> type="checkbox" name="cleaned" id="cleaned" value="Y" /></td>
<td><?php echo $rowmake2['cleanedby']; ?></td>
</tr>
<tr>
<td><input type="submit" name="Submit" value="Submit" />
<input name="submit" type="hidden" id="submit" value="1" /></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>
</form>
<p>&nbsp;</p>
<p>
<input type="button" name="button" id="LogOff" value="LogOff" onclick="document.location.href='logoff.php'" />
</p>
</body>
</html>


SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `dwc`
--

-- --------------------------------------------------------

--
-- Table structure for table `car_make`
--

CREATE TABLE IF NOT EXISTS `car_make` (
`make_id` int(11) NOT NULL auto_increment,
`make` varchar(20) default NULL,
`service` varchar(1) NOT NULL default 'N',
`serviceby` varchar(20) default NULL,
`testdrive` varchar(1) NOT NULL default 'N',
`testdriveby` varchar(20) default NULL,
`cleaned` varchar(1) NOT NULL default 'N',
`cleanedby` varchar(20) default NULL,
PRIMARY KEY (`make_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `car_make`
--

INSERT INTO `car_make` (`make_id`, `make`, `service`, `serviceby`, `testdrive`, `testdriveby`, `cleaned`, `cleanedby`) VALUES
(1, 'Alfa', 'N', NULL, 'N', NULL, 'N', NULL),
(2, 'Audi', 'N', NULL, 'N', NULL, 'N', NULL),
(3, 'BMW', 'N', NULL, 'N', NULL, 'N', NULL),
(4, 'Citroen', 'N', NULL, 'N', NULL, 'N', NULL);

smitho
02-17-2008, 10:49 AM
Just can't seem to get my head around this.

I set a variable for the service checkbox.

@$service=$_POST['service'];I've got 2 querys in this example one to create the dynamic table and this one which is used when the user clicks on the id so that only that data is displayed.


//////////////////////////////////////////////////////
$query3 = sprintf("SELECT * FROM car_make WHERE make_id=$recordid");
$result3 = mysql_query($query3);
$rowmake2 = @mysql_fetch_array($result3);

//////////////////////////////////////////////////////
i'm also doing a check that if the checkbox is ticked insert 'Y' into the database.


if($submit && $service){
$query = sprintf("UPDATE car_make SET service='$service', serviceby='$id' WHERE make_id='$recordid'");
mysql_query($query)or die(mysql_error);
header("Location: welcome.php?recordid=$recordid");
}
this the code for the checkbox input


<td><input name="service" type="checkbox" id="service" value="Y" <?php if (!(strcmp($rowmake2['service'],'Y'))) {echo "checked";} ?> /></td>
Now because I have value='Y' the check box can not be unckecked once the user has ticked it and submited the result. If I take the value away the checkbox will not update the database.

I need to be able to check and uncheck the checkbox and reflect that in the database. Am I going about this the wrong way?

smitho
02-17-2008, 11:27 AM
Getting closer I think.

If I use


if (isset($_POST['service'])) {
$service = $_POST['service'];
}else{
$service = 'N';
}Before if($submit && $service){ then I can check and uncheck the boxes. Now I just need to make it only apply the User Name (id) to the one you check or uncheck at the time, Currently if I check service the user id is apply to all the other options.

davidj
02-17-2008, 03:17 PM
you still after checking and unchecking the boxes depending on whats set in the db

smitho
02-17-2008, 10:17 PM
Finally worked this out.

As davidj mentioned and i knew that was my problem I had to check the database then compare the database to the form result. Once I did that it was easy. This will know check or uncheck the option and apply the user id so you know how did what.



if (isset($_POST['Submit'])) {

if ($rowmake2['service']!=$_POST['servicech']){
$query = sprintf("UPDATE car_make SET service='$service', serviceby='$id' WHERE make_id='$recordid'");
mysql_query($query)or die(mysql_error);
header("Location: welcome.php?recordid=$recordid");
}
if ($rowmake2['testdrive']!=$_POST['testdrivech']){
$query = sprintf("UPDATE car_make SET testdrive='$testdrive', testdriveby='$id' WHERE make_id='$recordid'");
mysql_query($query)or die(mysql_error);
header("Location: welcome.php?recordid=$recordid");
}
if ($rowmake2['cleaned']!=$_POST['cleanedch']){
$query = sprintf("UPDATE car_make SET cleaned='$cleaned', cleanedby='$id' WHERE make_id='$recordid'");
mysql_query($query)or die(mysql_error);
header("Location: welcome.php?recordid=$recordid");
}
}