[SOLVED] silly syntax! this is an easy one.

[mrheff]

hi guys and gals!

Grrrr i been fiddling with some silly syntax foir a while and wondered if anyone would be kind enough to help me, basicallly i made form to mailing list thing, but i want ppl to confirm via a link sent to them in email,

 

So... I added the field 'in' to be binary and wanted it to flip from 0 to 1

when someone has linked in...

 

but i keep getting this ...

 

Error in query:Unknown column '$mail' in 'where clause'

 

the variable is confirmed to be picked up... i just cant slot it in to my code right!

any ideas would be much appreciated, thank you geni-i and geni-iettes

 

 

$query = 'UPDATE `helen_mailinglist`.`emails` SET `in` = \'1\' WHERE `emails`.`email` = '"$mail"' LIMIT 1;'; 

[trq]

Sorry, but your query doesn't actually make allot of sense. Your trying to UPDATE a filed in the helen_mailinglist table, yet your WHERE clause points to another emails table.

 

Which is it?

 

$query = "UPDATE helen_mailinglist SET `in` = 1 WHERE emails = '$mail';";

 

Is correct as an example. but its hard to tell what your trying to do exactly.

[emehrkay]

'UPDATE `helen_mailinglist`.`emails` SET `in` = \'1\' WHERE `emails`.`email` = \''. $mail .'\' LIMIT 1;';

 

yeah i didnt look at the actual query, just seen the concatenation error

[mrheff]

hmm im only using that code cause it is the php  that phpmyadmin told me should be right, then i schanged the 'id' field to 'email' and the id number to the variable... name...

 

 

thats what phpmyadmin told me

 

$sql = 'UPDATE `helen_mailinglist`.`emails` SET `in` = \'1\' WHERE `emails`.`id` = 32 LIMIT 1;';

and this is how i changed it.....

$query = "UPDATE helen_mailinglist SET `in` = 1 WHERE emails = '$mail';";

 

 

My problem lies in the fact it cant actually read my variable and giving me this message

Error in query:Unknown column '$mail' in 'where clause'

[mrheff]

thanks man ill try that right now.

 

[mrheff]

"Error in query:Query was empty

 

damm.

 

do you think it could be to do with what youve mentioned befor?

<?
$mail = $_GET['mail']; 
echo $mail;


$connection=mysql_connect("*","*", "*") or die("Unable to connect!"); 

mysql_select_db("helen_mailinglist") or die("Unable to select database!"); 

//  EXECUTE QUERY ---> 
'UPDATE `helen_mailinglist`.`emails` SET `in` = \'1\' WHERE `emails`.`email` = \''. $mail .'\' ;'; 
//////-----> 
$result=mysql_query($query) or die("Error in query:".mysql_error()); 
//if ($result) 
   //echo mysql_affected_rows()." row inserted into the database effectively."; 

//  CLOSE CONNECTION ---> 
mysql_close($connection);
 print "<meta http-equiv=\"refresh\" content=\"0;URL=ok.htm\">";


?>

 

i was linking in like this confirm.php?mail="me@adress.com"

i have many mails the same in the test DB, as i wanted it so as it only allowed one of them to be sent mail, incase of multiple sign-ups hence the LIMIT 1.

my table looks like this

 

id   | in | email   | date |   ip |

--------------------------------------------------------

34 0   me@adresss.com   01/02/04 |93.83.749.47|

35 0   me@adresss.com   01/02/04 |93.83.749.47|

36 0   me@adresss.com   01/02/04 |93.83.749.47|

 

and im trying to get it to flip that 0 in the 'in' column to a '1'

 

again anyhelp appreciated!

[Illusion]

$query="UPDATE emails SET in = 1 WHERE emails = '$mail'";
[code]
We are not supposed to use LIMIT on UPDATE statement.Why do you have duplicate email ids, impose UNIQUE constraint there.

[/code]

[mrheff]

thank you very much guys gfor all your hastey responses,

ill try this right away illusion,

I was going off of what phpmyadmin told me, but i totally see the logic in that and will put that on

cheers ill report back on how it all goes.

 

[mrheff]

"Error in query: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 'in = 1 WHERE emails = '"heff@darkink.co.uk"'' at line 1

<br/>ohw bugger

$query = 'UPDATE emails SET in = 1 WHERE emails = '$mail';'

 

 

$query = 'UPDATE emails SET in = 1 WHERE emails = $mail;

 

'UPDATE `helen_mailinglist`.`emails` SET `in` = \'1\' WHERE `emails`.`email` = \''. $mail .'\' LIMIT 1;';

this last one reyturns....

 

Error in query:Query was empty

 

no luck with any of these im afraid

 

I really dont under stand with two top ones... it seems to pick the variable up ok and quotes it in the error message but tell me i have a syntax error.... GrrrRRRR

ANy ideas?

 

[Illusion]

$query="UPDATE emails SET in = 1 WHERE email=".$mail." LIMIT 1";

pass the query as it is , don't change double quotes into single quotes.

[trq]

$query="UPDATE emails SET in = 1 WHERE email=".$mail." LIMIT 1";

pass the query as it is , don't change double quotes into single quotes.

 

That query is invalid. Try...

 

$query="UPDATE emails SET `in` = 1 WHERE email='$mail'";

[mrheff]

Thanks, it seems to go through without any errors like that,

However its not changed the table values.... do you have any other idea?

infact maybe it is a permission.

[mrheff]

no not permisiions either.

[Illusion]

That query is invalid.

why so...........

[Zepo.]

Could it be that your defining $mail before connecting to the database? Try moving it under the database connect.

[mrheff]

im afraid its not that,

 

thanks though,

 

maybe someone could advise another method to do a similar job?

[trq]

However its not changed the table values.... do you have any other idea?

 

Explain exactly what field in what table you want to update with what variable. The problem is you haven't been at all clear in your description of what your trying to do.

[mrheff]

ok my table looks like this

 

 

id | in | email |

----------------------------------------

1 | 0 |ME@ADRESS.COM

2 | 0 |YOU@ADRESS.COM

2 | 0 |YOU@ADRESS.COM

 

WHEN the user clicks their confirmation link which would link to mailer/confirm.php?mail="me@adress.com"

i want it to update the in column to a 1, this will signify that they have confirmed for me and i can

 

select email where in =1

 

and mail to only that result., but this would be part of the mailing script rather than the confirm,

 

So in short i need to change the  "in" to a "1" value depending on the user email which is a _GET from the link.

 

I hope this makes sense thank you for your time and patience man.

[Gamic]

$mail=$_GET['mail'];//make sure you are actually getting the email

//echo ($mail);//debug
//$mail=some_validation_on_mail($mail);//validate all your inputs that you can't trust 

$query="update emails 
      set emails.in=1 
      where emails.email=\"".$mail."\";";

//echo ($query);//debug

$result=mysql_query($query);

(Let me know if this has been said already )

[mrheff]

hey thanks man, i got a band rehearsal now, but ill check that soon as,

im deffo getting the variable...

.. thanks alot ill report back as soon as i can

... oops i left this on....

..

..

... right well afte along spell away and a reassment i have spotted a fatal flaw in my plan!

the in column was set to decimal instead of binary i must of slipped on my mouse, what a numpty!

 

Thank you for all yur help guys,

And Gamic ive not seen it coded like that befor, ive used your syntax for the final one however, i ecpect a few of the answer i was given earlier worked as well, so thats to you guys aswell