Solved

PHP Code Issue

Posted on 2013-01-14
9
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

696 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