Make variables from SQL table

Wastedfun · May 17, 2014

Hello, to start off I am very new to this and trying to learn as I go by using sample code and putting things together as I need them. I am working on a basic database editor that will make a select able list from a sql table. In this case, "Employees". Then, after I select a name from that list it gives me the option to alter that information and save it. What I am missing however, is the option to pre-fill what the database already has while in the edit part. The drop down menu only shows the Employee name. So when I select the name, only the EmpID variable is transferred to the next part. I want all fields pre-filled out by defining variables from the SQL table.

So I cant figure out how to call the database, then the table, Find the PRIMARY KEY (which is the EmpID) and create variables from (FirstName, LastName and Pay), but only for the EmpID that was slected before hand. How can I do this? Once I can make those variables, I can use them to pre-fill the text areas..

Thank you in advance. Also if needed I will post the code I have so far.

<html>
<head>
<title>Edit Employee</title>
</head>
<body>

<?php
$dbhost = 'localhost';
$dbuser = 'Labor';
$dbpass = '***********';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

// Check connection
if(! $conn )
{
	die('Could not connect: ' . mysql_error());
}



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

	$EmpID = $_POST['EmpID'];
	$FirstName = $_POST['FirstName'];
	$LastName = $_POST['LastName'];
	$Pay = $_POST['Pay'];
	$OrgEmpID = $_POST['OrgEmpID'];
	

		$sql = "UPDATE Employees
  		      SET EmpID='$EmpID', FirstName='$FirstName', LastName='$LastName', Pay='$Pay'
  		      WHERE EmpID='$OrgEmpID'";

		mysql_select_db('Labor');
		$retval = mysql_query( $sql, $conn );
		if(! $retval )
		{
 		 die('Could not update data: ' . mysql_error());
		}
		echo "Updated data successfully\n";


}
	else
{

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

		$EmpID = $_POST['EmpID'];
		$FirstName = $_POST['FirstName'];
		$LastName = $_POST['LastName'];
		$Pay = $_POST['Pay'];
		$OrgEmpID = $_POST['EmpID'];
			
		
		
		
		echo '
		Update Employee
		<form method="post" action="'.$_PHP_SELF.'">
		<table width="400" border="0" cellspacing="1" cellpadding="2">
		<tr>
		<td width="100">Employee ID: </td>
		<td><input name="EmpID" type="text" id="EmpID" value="'.$EmpID.'"></td>
		</tr>
		<tr>
		<td width="100">First Name: </td>
		<td><input name="FirstName" type="text" id="FirstName" value="'.$FirstName.'"></td>
		</tr>
		<tr>
		<td width="100">Last Name: </td>
		<td><input name="LastName" type="text" id="LastName"  value="'.$LastName.'"></td>
		</tr>
		<tr>
		<td width="100">Hourly Pay: </td>
		<td><input name="Pay" type="text" id="Pay" value="'.$Pay.'"></td>
		</tr>
		<tr>
		<td width="100"> </td>
		<td> </td>
		</tr>
		<tr>
		<td width="100"> </td>
		<td>
		<input type="hidden" name="OrgEmpID" value="'.$OrgEmpID.'">
		<input name="save" type="submit" id="save" value="Save">
		</td>
		</tr>
		</table>
		</form>
		';
	}
		else
	{
		?>
		<form method="post" action="<?php $_PHP_SELF ?>">
		<table width="400" border="0" cellspacing="1" cellpadding="2">
		<tr>
		<td width="100">Select Employee to Edit</td>
		<td></td>
		</tr>
		<tr>
		<td width="100"> 


		<select name="EmpID" id="EmpID">
		    <option value='EmpID' disabled='disabled' selected='selected'>Please select a name</option>

		<?

		mysql_select_db('Labor');

		$result = mysql_query('SELECT EmpID, FirstName, LastName, Pay FROM Employees');
		while ($row = mysql_fetch_array($result)) 

		{
		
		echo '<option value="'.$row{"EmpID"}.'">'.$row{"FirstName"}.' '.$row{"LastName"}.'</option>';
		}

		?>

		</select>
		</td>
		<td> </td>
		</tr>
		<tr>
		<td width="100"> </td>
		<td>
		<input name="select" type="submit" id="select" value="Select">
		</td>
		</tr>
		</table>
		</form>
		<?php
	}
		

}
		?>

<br>
<a href="index.php">Home</a>
</body>
</html>

Ch0cu3r · May 17, 2014

You'd perform a SELECT query returning the record where the EmpID matches the name dropdown menu value submitted by your form. Example

if(isset($_POST['EmpID'))
{
   $EmpID = intval($_POST['EmpID']);
   $result = mysql_query('SELECT EmpID, FirstName, LastName, Pay FROM Employees WHERE EmpID = ' . $EmpID;
   list($FirstName, $LastName, $Pay) = mysql_fetch_row($result);

   // output form
   echo '<form method="post">
   Firstname: <input type="text" name="FirstName" value="'.$FirstName.'" /><br />
   Lastname: <input type="text" name="LastName" value="'.$LastName.'" /><br />
   Pay: <input type="text" name="Pay" value="'.$Pay.'" /><br />
   <input type="hidden" value="'.$EmpID.'" name="EmpID" />
   <input type="submit" name="submit" value="Update" />
   </form>';
}

Wastedfun · May 18, 2014

~~Thank you Ch0cu3r,~~

~~The line,~~

$result = mysql_query("SELECT EmpID, FirstName, LastName, Pay FROM Employees WHERE EmpID = $EmpID");
   list($FirstName, $LastName, $Pay) = mysql_fetch_row($result);

~~Resulted in:~~

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/EditEmp.php on line 55

~~I tried mysql fetch array also with no luck~~

EDIT: I added the selection for the database and it worked.

mysql_select_db('Labor');

Thanks again!

Edited May 18, 2014 by Wastedfun

ginerjm · May 18, 2014

you have four fields in your selection, but only 3 in your list(). I realize that list will work like that but it's still poor practice and should be giving you a notice error

Wastedfun · May 18, 2014

you have four fields in your selection, but only 3 in your list(). I realize that list will work like that but it's still poor practice and should be giving you a notice error

Yes, it messed up the order of my Variables. But when I noticed they were messed up, removing that was an easy fix. Thank you as well ;p

Sign In

Make variables from SQL table

Recommended Posts

Wastedfun

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

Wastedfun

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

Wastedfun

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information