Check if firstname and lastname exist before inserting records

[php-newbies]

I have a customer registration form up and running, the form allows customers to complete the forms and the data is then inserted into mysql db table via php form. I want to prevent duplicates records from already existing customers from creating a new records instead I want the customers to update their existing records in the backend. That is allow them to complete the form as normal, once they hit the submit button, the code should check if the customer first name and last name is already in the database if yes then it should update their records and if not it should insert new record to the table.

 

I do not want to alert the customer that their records already exist. I do not know where to start. Please help

 

Thank you so much in advance for your help.

[litebearer]

Have you attempted to write any code to accomplish your goal?

[php-newbies]

Have you attempted to write any code to accomplish your goal?

 

 

Hello litebearer, many thanks for your response. I have this code for my customer.php

 <?php
//connect  to the database 


$con = mysql_connect("localhost","usrn","pwd");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydb", $con);

$Firstname = ['Firstname'];
$Lastname = ['Lastname'];
$Address = ['Address'];
$Postcode = ['Postcode'];
$Phone = ['Phone'];
$Email = ['Email'];
$Problem =['Problem'];


mysql_query("INSERT INTO Customers (FirstName, LastName, Address, Postcode, Phone, Email, Problem)
VALUES ('$Firstname', '$Lastname', '$Address', '$Postcode', '$Phone', '$Email', '$Problem')");

?>

 

Need help to check  if the customer firstname and lastname already exist before inserting the records and if already exist it should not create a new id instead just update the table.

[Pikachu2000]

What you're thinking of doing is not at all practical. I know several people named John Smith.

[Psycho]

What you're thinking of doing is not at all practical. I know several people named John Smith.

I agree 100%. In addition to different people with the same names, what about people that use different versions of their name? For example, you might have someone that uses "Michael" one time and then "Mike" another time. You then have two records for the same person.

 

I understand what your concern is, but it is ill advised to automatically update a user's data because you *think* it is them. Pretty dangerous. I'll offer a suggestion for an alternative though, which does run contrary to your requirements - but it is a better approach.

 

When a user signs up do a search for any records that match ((FirstName AND LastName) OR emailAddress). You can also do phone number, or whatever makes sense for your application. If there are no matches then create a new record. If there are any matches, then provide the user some text such as:

 

Your data appears to match one or more existing users below. If your record is listed below and you are wanting to update your data , click the Update button next to your record. If you do not have an existing record, click the Add button.

 

Then list out each possible match providing enough detail for the user to recognize that it is their record: name, address, email, phone, etc. If the user clicks the "Update" button take them to a page to update thier data (you can even pre-poipulate the modified data that they chnaged on the previous page). Not sure what you mean by

I want the customers to update their existing records in the backend

But, if you mean a different page for updating, then this will work elegantly.

[php-newbies]

Thanks for your suggestion. Actually I have update.php to update existing records.  I think to check for firstname and phone number duing registration will be a good idea and if both matches then it should open up update.php automatically. But do not know how to link this files together.

 

Thanks ahain for your time

[Psycho]

Thanks for your suggestion. Actually I have update.php to update existing records.  I think to check for firstname and phone number duing registration will be a good idea and if both matches then it should open up update.php automatically. But do not know how to link this files together.

 

Well, I was suggesting Firstname+Lastname OR Phone Number. But, use whatever combinations make sense for your scenario.

 

To implement this should be quite easy. As I said, if there are any possible matches, list them out with a button to take the user to the "update.php" page. In fact, I would create a form for each possible match with hidden fields containing the data the user has just entered. Then on the update.php page you could modify the page to check if data was posted to it from the add page. If so, run a query on the passed record id, then create the update form with the data from the database - but overwrite anything (in the form fields) with what the user entered (and passed) from the add page. Then the user just needs to click the submit button for the edit script to submit the changes.

[php-newbies]

Hi again. I have this code up and running. Basically I have html form page with 2 text boxes namely Firstname and lastname plus submit button. Now when the user enter a random name or just hit the submit button this page still loads up though they are blank fields. What I want now is that I want to block the page from loading the fields if the user enter the wrong name or submitting an empty field instead display an error message. Many thanks

 

My code so far is this.

 

<?php


  //connect  to the database 

$con = mysql_connect("localhost","usrn","pwd");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydb", $con);

$Firstname = $_POST['Fname'];
$Lastname = $_POST["Lname"];
$result = mysql_query ("select * from Customer_Registration WHERE Firstname = '$Fname' AND Lastname = '$Lname' ");




$row = mysql_fetch_row($result);
$cf_id = $row[0];
$Firstname = $row[6];
$Lastname = $row[7];
$Address = $row[8];
$Postcode = $row[9];
$Phone = $row[10];
$Email = $row[11];
$Customer_Type = $row[12];
$Computer_type = $row[13];
$Computer_maker = $row[14];
$Model = $row[15];
$OS = $row[16];
$Appointment_date = $row[17];
$Problem = $row[18];

?>

<?php print( '<a href="http://www.gopcss.com/">Click here to go back to home page <p></a>');?>

        <form action="update-Exist-cus.php" method="post">

	<table width="30%" border="2" cellspacing="0" cellpadding="8">

	<tr><td width="" class="FormText">Customer ID:</td>

	<td width=""><?php echo $row[0]; ?></td></tr>

<tr><td width="10%" class="FormText">First name:</td>
	<td width=""><?php echo $row[6]; ?></td></tr>


<tr><td width="10%" class="FormText">Last name:</td>
	<td width=""><?php echo $row[7]; ?></td></tr>



<tr><td width="10%" class="FormText">Address:</td>
<td width="10%"><input  name="Address" type="text" value="<?php echo $Address; ?>"?> </td></tr>

<tr><td width="10%" class="FormText">Postcode:</td>
<td width="10%"><input  name="Postcode" type="text" value="<?php echo $Postcode; ?>"?> </td></tr>


<tr><td width="10%" class="FormText">Phone:</td>
<td width="10%"><input  name="Phone" type="text" value="<?php echo $Phone; ?>"?> </td></tr>

<tr><td width="10%" class="FormText">Email:</td>
<td width="10%"><input  name="Email" type="text" value="<?php echo $Email; ?>"?> </td></tr>

<tr><td width="10%" class="FormText">Customer:</td>
<td width="10%"><input  name="Customer_Type" type="text" value="<?php echo $Customer_Type; ?>"?> </td></tr>

<tr><td width="10%" class="FormText">Computer :</td>
<td width="10%"><input  name="Computer_type" type="text" value="<?php echo $Computer_type; ?>"?> </td></tr>

<tr><td width="10%" class="FormText">Manufactural:</td>
<td width="10%"><input  name="Computer_maker" type="text" value="<?php echo $Computer_maker; ?>"?> </td></tr>

<tr><td width="10%" class="FormText">Model:</td>
<td width="10%"><input  name="Model" type="text" value="<?php echo $Model; ?>"?> </td></tr>

<tr><td width="10%" class="FormText">Operating System:</td>
<td width="10%"><input  name="OS" type="text" value="<?php echo $OS; ?>"?> </td></tr>

<tr><td width="10%" class="FormText">Appointment:</td>
<td width="10%"><input  name="Appointment_date" type="text" value="<?php echo $Appointment_date; ?>"?> </td></tr>

  <tr><td width="10%" class="FormText">Problem:</td>
<td width="10%"><textarea name="Problem" rows="10" cols="20" ><?php echo $Problem; ?></textarea></td></tr>




<td width="10%"><input name="submit"  value="submit"  type="submit" <br> <input type="Submit" value="Cancel"</br></td>

<td width="10%"><input type="hidden" name="cf_id" value="<?php echo $row[0]; ?>" /></td></tr>
        </form>
        <?php



?>  

[php-newbies]

I am unsure how to accomplish this.

[Psycho]

You should have your form page POST back to itself.

 

The page should check if data was posted (so as not to do validations the first time the user access the page). If data was posted, validate the data. If an errors are found display an error message and repopulate the form with the data the user entered so they only have to fix the fields in error. If validation passes, then include() the page that will process the submitted data.

 

So, you should make changes on the prior page to do the validations. By the way, the logic, as you've explained it will be problematic if you have two people with the same name. The sample code below does NOT take that problem into consideration. This is just a rough framework to use as a starting point

<?php

//Set values for populating form
$fname = '';
$lname = '';
//error tracking variable
$errors = array();

//Check if form was posted
if($_SERVER['REQUEST_METHOD']=='POST')
{
    //Validate input
    $fname = trim($_POST['fname']);
    $lname = trim($_POST['lname']);

    if(empty($fname))
    {
        $errors[] = "First name is required.";
    }
    if(empty($lname))
    {
        $errors[] = "Last name is required.";
    }

    //If no format errors, check if there is a match
    if(count($errors))
    {
        $fnameSQL = mysql_real_escape_string($fname);
        $lnameSQL = mysql_real_escape_string($lname);
        $query = "SELECT *
                  FROM Customer_Registration
                  WHERE Firstname = '$fnameSQL'
                    AND Lastname  = '$lnameSQL'";
        $result = mysql_query($query) or die(mysql_error());
        if(mysql_num_rows($result)<1)
        {
            //No record found
            $errors[] = "No user found with the name '{$fname} {$lname}'";
        }
        else
        {
            //Include the page to update the user.
            //Use the results of this query to populate the form
            include('update-user.php');
            exit();
        }
    }
}

$errorMsg = '';
if(count($errors))
{
    $errorMsg .= "The following errors occured:";
    $errorMsg .= "<ul>";
    foreach($errors as $err)
    {
        $errorMsg .= "<li>{$err}</li>";
    }
    $errorMsg .= "</ul>";
}

?>
<html>
<body>

<div style="color:red;">$errorMsg</div>

<form action="" method="post">
First Name:
<input type="text" name="fname" value="<?php echo $fname; ?>"><br>

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

<button type="submit">Check Name</button>

</form>

</body>
</html>

[php-newbies]

You should have your form page POST back to itself.

 

The page should check if data was posted (so as not to do validations the first time the user access the page). If data was posted, validate the data. If an errors are found display an error message and repopulate the form with the data the user entered so they only have to fix the fields in error. If validation passes, then include() the page that will process the submitted data.

 

So, you should make changes on the prior page to do the validations. By the way, the logic, as you've explained it will be problematic if you have two people with the same name. The sample code below does NOT take that problem into consideration. This is just a rough framework to use as a starting point

<?php

//Set values for populating form
$fname = '';
$lname = '';
//error tracking variable
$errors = array();

//Check if form was posted
if($_SERVER['REQUEST_METHOD']=='POST')
{
    //Validate input
    $fname = trim($_POST['fname']);
    $lname = trim($_POST['lname']);

    if(empty($fname))
    {
        $errors[] = "First name is required.";
    }
    if(empty($lname))
    {
        $errors[] = "Last name is required.";
    }

    //If no format errors, check if there is a match
    if(count($errors))
    {
        $fnameSQL = mysql_real_escape_string($fname);
        $lnameSQL = mysql_real_escape_string($lname);
        $query = "SELECT *
                  FROM Customer_Registration
                  WHERE Firstname = '$fnameSQL'
                    AND Lastname  = '$lnameSQL'";
        $result = mysql_query($query) or die(mysql_error());
        if(mysql_num_rows($result)<1)
        {
            //No record found
            $errors[] = "No user found with the name '{$fname} {$lname}'";
        }
        else
        {
            //Include the page to update the user.
            //Use the results of this query to populate the form
            include('update-user.php');
            exit();
        }
    }
}

$errorMsg = '';
if(count($errors))
{
    $errorMsg .= "The following errors occured:";
    $errorMsg .= "<ul>";
    foreach($errors as $err)
    {
        $errorMsg .= "<li>{$err}</li>";
    }
    $errorMsg .= "</ul>";
}

?>
<html>
<body>

<div style="color:red;">$errorMsg</div>

<form action="" method="post">
First Name:
<input type="text" name="fname" value="<?php echo $fname; ?>"><br>

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

<button type="submit">Check Name</button>

</form>

</body>
</html>

 

 

Thank you so much for the code, but I have tried to run the code independently but it will not run. I am such I have messed up the code. But is there any way I can include part of your code in my php code so that I can still use my html page.

 

<?php

  //connect  to the database 

$con = mysql_connect("localhost","dbusrn","pwd");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydb", $con);


//Set values for populating form
$fname = '';
$lname = '';
//error tracking variable
$errors = array();

//Check if form was posted
if($_SERVER['REQUEST_METHOD']=='POST')
{
    //Validate input
    $fname = trim($_POST['fname']);
    $lname = trim($_POST['lname']);

    if(empty($fname))
    {
        $errors[] = "First name is required.";
    }
    if(empty($lname))
    {
        $errors[] = "Last name is required.";
    }

    //If no format errors, check if there is a match
    if(count($errors))


    {
        $fnameSQL = mysql_real_escape_string($fname);
        $lnameSQL = mysql_real_escape_string($lname);
        $query = "SELECT *
                  FROM Customer_Registration
                  WHERE Firstname = '$fnameSQL'
                    AND Lastname  = '$lnameSQL'";
        $result = mysql_query($query) or die(mysql_error());
        if(mysql_num_rows($result)<1)
        {
            //No record found
            $errors[] = "No user found with the name '{$fname} {$lname}'";
        }
        else
        {
            //Include the page to update the user.
            //Use the results of this query to populate the form
            include('update-Exist-cus.php');
            exit();
        }
    }
}

$errorMsg = '';
if(count($errors))
{
    $errorMsg .= "The following errors occured:";
    $errorMsg .= "<ul>";
    foreach($errors as $err)
    {
        $errorMsg .= "<li>{$err}</li>";
    }
    $errorMsg .= "</ul>";
}

?>
<html>
<body>

<div style="color:red;">$errorMsg</div>

<form action="" method="post">
First Name:
<input type="text" name="fname" value="<?php echo $fname; ?>"><br>

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

<button type="submit">Check Name</button>

</form>

</body>
</html> 

[Psycho]

I have tried to run the code independently but it will not run

 

That tells me nothing. What problems/errors did you experience. Besides, I specifically stated

 

This is just a rough framework to use as a starting point

 

 

[php-newbies]

I have tried to run the code independently but it will not run

 

That tells me nothing. What problems/errors did you experience. Besides, I specifically stated

 

This is just a rough framework to use as a starting point

 

Many thanks again.

 

when I tried to run the code, I got this: $errorMsg, when I type first name and last name and click check name nothing happens.

 

This is how the code looks like

 

<?php

//Set values for populating form
$fname = 'fname';
$lname = 'fname';
//error tracking variable
$errors = array();

//Check if form was posted
if($_SERVER['REQUEST_METHOD']=='POST')
{
    //Validate input
    $fname = trim($_POST['fname']);
    $lname = trim($_POST['lname']);

    if(empty($fname))
    {
        $errors[] = "First name is required.";
    }
    if(empty($lname))
    {
        $errors[] = "Last name is required.";
    }

    //If no format errors, check if there is a match
    if(count($errors))
    {

	  //connect  to the database 

$con = mysql_connect("localhost","dbusrn","db-pwd");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydb", $con);

        $fnameSQL = mysql_real_escape_string($fname);
        $lnameSQL = mysql_real_escape_string($lname);
        $query = "SELECT *
                  FROM Customer_Registration
                  WHERE Firstname = '$fnameSQL'
                    AND Lastname  = '$lnameSQL'";
        $result = mysql_query($query) or die(mysql_error());
        if(mysql_num_rows($result)<1)
        {
            //No record found
            $errors[] = "No user found with the name '{$fname} {$lname}'";
        }
        else
        {
            //Include the page to update the user.
            //Use the results of this query to populate the form
            include('update-Exist-cus.php');
            exit();
        }
    }
}

$errorMsg = '';
if(count($errors))
{
    $errorMsg .= "The following errors occured:";
    $errorMsg .= "<ul>";
    foreach($errors as $err)
    {
        $errorMsg .= "<li>{$err}</li>";
    }
    $errorMsg .= "</ul>";
}

?>
<html>
<body>

<div style="color:red;">$errorMsg</div>

<form action="" method="post">
First Name:
<input type="text" name="fname" value="<?php echo $fname; ?>"><br>

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

<button type="submit">Check Name</button>

</form>

</body>
</html> 

[Psycho]

As my sig states, I do not always test my code. I expect that the person that I am providing code to will at least take a minimum of effort to resolve typos. In this case it should be obvious that the $errorMsg variable wasn't being output - it was simply included in HTML.

 

Use this

<div style="color:red;"><?php echo $errorMsg; ?></div>

 

 

Also, this

    //If no format errors, check if there is a match
    if(count($errors))

 

Should be:

    //If no format errors, check if there is a match
    if(count($errors)==0)

[php-newbies]

Many thanks  mjdamato. All  working perfectly. For now the search criteria meet my requirement, if it ever occurs that we have 2 customers with the same first name and last name will have to modify the code.