Solved

Update mysql data using PHP

Posted on 2011-09-23
5
422 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
[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 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 110

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL limit and not so limited 13 41
Jquery HTML() with ajax... replacing content 2 32
HTML INPUT 6 22
Ajax on ASP 2 42
When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
In this tutorial viewers will learn how to embed Flash content in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <object> tag to embed Flash content.: To specify that the object is Flash content, d…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

730 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