Check Mysql table for existing user

Hey Guys,

I'm at it again. I'm playing around with Forms and entering information inputted into the form and then writing that information into a table in my database. I've done all the form validation and code to insert the information into the table. One snag I ran into is checking the table to see if the person already exists in the table. I'm doing this by Social Security Number since this would be the easiest way to check if the user exists. My code for some reason isn't working. I have been able to enter the same person with the same SSN multiple times. I think the issue is in the $check query and the results returned. It seems as if it is constantly returning 0 results. Leading me to think using the variable $ssn in the Select query is my problem. I have tried removing the quotes, using ticks and still the same result. I even echoed the value of the $ssn variable and it has the right value assigned to it. See code below.


<?php
//Connect to Database
$connection = mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');
if (!$connection) {
    die('Could not connect: ' . mysql_error());
}


//Sanitize Entries.....
$firstname = mysql_real_escape_string($_POST['firstname']);
$lastname = mysql_real_escape_string($_POST['lastname']);
$address = mysql_real_escape_string($_POST['address']);
$address1 = mysql_real_escape_string($_POST['address1']);
$city = mysql_real_escape_string($_POST['city']);
$state = mysql_real_escape_string($_POST['state']);
$zip = mysql_real_escape_string($_POST['zip']);
$email = mysql_real_escape_string($_POST['email']);
$phone = mysql_real_escape_string($_POST['phone']);
$ssn = mysql_real_escape_string($_POST['ssn']);

$check = "SELECT * FROM students WHERE students.ssn = '$ssn'" or die ("Error in query: $check. ".mysql_error());
$rs = mysql_query($connection, $check);
$data = mysql_fetch_array($rs, MYSQL_NUM);

if($data[0] > 1) {
    echo "User Already in Exists<br/>";
      header('Refresh: 5;url=student.php');
}

else
{

//Setup Query to insert information
mysql_query("INSERT INTO students (firstname, lastname, address, address1, city, state, zip, email, phone, ssn) VALUES ('$firstname', '$lastname', '$address', '$address1', '$city', '$state', '$zip', '$email', '$phone', '$ssn')");
echo $firstname . ' ' . $lastname . ' ' . "has been entered into the database";

                                                
header('Refresh: 5;url=student.php');
}

?>
rraymond1130Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pklucasCommented:
Try removing the single quotes around the $ssn in your select statement.
0
Ray PaseurCommented:
You can make a SELECT query to try to match a column in the table.  If the number of rows is greater than zero, you know what already exists in the table.

You can mark a column UNIQUE and MySQL will throw error #1062 if an attempt is made to insert a duplicate value into the column.
0
Ray PaseurCommented:
This dog won't hunt.  It's just an assignment statement, so there is no "or" condition.

$check = "SELECT * FROM students WHERE students.ssn = '$ssn'" or die ("Error in query: $check. ".mysql_error());

You must get off the MySQL extension.  PHP is doing away with MySQL.  This article teaches how to connect, select and run queries, and to test for errors and count the number of rows.  It maps MySQL, MySQLi and PDO extensions.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Ray PaseurCommented:
This needs remediation, too.  AntiPractice #26

$data = mysql_fetch_array($rs, MYSQL_NUM);
0
Ray PaseurCommented:
You can make your life a lot simpler if you never overload the function arguments with fragments of data, and if you test all function return values.  Use the exact minimum number of data elements.  Example of what not to do:

mysql_query("INSERT INTO students (firstname, lastname, address, address1, city, state, zip, email, phone, ssn) VALUES ('$firstname', '$lastname', '$address', '$address1', '$city', '$state', '$zip', '$email', '$phone', '$ssn')");

The reason to avoid statements like that is that you cannot print out the fully resolved query string, so it becomes impossible to debug (AntiPractice #9a).  And without retrieving the result from the function your script cannot know if the function worked (AntiPractice #23)!  Here's a better way:

$sql = "INSERT INTO students (firstname, lastname, address, address1, city, state, zip, email, phone, ssn) VALUES ('$firstname', '$lastname', '$address', '$address1', '$city', '$state', '$zip', '$email', '$phone', '$ssn')";
$res = mysql_query($sql);

After your script runs the query, there will be a value in $res and you can test for success.
0
Ray PaseurCommented:
This may not give you anything:

if($data[0] > 1) {
    echo "User Already in Exists<br/>";
      header('Refresh: 5;url=student.php');
}

The reason it may be useless goes to the nature of PHP output and server output buffering.  If there is not enough information to fill a buffer, the server may not send the buffer until the PHP script completes.

The other problem with this structure is that it produces browser output before the header() function is called.  It's a law of HTTP that all headers must come first and be complete before any browser output is created, so the header() will fail in this case.  Unless you've got PHP output buffering in place.  If the output buffering is in place, then the header will be sent, and the script will keep right on running until it ends, then the browser output stream will be sent to the client.  Whether it appears on the client's screen long enough for someone to read it is anybody's guess.
0
Chris StanyonWebDevCommented:
Ray's already alluded to this, but the best-practice approach here would be to set a UNIQUE index on your ssn column, and then catch the error. Here's a simple example using PDO (you need to get away from using the mySQL library anyway!)

<?php 
try {
	//connect to the DB
	$dbh = new PDO("mysql:host=localhost;dbname=yourDB", "yourUserName", "yourPassword");

	//prepare your INSERT statement using named parameters
	$stmt = $dbh->prepare("INSERT INTO students (firstname, ssn) value (:firstname, :ssn)");

	//setup the data to be inserted
	$data = array(
		'firstname'	=>	$_POST['firstname'],
		'ssn'		=>	$_POST['ssn']
	);
	
	//lets try the insert
	$result = $stmt->execute($data);
	
	//did it succeed?
	if ($result) {
		//yep!
		echo "Insert was successful";
	
	} else {
		//apparently not!
		//get the error info
		$err = $stmt->errorInfo();
		
		//error 1062 is a duplicate value on a UNIQUE column!
		if ($err[1] == 1062) echo "User Already Exists!";

		//lets see all of the error info, just for fun
		var_dump($err);
	}

} catch(PDOException $e) {
	//catch any PDO Errors we might have
	echo $e->getMessage();
}
?>

Open in new window

0
rraymond1130Author Commented:
pklucas, I tried that with no success. Ray I marked the SSN column in the table as UNIQUE and that worked, but how do you capture the error code that is thrown so you can prompt a message to the user?
0
Chris StanyonWebDevCommented:
raymond - read my previous code ;)
0
Chris StanyonWebDevCommented:
Actually, here's a neater way of doing it using the try / catch block :)

<?php
try {
	//setup the DB connection
	$dbh = new PDO("mysql:host=localhost;dbname=yourDB", "yourUserName", "yourPassword");
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	//prepare your INSERT statement used name parameters
	$stmt = $dbh->prepare("INSERT INTO students (firstname, ssn) value (:firstname, :ssn)");

	//setup the data to be inserted
	$data = array(
		'firstname'	=>	$_POST['firstname'],
		'ssn'		=>	$_POST['ssn']
	);
	
	//lets try the insert
	$result = $stmt->execute($data);
	
	echo "Insert was successful";

} catch (PDOException $e) {

	if ($e->errorInfo[1] == 1062) {
		//duplicate entry
		echo "Duplicate Entry";
	} else {
		//some other error
		echo $e->getMessage();
	}
}
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rraymond1130Author Commented:
Thanks Chris that worked.....
0
PortletPaulfreelancerCommented:
an SQL only observation:

in your original, you send this as a query:

SELECT * FROM students WHERE students.ssn = '$ssn'"

but all you wanted was true or false, so you should only ask for that:

SELECT count(students.ssn) as tf FROM students WHERE students.ssn = '$ssn'"

Please don't be a "select *" addict, specify the fields (columns) you need.

I think there is plenty of other PHP comments above that indicate this check query isn't really needed, and from an SQL perspective placing a unique constraint on students.ssn would certainly be the most reliable method. Then any inserts that you attempt that might have produced a duplicate will be rejected.

Don't forget latency when building these systems, it takes time between your check and the insert and the commit of that insert, those tiny gaps could be a problem in a multi-user environment. Best to have this data integrity matter dealt with in the data layer.

Ray, every time I see "PHP is doing away with MySQL" (or similar) I have to remind myself no, MySQL the db is OK, it's the PHP MySQL library (or API if that's the preferred term) that is being dropped :)
I'd not want to see a flurry of folks looking to find alternatives to MySQL (the db)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.