Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Banned
    Join Date
    Sep 2011
    Posts
    140
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Checking if a field exists in a MySQL table with PHP

    Note to the mod: I have posted this in both the PHP and MySQL area because the post depends on both technologies.


    I have a table called newsletters that looks like this:

    +----------------+------+--------+
    | email | mens | womens |
    +----------------+------+--------+
    | test1@test.com | 1 | 0 |
    | test2@test.com | 0 | 1 |
    +----------------+------+--------+
    I add to the table using this:
    mysql> INSERT INTO newsletters (email, mens) VALUES ('test1@test.com', 1);
    Lets says a user has already registered for the mens newsletter, and they now want to register for the womens, I guess I would use this command:
    mysql> INSERT INTO newsletters (email, womens) VALUES ('test1@test.com', 1);
    When I do, I get the following error:
    ERROR 1062 (23000): Duplicate entry 'test1@test.com' for key 'PRIMARY'
    I am using PHP to insert this information (from form data), so to work around this, I am going to use an "if" argument to check if the e-mail address already exists in the table, and if it does, then to use an "update/alter" command to update the existing entry.
    Does anyone know a command for PHP to check if something exists in a data base and then update it accordingly?
    Something like:

    if email $email exists, then {alter table newsletters change column womens VALUES (1);}
    elseif email $email null {INSERT INTO newsletters (email, womens) VALUES ('$email', 1);}
    I know that command is deeply flawed, I am only a few weeks old in the MySQL world.
    Last edited by Democrazy; 09-10-2011 at 05:09 AM.

  • #2
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    486
    Thanks
    10
    Thanked 33 Times in 32 Posts
    Idk why you want to update the same email address??
    But you can do this:

    PHP Code:
    // first query for a matching email 
    $query "SELECT email FROM newsletters WHERE email ='" $_POST['email'] . "'";
    // get the results
    $results mysql_query($query);
    // if results returns true then update the email field 
    if($results) {
    $query "UPDATE newsletters
    SET email = '"
    .$_POST['email']."'
    WHERE email='"
    .$_POST['email']."'";
    $results mysql_query($query);
    } else {
    // insert it into the database

    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^

  • #3
    Banned
    Join Date
    Sep 2011
    Posts
    140
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Hey Chris,

    Thanks for your reply!

    There has been a misunderstanding due to my behalf. Sorry. What I meant was, how I can change the field for "mens" or "womens" for the primary field (which is "email").

    So, someone has already signed up for the mens newsletter, and the fields sit at (email)(1)(0), and now that they want to sign upto the womens newsletter as well, I need to change the fields to (email)(1)(1).

    Note: I updated my original post to better clarify what I meant, if you can be bothered reading it again.
    Last edited by Democrazy; 09-10-2011 at 05:00 AM.

  • #4
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    once you have entered a record for mens you can't enter same email address for woman as well. What you can do is perform a check for email address first and if it is already there use UPDATE command rather than INSERT command. Insert command creates new record. When that person has already subscribed for male magazine, his email address is already in your table. So, use UPDATE command that will just alter the woman field of the record rather than inserting new record.

  • #5
    Banned
    Join Date
    Sep 2011
    Posts
    140
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Yes, shkhanal. Thats correct.
    What I am asking is how to use the update command exactly? I have tried time and time again, but getting nothing but errors.

  • #6
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    486
    Thanks
    10
    Thanked 33 Times in 32 Posts
    Do the same thing as above but change the update to this:

    PHP Code:
    $query "UPDATE newsletters
    SET mens='"
    .$_POST['mens']."' AND womens='".$_POST['womens']."' 
    WHERE email='"
    .$_POST['email']."'"
    Also, I just used the $_POST variable to show an example. In the real environment, you need to sanitize that $_POST variable before putting it in the database.
    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^

  • #7
    Banned
    Join Date
    Sep 2011
    Posts
    140
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Hey dude,

    I am getting this error:
    PHP Parse error: syntax error, unexpected T_STRING on line 54
    ... which is:
    INSERT INTO newsletters (email, mens) VALUES($_POST['e-mail'], 1);
    This is the whole code:

    $query = "SELECT email FROM newsletters WHERE email ='" . $_POST['email'] . "'";
    $results = mysql_query($query);
    if($results) {
    $query = "UPDATE newsletters SET mens = '".$_POST['mens']."' WHERE email = '".$_POST['email']."'";
    }
    else {
    INSERT INTO newsletters (email, mens) VALUES($_POST['e-mail'], 1);
    }
    Last edited by Democrazy; 09-10-2011 at 06:04 AM.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    There are only two ways to handle this in mysql and PHP. The first (and best) option is to first query for the record count using a simple SELECT count(email) AS cnt FROM newsletters WHERE email = $email and retrieve the cnt. If its 0, then you insert, otherwise you update.
    The other is to use either REPLACE or ON DUPLICATE UPDATE syntax. I'd choose the latter: INSERT INTO newsletters (email, mens, womens) VALUES ($email, $mens, $womens) ON DUPLICATE KEY UPDATE mens = $mens, womens = $womens;

  • Users who have thanked Fou-Lu for this post:

    Democrazy (09-10-2011)

  • #9
    Banned
    Join Date
    Sep 2011
    Posts
    140
    Thanks
    17
    Thanked 0 Times in 0 Posts
    I ended up using this approach as I find it allot easier to understand in my mind:

    INSERT INTO newsletters (email, mens, womens) VALUES ($email, $mens, $womens) ON DUPLICATE KEY UPDATE mens = $mens, womens = $womens;
    It worked and I thank you so much for your help! I hope good things come to you!

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    I'm curious why FouLu said
    The first (and best) option is to first query for the record count ...
    That method means you are making *TWO* round trips from PHP to MySQL to accomplish a single task.

    The second method, and the one you chose, means only ONE round trip and is much more efficient. Granted, there are some circumstances where the syntax for ON DUPLICATE is less than clear, but for something as simple as this it seems perfect.

    I would choose ON DUPLICATE every time until/unless it started getting too complex.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Chris Hick View Post
    Idk why you want to update the same email address??
    But you can do this:

    PHP Code:
    // first query for a matching email 
    $query "SELECT email FROM newsletters WHERE email ='" $_POST['email'] . "'";
    // get the results
    $results mysql_query($query);
    [
    B][COLOR="Red"]// if results returns true then update the email field [/COLOR][/B]
    if($results) {
    $query "UPDATE newsletters
    SET email = '"
    .$_POST['email']."'
    WHERE email='"
    .$_POST['email']."'";
    $results mysql_query($query);
    } else {
    // insert it into the database

    'mysql_query' returns true on succes only for INSERT, UPDATE, DELETE, DROP. For other kind of statements returns a resource.
    So if you try to echo $results, you'll have the same result whether the email exists in the table or not and you'll end up updating the table either way. You have to use mysql_fetch_array() in order to put the data in an array and then test if the array contains the value you were
    looking for.
    Last edited by hajimemasho; 09-21-2011 at 09:11 AM.


  •  

    Posting Permissions

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