Jump to content

How check an existing record with PHP MySql


dadedidhodong

Recommended Posts

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");
    
    
    }
    
    
?>
Link to comment
Share on other sites

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

Link to comment
Share on other sites

      $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 by Andy-H
Link to comment
Share on other sites

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! :]

Link to comment
Share on other sites

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 by PaulRyan
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.