• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6234
  • Last Modified:

Edit / delete fields using php/Mysql ?

Hi, I have a php application with a mysql database backend. This application is used to view a technicians schedule. The app does the following:
1) I have a drop down from where I select a technician name
2) I have a field where I enter the start date and the end date.
3) When I click the submit button my out put is as follows

tech      town     schedule date

Now I need this output to be a little different. I need to be able to delete or update the schedule in the following way:
tech      town     schedule date    edit                delete

When I click the edit link/button a  new window needs to open up allowing me to change the tech name and/or the schedule date. Let me assume this is page B and the prevoius form is page A. Once I update page B it should automatically close and page a needs to be automatically refreshed.

When I click delete link/button  a pop up needs to come up confirming that  I want to delete the record. And once I confirm this, the record needs to be deleted from the database and page A needs to be refreshed.

In the database all the schedules have a unique ID number. Let me know if you need me to post my code. Thanks for the help.

A
0
aej1973
Asked:
aej1973
  • 8
  • 3
1 Solution
 
HTorresCommented:
IT is definetively easier to doit in a single page, but here it goes:

sorry if it has typos, im writing on the fly....

index.php:
// so you can call back from deletion and changes.
<frameset framespacing=0 frameborder=0 border=0 rows=*>
 <frame name=principal src=main.php SCROLLING="no">
</frameset>
-----------------------
main.php
//create mysql connection
...
//query records
$sql="Select * from technicians  ..... ";
$result=mysql_query($sql,$dbh);
//start table
echo "<table ... ><tr><td>technician .... </tr>";
while ( $row=mysql_fetch_assoc($result) )
{
echo "<tr>";
echo "<td><a href=tech.details.php?id=$row[techid] class=links>$row[techid]</a></td>";
echo "<td>$row[town]</td>";
echo "<td>$row[scheduledate]</td>";
echo "<td><a href=tech.edit.php?id=$row[techid] class=links target=newwindow>edit</a></td>";
echo "<td><a href=tech.delete.php?id=$row[techid] class=links target=newwindow>delete</a></td>";
echo "</tr>";
}
echo "</table>";

-----------------------
tech.edit.php

//create connection
...
if ($action=="save") {

    //update
    $sql="UPDATE grupos SET `descr` = '" . $descr . "' WHERE `id` = '" . $id . "' LIMIT 1; ";
    $result = mysql_query($sql, $dbh );
   
    if (mysql_error()) {
      // Err updating field
      echo mysql_errno() . " : " . mysql_error() . "<br> ";
      $action=""; //so it can go back to form for validation or whatever.
    } else {
      //record updated
    echo "record was updated blah blah blah...";
     echo '<a href=main.php target=principal onclick="self.close();" >go back</a>';
    }
 
}

if ($action=="")
  {

//query
$sql="select * from technicians where id='$id' LIMIT 1";
$result=mysql_query ( ....
// check for results...
$count = mysql_num_rows($result);  
    if ($count==0)
    {
      //no results, try again (maybe calling ie history line, or manual query)
    } else {
     // display results...
    while ($row=mysql_fetch_assoc($result)
{
// form to modify...
  echo "<form method=POST name=form1 action=" . $PHP_SELF . "?action=save>";
  echo '<table border=0 class=content >';
....
  echo "<tr><td><input type=submit value=submit name=submit></td></tr>";
  echo "</table></form>";      
} //while
} //count
} //action

... same for tech.delete.php

hope this $0.0001 helps you.





0
 
HTorresCommented:
sorry, this line:
//update
    $sql="UPDATE grupos SET `descr` = '" . $descr . "' WHERE `id` = '" . $id . "' LIMIT 1; ";

should read:
    $sql="UPDATE technicians SET  ....  WHERE id='$id' LIMIT 1; ";

and in the tech.delete.php should read something like:
    $sql="DELETE from technicians WHERE id='$id' LIMIT 1; ";  
0
 
aej1973Author Commented:
Looks like I am missing something here. My code that bring up the list for the tech schedule is as follows
this page is : alter_tech_schedule.php

<?php
include "tech_connect.php";
$tempdate1 = $_POST['date1'];
$tempdate2 = $_POST['date2'];
$temptech = $_POST['D1'];

$sql = "SELECT t.first_name, t.last_name, c.city_name, c.state, s.schedule_date,s.schedule_code
FROM city_state c, tech_info t, tech_schedule s
WHERE t.tech_code = s.tech_code and c.city_code = s.city_code and s.schedule_date
between '$tempdate1' AND '$tempdate2' and t.tech_code = '$temptech'";
$result = mysql_query($sql);
?>
...........
................
<td ><?=$row['first_name']?> <?=$row['last_name']?></td>
<td><?=$row['city_name']?></td>
<td><?=$row['state']?></td>
<td><?=$row['schedule_date']?></td>
<td>><a href=schedule_delete.php?id=<?=$row[schedule_code]?> class=links target=newwindow>edit</a></td>
</tr>

How can I pass the schedule_code from this page to schedule_delete.php? Thanks for the help.

A
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!

 
HTorresCommented:

<td ><?=$row['first_name']?> <?=$row['last_name']?></td>
<td><?=$row['city_name']?></td>
<td><?=$row['state']?></td>
<td><?=$row['schedule_date']?></td>
<td>><a href=schedule_delete.php?id=<?=$row[schedule_code]?> class=links target=newwindow>edit</a></td>
</tr>

should read

<td ><?=$row['first_name']?> <?=$row['last_name']?></td>
<td><?=$row['city_name']?></td>
<td><?=$row['state']?></td>
<td><?=$row['schedule_date']?></td>
<td><a href=schedule_edit.php?id=<? echo $row[schedule_code]; ?> class=links target=newwindow>edit</a></td>
<td><a href=schedule_del.php?id=<? echo $row[schedule_code]; ?> class=links target=newwindow>delete</a></td>
</tr>

0
 
HTorresCommented:
tech.edit.php

//create connection
...
if ($action=="save") {

    //update
    $sql="UPDATE ....  LIMIT 1; ";
    $result = mysql_query($sql, $dbh );
   
    if (mysql_error()) {
      // Err updating field
      echo mysql_errno() . " : " . mysql_error() . "<br> ";
      $action=""; //so it can go back to form for validation or whatever.
    } else {
      //record updated
    echo "record was updated blah blah blah...";
     echo '<a href=main.php target=principal onclick="self.close();" >go back</a>';
    }
 
}

if ($action=="")
  {

//query
$sql="select * from technicians where id='$id' LIMIT 1";
$result=mysql_query ( ....
// check for results...
$count = mysql_num_rows($result);  
    if ($count==0)
    {
      //no results, try again (maybe calling ie history line, or manual query)
    } else {
     // display results...
    while ($row=mysql_fetch_assoc($result)
{
// form to modify...
  echo "<form method=POST name=form1 action=" . $PHP_SELF . "?action=save>";
  echo '<table border=0 class=content >';
....
  echo "<tr><td><input type=submit value=submit name=submit></td></tr>";
  echo "</table></form>";      
} //while
} //count
} //action
0
 
aej1973Author Commented:
Thank you. Let me try this and get back to you.

A
0
 
HTorresCommented:
tech.del.php

//create connection
...
if ($action=="delete") {

    //update
    $sql="DELETE FROM  ....  LIMIT 1; ";
    $result = mysql_query($sql, $dbh );
   
    if (mysql_error()) {
      // Err updating field
      echo mysql_errno() . " : " . mysql_error() . "<br> ";
      $action=""; //so it can go back to form for validation or whatever.
    } else {
      //record deleted
    echo "record was deleted blah blah blah...";
     echo '<a href=main.php target=principal onclick="self.close();" >go back</a>';
    }
 
}

if ($action=="delete") {
    echo "record was NOT deleted blah blah blah...";
     echo '<a href=main.php target=principal onclick="self.close();" >go back</a>';
    }

if ($action=="")
  {

//query
$sql="select * from technicians where id='$id' LIMIT 1";
$result=mysql_query ( ....
// check for results...
$count = mysql_num_rows($result);  
    if ($count==0)
    {
      //no results, try again (maybe calling ie history line, or manual query)
    } else {
     // display results...
    while ($row=mysql_fetch_assoc($result)
{
  echo '<table border=0 class=content >';
....
  echo "<tr><td><input type=submit value=submit name=submit></td></tr>";
  echo "</table>";
// form to confirm deletion...
  echo "<form method=POST name=form1 action=" . $PHP_SELF . ">";
 echo '<table border=0 class=content >';
echo "<tr><td>Are you sure you want to delete this record?</td>";
echo "<td><input type=radio name=action value=delete>Yes, delete it, im sure.<br>";
echo "<input type=radio name=action value=keep>No, dont delete<br>";
echo "</td></tr>";
echo "<tr><td><input type=submit value=submit name=submit></td></tr>";
 echo "</table></form>";      
} //while
} //count
} //action
0
 
HTorresCommented:
if ($action=="delete") {
    echo "record was NOT deleted blah blah blah...";
     echo '<a href=main.php target=principal onclick="self.close();" >go back</a>';
    }

should read

if ($action=="keep") {
    echo "record was NOT deleted blah blah blah...";
     echo '<a href=main.php target=principal onclick="self.close();" >go back</a>';
    }

0
 
aej1973Author Commented:
I have two questions :
1) what is this '$action' and where are you getting this from?
2) when I edit a certain row I am able to see the schedule_code on the browser but not on the page, why? I am using echo '$id' on the schedule_edit.php  page to be able to see the schedule_code. Thanks again for the help.

A

0
 
HTorresCommented:
1
$action is a flag im using in tech_del.php and tech_edit.php

once in tech_edit.php it has the value of  "", once the user clics on submit, it has the value of "save" or ""... it sets its value un the <form> declaration

once in tech_del.php it has the value of "", once the user clics on submit it has the value of "delete" or "keep" and it acts accordingly

2 in the <table> , while editing.. .the code should be like:

echo '<table border=0 class=content >';
echo "<tr><td>first name</td><td><input type=text name=name value='" . $row[first_name] . "'></td></tr>";
echo "<tr><td>last name</td><td><input type=text name=name value='" . $row[last_name] . "'></td></tr>";
//... and so on for each field., accordingly with your database fields descriptions. allways
echo "<tr><td><input type=submit value=submit name=submit></td></tr>";


0
 
HTorresCommented:
sorry,   typo

2 in the <table> , while editing.. .the code should be like:

echo '<table border=0 class=content >';
echo "<tr><td>first name</td><td><input type=text name=first_name value='" . $row[first_name] . "'></td></tr>";
echo "<tr><td>last name</td><td><input type=text name=last_name value='" . $row[last_name] . "'></td></tr>";
//... and so on for each field., accordingly with your database fields descriptions. allways
echo "<tr><td><input type=submit value=submit name=submit></td></tr>";



0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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