Link to home
Start Free TrialLog in
Avatar of laubacht
laubacht

asked on

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

Avatar of leakim971
leakim971
Flag of Guadeloupe image

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

Avatar of laubacht
laubacht

ASKER

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

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.
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

>>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.
>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

@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?
>>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;
        }
...
@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...
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

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

@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.
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>";
line 8 :


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

Open in new window

Or :


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

Open in new window

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>";
      
@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

ASKER CERTIFIED SOLUTION
Avatar of leakim971
leakim971
Flag of Guadeloupe image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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

Will open up new question to tackle editing of the rows.
Thanks for the points! Sorry, I forgot the question, I will check the new one.