Link to home
Start Free TrialLog in
Avatar of maximus81
maximus81Flag for United States of America

asked on

Update mysql data using PHP

I am having some trouble figuring out how to update some mysql code using the form I created below. How it works is I have a search that uses a $GET to pull the sql data but not sure what i need to do to after if($_SERVER['REQUEST_METHOD'] == "POST") so when i press the button it updates all the fields and displays the current information that's already in each text box. If anyone could help i would appreciate it. Thanks
<!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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<!-- TemplateBeginEditable name="doctitle" -->
<title></title>
</head>

<body>


<?php
$firstname = $_GET["fname"];
$lastname = $_GET["lname"];
$fullname = $firstname ." ". $lastname;
?>
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
      <tr>
        <td height="100" background="">
			<img src="" width="158" height="100" border="0">
		</td>
      </tr>
</table>
<table>
<tr>
<td valign="top">
	<link rel="stylesheet" type="text/css" href="sdmenu/sdmenu.css" />
	<script type="text/javascript" src="sdmenu/sdmenu.js">
		/***********************************************
		* Slashdot Menu script- By DimX
		* Submitted to Dynamic Drive DHTML code library: http://www.dynamicdrive.com
		* Visit Dynamic Drive at http://www.dynamicdrive.com/ for full source code
		***********************************************/
	</script>
	<script type="text/javascript">
	// <![CDATA[
	var myMenu;
	window.onload = function() {
		myMenu = new SDMenu("my_menu");
		myMenu.init();
	};
	// ]]>
	</script>
    
     <div style="float: left" id="my_menu" class="sdmenu">
           <div>
      <span>User Options</span>
<a href="atsmanage.php?fname=<?php echo $firstname; ?>&lname=<?php echo $lastname ?>&view=terminate">Terminate User</a>
</div>
      <div>
        <span></span>
<a href="atsmanage.php?view=info">Home</a>
<a href="adduser.php">Add User</a>
      </div>
      <div>
        <span>Admin</span>
<a href="addsup.php">Add Supervisor</a>
<a href="adddep.php">Add Department</a>
      </div>
            <div>
        <span>Reporting</span>
<a href="termreport.php">Terminated Users</a>
      </div>
    </div>


   <?php 
include ("search.php");

?>

<td width="1700" valign="top">
<?php


	$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db('ats');


$query = mysql_query("SELECT * FROM atsmain WHERE firstname = '".$firstname."' and lastname = '".$lastname."'") or die (mysql_error());

$row = mysql_fetch_assoc($query);

  $name = $firstname.".".$lastname;
  $windowslog = strtolower($name);
?>

<form action="euser.php?fname=<?php $_GET['fname'] ?>&lname=<?php $_GET['lname'] ?>" method="post">

<table class="outertable1" align="center">
	<tr>
		<td>
			<fieldset id="request_1">
			<legend>Employee Information</legend>
			<table width="650" class="innertable1">
				<tr>
					<td class="inputhead"><b>First Name: </b></td>
					<td class="inputdata"><input class="text" type="text" name="firstname" value="<?php echo $firstname ?>"></td>
					<td class="inputhead"><b>Last Name: </b></td>
					<td class="inputdata"><input class="text" type="text" name="lastname" value="<?php echo $lastname ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>Supervisor: </b></td>
					<td class="inputdata"><input class="text" type="text" name="supervisor" value="<?php echo $row['supervisor'] ?>"></td>
					<td class="inputhead"><b>Department: </b></td>
					<td class="inputdata"><input class="text" type="text" name="department" value="<?php echo $row['department'] ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>Date Employed: </b></td>
					<td class="inputdata"><input class="text" type="text" name="dateem" value="<?php echo $row['dateem'] ?>"></td>
					<td class="inputhead"><b>Hub Location: </b></td>
					<td class="inputdata"><input class="text" type="text" name="hub" value="<?php echo $row['hub'] ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>Windows Login: </b></td>
					<td class="inputdata"><input class="text" type="text" name="login" value="<?php echo $row['login'] ?>">
			</td>
				</tr>
			</table>
			</fieldset>
			<br>
			<fieldset id="request_2">
			<legend>Asset Information</legend>
			<table width="600" class="innertable1">
            	<tr>
					<td class="inputhead"><b>Asset Tag: </b></td>
					<td class="inputdata"><input class="text" type="text" name="asset" value="<?php echo $row['asset'] ?>"></td>
					<td class="inputhead"><b>Serial Number: </b></td>
					<td class="inputdata"><input class="text" type="text" name="serial" value="<?php echo $row['serial'] ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>Date Installed: </b></td>
					<td class="inputdata"><input class="text" type="text" name="installed" value="<?php echo $row['installed'] ?>"></td>
					<td class="inputhead"><b>Account Code: </b></td>
					<td class="inputdata"><input class="text" type="text" name="accode" value="<?php echo $row['accode'] ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>Equipment Type: </b></td>
					<td class="inputdata"><input class="text" type="text" name="etype" value="<?php echo $row['etype'] ?>"></td>
					<td class="inputhead"><b>CPU: </b></td>
					<td class="inputdata"><input class="text" type="text" name="cpu" value="<?php echo $row['cpu'] ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>Ram: </b></td>
					<td class="inputdata"><input class="text" type="text" name="ram" value="<?php echo $row['ram'] ?>"></td>
					<td class="inputhead"><b>Operating System: </b></td>
					<td class="inputdata"><input class="text" type="text" name="os" value="<?php echo $row['os'] ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>Hard Drive: </b></td>
					<td class="inputdata"><input class="text" type="text" name="hdrive" value="<?php echo $row['hdrive'] ?>"></td>
					<td class="inputhead"><b>Printers: </b></td>
					<td class="inputdata"><input class="text" type="text" name="printers" value="<?php echo $row['printers'] ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>Monitor Size: </b></td>
					<td class="inputdata"><input class="text" type="text" name="monone" value="<?php echo $row['monone'] ?>"></td>
					<td class="inputhead"><b>Monitor 2 Size: </b></td>
					<td class="inputdata"><input class="text" type="text" name="montwo" value="<?php echo $row['montwo'] ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>Monitor ICN: </b></td>
					<td class="inputdata"><input class="text" type="text" name="mononeicn" value="<?php echo $row['mononeicn'] ?>"></td>
					<td class="inputhead"><b>Monitor 2 ICN: </b></td>
					<td class="inputdata"><input class="text" type="text" name="montwoicn" value="<?php echo $row['montwoicn'] ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>Device Model Number: </b></td>
					<td class="inputdata"><input class="text" type="text" name="model" value="<?php echo $row['model'] ?>"></td>
					<td class="inputhead"><b>Previous User: </b></td>
					<td class="inputdata"><input class="text" type="text" name="prevuser" value="<?php echo $row['prevuser'] ?>"></td>
				</tr>
				<tr>
					<td class="inputhead"><b>App List: </b></td>
					<td class="inputdata"><input class="text" type="text" name="apps" value="<?php echo $row['apps'] ?>"></td>
				</tr>
			</table>
            
</fieldset>
<br>
<center><input class="submit" type="submit" name="form" value="update"></center>
</form>
<?php

if($_SERVER['REQUEST_METHOD'] == "POST") {


mysql_query("UPDATE atsmain SET fullname='".$fullname."',firstname='".$firstname."',lastname='".$lastname."' WHERE firstname = '".$firstname."' and lastname = '".$lastname."'") or die (mysql_error());

}

?>

<td valign="top" align="right" width="600">

<?php search(); ?>
            
</td>
</tr>
</table>
</body>
</html>

Open in new window

Avatar of user_n
user_n
Flag of United States of America image

Do you know how from in html works and how Get and POST works?
ASKER CERTIFIED SOLUTION
Avatar of maricksville
maricksville
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Having a look over this again.

Are you accessing this page via a page link such as: mylink.php?fname=personsfirstname&lname=personslastname.

If not then Im not sure how the values of your $_GET are being populated as your script seems to be locked in a cycle. You are trying to access data from your database matching on a firstname and lastname and then populate your form with this data, but you dont seem to indicate where the firstname and lastname values are coming from.

This line may not be doing what you expect it to do without echoing or printing the values

<form action="euser.php?fname=<?php $_GET['fname'] ?>&lname=<?php $_GET['lname'] ?>

Should be:

<form action="euser.php?fname=<?php echo $_GET['fname'] ?>&lname=<?php echo $_GET['lname'] ?>

If you are in fact accessing this page from a page link that includes the fname and lname variables then you should be able to access them by changing the line above. In that case try this:

Keep this at the top of your page:

<?php
$firstname = $_GET["fname"];
$lastname = $_GET["lname"];
$fullname = $firstname ." ". $lastname;
?>

Open in new window


And enter this where you current mysql_update script is:

<?php
if($_SERVER['REQUEST_METHOD'] == "POST")  {
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$fullname = $firstname ." ". $lastname;
mysql_query("UPDATE atsmain SET fullname = '".$fullname."', firstname = '".$firstname."', lastname = '".$lastname."' WHERE firstname = '".$firstname."' and lastname = '".$lastname."' limit 1") or die (mysql_error());
}
?>

Open in new window

Suggest you add error_reporting(E_ALL); to the top of the PHP script.

Next, use this to see the external variables:

var_dump($_GET);
var_dump($_POST);

Do not assemble your query in the function call.  Wrong:
mysql_query("UPDATE atsmain SET fullname='".$fullname."',firstname='".$firstname."',lastname='".$lastname."' WHERE firstname = '".$firstname."' and lastname = '".$lastname."'") or die (mysql_error());

Instead create a separate variable, something like this:
$sql = "UPDATE atsmain SET fullname='".$fullname."',firstname='".$firstname."',lastname='".$lastname."' WHERE firstname = '".$firstname."' and lastname = '".$lastname."'";
$res = mysql_query($sql);

The reason for this change is twofold. First, you can test $res to see if the query worked.  And second, you can print out the fully resolved query string if it failed.  This will make your debugging much easier.

Some tried-and-true examples of how to do things with PHP and MySQL are shown in the teaching example in this code snippet.  The man page references will be particularly helpful as you start using MySQL.

Best of luck with it, ~Ray

<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

Avatar of maximus81

ASKER

This was very helpful to me and I got this working now thanks to you.