[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Delete & Edit Links On mySQL Database Rows

Is there a way to add links to each row in a database display table that will allow a user to edit and/or delete ONLY that particular row?

Below is a copy of the script with what I am hoping to do.  As you can see, I just want to create a link like so..

<a href="edit.php">Edit Coverage</a>
<a href="delete.php">Delete Coverage</a>

I have not created pages to edit/delete yet as I think I can do the delete without needing a separate page.

The edit page (again, NOT yet created) will basically be the exact same input form with all the row values present in their respective spots in the form.  When the user finishes edits and hits submit, the row will be updated with the new values.


<?PHP

include "Connect.php";

// Display query
$result = mysql_query("SELECT * FROM Coverage ORDER BY yeardropdown, monthdropdown, daydropdown, pm1, startTimeHour, startTimeMinute");

echo "<table border='1' cellspacing='3' cellpadding='3' align='center'>
<tr>
<th>Who Needs Coverage</th>
<th>Who Is Taking Coverage</th>
<th>Date</th>
<th>Lab</th>
<th>Shift Start Time</th>
<th>Shift End Time</th>
<th>Comments</th>
<th>Edit Coverage</th>
<th>Delete Coverage</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['coverageNeed'] . "&nbsp;(#" . $row['empNumber1'] . ")</td>";
  echo "<td>" . $row['coverageTaking'] . "&nbsp;(#" . $row['empNumber2'] . ")</td>";
  echo "<td>" . $row['monthdropdown'] . "/" . $row['daydropdown'] . "/" . $row['yeardropdown'] . "</td>";  
  echo "<td>" . $row['Lab'] . "</td>";
  echo "<td>" . $row['startTimeHour'] . ":" . $row['startTimeMinute'] . "&nbsp;" . $row['pm1'] . "</td>"; 
  echo "<td>" . $row['endTimeHour'] . ":" . $row['endTimeMinute'] . "&nbsp;" . $row['pm2'] . "</td>";
  echo "<td>" . $row['comments'] . "</td>";
  echo EDIT TEXT LINK CODE;
  echo DELETE TEST LINK CODE;
  echo "</tr>";
  }
echo "</table>";

mysql_close($con)

?>

Open in new window

0
laubacht
Asked:
laubacht
  • 8
  • 8
  • 5
1 Solution
 
leakim971PluritechnicianCommented:
Hello laubacht,

Assuming you've a primary key (empNumber1 ?) in your table Coverage, yes you can :
Just put the table in a form and :

  echo "<a href=\"edit.php?empNumber=" . $row['empNumber1'] . "\">Edit Coverage</a>";
  echo "<a href=\"delete.php?empNumber=" . $row['empNumber1'] . "\">Edit Coverage</a>";

<?PHP

include "Connect.php";

// Display query
$result = mysql_query("SELECT * FROM Coverage ORDER BY yeardropdown, monthdropdown, daydropdown, pm1, startTimeHour, startTimeMinute");

echo "<form action='' method='GET' id='myForm'><table border='1' cellspacing='3' cellpadding='3' align='center'>
<tr>
<th>Who Needs Coverage</th>
<th>Who Is Taking Coverage</th>
<th>Date</th>
<th>Lab</th>
<th>Shift Start Time</th>
<th>Shift End Time</th>
<th>Comments</th>
<th>Edit Coverage</th>
<th>Delete Coverage</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['coverageNeed'] . "&nbsp;(#" . $row['empNumber1'] . ")</td>";
  echo "<td>" . $row['coverageTaking'] . "&nbsp;(#" . $row['empNumber2'] . ")</td>";
  echo "<td>" . $row['monthdropdown'] . "/" . $row['daydropdown'] . "/" . $row['yeardropdown'] . "</td>";  
  echo "<td>" . $row['Lab'] . "</td>";
  echo "<td>" . $row['startTimeHour'] . ":" . $row['startTimeMinute'] . "&nbsp;" . $row['pm1'] . "</td>"; 
  echo "<td>" . $row['endTimeHour'] . ":" . $row['endTimeMinute'] . "&nbsp;" . $row['pm2'] . "</td>";
  echo "<td>" . $row['comments'] . "</td>";
  echo "<a href=\"edit.php?empNumber=" . $row['empNumber1'] . "\">Edit Coverage</a>";
  echo "<a href=\"delete.php?empNumber=" . $row['empNumber1'] . "\">Edit Coverage</a>";
  echo "</tr>";
  }
echo "</table></form>";

mysql_close($con)

?>

Open in new window

0
 
laubachtAuthor Commented:
Simple enough, now on the delete page, how do I tell it to delete only that row?
<?php

include "Connect.php";

mysql_query("DELETE FROM Coverage WHERE $row['empNumber1']");

mysql_close($con);
?> 

Open in new window

0
 
laubachtAuthor Commented:
Something just occurred to me...

The emNumber1 I have set as the primary key, trouble is there may be multiple entries under that.  I may need to create a new column that assigns a numbers to that row otherwise I may delete all rows that have the same numbers.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
hieloCommented:
see example below:
<?PHP
//assuming that Connect has a variable named
//$link which holds a reference to your db connection
include "Connect.php";

if( isset($_GET['a']) )
{
	switch( $_GET['a'])
	{
		case 'edit':
			mysql_query("SELECT * FROM Coverage WHERE id=" . mysql_real_escape_string($_GET['id']) ) ) or die( mysql_error() );
			
			//here you need to display your edit form
			break;

		case 'delete':
			mysql_query("DELETE FROM Coverage WHERE id=" . mysql_real_escape_string($_GET['id']) ) . " LIMIT 1") or die(mysql_error());
			showTable($link);
			break;
	}
}
else
{
	showTable($link);
}


//assuming your Coverage has a column 'id' as the primary key
function showTable()
{

// Display query
$result = mysql_query("SELECT * FROM Coverage ORDER BY yeardropdown, monthdropdown, daydropdown, pm1, startTimeHour, startTimeMinute");

echo "<table border='1' cellspacing='3' cellpadding='3' align='center'>
<tr>
<th>Who Needs Coverage</th>
<th>Who Is Taking Coverage</th>
<th>Date</th>
<th>Lab</th>
<th>Shift Start Time</th>
<th>Shift End Time</th>
<th>Comments</th>
<th>Edit Coverage</th>
<th>Delete Coverage</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['coverageNeed'] . "&nbsp;(#" . $row['empNumber1'] . ")</td>";
  echo "<td>" . $row['coverageTaking'] . "&nbsp;(#" . $row['empNumber2'] . ")</td>";
  echo "<td>" . $row['monthdropdown'] . "/" . $row['daydropdown'] . "/" . $row['yeardropdown'] . "</td>";  
  echo "<td>" . $row['Lab'] . "</td>";
  echo "<td>" . $row['startTimeHour'] . ":" . $row['startTimeMinute'] . "&nbsp;" . $row['pm1'] . "</td>"; 
  echo "<td>" . $row['endTimeHour'] . ":" . $row['endTimeMinute'] . "&nbsp;" . $row['pm2'] . "</td>";
  echo "<td>" . $row['comments'] . "</td>";
  echo "<td><a href='?a=edit&id={$row['id']}'>Edit</a></td>";
  echo "<td><a href='?a=delete&id={$row['id']}'>Delete</a></td>";;
  echo "</tr>";
  }
echo "</table>";
mysql_close($con)
}


?>

Open in new window

0
 
hieloCommented:
>>The emNumber1 I have set as the primary key, trouble is there may be multiple entries under that.
Then it is not a primary key. Primary key column is unique. If you do not have a unique column, try adding an auto_number column named id and then try my post.
0
 
leakim971PluritechnicianCommented:
>Simple enough, now on the delete page, how do I tell it to delete only that row?
delete.php :
<?php

include "Connect.php";

if(isset($_GET['empNumber'])) {
     mysql_query("DELETE FROM Coverage WHERE empNumber1=" . $_GET['empNumber']);
     .........
     .........  
     mysql_close($con);
}
?>

Open in new window

0
 
laubachtAuthor Commented:
@hielo

I am getting a variety of Parse error: syntax errors on your code..

Parse error: syntax error, unexpected ')' in /URL/display.php on line 11

I've tried to clean it up, but they seem to duplicate themselves..

@leakim971

That's perfect, it worked great.  What I will likely do is on the delete page, bring up a warning box that asks if the user is sure they want to delete.  If they select yes, it will run the delete code and reload the display page.  If they select No, it will simply reload the display page.

Where can I put such a script in the delete.php file to do that?
0
 
hieloCommented:
>>I am getting a variety of Parse error
...
        switch( $_GET['a'])
        {
                case 'edit':
                        mysql_query("SELECT * FROM Coverage WHERE id=" . mysql_real_escape_string($_GET['id']) )  or die( mysql_error() );
                       
                        //here you need to display your edit form
                        break;

                case 'delete':
                        mysql_query("DELETE FROM Coverage WHERE id=" . mysql_real_escape_string($_GET['id'])  . " LIMIT 1") or die(mysql_error());
                        showTable($link);
                        break;
        }
...
0
 
laubachtAuthor Commented:
@hielo

Parse error: syntax error, unexpected '}' in /home/content/l/a/u/laubacht/html/MSCD/display.php on line 64

When } removed, it then turns to

Parse error: syntax error, unexpected $end in /home/content/l/a/u/laubacht/html/MSCD/display.php on line 67

And I cannot trace that one down...
0
 
leakim971PluritechnicianCommented:
Your may add two buttons and an hidden field to get the empNumber :
delete.php :
<html><head></head><body>
<strong>Are you sur you want to delete ?</strong>
<?php

include "Connect.php";

if(isset($_GET['empNumber'])) {
    echo "<form id='areyousure' method='POST' action='' >";
    echo "<input type='hidden' id='empNumber' value='" . $_GET['empNumber'] . "' />";
    echo "<input type='submit' id='yes' value='YES' />";
    echo "<input type='button' id='no' value='NO' onclick='javascript:history.go(-1)' />";
    echo "</form>"
}
else if(isset($_POST['empNumber'])) {
     mysql_query("DELETE FROM Coverage WHERE empNumber1=" . $_GET['empNumber']);
     .........
     .........  
     mysql_close($con);
}
?>
</body><html>

Open in new window

0
 
leakim971PluritechnicianCommented:
better like that ?
<html><head></head><body>
<?php

include "Connect.php";

if(isset($_GET['empNumber'])) {
    echo "<strong>Are you sur you want to delete ?</strong>";
    echo "<form id='areyousure' method='POST' action='' >";
    echo "<input type='hidden' id='empNumber' value='" . $_GET['empNumber'] . "' />";
    echo "<input type='submit' id='yes' value='YES' />";
    echo "<input type='button' id='no' value='NO' onclick='javascript:history.go(-1)' />";
    echo "</form>"
}
else if(isset($_POST['empNumber'])) {
     mysql_query("DELETE FROM Coverage WHERE empNumber1=" . $_GET['empNumber']);
     .........
     .........  
     mysql_close($con);
     echo "<strong>Record deleted !</strong>";
}
?>
</body></html>

Open in new window

0
 
laubachtAuthor Commented:
@leakim971

The 'No' button works just fine, however the yes doesn't do anything.  It won't display your RECORD DELETED text, either.  It just kind of reloads delete.php without doing anything to the database.
0
 
hieloCommented:
The only thing "odd" here:
 echo "<td><a href='?a=edit&id={$row['id']}'>Edit</a></td>";
  echo "<td><a href='?a=delete&id={$row['id']}'>Delete</a></td>";;

are the back-to-back semicolons. IF you left the braces in place, it should work fine. Otherwise you can break those two statements like you did the others:

  echo "<td><a href='?a=edit&id=" . $row['id'] . "'>Edit</a></td>";
  echo "<td><a href='?a=delete&id=" . $row['id'] . "'>Delete</a></td>";
0
 
leakim971PluritechnicianCommented:
line 8 :


echo "<form id='areyousure' method='POST' action='delete.php' >";

Open in new window

0
 
leakim971PluritechnicianCommented:
Or :


echo "<form id='areyousure' method='POST' action='" .$_SERVER['PHP_SELF']. "' >";

Open in new window

0
 
hieloCommented:
On my last post, change:
  echo "<td><a href='?a=delete&id=" . $row['id'] . "'>Delete</a></td>";
      
to:  
echo "<td><a href='?a=delete&id=" . $row['id'] . "' onclick='return confirm(\"Are you sure you want to delete this item?\")'>Delete</a></td>";
      
0
 
laubachtAuthor Commented:
@leakim971
Made the change, but it basically reloads the page; no deleting of the row.

@hielo
I'm writing a seperate page with your code so I can work on the two different solutions, so I'll give that a look midday on Saturday and let you know how/if it worked!

Thanks guys, this is definitely educational!
<html><head></head><body>
<?php

include "Connect.php";

if(isset($_GET['id'])) {
    echo "<center>";
	echo "<form id='areyousure' method='POST' action='" .$_SERVER['PHP_SELF']. "' >";
    echo "<input type='hidden' id='id' value='" . $_GET['id'] . "' />";
    echo "<input type='submit' id='yes' value='YES' />";
    echo "<input type='button' id='no' value='NO' onclick='javascript:history.go(-1)' />";
    echo "</form>";
    echo "</center>";
}
else if(isset($_POST['id'])) {
     mysql_query("DELETE FROM Coverage WHERE id=" . $_GET['id']);

     mysql_close($con);
     echo "<strong>Record deleted!</strong>";
}
?>

<?php

include "display.php";

?>

</body></html>

Open in new window

0
 
leakim971PluritechnicianCommented:
>Made the change, but it basically reloads the page; no deleting of the row.

Modification (from ID:26214867) :

Line 8 :   echo "<form id='areyousure' method='POST' action='" .$_SERVER['PHP_SELF']. "' >";
Line 9 :   echo "<input type='hidden' id='empNumber' name='empNumber' value='" . $_GET['empNumber'] . "' />";
Line 15 :   mysql_query("DELETE FROM Coverage WHERE empNumber1=" . $_POST['empNumber']);
<html><head></head><body>
<?php

include "Connect.php";

if(isset($_GET['empNumber'])) {
    echo "<strong>Are you sur you want to delete ?</strong>";
    echo "<form id='areyousure' method='POST' action='" .$_SERVER['PHP_SELF']. "' >";
    echo "<input type='hidden' id='empNumber' name='empNumber' value='" . $_GET['empNumber'] . "' />";
    echo "<input type='submit' id='yes' value='YES' />";
    echo "<input type='button' id='no' value='NO' onclick='javascript:history.go(-1)' />";
    echo "</form>";
}
else if(isset($_POST['empNumber'])) {
     mysql_query("DELETE FROM Coverage WHERE empNumber1=" . $_POST['empNumber']);
     .........
     .........  
     mysql_close($con);
     echo "<strong>Record deleted !</strong>";
}
?>
</body></html>

Open in new window

0
 
laubachtAuthor Commented:
@leakim971

Excellent, the code worked to remove the records!  Perfect, and thank you!

I am now working on the second half of this ridiculous question in the edit column where I can bring the original form back, but with the values of the row in the form already.  Form code below..

Basically the user clicks 'EDIT', the form page comes up with the values from that row already inputed in their respective fields.  After the changes remade, they hit submit and that row is then updated with the new values.

I assume the code to pull that up would look something like this for the text fields; is the same to bring the dropdown menus to the same values?

value="<?php echo $row['coverageNeed']; ?>

I've seen a few answers regarding this on the site, and each one offers some variation.  Your suggestions are welcome while I dink with what I've found so far.
<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>
    <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>
              </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
 
laubachtAuthor Commented:
Will open up new question to tackle editing of the rows.
0
 
leakim971PluritechnicianCommented:
Thanks for the points! Sorry, I forgot the question, I will check the new one.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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