checking record before inserting in sql using php

Question

i am adding records using INSERT statement. now i want to check whether the email has already been registered or already present in the records..if it is already present just give an error otherwise insert a new record.. this is how iam doing it... but the select query is not running...and still adding records without checking.please check my code and please suggest a solution. Thanks :) here is my code for

 manage-users.php
<?php include("../includes/config.php"); ?>
<?php
if ($_SESSION["isadmin"])
{
?>
<!DOCTYPE HTML>
<html>
<head>
<?php include("includes/pre-header.php");?>


<title>Admdin Home</title>
</head>
<body>
<div class="container">
<?php include("includes/header.php"); ?>
<?php include("includes/nav.php"); ?>
<div id="maincontent">

<div class="span-24 last">
<div id="breadcrumbs">
    <a href="">Home</a> >
    <a href="">Manage Users</a> >
    Add New
</div>
</div>
<?php include("includes/manage-users-aside.php"); ?>
<div class="span-18 last">
<h2 class="alt">Add New</h2>
<?php
if (isset($_GET["status"]))
{
if($_GET["status"]==1)
{
?>
<div class="success">
<?php
echo("<strong>User Has Been Added Successfully!</strong>");
?>
</div>
<?php
}
 if($_GET["status"]==2)
{
?>
<div class="success">
<?php
 echo("<strong>User Has Been Edited Successfully!</strong>");
?>
</div>
<?php
}
} 
 if($_GET["status"]==3)
{
echo ("<strong>This Account Already Exixts!. Please add a New One!</strong>");
}
?>
<form method="post" id="form" action="manage-users-action.php">
<label for="email">Email/Username:</label><input id="email" type="text" name="email" value="" class="text" /><br /><br />
<label for="password">Password:</label><input id="password" type="password" name="password"  value="" class="text" /><br /><br />
<label for="firstname">First Name:</label><input id="firstname" type="text" name="firstname" value="" class="text" /><br /><br />
<label for="lastname">Last Name:</label><input id="lastname" type="text" name="lastname"    value="" class="text" /><br /><br />
<label>Type:</label><br />
<input type="radio" name="type" value="S" />Student <br /> <br />
<input type="radio" name="type" value="T" />Teacher<br /><br />
<input type="submit" name="submit"  value="Submit" class="button" />
</form>
</div>
</div>

<?php include("includes/footer.php"); ?>
</div>
</body>

</html>
<?php
}
else
{
    header("Location: ".$fullpath."login/unauthorized.php");

 }
?>

this is manage-users-action.php

<?php include("../includes/config.php");?>
<?php
$fname=$_POST['firstname'];
$lname=$_POST['lastname'];
$type=$_POST['type'];
$email=$_POST['email'];
$pwd=$_POST['password'];
$recoverykey=md5(time());
$encpwd=md5($pwd);
$con=mysql_connect($dbserver,$dbusername,$dbpassword);
if (!$con) { die('Could not connect: ' . mysql_error()); }
mysql_select_db($dbname, $con);

$result= mysql_query("SELECT FROM accounts WHERE (email='".$email."')");
if(!$result){
$sql=("INSERT INTO accounts VALUES   (NULL,'".$email."','".$encpwd."','".$fname."','".$lname."','".$type."','".$recoverykey."')"    );
}
else
{
 header("Location: manage-uesrs.php?status=3");
}
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
else
    {
        header("Location:manage-users.php?status=1");
    }

mysql_close($con);
?>

Why check first? If the database has a unique constraint on that column, try the insert, if a duplicate index error is returned, handle it in code. You save a trip to the DB. — xQbert
– xQbert, Commented Sep 18, 2012 at 1:10
@AlainCollins I would but it doesn't address the specific question. Which is why I left it as a comment. Answers should address the specific question. I do in a round about way; but I don't think it's answerworthy unless the question changes to something like "What's the best way to handle checking for duplicates when inserting records" (my best way would be to have a stored procedure do the work and have the inline SQL removed as it opens the door to SQL injection.) I was just trying to get the author to think about alternatives. — xQbert
– xQbert, Commented Sep 18, 2012 at 1:32
You checked his code and offered a solution as he requested. That's an answer to me, SO-nazis be damned. OK, I'll just upvote your comment then. Sigh. — Alain Collins
– Alain Collins, Commented Sep 18, 2012 at 1:36
@Riu, since you think it's the SELECT that's "not running", have you run the exact SELECT statement by hand? — Alain Collins
– Alain Collins, Commented Sep 18, 2012 at 1:38

Zoltan Toth · Accepted Answer · 2012-09-18 01:01:00Z

5

Instead of

if (!$result) {

try

if ( mysql_num_rows($result) == 0 )

Your query always returns a result - even when there is no record in the DB - that's why your condition never worked.

answered Sep 18, 2012 at 1:01

Zoltan Toth

47.8k12 gold badges132 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Robbie · Accepted Answer · 2012-09-18 02:39:04Z

You actually have several errors in the flow. Zolthan is right, but you'd still end up with two entries in the database as your code would carry on executing after the "header". Always exit(); after a 'header location` call.

You also need to validate / make safe your data (otherwise $retval will be false, and it'll error if you followed Zoltan to the letter.)

Fixing things up :

// Validate you have an valid email
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
     header("Location: manage-uesrs.php?status=ErrorInSQL");    // Note: location should take a full URL. This works in all browsers I know of, but is not strictly correct.
     exit();  // Critical - otherwise you script will continue to run.
}

// Than sanatize your data. Use PDO or mysql; for for now I'll use your code
$email = mysql_real_escape_string($email);
// Repeat for the other fields

$result= mysql_query("SELECT FROM accounts WHERE (email='".$email."')"); 
if (!$result) { 
     header("Location: manage-uesrs.php?status=ErrorInSQL");    // Note: location should take a full URL. This works in all browsers I know of, but is not strictly correct.
     exit();  // Critical - otherwise you script will continue to run.
} else (mysql_num_rows($result) > 0 )  
     header("Location: manage-uesrs.php?status=NotUniqueURL");
     exit();  // Critical - again.
}

// As we're here, we can now do thq SQL as you have
// Remmber mysql_real_escape_string on all variables (or use PDO / mysqli prepared statements)
$sql=("INSERT INTO accounts VALUES   (NULL,'".$email."','".$encpwd."','".$fname."','".$lname."','".$type."','".$recoverykey."')"    ); 
if (mysql_query($sql,$con)) {
    header("Location:manage-users.php?status=1"); 
    exit();   // ;)
} else {
    header("Location: manage-uesrs.php?status=ErrorInSQL");
    exit();   // ;)
}

But, as suggested by xQbert, your best approach is in one query.

Create a "unique" index in the database on the field "email".

// Validate you have an valid email
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
     header("Location: manage-uesrs.php?status=ErrorInSQL");    // Note: location should take a full URL. This works in all browsers I know of, but is not strictly correct.
     exit();  // Critical - otherwise you script will continue to run.
}

// Than sanatize your data. Use PDO or mysql; for for now I'll use your code
$email = mysql_real_escape_string($email);
// Repeat for the other fields

// Dive traight into the SQL
// Remmber mysql_real_escape_string on all variables (or use PDO / mysqli prepared statements)
$sql=("INSERT INTO accounts VALUES   (NULL,'".$email."','".$encpwd."','".$fname."','".$lname."','".$type."','".$recoverykey."')"    ); 
if (mysql_query($sql,$con)) {
    header("Location:manage-users.php?status=1"); 
    exit();   // ;)
} else {
    // This could error because it is in use, or you have error in your sql. So debug with mysql_error() initially to get your SQL correct, then when you're sure that is right, assume any error is duplicate e-mail. You could alsocheck with with mysql error codes to be extra safe.  
    header("Location: manage-uesrs.php?status=AlreadyInUse");
    exit();   // ;)
}

Collectives™ on Stack Overflow

checking record before inserting in sql using php

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related