Deleting a row in MySQL database

mattw1100 · July 20th, 2008, 08:14 PM

Hi all,

I want to setup a delete link beside each record that is returned and not sure how to do so. Can someone point me in the right direction. Also I have a minor annoying error that occurs ever time I refresh the page it adds a row to MySQL database. Should I just be closing the MySQL connection at some point?

Code:

<?php

// Start the session
  session_start();
  header("Cache-control: private"); //IE 6 Fix

  // Font
  echo "";

  // Include Top Menu
  include("top.php");
  include("delete.php");

    // Validate the User
  if (validateuser() == true) {


     echo "This is the area where you will setup your class list. Just another tool to help you in the class room";
 




?>

<form method="POST" action="class_list.php">
<input type='hidden' value='2' name='submitted_form'>

<tr>
  <td width="32%" align="right" style="font-family: verdana, arial, 'ms sans serif', sans-serif; font-size: 11px; color: #ffffff">
  Student Name: </td>
  <td height="24" width="68%" style="font-family: verdana, arial, 'ms sans serif', sans-serif; font-size: 11px; color: #ffffff"> &nbsp;<input type="text" name="name" maxlength="200"/></td>
</tr>

<tr>
  <td width="32%" align="right" style="font-family: verdana, arial, 'ms sans serif', sans-serif; font-size: 11px; color: #ffffff">
  Student Email Address: </td>
  <td height="24" width="68%" style="font-family: verdana, arial, 'ms sans serif', sans-serif; font-size: 11px; color: #ffffff"> &nbsp;<input type="text" name="email" maxlength="200"/></td>
</tr>

<tr>
  <td height="35" style="font-family: verdana, arial, 'ms sans serif', sans-serif; font-size: 11px; color: #ffffff">
                                <p align="center">
            </td>
  <td height="35" style="font-family: verdana, arial, 'ms sans serif', sans-serif; font-size: 11px; color: #ffffff">
                                &nbsp;<input type='submit' value='Create Student'>
                                <p align="center">

</tr>



</form>

<?php

// Get Username from Session
    $username_sess = $_SESSION['username'];

   // Submitted Form Variable from the form
     $sf = $_POST["submitted_form"];

     $sname = $_POST["name"];
     $semail = $_POST["email"];

//Test attempt session 

$getuserid = mysql_query("SELECT usernumber FROM students");
$id = mysql_num_rows($getuserid);

// Get userlist from database (assume since there is a user logged in that there is at least 1 user)
     $userlist = mysql_query("SELECT sname FROM students");
     $userlist1 = mysql_num_rows($userlist);

     $em = mysql_query("SELECT semail FROM students");
     $emailresult=mysql_query($em);



// New user number = User number + 1
      $usernumber = $userlist1 + 1;

mysql_query("INSERT INTO students VALUES ('$sname','$semail','$username_sess','$usernumber')");      


echo "<table border='1' width='50%' cellspacing='0' cellpadding='0'>
           <tr><td width='20%'><b>Username</b></td><td>Email</td>                 <td>Delete Student</td>
           </tr>";



     // Loop for all users
     for ($x = 0; $x < $userlist1; $x++) {

     // Get student username
     $sname = mysql_result($userlist,$x,0);
     $semail = mysql_result($em,$x,0);

     $usernumber = mysql_result($id,$x,0);   


        echo "<tr><td>$sname</td><td>$semail</td> <td><a href=delete.php?cmd=delete&getuserid=$id>Delete</a></td><td><a href=edit.php>Edit</a></td>
              <td>";


}



  }
  else {
     echo "Please Log In";
  }


?>

Not sure what really to put into my delete.php page, right now I have the following but returns a blank page:

Code:

<?
if($_GET["cmd"]=="delete")
{
$sql = "DELETE FROM students WHERE usernumber=$id";
    $userlist = mysql_query($sql);
    echo "Row deleted!";
}
?>

One further question, there is probably a more efficient way to load results from the database then to run both:

Code:

  
$userlist = mysql_query("SELECT sname FROM students");
$userlist1 = mysql_num_rows($userlist);

$em = mysql_query("SELECT semail FROM students");
$emailresult=mysql_query($em);

Is there a quick fix solution to this one also? Thanks everyone!

andychamberlainuk · July 30th, 2008, 04:13 AM

Hi,

I've got a few pointers for you. Firstly, if you change your queries to this, it will show you any errors in your query.

e.g. $result = mysql_query($sql) or die(mysql_error());

Now, as you your code, I'll put it in order:

1) Refresh problem
If you move all the processing PHP code to the top of the page before the HTML, you can run all the queried before you know there is a problem (e.g. duplicate). Add the following code (+ braces after)

if (isset($_POST['submit']))
{
    //form processing here
}

2) Delete user blank page
Ok, here I think you are using the variable $id with regiser_globals turned off. Maybe change it to: -

if (is_numeric($_GET['id']))
{
    //other validation to prevent hackers
    $id = $_GET['id'];
} else {
    die("Error");
}
3) Query
Try $sql = "SELECT * FROM students";

This way, you can load all the fields from students.

Hope this helps

mattw1100 · July 31st, 2008, 07:36 PM

Hey,

Thanks for your help! I tried modifying the delete.php page again and it returns that the row has been deleted but it has not and I can't figure it out for the life of me.

Code:

// Include database connection
  include("connect.php");

if($_GET["cmd"]=="delete"){

       $id = $_GET['id'];
       $sql = "DELETE FROM students WHERE usernumber=$id";
       echo "Row deleted!";

 }else {
    die("Error");

}

andychamberlainuk · August 21st, 2008, 05:28 PM

try putting print_r($_GET); or print_r($_POST); at the top of the page. this tells you what has been sent from the form. should shed a bit of lite on the situation...