SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Change a value in mysql from a link?

    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
    "You know what you know - but that's all you know!"

  2. #2
    SitePoint Evangelist cyngon's Avatar
    Join Date
    Aug 2001
    Location
    Livonia, MI, USA
    Posts
    513
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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:

    Code:
    http://example.com/validate.php?id=123
    Last edited by cyngon; Jun 19, 2002 at 18:56.

  3. #3
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Bryan,
    but how do I get the id number into the link ?
    Regards,
    Mike
    "You know what you know - but that's all you know!"

  4. #4
    SitePoint Zealot oodie's Avatar
    Join Date
    Jul 2000
    Location
    Misty Mountain
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use mysql_insert_id()
    mysql_insert_id() returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query
    PHP Code:
    // 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"

  5. #5
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    "You know what you know - but that's all you know!"

  6. #6
    SitePoint Zealot oodie's Avatar
    Join Date
    Jul 2000
    Location
    Misty Mountain
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)
    PHP 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??

  7. #7
    SitePoint Evangelist cyngon's Avatar
    Join Date
    Aug 2001
    Location
    Livonia, MI, USA
    Posts
    513
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •