Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 20th, 2004, 02:09 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default php/mysql delete button and delete query

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...
Reply With Quote
  #2 (permalink)  
Old April 20th, 2004, 03:34 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: , , .
Posts: 1,285
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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

<><
Reply With Quote
  #3 (permalink)  
Old April 20th, 2004, 03:59 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old April 20th, 2004, 04:14 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: , , .
Posts: 1,285
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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

<><
Reply With Quote
  #5 (permalink)  
Old April 20th, 2004, 06:42 PM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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
::::::::::::::::::::::::::::::::::::::::::
Reply With Quote
  #6 (permalink)  
Old April 21st, 2004, 12:13 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old April 21st, 2004, 12:31 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: , , .
Posts: 1,285
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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

<><
Reply With Quote
  #8 (permalink)  
Old April 21st, 2004, 01:24 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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...
Reply With Quote
  #9 (permalink)  
Old April 21st, 2004, 09:07 PM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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
::::::::::::::::::::::::::::::::::::::::::
Reply With Quote
  #10 (permalink)  
Old April 22nd, 2004, 03:05 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to delete file System.IO.Delete error maricar C# 13 March 14th, 2014 07:50 AM
Commit.PHP and Delete.PHP in WROX's book GiFos Beginning PHP 0 January 30th, 2007 10:51 PM
How to delete the added button in outlook haiying Access VBA 0 June 10th, 2005 08:10 AM
how to delete a row when click delete hyperlink naveenkumarg1 Pro JSP 1 August 16th, 2004 02:29 AM



All times are GMT -4. The time now is 12:25 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.