Solved

PHP Code Issue

Posted on 2013-01-14
9
242 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…

760 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

22 Experts available now in Live!

Get 1:1 Help Now