Page 1 of 1
I cannot get the selected email addresses to delete
Posted: Tue Apr 07, 2009 9:58 am
by embtech
Hello to all. I have been working on this for some time now. I was getting syntax errors at first then once I got those figured out I was getting unknown column errors. Now that is all fixed and when I select on of the check boxes to delete an email address from the database, the code runs and tells me that the customer(s) have been deleted. But when the list refreshes none are deleted. I also verify this by looking directly at the database itself. What am I missing here? below are the 2 queries that run in the script. If need be, I can post the whole script.
Thanks for your help
Code: Select all
//Delete the customer rows (only if the form has been submitted)
if (isset($_POST['submit']))
{
foreach ($_POST['todelete'] as $delete_id) {
$query="DELETE FROM email_list WHERE id = ('$delete_id')";
mysql_query($query, $dbc) or die ('Error in query: ' .mysql_errno() . mysql_error());
}
echo 'Customer(s) removed.<br />';
}
// Display the customer rows with checkboxes for deleting
$query="SELECT * FROM email_list";
$result = mysql_query($query, $dbc);
while ($row = mysql_fetch_array($result)){
echo '<input type="checkbox" value"' . $row['id'] .'"name="todelete[]" />';
echo $row['first_name'];
echo ' '.$row['last_name'];
echo ' '.$row['email'];
echo '<br />';
}
Re: I cannot get the selected email addresses to delete
Posted: Tue Apr 07, 2009 8:05 pm
by califdon
There are a couple of points here. A mysql_query() command doesn't fail (doesn't produce an error) just because it doesn't find any rows that meet its WHERE clause, so if you want to find out whether any rows were affected by a DELETE query, you have to test the function mysql_affected_rows().
http://us3.php.net/function.mysql-affected-rows
It's probably not matching any rows because of your SQL syntax.
$query="DELETE FROM email_list WHERE id = ('$delete_id')";
The parentheses don't belong there, and if the id is an Integer (most id's are), neither do the single-quotes.
Re: I cannot get the selected email addresses to delete
Posted: Tue Apr 07, 2009 10:13 pm
by embtech
sorry double post for some reason
Re: I cannot get the selected email addresses to delete
Posted: Tue Apr 07, 2009 10:13 pm
by embtech
califdon wrote:There are a couple of points here. A mysql_query() command doesn't fail (doesn't produce an error) just because it doesn't find any rows that meet its WHERE clause, so if you want to find out whether any rows were affected by a DELETE query, you have to test the function mysql_affected_rows().
http://us3.php.net/function.mysql-affected-rows
It's probably not matching any rows because of your SQL syntax.
$query="DELETE FROM email_list WHERE id = ('$delete_id')";
The parentheses don't belong there, and if the id is an Integer (most id's are), neither do the single-quotes.
Thanks for the input. I am pretty sure I tried it w/o the parentheses and quotes, but I did get syntax errors. With it this way I get no errors but do not get the selected emails to delete even though the customer(s) removed message is displayed. I will try your suggestions and let you know.
Re: I cannot get the selected email addresses to delete
Posted: Wed Apr 08, 2009 12:02 am
by califdon
embtech wrote:califdon wrote:There are a couple of points here. A mysql_query() command doesn't fail (doesn't produce an error) just because it doesn't find any rows that meet its WHERE clause, so if you want to find out whether any rows were affected by a DELETE query, you have to test the function mysql_affected_rows().
http://us3.php.net/function.mysql-affected-rows
It's probably not matching any rows because of your SQL syntax.
$query="DELETE FROM email_list WHERE id = ('$delete_id')";
The parentheses don't belong there, and if the id is an Integer (most id's are), neither do the single-quotes.
Thanks for the input. I am pretty sure I tried it w/o the parentheses and quotes, but I did get syntax errors. With it this way I get no errors but do not get the selected emails to delete even though the customer(s) removed message is displayed. I will try your suggestions and let you know.
The appearance of the message is to be expected because you have coded it to appear unless the
die is triggered, but as I explained, a failure to match a row does
not constitute an error. That's why you must test for whether any rows have been affected.
Re: I cannot get the selected email addresses to delete
Posted: Wed Apr 08, 2009 9:31 am
by embtech
Ok update to the issue. I removed the single quotes and parentheses from around the $delete_id and get error 1064-syntax error. The exact error is:
Error in query: 1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Before that I tried running the affected rows function and no rows seemed to be affected, as I did not get a "reply". Any other suggestions?
Thanks for your help Cal.
Oh, and yes, the $delete_id is tied to my primary key (id column) in the database which is an integer.
Re: I cannot get the selected email addresses to delete
Posted: Wed Apr 08, 2009 11:58 am
by califdon
embtech wrote:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
So have you looked at line 1?
Re: I cannot get the selected email addresses to delete
Posted: Wed Apr 08, 2009 1:59 pm
by embtech
Yes I have stared at line 1. I am assuming that line 1 would be the first query request. I have focused on the line with the query in the fact that anytime I make a change there, the error is associated with that line. Keep in mind, something that I did not mention in the OP, I am very new to php/mysql and am trying to learn how to crawl here. And the fact that the learning tool I am using, the syntax from that book does not work with my versions of php/mysql. So along with each step of the learning process, I have to try to figure out what the correct sytax is for my versions on the server. Which in the end will only make me a better programmer. The error script that is presented is somewhat cryptic to me. I am sorry if you are getting frustrated with me. I feel that frustration myself.
Re: I cannot get the selected email addresses to delete
Posted: Wed Apr 08, 2009 4:43 pm
by califdon
embtech wrote:Yes I have stared at line 1. I am assuming that line 1 would be the first query request. I have focused on the line with the query in the fact that anytime I make a change there, the error is associated with that line. Keep in mind, something that I did not mention in the OP, I am very new to php/mysql and am trying to learn how to crawl here. And the fact that the learning tool I am using, the syntax from that book does not work with my versions of php/mysql. So along with each step of the learning process, I have to try to figure out what the correct sytax is for my versions on the server. Which in the end will only make me a better programmer. The error script that is presented is somewhat cryptic to me. I am sorry if you are getting frustrated with me. I feel that frustration myself.
OK, then let me try to give you some tips about programming with PHP and MySQL. When you receive an error message, the line number refers to the numbered line in the then-executing script; the line number that appears in most editors. This can get a little more complicated if you have "include files". Since an error occurs when the parser fails to understand your code, the place it tells you to look is always AFTER where your mistake is. But the content of the error message is SUPER critical to determining what's going wrong, so any time you post a question in a forum, related to an error message you have received, always BEGIN by stating the FULL text of the error message and then show your code just before the line number it mentions. Learn to read and understand the error messages, they are your best friends.
Try to avoid "code thrashing". By that I mean when something isn't working, just trying something else (like parentheses or quotes), because you're more likely to make it
worse. Instead, LOOK UP THE PROPER SYNTAX, either in a book or by using Google to search.
Learn the correct way, don't learn all the wrong ways! There are some great reference and tutorial web sites. As a beginner, take advantage of them! One of the best is
http://w3schools.com and others include
http://www.php.net/manual/en/getting-started.php and
http://dev.mysql.com/tech-resources/art ... intro.html.
Re: I cannot get the selected email addresses to delete [SOLVED]
Posted: Sat Apr 11, 2009 11:31 am
by embtech
After much grief about this script, I FINALLY found the issue this morning. I was never assigning the value of the check box to anything because I forgot one little detail. See below for the difference in the one that was causing my hair to grey faster and the one that works. Califdon thanks for your help and advice.
The problem child:
Code: Select all
echo '<input type="checkbox" value"' . $row['id'] .'"name="todelete[]" />';
And the tiny thing that I forgot:
Code: Select all
echo '<input type="checkbox" value[color=#FF0000]=[/color]"' . $row['id'] .'"name="todelete[]" />';
Re: I cannot get the selected email addresses to delete
Posted: Sat Apr 11, 2009 12:12 pm
by califdon
"I forgot one little detail." Ah yes, poems could be written about that short phrase! As with most programming languages, PHP is unforgiving and has no pity on us mortals. I often quote the best programming instructor I ever knew, who said that computer programming boils down to just 2 all-important rules:
- Everything matters!
- Never give up!
I keep those 2 rules in mind every time I'm programming.
Re: I cannot get the selected email addresses to delete
Posted: Sat Apr 11, 2009 12:30 pm
by embtech
califdon wrote:"I forgot one little detail." Ah yes, poems could be written about that short phrase! As with most programming languages, PHP is unforgiving and has no pity on us mortals. I often quote the best programming instructor I ever knew, who said that computer programming boils down to just 2 all-important rules:
- Everything matters!
- Never give up!
I keep those 2 rules in mind every time I'm programming.
I will keep those 2 rules in mind as I progress through my learning. Especially #2. As you can tell, I follow rule 2 very well...lol