Solved

PHP Code Issue

Posted on 2013-01-14
9
248 Views
Last Modified: 2013-01-14
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

0
Comment
Question by:wantabe2
  • 5
  • 4
9 Comments
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38775363
This would require a page reload...
0
 
LVL 15

Author Comment

by:wantabe2
ID: 38775435
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.
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38775492
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 ?
?
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 15

Author Comment

by:wantabe2
ID: 38775528
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

0
 
LVL 15

Author Comment

by:wantabe2
ID: 38775539
Attached is a snapshot of my DB...
clerks.JPG
0
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 500 total points
ID: 38775576
OK, try below

<?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(); 

$sql = "UPDATE clerk_names SET active = 0 WHERE clerk_id = " . $row[2] . " " );
$res = $mysqli->query($sql); 
$res->free(); 
?> 

Open in new window

0
 
LVL 15

Author Comment

by:wantabe2
ID: 38775596
You rock! That was it & I thank you VERY VERY VERY much!!!
0
 
LVL 15

Author Closing Comment

by:wantabe2
ID: 38775601
You rock! That was it & I thank you VERY VERY VERY much!!!
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 38775608
No problem, cheers !
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ROBOFTP UNZIP 1 37
insert into database through form with dynamic fields. 2 28
MS Access - need to reduce row size 25 51
paypal ipn url 5 52
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
The viewer will learn how to count occurrences of each item in an array.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now