CODEWITHSUNDEEP
phpmysql
jonasd
jonasd

Reputation: 29

PHP: looking to query if email is unique, then if so, query to add the record to database

Am not experienced, so appreciate any help.

Am looking to build a PHP to confirm that registration of user has a unique email address. If unique, then to add to sql database, and echo out respective value.

This is what I have - the echo value is produced if the email address is duplicate, but the echo value is not produced and the db not updated if it's unique. 

<?php
$email= $_GET["email"];
$firstname = $_GET["firstname"];
$lastname = $_GET["lastname"];

$con = mysql_connect("user","db","pass");
mysql_select_db("db");

$query1 = "SELECT * FROM `db` WHERE `Email` = '$email'";

$result = mysql_query($query1);



if ( mysql_num_rows ($result) == 1 )
{$value="Already registered";

}
else
{
 mysql_query("INSERT INTO db (Email, FirstName, LastName) VALUES ('$email','$firstname','$lastname')");
mysql_close($con);

$value="Confirmed";


}

echo $value;

Upvotes: 2

Views: 5358

Answers (4)

Vitalii Maslianok
Vitalii Maslianok

Reputation: 1631

To solve this problem I would use Ajax:

php check_email.php

$email= $_POST["email"];
$firstname = $_GET["firstname"];
$lastname = $_GET["lastname"];
...
$query1 = "SELECT * FROM `db` WHERE `Email` = '$email'";
$result = mysql_query($query1);
if (mysql_num_rows ($result)){
  echo "yes";
} else {
  mysql_query("INSERT INTO db (Email, FirstName, LastName) VALUES ('$email','$firstname','$lastname')");
  echo "no";
}

HTML:

    <form method="post" action=""/>
      <input name="email" type="text" id="email" value=""/>
      <input name="firstname" type="text" id="firstname" value=""/>
      <input name="lastname" type="text" id="lastname" value=""/>
      <input name="Submit" type="button" id="submit" value="Go"/>
      <div id="msg"></div>
    </form>
    <!--load latest version of jquery-->
    <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
    <script>
    //JS (use jquery):
    $("#submit").click(function () {
        $.ajax({
          type: "POST",
          url: "check_email.php",
          dataType: "text",
          //send data to php
          data: {email:$("#email").val(),firstname:$("#firstname").val(),lastname:$("#lastname").val()},
          //get answers
          success: function (response) {
            if(responce=="yes") $("#msg").html("email exists!")
            else if (responce=="no") $("#msg").html("Email was successfully added to the database!")
          },
          error: function (xhr, ajaxOptions, thrownError) {
            alert(xhr.status);
            alert(thrownError);
          }
        });
    });
</script>

Upvotes: 0

noj
noj

Reputation: 6759

$query1 = mysql_query("SELECT COUNT(*) AS num FROM `db` WHERE `Email` = '$email'");
$result = mysql_fetch_assoc($query1);

if ($result['num'] == 1)
{
   // already registered
}
else
{

}

Also you've completely forgot about sql injection. You need to ensure all values supplied by the user are safe to run through the database. You can do this using mysql_real_escape_string(). i.e:

$email = mysql_real_escape_string($_GET['email']);

Upvotes: 0

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

Instead of getting all rows, take only one of them like this. It will save lots of memory.

$query1 = "SELECT 1 FROM `db` WHERE `Email` = '$email' LIMIT 1";

Then the following line 

if ( mysql_num_rows ($result) == 1 )

will be logically correct. Otherwise if there are 2 or more records in the database, the result is false. With my fix this if will be logical.

The other solution would be to select the number of the rows, but since the emails are to be unique in the database, there is no point doing something like 

$query1 = "SELECT COUNT(*) FROM `db` WHERE `Email` = '$email'";

However this way, instead of checking the amount of rows in the result, you can check the result without fearing it being empty. But again, since your database's logic is to have one email only once, there is no point checking how many of them there are since the result will be either 0 or 1 :)

Upvotes: 1

Anant Dabhi
Anant Dabhi

Reputation: 11104

  1. make sure your code not give any error

  2. as your if condition

    if ( mysql_num_rows ($result) == 1 )

that means if you have a more then >1 recoded it false so change it to

if ( mysql_num_rows ($result) >0 )

Upvotes: 0

Related Questions