PHP Beginner: Deleting a record from the database

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 :-)
luna621Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard QuadlingSenior Software DeveloperCommented:
Can you add in ...

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

after the array is created.

What is the query being used?

0
Richard QuadlingSenior Software DeveloperCommented:
Also, where are you ....

mysql_query($delquery[0]);

You have to issue the query to actually get a delete!
0
luna621Author Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

luna621Author Commented:
!!  Oh !!  It seems to me working!  But, what if the user enters a number not in the database...?
0
KennyTMCommented:
Then nothing is deleted.

(and hello again)
0
Richard QuadlingSenior Software DeveloperCommented:
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
luna621Author Commented:
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
luna621Author Commented:
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
Richard QuadlingSenior Software DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard QuadlingSenior Software DeveloperCommented:
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
KennyTMCommented:
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
luna621Author Commented:
Yup, person_id is an auto inc type.  Let me try your suggestion.
0
luna621Author Commented:
@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
Richard QuadlingSenior Software DeveloperCommented:
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
KennyTMCommented:
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
luna621Author Commented:
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
luna621Author Commented:
Points time!!! :-)
0
luna621Author Commented:
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
Richard QuadlingSenior Software DeveloperCommented:
Aw, gee, shucks!
0
Richard QuadlingSenior Software DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.