How to check two rows from table that the value of them are not repeated at other rows

Let’s say I have a table called services:

And I made a form for updating this table:

<form action='' method='POST'>
    <div class='box box-default'>
        <div class='box-header with-border'>
            <h3 class='box-title'><span style='color:blue'><span>".$home['name_service']."</span></h3>
            <div class='box-tools pull-right'>
                <button type='button' class='btn btn-box-tool' data-widget='collapse'><i class='fa fa-minus'></i></button>
            </div>
        </div>
                                    
        <div class='box-body'>
            <div class='row'>
                <div class='col-md-6'>
                    <div class='form-group'>
                        <label for='ser'>Name of Service:</label>
                        <input name='sname' type='text' class='form-control' value='".$home['name_service']."'>
                    </div>
                    <div class='form-group'>
                        <label for='ser'>Background Color of Box:</label>
                        <input name='bgbox' type='text' class='form-control' value='".$home['bg_box']."'>
                    </div>
                    <div class='form-group'>
                        <label for='ser'>Description of Service:</label>
                        <input name='sdesc' type='text' class='form-control' value='".$home['desc_service']."'>
                    </div>
                    <div class='form-group'>
                        <label for='ser'>Link of Service:</label>
                        <input name='slink' type='text' class='form-control' value='".$home['link_service']."'>
                    </div>
                </div>
            </div>
        </div>
                                
        <p>
            <button name='update' type='submit' class='btn btn-success btn-lg'>Update</button>
            <button type='button' class='btn btn-danger btn-lg'>Delete</button>&nbsp;
            <input type='checkbox' name='showing'><label for='showing'>&nbsp; Hide from showing</label></br>
        </p><hr></hr>
    </div>
</form>

For action part I coded this:

if (isset($_POST['update'])){
    $servicename = $_POST['sname'];
    $backgroundbox = $_POST['bgbox'];
    $servicedesc = $_POST['sdesc'];
    $servicelink = $_POST['slink'];
    $updatation = new Page();
    $notice[] = $updatation->CheckSname2($servicename,$backgroundbox,$servicedesc,$servicelink);    
    $notice = $updatation->getNotice();
}

And here is the CheckSname2() method that I’ve called:

public function CheckSname2($servicename,$backgroundbox,$servicedesc,$servicelink)
{
    if(!empty($servicename)&&!empty($backgroundbox)&&!empty($servicedesc)&&!empty($servicelink))
    {
        $chk1 = $this->_db->prepare("SELECT name_service FROM homepage WHERE name_service = ?");
        $chk1->bindParam(1,$servicename);
        $chk1->execute();
        if($chk1->rowCount() == 1)
        {
            $notice['service_name'] = "Try different service name";
            return $this->notice;
        }else{
            $chk2 = $this->_db->prepare("SELECT link_service FROM homepage WHERE link_service = ?");
            $chk2->bindParam(1,$servicelink);
            $chk2->execute();
            if($chk2->rowCount() == 1)
            {
                $notice['service_link'] = "Try different page url";
                return $this->notice;
            }else{
                $this->UpdateService($servicename,$backgroundbox,$servicedesc,$servicelink);
                $notice['success_message'] = "New service was successfully added";
                return $this->notice;
            }
        }
    }
}

As you can see, basically I want the user to insert a name_service that are not duplicated at any other row. And if it’s not, then the method checks for link_service also. And if these two are not repeated, it calls UpdateService() method.

Now the problem comes from these two inputs at form, where I retrieve the CURRENT data from table which is belonged to the user:

<input name='sname' type='text' class='form-control' value='".$home['name_service']."'>
<input name='slink' type='text' class='form-control' value='".$home['link_service']."'>

For example, if the current name_service is banana and link_service is banana-place, and the user changes the banana-place to anything else, the table won’t be updated because CheckSname2() method checks name_service also from table. And because the value is not changed yet it returns error.

Now I want this method to check that if the user types any OTHER name_service OR link_service that are belonged to other users, return custom error messages, otherwise it has to update the table, even if user changes one field!

Note: UpdateService() method simply updates the table and works fine, so there’s no need to include that.

Answer

If I understand correct, you want to check if the combination name_service and link_service is unique. A simple check for that is to run a select query with a WHERE clause that requires them both to exist.

SELECT table_id FROM table
   WHERE name_service = "banana"
   AND link_service = "banana_link"
   LIMIT 1

If this query returns no result at all (rowCount() == 0). the combination is unique and you can proceed updating or inserting.

Otherwise the combination does exist and you can simply message: This combination already exists. Change either the name or the link

Advertisement

Answer