Needing to verify record doesn't already exist.

[magickfirebird]

I'm at a point that I need to verify that the record that is being entered doesn't already exist. I seem to be overlooking in the tutorials how to do this comparison. I'm also not sure if it can be done at the same time as making sure a required field is not empty of if it needs to run before all of the check for empty fields is ran.

 

if (empty($_POST["loginid"]))

{$loginidErr = "A login ID is required";}

else {$loginid = reqcheck($_POST["loginid"]);

if (!preg_match("/^[a-zA-Z ]*$/",$loginid))

{$loginidErr = "Only letters and white space allowed";}}

[justsomeguy]

You can send a select query to look up records with that value, and if any are returned then it already exists.

[magickfirebird]

Ok, my comparison is part Wof a registration page for my website. I was able to write code that if loginid was already in the table it came back with an error message that Login ID already existed. Except that when I put in a different loginid it still came up with the error message. At the current time when the form is submitted it shows up below the form to go over before submitting into the table. When I put in the new loginid it does change to information below but it still has the error message.

 

Now the problem is, that while trying to fix this problem I have a new problem. It currently shows the error message that it is required for each field, and when I enter the information and it goes below the error messages leave except for the loginid error message which changes to login ID already exists (when it doesn't).

 

I'm sure it's just something that I left out or added to much to. I would appreciate your looking over some of the code for me.

 

$sql = "SELECT loginid FROM test5";

$result = $conn->query($sql);

{if (exists)

{$loginidErr = "Login ID already exist. Select new Login ID.";}

else {$loginid = reqcheck($_POST["loginid"]);}

if (empty($_POST["firstname"]))

{$firstnameErr = "First Name is required";}

else {$firstname = reqcheck($_POST["firstname"]);

if (!preg_match("/^[a-zA-Z ]*$/",$firsename))

{$firstnameErr = "Only letters and white space allowed";}}

if (empty($_POST["lastname"]))

{$lastnameErr = "Last Name is required";}

else {$lastname = reqcheck($_POST["lastname"]);

if (!preg_match("/^[a-zA-Z ]*$/",$lastname))

{$lastnameErr = "Only letters and white space allowed";}}

.

.

.

function query($data)

{$data = trim($data);

$data = stripslashes($data);

$data = htmlspecialchars($data);

return $data;}

function reqcheck($data)

{$data = trim($data);

$data = stripslashes($data);

$data = htmlspecialchars($data);

return $data;}

.

.

.

<span class="error">* required field.</span>

<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post">

<br /> First Name: <input type="text" name="firstname" value="<?php echo $firstname;?>" />

<span class="error">* <?php echo $firstnameErr;?></span><br /><br />

Last Name: <input type="text" name="lastname" value="<?php echo $lastname;?>" />

<span class="error">* <?php echo $lastnameErr;?></span><br /><br />

Login id: <input type="text" name="loginid" value"<?php echo $loginid;?>" />

<span class="error">* <?php echo $loginidErr;?></span><br /><br />

.

.

[justsomeguy]

$sql = "SELECT loginid FROM test5";$result = $conn->query($sql);{if (exists)

You're selecting all rows from the table, not looking for a specific one. Use a WHERE clause in the SQL query to filter the results. Also, "if (exists)" - you're not using a variable (or it would be $exists, but you don't have a variable called $exists), so that might always be true if it treats exists as a failed constant lookup and converts to a string. Add this to the top of your code:

ini_set('display_errors', 1);error_reporting(E_ALL);

Also, that query function you posted does not do anything to protect against SQL attacks. You should look into PDO and start using prepared statements.

[magickfirebird]

Before starting on my registration page I created a few tables and was able to insert into them. Then I worked on printing the files out from the tables. After both of these I started working on the registration page. While putting together my print page I did use the PDO code that I found on this site. After reading your last post I went back to my insert programs to make it into a prepared statement program. Of course, the first few times I ran it, it came up with errors, mostly typing errors, normal stuff. Now it runs without any errors and shows the message that a new record was created but in fact it was not inserted into the table. Would you look over the code for me? Thank you.

 

// create connection

$conn = new MySQLi($servername, $username, $password, $dbname);

// check connection

if ($conn->connect_error) {die("Connection failed:" . $conn->connect_error);}

// prepare and bind

$stmt = $conn->prepare("INSERT INTO test5 (firstname, lastname, loginid, email, password)

VALUES (?, ?, ?, ?, ?)");

$stmt->bind_param("sss", $firstname, $lastname, $loginid, $email, $password);

// set parameters and execute

$firstname = "jon";

$lastname = "see";

$loginid = "tank";

$email = "jon@exeee.com";

$password = "super";

$stmt->execute();

echo "New records created successfully";

$stmt->close();

$conn->close();

[Ingolme]

"mysqli" should all be lower case.

$conn = new MySQLi($servername, $username, $password, $dbname);

 

You only have type identifiers for three variables, but you're binding five:

$stmt->bind_param("sss", $firstname, $lastname, $loginid, $email, $password);

 

The only way this code could be running without errors is if you have error reporting disabled.

[magickfirebird]

My page comes up with the form as I intend for it to look. I am opening with PDO:

try {$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

echo "Connect successfully";}

catch(PDOException $e)

{echo "Connection failed: " . $e->getMessage();}

 

And it checks to make sure the field is not empty and that correct:

if (empty($_POST["lastname"]))

{$lastnameErr = "Last Name is required";}

else {$lastname = regcheck($_POST["lastname"]);

if (!preg_match("/^[a-zA-Z ]*$/",$lastname))

{$lastnameErr = "Only letters and white space allowed"; }}

 

if (empty($_POST["loginid"]))

{$loginidErr = "Login ID is required";}

else {$loginid = regcheck($_POST["loginid"]);

if (!preg_match("/^[a-zA-Z ]*$/",$loginid))

{$loginidErr = "Only letters and white space allowed"; }}

 

I am back to trying to compare loginid that is entered into the form to the loginid’s that have already been entered into the table, test5. This is the last try that I’ve made, and of course it is not working without errors. :

 

// This is where the form field is checked to see if the field is blank or filled

if (empty($_POST["loginid"]))

{$loginidErr = "Login ID is required";}

else {$loginid = regcheck($_POST["loginid"]);

if (!preg_match("/^[a-zA-Z ]*$/",$loginid))

{$loginidErr = "Only letters and white space allowed"; }}

// and to make sure that the login id used is not already being used.

$sql = "SELECT 'loginid' FROM test5";

$result = $conn->query($sql);

if (compare($loginid = "loginid"))

{$loginidErr = "Login ID already exist. Select new Login ID.";}

else {$loginid = reqcheck($_POST["loginid"]);}

[justsomeguy]

$sql = "SELECT 'loginid' FROM test5";

You're still doing the same thing. Which login ID are you looking for? Right now you're telling it to select all of them, just tell it to select the one you're looking for. Also, do not put field names in quotes, that query is going to return the text "loginid", not the contents of that field in the table.