?
Solved

Editing mySQL Database Row In Form

Posted on 2010-01-11
12
Medium Priority
?
197 Views
Last Modified: 2013-12-12
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
Comment
Question by:laubacht
[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
  • 7
  • 5
12 Comments
 
LVL 6

Expert Comment

by:Ludwig Diehl
ID: 26287608
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
 
LVL 6

Expert Comment

by:Ludwig Diehl
ID: 26287698
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
 
LVL 1

Author Comment

by:laubacht
ID: 26297884
@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
Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

 
LVL 6

Expert Comment

by:Ludwig Diehl
ID: 26298395
No prob, let me know if it worked out.
0
 
LVL 1

Author Comment

by:laubacht
ID: 26318081
@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
 
LVL 6

Expert Comment

by:Ludwig Diehl
ID: 26319108
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
 
LVL 1

Author Comment

by:laubacht
ID: 26324772
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
 
LVL 6

Accepted Solution

by:
Ludwig Diehl earned 2000 total points
ID: 26326667
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
 
LVL 1

Author Closing Comment

by:laubacht
ID: 31675576
Well done, thank you!
0
 
LVL 6

Expert Comment

by:Ludwig Diehl
ID: 26327586
It's always good to help!. Cheers!
0
 
LVL 6

Expert Comment

by:Ludwig Diehl
ID: 26327641
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
 
LVL 1

Author Comment

by:laubacht
ID: 26327652
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

WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 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