How check an existing record with PHP MySql

[dadedidhodong]

This code is to let user add a new record

<form action="processAdd.php" method="post">
<tr><td></td><td><input type="text" placeholder="Last Name" name="lname" value="" required></td></tr></br>
<tr><td></td><td><input type="text" placeholder="First Name" name="fname" value="" required></td></tr></br>
<tr><td></td><td><input type="text" placeholder="Course" name="course" value="" required></td></tr></br>
<tr><td colspan=2><input type="submit" name="submit" value="Submit"></td></tr>
</form>

The codes below is where the user inputs are being processed. But I don't want a duplicate record of the last name and the first name. I can always do that by altering the table I've created in the database and set it to UNIQUE so that no similar record can be inserted. But what I wanna do is when the user inputs a similar records (i.e. last name and firt name) it will display an error message. I don't know what's wrong with my codes, it doesn't display a syntax error though when I run it, but it keeps on adding similar records on my database. I've tried the

<?php
    session_start();
   
    $dbconnect = mysql_connect("localhost","root","");
    $db = mysql_select_db("web", $dbconnect);
    
    $sql = mysql_query("SELECT * FROM tbl_student WHERE stud_laneme =  '".$_POST["lname"]."' AND stud_fname = ".$_POST["fname"]."',");
    $result = mysql_query($sql);
    
    if(mysql_num_rows($result) > 0)
    {        
    echo "Record already exists";   
    }
    else
    {
    $sql="INSERT INTO `web`.`tbl_student`(
                          `stud_id`,
                          `stud_lname`,
                          `stud_fname`,
                          `stud_course`
                          )
                          VALUES(
                          NULL,
                          '".$_POST["lname"]."',
                          '".$_POST["fname"]."',
                          '".$_POST["course"]."'
                          )";
    
    $result = mysql_query($sql);
    
    header("location:displayRecord.php");
    
    
    }
    
    
?>

[Samuz]

You shouldn't use first name & last name as unique identifiers, because anyone could have the same problem.

 

Use email or a specific ID number, like National Insurance or whatever, as long as its completely UNIQUE.

 

Then before you insert anything into the database, run a select query to check if that unique field appears in your table more than once. 

 

If it appears more than once, display an error message saying that user is already in the database, else you simply insert as you have been doing

[Andy-H]

      $sql = mysql_query("SELECT * FROM tbl_student WHERE stud_laneme =  '".$_POST["lname"]."' AND stud_fname = ".$_POST["fname"]."',");
    $result = mysql_query($sql);

The single quotes are messed up in your query, also you run mysql_query on the resource returned from mysql_query, and you should use mysql_real_escape_string to prevent SQL injection, it should be:

$sql = "SELECT * FROM tbl_student WHERE stud_laneme = '". mysql_real_escape_string($_POST['lname']) ."' AND stud_fname = '". mysql_real_escape_string($_POST['fname']) ."'";
$result = mysql_query($sql);

In addition, if you're only looking for one result, appending LIMIT 1 will stop mysql from continuing it's search once it has found a result, and mysql_ functions have been deprecated, you should look into pdo or mysqli

 

As for your original problem, that will be down to the duplicate mysql_query

Edited August 26, 2013 by Andy-H

[dadedidhodong]

Use email or a specific ID number, like National Insurance or whatever, as long as its completely UNIQUE.

 

Yes, but the program I'm working is actually just a practice. Thank you by the way for your advice :]

 

The single quotes are messed up in your query, also you run mysql_query on the resource returned from mysql_query, and you should use mysql_real_escape_string to prevent SQL injection, it should be:

Really, they are annoying, but the program don't seem to work whenever I remove single quotes. But I'll try your codes, thank you! :]

[PaulRyan]

In this query, you have written a column name incorrectly.

$sql = mysql_query("SELECT * FROM tbl_student WHERE stud_laneme =  '".$_POST["lname"]."' AND stud_fname = ".$_POST["fname"]."',");
$result = mysql_query($sql);

It should be like this notice stud_laneme from previous code is now stud_lname.

  //### Ternary operators, quick and easy
  $firstname = isset($_POST['fname']) ? mysql_real_escape_string(trim($_POST['fname'])) : FALSE ;
  $lastname  = isset($_POST['lname']) ? mysql_real_escape_string(trim($_POST['lname'])) : FALSE ;
 
  //### Check variables
  if(empty($firstname)) {
    echo 'Firstname is empty.';
    exit;
  } else if(empty($lastname)) {
    echo 'Lastname is empty.';
    exit;
  } else {

    //### Perform query
    $sql = mysql_query("SELECT * FROM `tbl_student` WHERE `stud_fname` = '{$firstname}' AND `stud_lname` = '{$lastname}'");
    $result = mysql_query($sql) or die(mysql_error());
    
  }

I have added in some simple error checking too, as you can see it is a lot cleaner and easier to read.

Edited August 27, 2013 by PaulRyan