Solved

Update mysql data using PHP

Posted on 2011-09-23
5
418 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:maximus81
5 Comments
 
LVL 9

Expert Comment

by:user_n
ID: 36591345
Do you know how from in html works and how Get and POST works?
0
 
LVL 2

Accepted Solution

by:
maricksville earned 500 total points
ID: 36591982
Can you have a look at lines 103 and 105. You have two input text fields with the respective names 'firstname' and 'lastname'.

<input class="text" type="text" name="firstname" value="<?php echo $firstname ?>">
<input class="text" type="text" name="lastname" value="<?php echo $lastname ?>">

Open in new window


When you submit this form you are submitting via a POST action and sending the values as $_POST['firstname'] and $_POST['lastname']. At the top of your page you are trying to access $_GET data with the following keys $_GET['fname'] and $_GET['lname'].

If Im following with what you're trying to do you need to change the code at the top of your page to the following, and note the that I have used single quotations.

$firstname = $_POST['fname'];
$lastname = $_POST['lname'];
$fullname = $firstname ." ". $lastname;

Open in new window


You could also move the update script to the top of your page as well and make it look something like this:

<?php
if($_SERVER['REQUEST_METHOD'] == "POST")  {
$firstname = $_POST['fname'];
$lastname = $_POST['lname'];
$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


Unless you have a specific reason not to...you absolutely MUST include 'limit 1' at the end of your UPDATE and DELETE mysql functions:

update MYTABLE set test = test where clause LIMIT 1

Otherwise you run the possibility of updating every single row of the table by mistake. On another note it would also be preferable to update where your data matches a unique ID number and not a name. As an example, if you have two people both with the names John Smith then your script will update both of them as you are simply matching against their names which are not unique.

In my version you would update only the first instance of someone named John Smith, which is still not right becase it may be the wrong John Smith.

Hope this helps.
0
 
LVL 2

Expert Comment

by:maricksville
ID: 36592006
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

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36592585
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

0
 

Author Closing Comment

by:maximus81
ID: 36594200
This was very helpful to me and I got this working now thanks to you.
0

Featured Post

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.

Join & Write a Comment

SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
This article discusses four methods for overlaying images in a container on a web page
In this tutorial viewers will learn how to embed videos in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <video> tag to insert a video. Define the src as the URL of your video; this is similar to …
The viewer will learn how to dynamically set the form action using jQuery.

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

23 Experts available now in Live!

Get 1:1 Help Now