[Resolved] Wierd sql deleting rows problem!

[move3rd]

Hi,

I have a mailing list script that sends out an e-mail containing a confirmation link when somone signs-up. When the the link is clicked my script should set the 'active' colum to '1'.

For some reason it's deleting the row instead of updating it. I cannot understand how this can happen as I do not use the 'DELETE FROM' sql stament in the part of the script that deals with confirmation.

This is the code that deal with confirmation:

The confirmation link looks like this:

http://dev.junglejar.net/list.php?a=confirm&code=4vh6lfgxyg&artist=Gnosis

THe confirmation section of the script:

case "confirm":
         if(isset($_GET['code'])) {
             if(mysql_query("SELECT * FROM ".MY_TABLE." WHERE code='".$_GET['code']."' AND artist='".$_GET['artist']."'")) {
                 mysql_query("UPDATE ".MY_TABLE." SET active = '1' WHERE code = '".$_GET['code']."' AND artist = '".$_GET['artist']."'");

echo "Thank you for confirming, you are now added to the list";
             } else {

echo "You have the wrong code or id, please double check";
             }
         } else {

 echo "Please use the link in email to confirm your subscription";
         }
break;

Here is the sign-up code:

switch($a) {
    case "add":
         if(!empty($_POST['email'])) {
             $alpha = array(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,0,1,2,3,4,5,6,7,8,9);
             $code = "";
             while($i < 10) {
                 $code .= $alpha[rand(0,count($alpha))];
                 $i++;
             }
             trim($code);

         if(mysql_num_rows(mysql_query("SELECT * FROM ".MY_TABLE." WHERE email='".$_POST['email']."' AND artist='".$_POST['artist']."'")) != 0) {
             echo "You are already on the list.";
             exit;
         }    
         mysql_query("INSERT INTO ".MY_TABLE." (email,code,date,artist) VALUES ('".$_POST['email']."','".$code."','".date("U")."','".$_POST['artist']."')") or die(mysql_error());

$message = "Thank you for signing up to the ".$artist." mailing list, to confirm your subscription please visit [url]http://[/url]".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?a=confirm&code=".$code."&artist=".$_POST['artist']." if you did not sign up just ignore this email and you will get no further mailings.";

mail($_POST['email'],$_POST['artist']." Mailing List Confirmation",$message,"From: list-bot@".$_SERVER['SERVER_NAME']);

echo "Thank you for joinging the ".$artist." mailing list/
			 Check your email and click on the confirmation link to begin receiving mailings.";
         } else {

echo "Please enter an email address";
         }
         break;

I'm really confused! 😕

[planetsim]

Thats a weird problem, is that the whole script by the way?

also this
if(mysql_query("SELECT * FROM ".MY_TABLE." WHERE code='".$GET['code']."' AND artist='".$GET['artist']."'")) {

Maybe should use mysql_num_rows(); as its only checking if the query works it wont try to work out if there are any rows or not.

[move3rd]

Here is the hole script. Sorry if it's a bit messy I think its becouse of my screen res.

//Connect to the database
$db = mysql_connect(MY_HOST,MY_USERNAME,MY_PASSWORD) or die("Could not connect to the database server");
mysql_select_db(MY_DB,$db);


//Check if user had authorisation.
function isAuthed() {
    if($_SESSION['user_group'] == "admin") {
        return true;
    } else {
        echo "<form action=\"".$_SERVER['PHP_SELF']."?a=login\" method=\"post\">";
        echo "Password:<br>";
        echo "<input type=\"password\" name=\"password\">";
        echo "<input type=\"submit\" value=\"Login\">";
        echo "</form>";
        exit;
    }
}

//Delete, old unactivated signups.

$date = strtotime("1 week"); // How long to wait before deleting unconfirmed e-mail addys
mysql_query("DELETE FROM ".MY_TABLE." WHERE active='0' AND date<".$date);

switch($a) {
    case "add":
         if(!empty($_POST['email'])) {
             $alpha = array(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,0,1,2,3,4,5,6,7,8,9);
             $code = "";
             while($i < 10) {
                 $code .= $alpha[rand(0,count($alpha))];
                 $i++;
             }
             trim($code);



         if(mysql_num_rows(mysql_query("SELECT * FROM ".MY_TABLE." WHERE email='".$_POST['email']."' AND artist='".$_POST['artist']."'")) != 0) {
             echo "You are already on the list.";
             exit;
         }    
         mysql_query("INSERT INTO ".MY_TABLE." (email,code,date,artist) VALUES ('".$_POST['email']."','".$code."','".date("U")."','".$_POST['artist']."')") or die(mysql_error());
         $message = "Thank you for signing up to the ".$artist." mailing list, to confirm your subscription please visit [url]http://[/url]".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?a=confirm&code=".$code."&artist=".$_POST['artist']." if you did not sign up just ignore this email and you will get no further mailings.";
         mail($_POST['email'],$_POST['artist']." Mailing List Confirmation",$message,"From: list-bot@".$_SERVER['SERVER_NAME']);
         echo "Thank you for joinging the ".$artist." mailing list/
		 Check your email and click on the confirmation link to begin receiving mailings.";
     } else {
         echo "Please enter an email address";
     }
     break;

case "remove":
    if(isset($_GET['code'])) {
        $result = mysql_query("SELECT * FROM ".MY_TABLE." WHERE email='".$_GET['email']."' AND code = '".$_GET['code']."'");
        if($result) {
            mysql_query("DELETE FROM ".MY_TABLE." WHERE code='".$_GET['code']."' AND email='".$_GET['email']."'");
            echo "You have been unsubscribed";
        } else {
            echo "Error unsubscribing";
        }
    } else {
        echo "Please use the link in the email to unsubscribe";
    }
    break;

	case "remove_all":
    if(isset($_GET['email'])) {
        $result = mysql_query("SELECT * FROM ".MY_TABLE." WHERE email='".$_GET['email']."'");
        if($result) {
            mysql_query("DELETE FROM ".MY_TABLE." WHERE email='".$_GET['email']."'");
            echo "You have been unsubscribed";
        } else {
            echo "Error unsubscribing";
        }
                                } else {
        echo "Please use the link in the email to unsubscribe to all Junglejar mailing lists.";
                              }
    break;

case "confirm":
     if(isset($_GET['code'])) {
         if(mysql_query("SELECT * FROM ".MY_TABLE." WHERE code='".$_GET['code']."' AND artist='".$_GET['artist']."'")) {
             mysql_query("UPDATE ".MY_TABLE." SET active = '1' WHERE code = '".$_GET['code']."' AND artist = '".$_GET['artist']."'");
             echo "Thank you for confirming, you are now added to the list";
         } else {
             echo "You have the wrong code or id, please double check";
         }
     } else {
         echo "Please use the link in email to confirm your subscription";
     }
     break;

case "admin":
     isAuthed();
     echo "<b>Mailing list manager</b><br>";
     echo "<a href=\"".$_SERVER['PHP_SELF']."?a=newmail\">Compose new mailing</a><br>";
     echo "<a href=\"".$_SERVER['PHP_SELF']."?a=delusers\">Remove Addresses</a>";
     break;

case "newmail":
     isAuthed();
     echo "<b>Mailing list manager - New Mailing</b><br>";
     echo "<form action=\"".$_SERVER['PHP_SELF']."?a=sendmail\" method=\"post\">";
     echo "Subject<br><input type=\"text\" name=\"subject\"><br>";
     echo "Message<br><textarea name=\"message\" cols=\"35\" rows=\"10\"></textarea><br>";
	 echo "<input type=\"text\" name=\"artist\" value=\"".$artist."\">";
     echo "<input type=\"submit\" value=\"Send\"></form>";
     break;

case "sendmail":
     isAuthed();
     if(!empty($_POST['message']) && !empty($_POST['subject'])) {
         $addresses = mysql_query("SELECT * FROM ".MY_TABLE." WHERE active = '1' AND artist = '".$_POST['artist']."'");
         while($email = @mysql_fetch_array($addresses)) {
             mail($email['email'],stripslashes($_POST['subject']),stripslashes($_POST['message'])."
This e-mail is not spam. You have recieved this e-mail after joining a mailing list at junglejar.com.			 
Please do not replay to this e-mail. We do not monitor the reply address.				 
======================================================================
To unsubscribe from this mailing list go to this address: 
[url]http://[/url]".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?a=remove&email=".$email['email']."&code=".$email['code']."
======================================================================
If you are subscribed to more than one list you can unsubscribe from 
ALL Junglejar mailing lists by going to this address:
[url]http://[/url]".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?a=remove_all&email=".$email['email']

,"From: list-bot@".$_SERVER['SERVER_NAME']) or die("Could not send email");
            }
            echo "Your email has been sent.";
        } else {
                echo "Please fill out the subject and message";
        }
        break;

case "delusers":
     isAuthed();
     $users = mysql_query("SELECT * FROM ".MY_TABLE);
     echo "<b>Mailing List Manager - Delete Users</b><br>";
     echo "<form action=\"".$_SERVER['PHP_SELF']."?a=delete\" method=\"post\">";
     while($user = @mysql_fetch_array($users)) {
         echo "<input type=\"checkbox\" name=\"users[]\" value=\"".$user['email']."\">
		 E-mail: ".$user['email']." - List: ".$user['artist']."<br>";
     }
     echo "<input type=\"submit\" value=\"Delete selected\"></form>";
     break;

case "delete":
     isAuthed();
     if(!empty($_POST['users'])) {
         $count = 0;
         foreach($_POST['users'] as $none => $user) {
             mysql_query("DELETE FROM ".MY_TABLE." WHERE email = '".$user."' AND artist= '".$artist."' ") or die(mysql_error());
             $count++;
         }
         echo $count." addresses deleted";
     } else {
         echo "Please select users to delete";
     }
     break;




case "login":
     if($_POST['password'] == PASSWORD) {
         $_SESSION['user_group'] = "admin";
         echo "You are successfully logged in. <a href=\"".$_SERVER['PHP_SELF']."?a=admin\">Click here to continue</a>.";
     } else {
         echo "That password is incorrect";
     }
     break;

default:
        $artist = $_GET['artist'];
        echo "Enter your email to join the $artist mailing list:<br>";
        echo "<form action=\"".$_SERVER['PHP_SELF']."?a=add\" method=\"post\">";
        echo "<input type=\"text\" name=\"email\"><br>";
		echo "<input type=\"hidden\" name=\"artist\" value=\"$artist\"><br>";
        echo "<input type=\"submit\" value=\"Join\">";
        echo "</form>";
        break;

}

[move3rd]

This is what was causing the problem:

//Delete, old unactivated signups. 

$date = strtotime("1 week"); // How long to wait before deleting unconfirmed e-mail addys 

I changed "1 week" to "One week ago".

Shame I had to waste 11 hours trying to figure that one out! Thats got to be a world record surely! :eek: :eek: :eek: