?
Solved

PHP Beginner: Deleting a record from the database

Posted on 2006-04-20
20
Medium Priority
?
295 Views
Last Modified: 2013-12-12
Hello again.  I was able to add to my database -- quick refresher of my situation: http://www.experts-exchange.com/Web/Web_Languages/PHP/PHP_Databases/Q_21817323.html.  Now I'm trying to delete.  So far, I've been able to access the database and display on the screen the contents of the person table (person_id, firstname, middlename, and lastname).  I don't think I'm deleting correctly.  I modified the add code to try to make it delete -- I have a form where the user just types in the person_id, and clicks the "Submit" button ( http://img92.imageshack.us/img92/1529/delete4pc.png ):

          $delquery[0] = "DELETE FROM person WHERE person_id = $person_id";



But, it's not deleting.  Partial code:
-----------------------------------------------------------

...
<?php
error_reporting(E_ALL);
session_start();
  header("Pragma: no-cache");

  include("util.php");
      securityCheck();

      $locationFeedback = "&nbsp;";
      $feedback = "&nbsp;";

      $isPostback = FALSE;
  if (array_key_exists("Submit", $_REQUEST)) {
    $isPostback = TRUE;
  }

  if ($isPostback) {
        $person_id = getRequestValue("person_id");

            $validated = true;

            if ($validated) {

//          $sql = Array();
//          $res = Array();
//          $last_ids = Array();
          $delquery = Array();

//          $sql[0] = "INSERT INTO  person (password, firstname, middlename, lastname) VALUES ('$password', '$firstname', '$middlename', '$lastname')";
//          $sql[1] = "INSERT INTO member (person_id, employer, graduation_date, subnewsletter, email_address) VALUES (LAST_INSERT_ID(), '$employer', '$graduation_date', '$subnewsletter', '$email_address')";
//          $sql[2] = "INSERT INTO phone (phone_number, member_id) VALUES ('$phone_number', LAST_INSERT_ID())";

          $delquery[0] = "DELETE FROM person WHERE person_id = $person_id";  // <-------------- HERE IS THE LINE
//          $delquery[1] = "DELETE FROM member WHERE person_id = ";
//          $delquery[2] = "DELETE FROM phone WHERE phone_id = ";

          // print_r ($sql);

          $conn = getConnection();
          $feedback = "Member deleted from database successfully.";

//          for ($i = 0; $i < count($sql); ++$i) {
//            $res[$i] = mysql_query($sql[$i], $conn);
//            $last_ids[$i] = mysql_insert_id($conn);
//            if (!$res[$i]) {
//              $err = mysql_error ($conn);
//              for ($j = $i-1; $j >= 0; -- $j) {
//                mysql_query($delquery[$j] . $last_ids[$j]);
//              }
//              $feedback = "Error, unable to insert record into database: <br />$err";
//              break;
//                }
//              }
       }

echo $feedback;
}
?>
...

            <!-- main content -->
    <td width="924" valign=top class="style8">
      <div style="margin: 20px">          <!-- InstanceBeginEditable name="EditRegion" -->
        <p>Home -&gt; Staff
        View -&gt; Delete A Member<span class="style47"></span></p>
        <p class="style46">Delete A Member From Database </p>
        <?php
                        $isPostBack = true;

            if ($isPostBack) {
                   $sql = "SELECT * FROM person";
                 }
                         //echo $sql;
                        $result = mysql_query($sql, getConnection());
                if (mysql_num_rows($result) == 0) {
                       echo("<p style='color:red'>No members in database found.</p>");
                }
                else {
                  echo("<table border='1' cellspacing='0'>");
                      echo("<tr bgcolor='#99CCFF'><th>person_id</th><th>First Name</th><th>Middle Name</th><th>Last Name</th></tr>");
                    while($row = mysql_fetch_array($result)) {
                           echo("<tr>");
                        //person_id
                        $person_id = "$row[0]";
                        if (strlen(trim($person_id)) > 0) {
                          echo("<td>$person_id</td>");
                        }
                        else {
                          echo("<td>N/A</td>");
                        }
                        //firstname
                        $firstname = $row[2];
                        if (strlen(trim($firstname)) > 0) {
                          echo("<td>$firstname</td>");
                        }
                        else {
                          echo("<td>N/A</td>");
                        }
                        //middlename
                        $middlename = $row[3];
                        if (strlen(trim($middlename)) > 0) {
                              echo("<td>$middlename</td>");
                        }
                        else {
                              echo("<td>N/A</td>");
                        }
                        //lastname
                        $lastname = $row[4];
                        if (strlen(trim($lastname)) > 0) {
                              echo("<td>$lastname</td>");
                        }
                        else {
                              echo("<td>N/A</td>");
                        }
                        echo("</tr>");
                  }
                  echo("</table>");
            }
?>

<!-- Delete Form starts here -->

        <p><span class="style44"></span></p>
        <form name="form1" method="post">

            <table border="0">
            <!-- This gathers information for the deletion. -->
          <tr>
            <th>Delete Member with person_id:</th>
            <td><input name="person_id" id="person_id"><?php echo(getRequestValue("person_id"))?></td>
          </tr>
        </table>
          <br>
          <input type="submit" name="Submit" value="Submit">
          <input type="reset" name="Submit2" value="Clear">
          <p align="center">&nbsp;</p>
        </form>
                        <p style="color:red"><?php echo($feedback); ?></p>
        <!-- InstanceEndEditable -->
        <p>&nbsp;</p>

<!-- Delete Form ends here -->
...


Thank you for any help :-)
0
Comment
Question by:luna621
  • 9
  • 8
  • 3
20 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16496596
Can you add in ...

echo "Delete query is {$delquery[0]}.<br />\n";

after the array is created.

What is the query being used?

0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16496606
Also, where are you ....

mysql_query($delquery[0]);

You have to issue the query to actually get a delete!
0
 

Author Comment

by:luna621
ID: 16496613
I don't think I did it right, but I was trying this: $delquery[0] = "DELETE FROM person WHERE person_id = $person_id";

$person_id should be retrieved from the form the user inputted.  I want to delete that person_id from the database.  Hope that makes sense.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:luna621
ID: 16496628
!!  Oh !!  It seems to me working!  But, what if the user enters a number not in the database...?
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16496639
Then nothing is deleted.

(and hello again)
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16496640
Then nothing will be deleted (GOOD).

CHeck the mysql_num_rows() after you issue the delete.

This will reflect the number of rows deleted/inserted/updated.

But not always the number of rows selected! Go figure.

See the PHP Manual on mysql_num_rows.
0
 

Author Comment

by:luna621
ID: 16496641
How do I check the database to see if that person_id exists??

---------------------------------------------------

<?php
error_reporting(E_ALL);
session_start();
  header("Pragma: no-cache");

  include("util.php");
      securityCheck();

      $locationFeedback = "&nbsp;";
      $feedback = "&nbsp;";

      $isPostback = FALSE;
  if (array_key_exists("Submit", $_REQUEST)) {
    $isPostback = TRUE;
  }

  if ($isPostback) {
        $person_id = getRequestValue("person_id");

            $validated = true;

            if ($validated) {
          $delquery = Array();
          $delquery[0] = "DELETE FROM person WHERE person_id = $person_id";

          $conn = getConnection();
          $feedback = "Member deleted from database successfully.";

          mysql_query($delquery[0]);

//          for ($i = 0; $i < count($sql); ++$i) {
//            $res[$i] = mysql_query($sql[$i], $conn);
//            $last_ids[$i] = mysql_insert_id($conn);
//            if (!$res[$i]) {
//              $err = mysql_error ($conn);
//              for ($j = $i-1; $j >= 0; -- $j) {
//                mysql_query($delquery[$j] . $last_ids[$j]);
//              }
//              $feedback = "Error, unable to delete record from database: <br />$err";
//              break;
//                }
//              }
       }

echo $feedback;
}
?>
0
 

Author Comment

by:luna621
ID: 16496652
Wow, you guys are fast.  I guess I just wanted to do a little error message.  Maybe "person_id does not exist.  Unable to delete from database".  Or something like that.
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 1800 total points
ID: 16496662
I would issue the SQL of ...

$r_results = mysql_query("SELECT COUNT(*) AS Counter FROM person WHERE person_id = $person_id");
$a_row = mysql_fetch_assoc($r_results);
if (1 == $a_row['Counter'])
 {
 // Id exists
 }
else
 {
 // Id does NOT exist
 }



0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16496699
If $a_row['Counter'] is not 0 or 1 then we have a REAL problem. person_id SHOULD be an auto_inc type column from the DB. You should never assign it directly UNLESS you are importing data from another system and you've told the DB you are supplying the identity columns (like you would need to in MSSQL).
0
 
LVL 8

Expert Comment

by:KennyTM
ID: 16496748
Hi, RQuadling

mysql_num_rows() can only check number of rows selected by SELECT-like statements. For UPDATE, INSERT, DELETE, mysql_affected_rows() [http://hk2.php.net/mysql_affected_rows] should be used instead.
0
 

Author Comment

by:luna621
ID: 16496756
Yup, person_id is an auto inc type.  Let me try your suggestion.
0
 

Author Comment

by:luna621
ID: 16496798
@KennyTM: so for this purpose, it should be alright to use them??  I am only inserting, deleting, and later on will work on updating.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16496807
Ah yes. Sorry. I got confused with odbc_num_rows.

PHP Manual ...

odbc_num_rows() will return the number of rows in an ODBC result. This function will return -1 on error. For INSERT, UPDATE and DELETE statements odbc_num_rows() returns the number of rows affected. For a SELECT clause this can be the number of rows available.

Note: Using odbc_num_rows() to determine the number of rows available after a SELECT will return -1 with many drivers.


Sorry. No mistook.
0
 
LVL 8

Assisted Solution

by:KennyTM
KennyTM earned 200 total points
ID: 16496826
If you only deletes you can just check the returned value of mysql_affected_rows(), i.e.,

mysql_query("DELETE FROM person WHERE person_id = $person_id");
if (mysql_affected_rows() == 1) {
  // successfully deleted
} else {
  // not success _OR_ more than 1 rows deleted.
}
0
 

Author Comment

by:luna621
ID: 16496882
Ah, I understand now.  Okay, I'm gonna take a little break (otherwise known as a girl's beauty sleep) and work on this again tomorrow.  I'm going to try to update according to the submitted person_id.  If I have any more questions, I'll make a new post.  Thank you for all the help!! :-)
0
 

Author Comment

by:luna621
ID: 16496894
Points time!!! :-)
0
 

Author Comment

by:luna621
ID: 16496910
Did I mention that you two are my favorite experts?  'Night you two, and hopefully I'll converse with the two of you again soon. :-)
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16496940
Aw, gee, shucks!
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16496996
As I lay me down to sleep,
I pray the backup, my code, does keep.
And if I crash before I wake,
I pray the sysop, my code, does take.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses
Course of the Month16 days, 23 hours left to enroll

864 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