Change a value in mysql from a link?

[vbthanks]

Hi,
I've written a mailing list script that sends a confirmation email to the subscriber after details are put into a form (and db) - all working okay.
I'd like to include a link in the confirmation email that changes the 'validated' field in my database from '0' to '1'using 'id' and 'email' as keys.
My db fields are 'id', 'email', 'validated', 'date', 'name', etc, etc...

It's the link i'm having problems with, any help appreciated....
Regards,
Mike

[cyngon]

You would need to write a PHP script that updates the field. Then the link in the email would be to that PHP script.

There is no need to pass both the id and the email fields to the script. Instead, you should just pass the id field.

Here is a script that should get you started:

<?php

mysql_connect('localhost', 'user', 'pass');
mysql_select_db('db_name');
mysql_query("UPDATE table_name SET validated='1' WHERE id='".$HTTP_GET_VARS['id']."'");

?>

And the link would be like:

http://example.com/validate.php?id=123

[vbthanks]

Thanks Bryan,
but how do I get the id number into the link ?
Regards,
Mike

[oodie]

Use mysql_insert_id()

mysql_insert_id() returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query

// insert data to MySQL
mysql_query("INSERT ....");

// get the latest id
$id = mysql_insert_id();

// create link to be put in email, e.g
$link = "http://something.com/process.php?action=validate&id=$id"; 

[vbthanks]

This will not work. What if two people join up, don't verify for a while and another joins and verifies ? The specific id needs to be in the url.
Regards,
Mike

[oodie]

I believe that should work. For example, when user1 join the list, he'll get $id=1 and the link will be (using cyngon's code) http://example.com/validate.php?id=1 When user2 later join, he'll get $id=2 and the link that he gets will be http://example.com/validate.php?id=2 So even after user2 validate his subscription, when user1 click the link in his email, the query that updates the database will be (again, using cyngon's code)

mysql_query("UPDATE table_name SET validated='1' WHERE id='2'"); 

It won't get mixed up with user2's validation. To be on the safer side, you can write-lock the table so nothing can happen between mysql_query("INSERT...") and mysql_insert_id() and you are guaranteed to get the correct id everytime.

Anyone has different thought??

[cyngon]

Assuming the id colum is an AUTO_INCREMENT PRIMARY KEY, you won't have to worry about any conflicts.

Between the code I posted, and oodie's suggestion about using mysql_insert_id(), that should do the trick.