Check database for same entry

Question

I'm adding an entry to the database and I need to check if it has already an entry. I am using MVC

view

<form action="<?php echo base_url(); ?>some_controller/insertServ_idx" method="post">
<center>Service Name: <input type="text"  name="ci_name"/>
    <input type="submit" class="classname" value="Save"/></center><br>

controller

public function index_addServ(){
    /* A pop up dialog will appear containing the details on how to add a new service
     */

        $data['current_user']=$this->session->userdata('email');
        $appname = $this->input->post('appname');
        $data['people'] = $this->some_model->getPeople();
        $data['mapList'] = $this->some_model->getMapped();
        $data['serv'] = $this->some_model->getServiceApp($appname);
        $data['appServList'] = $this->some_model->getApp_serv();
        $this->load->view('templates/header.php',$data);
        $this->load->view('some_page/index_addServ.php',$data);


}

model

public function addCI($ci_name){
    /* Adds a new service
     */
    $ci_name = $this->db->escape_str($ci_name);

    $queryStr = "INSERT INTO appwarehouse.service(service) VALUES ('$ci_name');"; 
    $query = $this->db->query($queryStr);
    return $query;
}

I know I need to add another select statement to check if there are rows already. But how?

Answer 1

You should first have a unique identifier as an index, for example $ci_name. Make sure it is unique. Then add the ON DUPLICATE KEY UPDATE syntax found in the manual. That way you don't have to run 2 queries and do it all in one.

EDIT

It is common to at least have a last_updated or similar timestamp field in your database table. That way your your full query will look somthing like this:

"INSERT INTO appwarehouse.service(service) VALUES ('$ci_name') 
ON DUPLICATE KEY UPDATE last_updated=" + time();

See here for more info

Answer 2

For the quick info there are 2 ideas that hit my mind.

1) Make the Field unique

2) Check if it already exists by comparison(bad approach but do able)

public function addCI($ci_name){
    /* Adds a new service
     */
    $ci_name = $this->db->escape_str($ci_name);

    $queryStr = "INSERT INTO appwarehouse.service(service) VALUES ('$ci_name')";//if feild is unique then this query will throw some exception or error, w/e just check it if there is error or exception then due to uniqueness then simply do w/e you want 
if (mysql_errno()) {
    die('do what ever you wanna do');// coming here means query sent an error and there is a duplicate entry if assuming your query have no syntax error.

}else{
    $query = $this->db->query($queryStr);}
    return $query;
}

other way is to simply

2)

     $ci_name = $this->db->escape_str($ci_name);

        $queryStr = "Select value from table where value= ('$ci_name')";
        $query = $this->db->query($queryStr);
        if(mysql_num_rows($query)>0){
           echo "result already exists";
         }else{
$queryStr = "INSERT INTO appwarehouse.service(service) VALUES ('$ci_name');"; 
        $query = $this->db->query($queryStr);
}

Answer 3

public function addCI($ci_name){
/* Adds a new service
 */
$ci_name = $this->db->escape_str($ci_name);

$queryStr = "INSERT INTO appwarehouse.service(service) VALUES            ('$ci_name')";//if feild is unique then this query will throw some exception or   error, w/e just check it if there is error or exception then due to uniqueness then simply do w/e you want 
  if (mysql_errno()) {
die('do what ever you wanna do');// coming here means query sent an error   and there is a duplicate entry if assuming your query have no syntax error.

 }else{
$query = $this->db->query($queryStr);}
return $query;
   }