php/mysql delete button and delete query

dungey · April 20th, 2004, 01:09 PM

Hello all,

I need some direction... I am not the greatest of programmers and am trying to delete a user account through php page from database and am unable to do so. I have been successful in inserting to the the db from php form. here is the code. I know there is definitely a logic problem.

<? include('db_connect.inc') ?>
</head>
<body>

<?

$query = "SELECT id, name, email_address FROM faculty_mailing_list GROUP BY (id) ASC";
$result = mysql_query($query);
print "<table border=0>\n";
print"<tr><td><b>ID</b></td><td><b>Name</b></td><td><b>E-mail</b></td><td><b>Remove</b></td></tr>\n";
while ($row = mysql_fetch_assoc($result)) {
print "<tr>";
print "<td bgcolor=f1f1f1>";
$id = $row['id'];
print $id;
print "</td>";
print "<td bgcolor=f1f1f1>";
$name = $row['name'];
if (empty($name)) print $name = "Empty";
print $name;
print "</td>";
print "<td>";
$email = "<a href=mailto:".$row['email_address'].">".$row['email_address']."</a>";
if (empty($email)) $email = " Empty";
print $email;
print "</td>";
print "<td>";
print "<form action=PHP_SELF Method='POST'> ";
print "<input type=submit name=remove value='remove'>";
if(isset($_POST['remove'])){
$query = "DELETE FROM faculty_mailing_list WHERE id='1'";
}
print "</form>";
print "</td>"; print "</tr>\n";
}
print "</table>";
mysql_close();
?>

After displaying the users in the page I want it to have a remove button next to each record so I would be able to delete the user if no longer needed....

Thanks for all your assistance...

Snib · April 20th, 2004, 02:34 PM

You never actually query the database. You set $query to the correct string, but you never use mysql_query($query) on the delete string.

Just add this after the line you set $query to the delete string:

Code:

$result = mysql_query($query);

HTH,

Snib

<><

dungey · April 20th, 2004, 02:59 PM

Hi Snib,

After doing what you said it still doesn't delete the record but gives me this message:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:\Inetpub\wwwroot\php_mysql\mysql_query.php on line 17

and also the record still exists...

--Dungey

Snib · April 20th, 2004, 03:14 PM

I may be wrong, but I don't think DELETEing something supplies a result, because there's nothing to write out like you can with a SELECT statement.

So don't use mysql_fetch_assoc() or anything on the DELETE query, and it should delete correctly.

Does it delete correctly anyway, even with the error?

HTH,

Snib

<><

richard.york · April 20th, 2004, 05:42 PM

All queries return a result handle, even DELETE queries, if you don't get a result handle its likely due to an error.

Look for errors in the query!

if (FALSE === ($result = mysql_query($query)))
{
echo mysql_error()."<br />";
echo $query;
}

mysql_query will return FALSE if it was not able to execute the query, do a simple check of the variable then spit out the error.

Regards,
Rich

::::::::::::::::::::::::::::::::::::::::::
The Spicy Peanut Project
http://www.spicypeanut.net
::::::::::::::::::::::::::::::::::::::::::

dungey · April 21st, 2004, 11:13 AM

Hello,

I couldn't make your code work richard... kept on going in a loop and producing the same record over and over... I know its something I did... Snib, your suggestion does allow me to delete when I take the code and place it outside the while loop, but it also deletes all the records from my db,

this is the code and all I want it to do is delete the record that I don't want by clicking the remove button... Now my sql statement may be wrong but Iam trying to tell it to delete from faculty_mailing_list where id=id does that make sense...

        $query = "SELECT id, name, email_address FROM faculty_mailing_list GROUP BY (id) ASC";
        $result = mysql_query($query);

        print "<table border=0>\n";
        print"<tr><td><b>ID</b></td><td><b>Name</b></td><td><b>E-mail</b></td><td><b>Remove</b></td></tr>\n";

        while ($row = mysql_fetch_assoc($result)) {
                print "<tr>";
                print "<td bgcolor=f1f1f1>";
                $id = $row['id'];
                print $id;
                print "</td>";
                print "<td bgcolor=f1f1f1>";
                $name = $row['name'];
                if (empty($name)) print $name = "Empty";
                print $name;
                print "</td>";
                print "<td>";
                $email = "<a href=mailto:".$row['email_address'].">".$row['email_address']."</a>";
                if (empty($email)) $email = " Empty";
                print $email;
                print "</td>";
                print "<td>";
                print "<form action=".$_SERVER['PHP_SELF']." Method='POST'> ";
                print "<input type=submit name=remove value='remove'>";
                if(isset($_POST['remove'])){
                    $query = "DELETE FROM faculty_mailing_list WHERE id=id";
                    $result = mysql_query($query);
                }
                print "</form>";
                print "</td>";
                print "</tr>\n";
        }
        print "</table>";
        mysql_close();

my logic may be totally wrong, and also I want to understand how to deal with forms and using php, is there a good site on validating forms using php?

thanks for all your help...

--Dungey

Snib · April 21st, 2004, 11:31 AM

Hope you didn't lose anything important from your DB.... :D

I think you had a typo and instead of writing 'WHERE id=$id' you wrote 'WHERE id=id'. I don't know if this code is direct copy-paste, but if it is, you should get an error about that id.

About learning validating forms, I prefer books, personally. I don't know any particular site to learn that, but you could probably find something on Google.

http://google.com/search?q=validating+forms+php

HTH,

Snib

<><

dungey · April 21st, 2004, 12:24 PM

Snib,
I didn't lose anything important, its a test db...Thank goodness...
Thanks for the link...
The id was a typo... I have no problem querying the db and displaying the record in tables for viewing, but when I insert the if statement and want it to display the all the records it gives me the error:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in Inetpub\wwwroot\php_mysql\mysql_query.php on line 17

and displays only one record, I can delete the record one at a time but when I refresh the page it deletes everytign in the db...

Am I using the right logic

                if(isset($_POST['remove'])){
                    $query = "DELETE FROM faculty_mailing_list WHERE id=id";
                    $result = mysql_query($query);
                }

by saying if remove isset do the following, or is there another way of trying to say onlick delete the record?
I am really getting confused with the isset... Iam i using it in the right contect...

thanks snib i appreciate all your help...

richard.york · April 21st, 2004, 08:07 PM

You're still not reporting any errors from the database. In a larger project you'd be utterly clueless. PHP isn't going to give you errors from your SQL syntax unless those errors cause errors in your PHP syntax.

Code:

    if(isset($_POST['remove'])){                
        $query = "DELETE FROM faculty_mailing_list WHERE id=id";
        $result = mysql_query($query);
    }

So if you say where id=id its more or less the same thing as not having a WHERE clause at all. In fact, I think your entire logic is off.
You're going to probably want to be able to delete more than one row at once. On page 1 output something like this:

// DB stuff here, make a loop and output every id to a checkbox field.

    $result = mysql_query("SELECT id, field, field2 FROM table");

    while ($data = mysql_fetch_array($result))
    {
        echo "<input type='checkbox' name='id[]' value='$the_id'>$the_id</input><br />";
    }

  // Then add a button later..

    <input type='submit' name='remove' value='Delete' />

If you'll notice I'm using empty bracket notation in the name of the checkbox field, when this gets to PHP it'll create a multidiminsional array.

     if (isset($_POST['remove']))
     {
          foreach ($_POST['id'] as $the_id)
          {
               if (!mysql_query("DELETE FROM table WHERE id = '$the_id'"))
               {
                    echo mysql_error();
               }
          }
     }

And there you have it. foreach will iterate through the $_POST['id'] array, extracting the value, that value is then passed to a mysql_query where that record is deleted.

Regards,
Rich

::::::::::::::::::::::::::::::::::::::::::
The Spicy Peanut Project
http://www.spicypeanut.net
::::::::::::::::::::::::::::::::::::::::::

nikolai · April 22nd, 2004, 02:05 PM

Uhhh... am I the only person that noticed that you're overwriting $result within the while() loop, and that $result is the iteration control variable?

You have:

$result = mysql_query("SELECT some crap...");

while ($row = mysql_fetch_array($result))
{
    ...

    $result = mysql_query("DELETE FROM...");
}

Uh, that's bad. You need to assign the result from your delete operation to a DIFFERENT variable, otherwise you're just hosing your loop.

Your loop is clearly meant to iterate over each row returned from the SELECT statement, but you trash the handle you had to that result set after you delete the first row.

DELETE queries still return a result resource, but not to a rowset. There is no rowset, since you're not SELECTING rows. However, you can call mysql_affected_rows() to get the number of rows affected by DELETE (or INSERT or UPDATE) queries.
  http://www.php.net/mysql_affected_rows

Take care,

Nik
http://www.bigaction.org/