Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Editing mySQL Database Row In Form

I am finding a wide range of solutions, none which fully work for me..

I am trying to allow a user the ability to edit a row of data in a database in a form similar to the original input form.  The form code I want to edit is in the snippet.

Here is the edit link on the display.php page...

echo "<td align=center><a href=\"coverageEdit.php?id=" . $row['id'] . "\">Edit Coverage</a></td>";

The form snippet below is the original form page (coverageForm.html) that inputs the original data into the database.

Basically, I want to use the same layout and pull the values from the selected row and place them into the form where edits can be made and submitted.  The new values would then take the place of the old values in the database.

Also, is there a way to highlight the row to make it a different background color when the display is loaded to show the updated row?  This different background color would go away if you reloaded the page.
<!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>

<script type="text/javascript">

var monthtext=['01','02','03','04','05','06','07','08','09','10','11','12'];

function populatedropdown(dayfield, monthfield, yearfield){
var today=new Date()
var dayfield=document.getElementById(dayfield)
var monthfield=document.getElementById(monthfield)
var yearfield=document.getElementById(yearfield)
//for (var i=0; i<31; i++)
//dayfield.options[i]=new Option(i, i)
//dayfield.options[today.getDate()]=new Option(today.getDate(), today.getDate(), true, true) //select today's day
for (var i=1; i<=31; i++)
        {
                var d = (i<10? '0'+i : i);
                if( today.getDate()!=i)
                {
                        dayfield.options[i]=new Option(  d, d);
                }
                else
                {
                        dayfield.options[i]=new Option(d,d, true, true);//select today's day
                }
        }
for (var m=0; m<12; m++)
monthfield.options[m]=new Option(monthtext[m], monthtext[m])
monthfield.options[today.getMonth()]=new Option(monthtext[today.getMonth()], monthtext[today.getMonth()], true, true) //select today's month
var thisyear=today.getFullYear()
for (var y=0; y<2; y++){
yearfield.options[y]=new Option(thisyear, thisyear)
thisyear+=1
}
yearfield.options[0]=new Option(today.getFullYear(), today.getFullYear(), true, true) //select today's year
}

</script>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<table width="950" border="5" align="center" cellpadding="0" cellspacing="1">
  <tr>
    <td align="center"><form id="form1" name="form1" method="post" action="insert.php">
    
    <h2>LABTECH COVERAGE REQUEST FORM</h2>
    <p><strong>(please take extra care to ensure all information is correct BEFORE submitting)</strong></p>
    <table width="780" border="0" cellspacing="1" cellpadding="0">
      <tr>
        <td width="311"><label>Person Needing Coverage<br />
            <input name="coverageNeed" type="text" id="coverageNeed" size="50" />
        </label></td>
        <td width="75"><label>Emp # <br />
          <input name="empNumber1" type="text" id="empNumber1" size="4" maxlength="3" />
        </label></td>
        <td width="314"><label>Person Picking Up Coverage<br />
          <input name="coverageTaking" type="text" id="coverageTaking" size="50" />
        </label></td>
        <td width="75"><label>Emp #<br />
          <input name="empNumber2" type="text" id="empNumber2" size="4" maxlength="3" />
        </label></td>
      </tr>
    </table>
      <br />
      <table width="850" border="0" cellspacing="1" cellpadding="0">
        <tr>
          <td width="200"><label>Which Lab: 
              <select name="Lab" id="Lab">
                <option value="SelectLab" >Select Lab</option>
                <option value="SO-103">South 103</option>
                <option value="WC-244">West 244</option>
                <option value="WC-243">West 243</option>
                <option value="WC-262">West 262</option>
                <option value="SI-1086">Science 1086</option>
                <option value="SI-1088">Science 1088</option>
                <option value="ART-277">Art 277</option>
                <option value="PL-246">Plaza 246</option>
                <option value="PL-307">Plaza 307</option>
                <option value="KC-317">King Center 317</option>
                <option value="WIA-119">WIA 119</option>
                <option value="TIV-225">Tivoli 225</option>
                <option value="WC-241">West 241 (LAC)</option>
              </select>
          </label></td>
          <td width="200">

<label>Date: <select name="monthdropdown" id="monthdropdown">
</select> </label>
<select name="daydropdown" id="daydropdown">
</select> 
<select name="yeardropdown" id="yeardropdown">
</select></label> </td>
          <td width="223" align="center"><label>Start:
              <select name="startTimeHour" id="startTimeHour">
                <option value="01">01</option>
                <option value="02">02</option>
                <option value="03">03</option>
                <option value="04">04</option>
                <option value="05">05</option>
                <option value="06">06</option>
                <option value="07">07</option>
                <option value="08">08</option>
                <option value="09">09</option>
                <option value="10">10</option>
                <option value="11">11</option>
                <option value="12">12</option>
              </select></label>
          :
          <select name="startTimeMinute" id="startTimeMinute">
            <option value="00">00</option>
            <option value="15">15</option>
            <option value="30">30</option>
            <option value="45">45</option>
          </select>
          <select name="pm1" id="pm1">
            <option value="AM">AM</option>
            <option value="PM">PM</option>
          </select>          </td>
          <td width="222" align="center"><label>End:
              <select name="endTimeHour" id="endTimeHour">
                <option value="01">01</option>
                <option value="02">02</option>
                <option value="03">03</option>
                <option value="04">04</option>
                <option value="05">05</option>
                <option value="06">06</option>
                <option value="07">07</option>
                <option value="08">08</option>
                <option value="09">09</option>
                <option value="10">10</option>
                <option value="11">11</option>
                <option value="12">12</option>
              </select></label>
          :
          <select name="endTimeMinute" id="endTimeMinute">
            <option value="00">00</option>
            <option value="15">15</option>
            <option value="30">30</option>
            <option value="45">45</option>
          </select>
          <select name="pm2" id="pm2">
            <option value="AM">AM</option>
            <option value="PM">PM</option>
          </select></td>
        </tr>
      </table>
      <p>
        <label>Additional Information
        <input name="comments" type="text" id="comments" value="None" size="100" maxlength="100" />
        </label>
      </p>
      <table width="500" border="0" cellspacing="1" cellpadding="0">
        <tr>
          <td align="center"><label>
            <input type="submit" name="submit" id="submit" value="Send Coverage" />
          </label></td>
          <td align="center"><label>
            <input type="reset" name="reset" id="reset" value="Reset Form" />
          </label></td>
        </tr>
      </table>
      <p>&nbsp;</p>
    </form></td>
  </tr>
</table>

<script type="text/javascript">

//populatedropdown(id_of_day_select, id_of_month_select, id_of_year_select)
window.onload=function(){
populatedropdown("daydropdown", "monthdropdown", "yeardropdown")
}
</script>
</body>
</html>

Open in new window

0
laubacht
Asked:
laubacht
  • 7
  • 5
1 Solution
 
Ludwig DiehlSystems ArchitectCommented:
Well, the simplest way ( not the best) would be this one:



and in the form don't forget to pass the row Id so u can update it in the action form. You coud use a hidden input:

<input type="hidden" value="<?php echo $row[id];?>">

the action form:


$id=$_POST['id'];
$value1=$_POST['column1'];

"UPDATE table SET column1='$value1',column2='$value2'..... WHERE id=$id";

You should addslashes to the values u are storing u can use addslashes or $value1=$cn->real_escape_string($value1);

Hope it helps







//your connection:
if(!$_POST['id'])
    die('Id not given!');
$cn=new mysqli('host','user','pwd','dbname');
$rs=$cn->query("SELECT * FROM table WHERE id=$id");
$num_rows=$rs->num_rows;
if($num_rows==0)
    die('Check if record exists');
elseif($num_rows>1)
    die('Too many records found!');
$row=$rs->fetch_row();   //or you could use fetch_assoc() if u want named colums
//for text inputs data retrieval:
<input name="coverageNeed" type="text" id="coverageNeed" size="50" value="<?php echo stripslashes($row[0]); /*$row['columnname'] in case u use fetch_assoc*/?>" />

//for selects data retrieval:
//you could re-write de whole select using php populating it from a source (db or array):
//populating from array
for($nX=1;$nX<=12;$nX++)
{   
    $sValue=str_pad($nX,2,'0',STR_PAD_LEFT);
    $aStartTimeHour[$sValue]=$sValue;
} 
?>
<select name="startTimeHour" id="startTimeHour">
<!--IN CASE U ALLOW NULL VALUES-->
    <option value="" >-Select-</option> 
<?php
    foreach($aStartTimeHour as $sValue => $sText)
    {       
        $sSelected=($sValue==$row['id'] ? "selected=\"selected\"" : "");
        echo "<option value=\"$sValue\" $sSelected>$sText</option>\n";
            
    }
?>    
</select>

<?php
//populating from db


$rsSelect1=$cn->query("SELECT value,text FROM sourcetable");

?>
<select name="Lab" id="Lab">
    <option value="SelectLab" >Select Lab</option>
<?php
while($rowSelect1=$rsSelect1->fetch_row())
{
    $sValue=stripslashes($rowSelect1[0]);
    $sText=stripslashes($rowSelect1[1]);
    $sSelected=($sValue==$row['id'] ? "selected=\"selected\"" : "");
    echo "<option value=\"$sValue\" $sSelected>$sText</option>\n";
}
?>
</select>

Open in new window

0
 
Ludwig DiehlSystems ArchitectCommented:
oh I forgot. You could also populate your selects just using simply HTML and selecting their values using js. eg:
<select name="Lab" id="Lab">
                <option value="SelectLab" >Select Lab</option>
                <option value="SO-103">South 103</option>
                <option value="WC-244">West 244</option>
                <option value="WC-243">West 243</option>
                <option value="WC-262">West 262</option>
                <option value="SI-1086">Science 1086</option>
                <option value="SI-1088">Science 1088</option>
                <option value="ART-277">Art 277</option>
                <option value="PL-246">Plaza 246</option>
                <option value="PL-307">Plaza 307</option>
                <option value="KC-317">King Center 317</option>
                <option value="WIA-119">WIA 119</option>
                <option value="TIV-225">Tivoli 225</option>
                <option value="WC-241">West 241 (LAC)</option>
   </select>

<!--and at the end of your page>
<script type="text/javascript">
function selectByValue(obj,value)
{
var obj=getElementById(obj);
    if(!obj)
        return false;
	var aOptions=obj.options;
	var nOptions=aOptions.length;
	var x;
	for(x=0; x<nOptions;x++)
	{		
		if(aOptions[x].value==value)
		{		
			aOptions[x].selected=true;
			break;
		}		
	}	
}
 selectByValue("Lab","<?php echo $row['column']; ?>");
.
.
.

</script>

Open in new window

0
 
laubachtAuthor Commented:
@ludwigDiehl
Thanks for the assistance, I've been busy with another project and haven't had a chance to test this out.  I will get back to you early tomorrow with the results! Thanks!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ludwig DiehlSystems ArchitectCommented:
No prob, let me know if it worked out.
0
 
laubachtAuthor Commented:
@ludwigDiehl
Okay, so I have finally sat back down in front of this and am working it out.  I have created the edit page which is coverageEdit.php and it basically replicates the original entry form.  I have tweaked some of the code you've passed along, but I am a bit confused on a couple things..

1. <input type="hidden" value="<?php echo $row[id];?>">

I just added that beneath my FORM line.. right?

************************

2. $id=$_POST['id'];
$value1=$_POST['column1'];

"UPDATE table SET column1='$value1',column2='$value2'..... WHERE id=$id";

You should addslashes to the values u are storing u can use addslashes or $value1=$cn->real_escape_string($value1);

I'm not sure where this is suppose to go on the editCoverage.php

I have included the editCoverage.php code in the snippet below to show you where I am at..
<!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>

<script type="text/javascript">

var monthtext=['01','02','03','04','05','06','07','08','09','10','11','12'];

function populatedropdown(dayfield, monthfield, yearfield){
var today=new Date()
var dayfield=document.getElementById(dayfield)
var monthfield=document.getElementById(monthfield)
var yearfield=document.getElementById(yearfield)
//for (var i=0; i<31; i++)
//dayfield.options[i]=new Option(i, i)
//dayfield.options[today.getDate()]=new Option(today.getDate(), today.getDate(), true, true) //select today's day
for (var i=1; i<=31; i++)
        {
                var d = (i<10? '0'+i : i);
                if( today.getDate()!=i)
                {
                        dayfield.options[i]=new Option(  d, d);
                }
                else
                {
                        dayfield.options[i]=new Option(d,d, true, true);//select today's day
                }
        }
for (var m=0; m<12; m++)
monthfield.options[m]=new Option(monthtext[m], monthtext[m])
monthfield.options[today.getMonth()]=new Option(monthtext[today.getMonth()], monthtext[today.getMonth()], true, true) //select today's month
var thisyear=today.getFullYear()
for (var y=0; y<2; y++){
yearfield.options[y]=new Option(thisyear, thisyear)
thisyear+=1
}
yearfield.options[0]=new Option(today.getFullYear(), today.getFullYear(), true, true) //select today's year
}

</script>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Coverage Request Form</title>
<style type="text/css">
<!--
.style1 {color: #FF0000}
-->
</style>
</head>

<?PHP

include "Connect.php";

if(!$_POST['id'])
    die('Id not given!');
// $cn=new mysqli('host','user','pwd','dbname');
$rs=$cn->query("SELECT * FROM Coverage WHERE id=$id");
$num_rows=$rs->num_rows;
if($num_rows==0)
    die('Check if record exists');
elseif($num_rows>1)
    die('Too many records found!');
$row=$rs->fetch_row();   //or you could use fetch_assoc() if u want named colums

?>

<body>
<table width="950" border="5" align="center" cellpadding="0" cellspacing="1">
  <tr>
    <td align="center"><form id="form1" name="form1" method="post" action="insert.php">
    <input type="hidden" value="<?php echo $row[id];?>">
    
    <h2>LABTECH COVERAGE REQUEST FORM (EDIT COVERAGE)</h2>
    <p><strong>(<span class="style1">please take extra care to ensure all information is correct BEFORE submitting</span>)</strong></p>
    <table width="780" border="0" cellspacing="1" cellpadding="0">
      <tr>
        <td width="311"><label>Person Needing Coverage<br />
            <input name="coverageNeed" type="text" id="coverageNeed" size="50" value="<?php echo $E_coverageNeed;?>" />
        </label></td>
        <td width="75"><label>Emp # <br />
          <input name="empNumber1" type="text" id="empNumber1" size="4" maxlength="3" value="<?php echo $E_empNumber1;?>" />
        </label></td>
        <td width="314"><label>Person Picking Up Coverage<br />
          <input name="coverageTaking" type="text" id="coverageTaking" size="50" value="<?php echo $E_coverageTaking;?>" />
        </label></td>
        <td width="75"><label>Emp #<br />
          <input name="empNumber2" type="text" id="empNumber2" size="4" maxlength="3" value="<?php echo $E_empNumber2;?>" />
        </label></td>
      </tr>
    </table>
      <br />
      <table width="850" border="0" cellspacing="1" cellpadding="0">
        <tr>
          <td width="200"><label>Which Lab: 
              <select name="Lab" id="Lab">
                <option value="SelectLab" >Select Lab</option>
                <option value="SO-103">South 103</option>
                <option value="WC-244">West 244</option>
                <option value="WC-243">West 243</option>
                <option value="WC-262">West 262</option>
                <option value="SI-1086">Science 1086</option>
                <option value="SI-1088">Science 1088</option>
                <option value="ART-277">Art 277</option>
                <option value="PL-246">Plaza 246</option>
                <option value="PL-307">Plaza 307</option>
                <option value="KC-317">King Center 317</option>
                <option value="WIA-119">WIA 119</option>
                <option value="TIV-225">Tivoli 225</option>
                <option value="WC-241">West 241 (LAC)</option>
              </select>
          </label></td>
          <td width="200">

<label>Date: <select name="monthdropdown" id="monthdropdown">
</select> </label>
<select name="daydropdown" id="daydropdown">
</select> 
<select name="yeardropdown" id="yeardropdown">
</select></label> </td>
          <td width="223" align="center"><label>Start:
              <select name="startTimeHour" id="startTimeHour">
                <option value="01">01</option>
                <option value="02">02</option>
                <option value="03">03</option>
                <option value="04">04</option>
                <option value="05">05</option>
                <option value="06">06</option>
                <option value="07">07</option>
                <option value="08">08</option>
                <option value="09">09</option>
                <option value="10">10</option>
                <option value="11">11</option>
                <option value="12">12</option>
              </select></label>
          :
          <select name="startTimeMinute" id="startTimeMinute">
            <option value="00">00</option>
            <option value="15">15</option>
            <option value="30">30</option>
            <option value="45">45</option>
          </select>
          <select name="pm1" id="pm1">
            <option value="AM">AM</option>
            <option value="PM">PM</option>
          </select>          </td>
          <td width="222" align="center"><label>End:
              <select name="endTimeHour" id="endTimeHour">
                <option value="01">01</option>
                <option value="02">02</option>
                <option value="03">03</option>
                <option value="04">04</option>
                <option value="05">05</option>
                <option value="06">06</option>
                <option value="07">07</option>
                <option value="08">08</option>
                <option value="09">09</option>
                <option value="10">10</option>
                <option value="11">11</option>
                <option value="12">12</option>
              </select></label>
          :
          <select name="endTimeMinute" id="endTimeMinute">
            <option value="00">00</option>
            <option value="15">15</option>
            <option value="30">30</option>
            <option value="45">45</option>
          </select>
          <select name="pm2" id="pm2">
            <option value="AM">AM</option>
            <option value="PM">PM</option>
          </select></td>
        </tr>
      </table>
      <p>
        <label>Additional Information
        <input name="comments" type="text" id="comments" value="None" size="100" maxlength="100" />
        </label>
      </p>
      <p class="style1"><strong>PLEASE DOUBLE CHECK TO ENSURE ALL INFORMATION IS CORRECT BEFORE SUBMITTING!!!</strong></p>
      <table width="500" border="0" cellspacing="1" cellpadding="0">
        <tr>
          <td align="center"><label>
            <input type="submit" name="submit" id="submit" value="Send Coverage" />
          </label></td>
          <td align="center"><label>
            <input type="reset" name="reset" id="reset" value="Reset Form" />
          </label></td>
        </tr>
      </table>
      <p><a href="display.php"><strong>VIEW COVERAGE SHEET</strong></a></p>
      </form></td>
  </tr>
</table>

<script type="text/javascript">

//populatedropdown(id_of_day_select, id_of_month_select, id_of_year_select)
window.onload=function(){
populatedropdown("daydropdown", "monthdropdown", "yeardropdown")
}
</script>

<!-- BEGIN POPULATING JS CODE FOR DROPDOWNS -->
<script type="text/javascript">
function selectByValue(obj,value)
{
var obj=getElementById(obj);
    if(!obj)
        return false;
	var aOptions=obj.options;
	var nOptions=aOptions.length;
	var x;
	for(x=0; x<nOptions;x++)
	{		
		if(aOptions[x].value==value)
		{		
			aOptions[x].selected=true;
			break;
		}		
	}	
}
 selectByValue("Lab","<?php echo $row['Lab']; ?>");
 selectByValue("monthdropdown","<?php echo $row['monthdropdown']; ?>"); 
 selectByValue("daydropdown","<?php echo $row['daydropdown']; ?>");
 selectByValue("yeardropdown","<?php echo $row['yearddropdown']; ?>");
 selectByValue("startTimeHour","<?php echo $row['startTimeHour']; ?>");
 selectByValue("startTimeMinute","<?php echo $row['startTimeMinute']; ?>");
 selectByValue("pm1","<?php echo $row['pm1']; ?>");
 selectByValue("endTimeHour","<?php echo $row['endTimeHour']; ?>");
 selectByValue("endTimeMinute","<?php echo $row['endTimeMinute']; ?>");
 selectByValue("pm2","<?php echo $row['pm2']; ?>");


</script>
<!-- END POPULATING JS CODE FOR DROPDOWNS -->

</body>
</html>

Open in new window

0
 
Ludwig DiehlSystems ArchitectCommented:
1. <input value="<?php echo $row[id];?>" type="hidden">you are right however u must give it a name so u can get its value from $_POST
<input name="id" value="<?php echo $row['id'];?>" type="hidden">

2. $value1=real_escape_string($_POST['Lab']);
    $value2=real_escape_string($_POST['monthdropdown']);
 these are the values u are passing to editCoverage.php which will be updated.

3. You are not assigning any value to $id aprox line 60 right after die('Id not given!'); you should add $id=$_GET['id'];

3. Javascript syntax errors: don't forget to add ";"  at the end of each line. Check carefully that coz it might result in unexpected errors.

4.  getElementById(obj); should be document.getElementById(obj) in the selectByValue function. Oh by the way it'd be better if u rename obj to id so you have this:
function selectByValue(id,value)
{
var obj=getElementById(id);
    if(!obj)
        return false;


5. I suggest you use lower case when naming your table columns.


0
 
laubachtAuthor Commented:
2. $value1=real_escape_string($_POST['Lab']);
   $value2=real_escape_string($_POST['monthdropdown']);
these are the values u are passing to editCoverage.php which will be updated.

Do those go on the display.php page, or are these living on the coverageEdit.php page?  Or do these work on the page along with the selectByValue?

selectByValue("Lab","<?php echo $row['Lab']; ?>");
selectByValue("monthdropdown","<?php echo $row['monthdropdown']; ?>");  
0
 
Ludwig DiehlSystems ArchitectCommented:
2. $value1=real_escape_string($_POST['Lab']);
   $value2=real_escape_string($_POST['monthdropdown']);
these are the values u are passing to editCoverage.php which will be updated.

0
 
laubachtAuthor Commented:
Well done, thank you!
0
 
Ludwig DiehlSystems ArchitectCommented:
It's always good to help!. Cheers!
0
 
Ludwig DiehlSystems ArchitectCommented:
just remember to improve security coz it's just the beginning... You might want to take a look at mysqli statements so you can prepare a params before inserting or updating any value on your database. check
mysqli_stmt_prepare(). That's another story but keep it in mind as well as some other security and performance improvements you can get tweaking it a little. Best Regards!.
0
 
laubachtAuthor Commented:
Yeah, I have a ways to go.. I'm pulling this all together piece-by-piece.  The security and such will probably be further down the road as I am piecing this project together.  I appreciate your assistance very much!  This site is a wonderful resource and has already taught me more than I ever would've learned on my own!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now