asked on

PHP Code Issue

The attached code pulls data from my MySQL database & displays it in a browser & it works great but I need it to do one more thing & I can't figure out a way to make it work & ask for your help. If you look at lines 177 - 187, this is where I need the assistance. At this time these lines of code pull the NAMES from the CLERK_NAMES table WHERE the ACTIVE field is set to 1.

Here is what I need help with...once the user clicks the "Place Assignments" button, I need for the ACTIVE field to be updated to a 0....can anyone help me with this? Thanks

<p> <b> <i>To go to the main page <a href="http://152.15.57.94/xflow/index.html">click here</a>.</b> </i></p>

<html>  
<body bgcolor=""> 
<head> 

<?php 

$page_title = 'Edit a Record';

$con = mysql_connect("localhost","uname","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("flow", $con);

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

if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { 
	$id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) {
	$id = $_POST['id'];
} else { // No valid ID, kill the script.
	echo '<h1 id="mainhead">Page Error</h1>
	<p class="error">This page has been accessed in error.</p><p><br /><br /></p>';
	include ('./includes/footer.html'); 
	exit();
}

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

	$errors = array();
	
	if (empty($_POST['fname'])) {
		$errors[] = 'You forgot to enter the offenders first name.';
	} else {
		$fn = $_POST['fname'];
	}
	
	if (empty($_POST['lname'])) {
		$errors[] = 'You forgot to enter the offenders last name.';
	} else {
		$ln = $_POST['lname'];
	}
		
		if (empty($_POST['location'])) {
		$errors[] = 'You forgot to enter a location.';
	} else {
		$loc = $_POST['location'];
	}
	
		if (empty($_POST['employee'])) {
		$errors[] = 'You forgot to enter a employee.';
	} else {
		$off = $_POST['employee'];
	}
		
			if (empty($_POST['item'])) {
		$errors[] = 'You forgot to enter a item number.';
	} else {
		$dkt = $_POST['item'];
	}
	
			if (empty($_POST['mname'])) {
		$errors[] = 'You forgot to enter a middle name. If none, please enter NONE';
	} else {
		$mn = $_POST['mname'];
	}
		
	if (empty($errors)) { 
	
	$query = "UPDATE pinfo SET fname='$fn', lname='$ln', location='$loc', employee='$off', item='$dkt', mname='$mn' WHERE fid=$id";
	$result = mysql_query($query);
	
			if (mysql_affected_rows() == 1)
	echo '<p><b><center>The assignment has been submitted successfully! </p><p>An email containing all this information has been sent to the assigned employee.</b></p></center>';
				
switch($_REQUEST['employee']) { 
case "John Doe":
$Email = "John_Doe@mydomain.com";
break;
case "Jane Doe":
$Email = "jane_doe@mydomain.com";
break;
case "Greg Johnson":
$Email = "greg_johnson@mydomain.com";
break;
default:
echo "Error, no employee selected!!!";
break; 
}

$Message = "Someone Has Been Assigned to You Below.\n \nName : ${_REQUEST['fname']} ${_REQUEST['mname']} ${_REQUEST['lname']} \nitem No : ${_REQUEST['item']} \nemployee : ${_REQUEST['employee']} \nLocation : ${_REQUEST['location']}";
$Subject  = "Assignment";
$Headers = "From: prog@mydomain.com \n"; 

 	if (mail($Email, $Subject, $Message, $Headers)) 
						
exit();

		
	} else { 
	
		echo '<h1 id="mainhead">Error!</h1>
		<p class="error">The following error(s) occurred:<br />';
		foreach ($errors as $msg) { 
			echo " - $msg<br />\n";
		}
		echo '</p><p><b>If you continue to get this error call IT.</b></p><p><br /></p>';
		
	} 

} 

$query = "SELECT fname, lname, location, item, mname FROM info WHERE fid = " . $_REQUEST['id'];
$result = @mysql_query ($query); // Run the query.

if (mysql_num_rows($result) == 1) { 

	$row = mysql_fetch_array ($result, MYSQL_NUM);
		
?>	
<body style="background-image:url(FadedBG.png); background-repeat:no-repeat; background-attachment:fixed; background-position:center;">
<?php


echo '<form action="assign_test.php" method="post">
<legend><h3> This section assigns the it to an employee & a clerk. Your person will not be notified until the employee enters the information.</h2></legend>

<table width="95%" border="1">
<tr>
<td><b>item No:</b> <br><input type="text" name="item" size="15" maxlength="30" value="'.$row[3].'" /><br> </td>
<td><b>Location: </b><br><input type="text" name="location" size="15" maxlength="30" value="'.$row[2].'" /><br></td>
</tr>

<tr>
<td><b>First Name:</b> <br><input type="text" name="fname" size="15" maxlength="30" value="'.$row[0].'" /><br /> </td>
<td><b>Middle Name:</b> <br><input type="text" name="mname" size="15" maxlength="30" value="'.$row[4].'" /><br /> </td>
<td><b>Last Name (Corporation/Partnership):</b> <br><input type="text" name="lname" size="25" maxlength="30" value="'.$row[1].'" /><br /> </td>
</tr>

<td>
<b>Assign To This employee:<br> 
<select name="employee"> 
<option value=""></option>
<option value="John Doe">John Doe</option>
<option value="Jane Doe">Jane Doe</option>
<option value="Greg Johnson">Greg Johnson</option>
</select> 
</td>

</tr>

</tr>

</table>

<br>

<input type="hidden" name="submitted" value="TRUE"  />
<input type="hidden" name="id" value="' . $id . '" />
</form>';

} else { // Not a valid user ID.
	echo '<h1 id="mainhead">Page Error</h1>
	<p class="error">This page has been accessed in error.</p><p><br /><br /></p>';
}

mysql_close(); // Close the database connection.

?>

<?php
$mysqli = new mysqli('localhost', 'uname', 'password', 'flow'); 
$sql = "SELECT names FROM clerk_names WHERE active = '1' ORDER BY RAND() LIMIT 1"; 
$res = $mysqli->query($sql); 
$row = $res->fetch_row(); 
$randomName = $row[0];  
$res->free(); 
?> 

<td><b>Automated Clerk Assignment:</b> <br><input type="text" name="cname" size="21" maxlength="40" value="<?php echo $randomName;?>" /><br /> </td>
<br>
<div align="left"><input type="submit" name="submit" value="Place Assignments" /></div>

Open in new window

Lukasz Chmielewski

This would require a page reload...

wantabe2

ASKER

Could I add something like this to the SQL on line 178?

$update=mysql_query( "SELECT clerk_id, names, active FROM clerk_names WHERE active = 1" );
mysql_query( "UPDATE clerk_names SET active = 0 WHERE clerk_id = " . $row["clerk_id"] . " " );

I've tried this code but can't it to work....can you suggest.

Lukasz Chmielewski

I'm not sure if this is what you're up to, but I think these two queries combine into one:

UPDATE clerk_names SET active = 0 WHERE clerk_id = (SELECT clerk_id FROM clerk_names WHERE active = 1)

or just set the active to 0 where active equals 1 ?
?

wantabe2

ASKER

Thanks & sorry if I'm not being clear....the code below is where I'm having the issue...the code below pulls data from my DB & displays it a random name in my form. I have 3 fields in my DB; clerk_id, names, and active. Each name is set to a 1 in the active field & once the code below runs & displays the name in the form I'd like for the active field to be automatically set to a 0 for the field that is chosen. By doing this, there's no chance the name will appear again because it is pulling only names with the active set to a 1. Once it is set to a 0 it won't show at random anymore.

<?php
$mysqli = new mysqli('localhost', 'uname', 'pword', 'flow'); 
$sql = "SELECT names, active, clerk_id FROM clerk_names WHERE active = '1' ORDER BY RAND() LIMIT 1";
$res = $mysqli->query($sql); 
$row = $res->fetch_row(); 
$randomName = $row[0];  
$res->free(); 
?>

Open in new window

wantabe2

ASKER

Attached is a snapshot of my DB...
clerks.JPG

ASKER CERTIFIED SOLUTION

Lukasz Chmielewski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wantabe2

ASKER