Checking if a field exists in a MySQL table with PHP

[Democrazy]

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 |
+----------------+------+--------+
| [email protected] | 1 | 0 |
| [email protected] | 0 | 1 |
+----------------+------+--------+

I add to the table using this:

mysql> INSERT INTO newsletters (email, mens) VALUES ('[email protected]', 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 ('[email protected]', 1);

When I do, I get the following error:

ERROR 1062 (23000): Duplicate entry '[email protected]' 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.

[Chris Hick]

Idk why you want to update the same email address??
But you can do this:

// 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
} 

[Democrazy]

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.

[shkhanal]

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.

[Democrazy]

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.

[Chris Hick]

Do the same thing as above but change the update to this:

$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.

[Democrazy]

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);
}


[Fou-Lu]

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;

[Democrazy]

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!

[Old Pedant]

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.

[hajimemasho]

Idk why you want to update the same email address??
But you can do this:

// 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.