Hello there,

My intentions are to enter the employee id in the search page, and have the results displayed below the area to enter the search criteria with the select box and update button( i only want to update 1 field) here are the codes.....

My problem is whenever i enter the employee id in the search box, apart from the results being displayed, the die("Update not successful"); message is also displayed, therefore my update doesn't work...

please help me, i am not sure what else to do (due to my lack of php knowledge)...

:( :S :sad:

SearchUser.php

<!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"><!-- InstanceBegin template="/Templates/TAISadministratorPortal.dwt" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>TAIS Administrator's Portal</title>
<!-- InstanceEndEditable -->
<!-- InstanceBeginEditable name="head" -->
<style type="text/css">
<!--
.style28 {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 18px;
	font-weight: bold;
	color: #006600;
}
.style29 {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 14px;
	font-weight: bold;
}
-->
</style>
<!-- InstanceEndEditable -->
<style type="text/css">
<!--
.style27 {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 10px;
	font-weight: bold;
}
body {
	background-color: #CCFFCC;
}
-->
</style>
</head>

<body onload="goforit()">
<table width="200" border="0" align="center">
  <tr>
    <th colspan="6" scope="col"><img src="images/apBanner2.png" width="962" height="225" /></th>
  </tr>
  <tr>
    <td width="155"><a href="apHome.php"><img src="images/btnHome.png" border="0" /></a></td>
    <td width="155"><a href="randomPassword.php"><img src="images/btnResetPassword.png" border="0" /></a></td>
    <td width="155"><a href="searchUser.php"><img src="images/btnAccessLevel.png" border="0" /></a></td>
    <td width="155"><img src="images/btnDeleteUser.png" /></td>
    <td width="327" colspan="2">
		<div align="center">
		  <script>
			/*
			Live Date Script- 
			© Dynamic Drive ([url]www.dynamicdrive.com[/url])
			For full source code, installation instructions, 100's more DHTML scripts, and Terms Of Use,
			visit [url]http://www.dynamicdrive.com[/url]
			*/
			
			var dayarray=new Array("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
			var montharray=new Array("January","February","March","April","May","June","July","August","September","October","November","December")
			
			function getthedate()
			{
				var mydate=new Date()
				var year=mydate.getYear()
				if (year < 1000)
					year+=1900
				var day=mydate.getDay()
				var month=mydate.getMonth()
				var daym=mydate.getDate()
				if (daym<10)
					daym="0"+daym
				var hours=mydate.getHours()
				var minutes=mydate.getMinutes()
				var seconds=mydate.getSeconds()
				var dn="AM"
				if (hours>=12)
					dn="PM"
				if (hours>12)
				{
					hours=hours-12
				}
				if (hours==0)
					hours=12
				if (minutes<=9)
					minutes="0"+minutes
				if (seconds<=9)
					seconds="0"+seconds
				//change font size here
				var cdate="<small><font color='006600' face='Verdana'><b>"+dayarray[day]+" "+montharray[month]+" "+daym+", "+year+"    "+hours+":"+minutes+":"+seconds+" "+dn
				+"</b></font></small>"
				if (document.all)
				document.all.clock.innerHTML=cdate
				else if (document.getElementById)
				document.getElementById("clock").innerHTML=cdate
				else
				document.write(cdate)
			}
			if (!document.all&&!document.getElementById)
				getthedate()
			function goforit()
			{
				if (document.all||document.getElementById)
					setInterval("getthedate()",1000)
			}			
			</script>
		  <span id="clock"></span>
        </div></td>
  </tr>
  <tr>
    <td colspan="6">&nbsp;</td>
  </tr>
  <!-- InstanceBeginEditable name="body" -->
  <?php
	include('validateSearch.php');
  ?>
  <tr>
    <td colspan="6">
		  	<form id="form" name="form" method="post" action="">
				<table>					
				  <tr>
					  <td>
					      <p align="left"><span class="style4 style25 style28">Grant Access Level to  User</span></p>
					      <p align="left"><span class="style21 style29">Enter User's Employee ID into text box, and submit. </span></p>
					  </td>
				  </tr>
   				  <tr><td><p>&nbsp;</p></td></tr>
				  <tr>
				  	<td>
				      <table width="950">
						<tr>
						  <td><p>&nbsp;</p></td>
						  <td><p>&nbsp;</p></td>
						  <td width="125" scope="row"><div align="left" class="style13 style29">Employee ID</div></td>
						  <td width="356"><div align="left">
							<input name="empNo" type="text" id="empNo" size="10" maxlength="6" />
						  </div>
						  </td>
						  <td><p>&nbsp;</p></td>
						  <td><p>&nbsp;</p></td>
						</tr>
						<tr>
						  <td><p>&nbsp;</p></td>
						  <td><p>&nbsp;</p></td>
						  <td><p>&nbsp;</p></td>
						  <td><div align="left">
							<input name="btnSubmit" type="submit" id="btnSubmit" value="Submit" />
						  </div>
						  </td>
						  <td><p>&nbsp;</p></td>
						  <td><p>&nbsp;</p></td>
						</tr>
						<tr>
						  <td colspan="6"><p>&nbsp;</p></td>
						</tr>						
					  </table>			
					<? validateData(); ?>
				  </td>
				</tr>

			</table>
		</form>		
	</td>
  </tr>
  <!-- InstanceEndEditable -->
  <tr>
    <td colspan="6" bgcolor="#339900">&nbsp;</td>
  </tr>
  <tr>
    <td colspan="6"><div align="center"><span class="style27">&copy;ITT Department, Petrotrin.</span></div></td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>

validateSearch.php

<?php

include("./connect.php");

function confirmData($empNo)
{
   global $conn;
   $q = "SELECT EmployeeID, FirstName, LastName, Initials, username, accessLevel FROM Operators WHERE EmployeeID =".$empNo;
   $result = mysql_query($q,$conn);
   $myrow=mysql_fetch_row($result);
   if(!$result || (mysql_numrows($result) < 1))
   {
      return 1; //Indicates empNo failure
   }
   else  
   {
	   echo"<tr><td>";
	   print("<center>
	   
	   		  <table border='1' width='900'>
				<tr align='center' bgcolor='#339900'>
					<td><font face='verdana' size='2' color='white'><b>Employee ID</b></font></td>
					<td colspan='2'><font face='verdana' size='2' color='white'><b>Name</b></font></td>
					<td><font face='verdana' size='2' color='white'><b>Initials</b></font></td>
					<td><font face='verdana' size='2' color='white'><b>Username</b></font></td>
					<td><font face='verdana' size='2' color='white'><b>Access Level</b></font></td>
					<td colspan='2'><font face='verdana' size='2' color='yellow'><b>Select Access to be Granted and Update-Option 1</b></font></td>
					<td colspan='2'><font face='verdana' size='2' color='white'><b>Testing</b></font></td>
				</tr>");
		print("<tr>");
		foreach($myrow as $key => $value)
		print("<td><font face='verdana' size='2'>$value</font></td>");
		print("<form id='formTrial' name='formTrial' method='post' action=''>");		
			
		print("<td>
				<select id='access' name=accessType width='15'>
					<option value=".$myrow[5].">Choose Access Level</option>
					<option value='NULL'>NULL</option>
					<option value='User'>User</option>
					<option value='Admin'>Admin</option>
					<option value='SysAdmin'>SysAdmin</option>
				</select>
			   </td>");
		$accessType=$_POST['access'];
		 
		print("<td><input id='upDate' name='upDate' type='submit' value='Update' /></td></form>");
		$upDate = $_POST['upDate'];				
		print("<td><font face='verdana' size='2'><center><a href=\'update.php?EmployeeID='.$myrow[0].' />Update</a></center></font></td>");
		print("<td bgcolor='red'>");	
			$empNum = $myrow[0];
			echo $empNum;
		print("</td>");				
											
		print("</tr></table></center>");
		//print_r($_POST);	
		if($upDate=TRUE)
		{
			$q2 = "UPDATE Operators SET accessLevel=".$accessType." WHERE EmployeeID =".$myrow[0];
			$result2 = mysql_query($q2, $conn) or die("Update not successful");		
		}
	}  	
}
function validateData()
{
	global $conn;
	
	
	$empNo = $_POST['empNo']; // the value of empNo is take in a variable

	$btnSubmit = $_POST['btnSubmit']; 	
	if(isset($btnSubmit))
	{
		if(!$empNo)
		{
			echo '<center>				
			<font face="verdana" size="2" color="red"><b>Employee ID field is empty!</b></font></center>
			</td>
		</tr>
	</table>
</form>		
</td>
</tr>			
<tr>
	<td colspan="6" bgcolor="#339900">&nbsp;</td>
</tr>
<tr>
	<td colspan="6"><div align="center"><span class="style27">&copy;ITT Department, Petrotrin.</span></div></td>
</tr>
</table>
</body>';
			die();
		}
						
   		$mainResult = confirmData($empNo);
		/* Check error codes */
		if($mainResult == 1)
		{
   			echo '<center>				
			<font face="verdana" size="2" color="red"><b>The Employee ID <font color="black">"'.$empNo.'"</font> does not exist in the database.</b></font></center>
			</td>
		</tr>
	</table>
</form>		
</td>
</tr>			
<tr>
	<td colspan="6" bgcolor="#339900">&nbsp;</td>
</tr>
<tr>
	<td colspan="6"><div align="center"><span class="style27">&copy;ITT Department, Petrotrin.</span></div></td>
</tr>
</table>
</body>';				
			die();
		}		
     }
}
?>

Print the update query and see if it executes in phpmyadmin/mysql console. What do you get ? A result or an error ? You can also try $result = mysql_query($query,$conn) or die (mysql_error()); This will also give the error message about why the query failed![code=php]
$result = mysql_query($query,$conn) or die (mysql_error()); This will also give the error message about why the query failed!

I did try the query in the phpmyadmin/mysql console with static variables though, and it executed properly, it updated correctly, but I will now try the mysql_error() in the die...

so I tried the

die (mysql_error())

and this is the error message i got:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE EmployeeID =114301' at line 1

So, that's the problem..There is a syntax error somewhere.. Now, print the query and tell us what it says.. And also, execute it with dynamic variables..

Where do I print the query with the dynamic variables? The place where I entered the query with the static variables before was in the MySQL Query Browser...

For example, you have this query.

$q2 = "UPDATE Operators SET accessLevel=".$accessType." WHERE EmployeeID =".$myrow[0];

You just have to echo it.

echo $q2;

Then you will have the query with dynamic variables.

Ok,

I echoed $q2, and this is what I see:
UPDATE Operators SET accessLevel= WHERE EmployeeID =114301
it seems as though it's not seeing anything for $accessType

Yes. Tht might be because of this.

<select id='access' name=accessType width='15'>
$accessType=$_POST;

It should have been $_POST and you are trying with $_POST, which will be empty.

OK, I changed it to

$_POST['accessType']

but the die message still shows...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE EmployeeID =114301' at line 1

Oh, you can try one more thing.. wrap your values within single quotes. ie.,

$q2 = "UPDATE Operators SET accessLevel='".$accessType."' WHERE EmployeeID =".$myrow[0];

This will not give you any syntax error ( I am sure!).

Once you are done with it, check why $accessType isn't holding any values.

What I have noticed is that whenever i press the 'submit' button from the searchUser.php page, I get the results in the table as well as the die message...even though I had an 'if' statement with respect to the 'update' button...

Okay.. So, it inserts a record to the table ? I don't understand the flow of your script, so I can't say much. But why do you have die(); at most places ? :-/

ok, well...I have the searchUser.php script which displays the input box and submit button...just under the submit button, there is a call for the validateData function in the validateUser.php script, when the submit button is pressed, the validateData function checks the input box (validation code) and in the midst of the validateData function, the confirmData function is called; that is where I put the results to show provided the data was confirmed...it is within these results that I want the form for the update, i.e. alongside the results, there is a select box with the update button, so that when I choose from my options and press the update button, it updates the particular field in the table in the database with the value of the selection...

Yes, it did update the data in the table, setting it to a blank (which means the update works)...but like you said...$accessType doesn't hold any values...but I do not understand why that's so, did I code it correctly?

print("<td>
				<select id='access' name='accessType' width='15'>
					<option value='.$myrow[5].'>Choose Access Level</option>
					<option value='NULL'>NULL</option>
					<option value='User'>User</option>
					<option value='Admin'>Admin</option>
					<option value='SysAdmin'>SysAdmin</option>
				</select>
			   </td>");
		$accessType=$_POST['accessType'];

I put die(); in many places for testing purposes really...

Okay! here is why it doesn't work. First of all, you are calling the function validatedata() in searchuser.php. This function will call the function confirmdata passing empno as a parameter. You fetch the record for that particular empno and display it along with some elements like select box. And,

$accessType=$_POST;

print("<td><input id='upDate' name='upDate' type='submit' value='Update' /></td></form>");
$upDate = $_POST;
print("<td><font face='verdana' size='2'><center><a href=\'update.php?EmployeeID='.$myrow[0].' />Update</a></center></font></td>");
print("<td bgcolor='red'>");
$empNum = $myrow[0];
echo $empNum;
print("</td>");

print("</tr></table></center>");
//print_r($_POST);
if($upDate=TRUE)
{
$q2 = "UPDATE Operators SET accessLevel=".$accessType." WHERE EmployeeID =".$myrow[0];
$result2 = mysql_query($q2, $conn) or die("Update not successful");
}

This part will not work because there is no function call after the page is submitted. Why not put this part somewhere else in the script ? I haven't tested this, but it should be something like this.

<?php

include("./connect.php");


if($_POST['upDate']) {
$accessType=$_POST['accesstype'];
$empnumber = $_POST['empnumber'];
 	$q2 = "UPDATE Operators SET accessLevel=".$accessType." WHERE EmployeeID =".$empnumber;
	$result2 = mysql_query($q2, $conn) or die("Update not successful");		
}


function confirmData($empNo)
{
   global $conn;
   $q = "SELECT EmployeeID, FirstName, LastName, Initials, username, accessLevel FROM Operators WHERE EmployeeID =".$empNo;
   $result = mysql_query($q,$conn);
   $myrow=mysql_fetch_row($result);
   if($result)
   {
      return 1; //Indicates empNo failure
   }
   else  
   {
	   echo"<tr><td>";
	   print("<center>
	   
	   		  <table border='1' width='900'>
				<tr align='center' bgcolor='#339900'>
					<td><font face='verdana' size='2' color='white'><b>Employee ID</b></font></td>
					<td colspan='2'><font face='verdana' size='2' color='white'><b>Name</b></font></td>
					<td><font face='verdana' size='2' color='white'><b>Initials</b></font></td>
					<td><font face='verdana' size='2' color='white'><b>Username</b></font></td>
					<td><font face='verdana' size='2' color='white'><b>Access Level</b></font></td>
					<td colspan='2'><font face='verdana' size='2' color='yellow'><b>Select Access to be Granted and Update-Option 1</b></font></td>
					<td colspan='2'><font face='verdana' size='2' color='white'><b>Testing</b></font></td>
				</tr>");
		print("<tr>");
		foreach($myrow as $key => $value)
		print("<td><font face='verdana' size='2'>$value</font></td>");
		print("<form id='formTrial' name='formTrial' method='post' action=''>");		
			
		print("<td>
				<select id='access' name=accessType width='15'>
					<option value=".$myrow[5].">Choose Access Level</option>
					<option value='NULL'>NULL</option>
					<option value='User'>User</option>
					<option value='Admin'>Admin</option>
					<option value='SysAdmin'>SysAdmin</option>
				</select>
			   </td>");

	}
	print "<input type='hidden' name='empnumber' value='".$myrow[0]."'>";
	print("<td><input id='upDate' name='upDate' type='submit' value='Update' /></td></form>");  	
	print("<td><font face='verdana' size='2'><center><a href=\'update.php?EmployeeID='.$myrow[0].' />Update</a></center></font></td>");
print("<td bgcolor='red'>");	
print("</td>");				
									
print("</tr></table></center>");

}
function validateData()
{
	global $conn;
	
	
	$empNo = $_POST['empNo']; // the value of empNo is take in a variable

	$btnSubmit = $_POST['btnSubmit']; 
	if(isset($btnSubmit))
	{
		if(!$empNo)
		{
			echo '<center>				
			<font face="verdana" size="2" color="red"><b>Employee ID field is empty!</b></font></center>
			</td>
		</tr>
	</table>
</form>		
</td>
</tr>			
<tr>
	<td colspan="6" bgcolor="#339900">&nbsp;</td>
</tr>
<tr>
	<td colspan="6"><div align="center"><span class="style27">&copy;ITT Department, Petrotrin.</span></div></td>
</tr>
</table>
</body>';
			die();
		}
						
   		$mainResult = confirmData($empNo);
		/* Check error codes */
		if($mainResult == 1)
		{
   			echo '<center>				
			<font face="verdana" size="2" color="red"><b>The Employee ID <font color="black">"'.$empNo.'"</font> does not exist in the database.</b></font></center>
			</td>
		</tr>
	</table>
</form>		
</td>
</tr>			
<tr>
	<td colspan="6" bgcolor="#339900">&nbsp;</td>
</tr>
<tr>
	<td colspan="6"><div align="center"><span class="style27">&copy;ITT Department, Petrotrin.</span></div></td>
</tr>
</table>
</body>';				
			die();
		}		
     }
}
?>

Btw, use mysql_real_escape_string to sanitize user input values.

Hi,

I made the changes (I even tried the mysql_real_escape_string) but when I tried to load the page to begin the search, nothing shows...

if($_POST['upDate']) 
{
	$accessType=$_POST['accessType'];
	$empnumber = $_POST['empnumber']; 	
	$q2 = sprintf("UPDATE Operators SET accessLevel=".$accessType." WHERE EmployeeID =".$empnumber), mysql_real_escape_string($accessType, $conn), mysql_real_escape_string($empnumber, $conn);
	$result2 = mysql_query($q2, $conn) or die("Update not successful");	
}

when I took out the mysql_real_escape_string code, then everything shows perfectly, but the update doesn't work...

if($_POST['upDate']) 
{
	$accessType=$_POST['accessType'];
	$empnumber = $_POST['empnumber']; 	
	$q2 = "UPDATE Operators SET accessLevel=".$accessType." WHERE EmployeeID =".$empnumber);
	$result2 = mysql_query($q2, $conn) or die("Update not successful");	
}

Hmm.. Print out the query again.. And also, putting print_r($_POST); (on top of the page) helps alot while debugging.

This is my code with the print_r($_POST); as well as the print out of the query...

print_r($_POST);
if($_POST['upDate']) 
{
	$accessType=$_POST['accessType'];
	$empnumber = $_POST['empnumber']; 	
	$q2 = "UPDATE Operators SET accessLevel=".$accessType." WHERE EmployeeID =".$empnumber;
	echo $q2;
	$result2 = mysql_query($q2, $conn) or die("Update not successful");	
}

When I entered the EmployeeID and clicked search, this is the message I'm seeing...

Array ( [empNo] => 114301 [btnSubmit] => Submit )


Then when I entered my selection on the select box, this is the message....

Array ( [empNo] => [accessType] => User [empnumber] => 114301 [upDate] => Update ) UPDATE Operators SET accessLevel=User WHERE EmployeeID =114301Update not successful

You missed out ''. Use this query instead.

$q2 = "UPDATE Operators SET accessLevel='".$accessType."' WHERE EmployeeID =".$empnumber;
Comments
This guy definitely knows his stuff...
This question has already been answered. Start a new discussion instead.