Link to home
Start Free TrialLog in
Avatar of rraymond1130
rraymond1130

asked on

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

?>
Avatar of pklucas
pklucas
Flag of United States of America image

Try removing the single quotes around the $ssn in your select statement.
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.
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.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
This needs remediation, too.  AntiPractice #26

$data = mysql_fetch_array($rs, MYSQL_NUM);
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.
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.
Avatar of Chris Stanyon
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

Avatar of rraymond1130
rraymond1130

ASKER

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?
raymond - read my previous code ;)
ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Chris that worked.....
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)