Logic for already exist record check but only in case of updated form values

Question

I am working on a module called county manager I have issues with checking for already existing county with its country in the counties mysql table.

Database table Counties table

Let me explain

Add Page In add page i am having 2 fields (check screenshot)

This is my code for saving the county in table

$country = stripslashes($_POST["country"]);
$county_name = stripslashes($_POST["county_name"]);

// County Already Exist Check
$sql = "SELECT county_id FROM counties WHERE country='$country' AND county_name='$county_name' LIMIT 1";
$query = $mysql->query($sql);

if($mysql->rowCount($query) > 0)
{
    header("location:counties_add.php?type=warning&msg=" .urlencode("County With This Country Already Exist"));
    exit();
}

$sql = "INSERT INTO ". TABLE_COUNTIES ."";
$sql .= "(country, county_name, datecreated, datemodified) VALUES";
$sql .= "('$country', '$county_name', now(), now())";
$query = $mysql->query($sql);

$msg = "County Added Successfully";
header("location:counties_view.php?type=success&msg=" .urlencode($msg));
exit();

In add page it succesfully checks (whether the county that is being added along with the country already exists in the table or not) before inserting a record

But its not working in my edit page or you can say i am unable to find the logic behind how to check it.

Edit Page

Check out my edit page below

This is code for editing page

<!-- Form Starts -->
<form id="myform" name="myform" method="post" action="counties_edit_process.php" accept-charset="utf-8">

<!-- Widget Starts -->
<div class="widget">
    <div class="title js_opened">
        <div class="icon"><img src="themes/<?php echo ADMIN_PANEL_THEME; ?>/images/icons/navigation/counties<?php echo $retina_suffix; ?>.png" width="24" height="24" alt="" /></div>
        <span>Fill The Fields Marked With *</span>
    </div>
    <div class="content">
        <div class="form_row first">
            <label>Country</label>
            <div class="form_right">
                <select name="country">
                    <option value="">Please Choose An Option</option>
                    <option value="England" <?php if ($dbData["country"]=="England") echo "selected=\"selected\""; ?> >England</option>
                    <option value="Scotland" <?php if ($dbData["country"]=="Scotland") echo "selected=\"selected\""; ?> >Scotland</option>
                    <option value="Wales" <?php if ($dbData["country"]=="Wales") echo "selected=\"selected\""; ?> >Wales</option>
                </select>
            </div>
            <div class="clear"></div>
        </div>
        <div class="form_row last">
            <label>Country Name</label>
            <div class="form_right"><input type="text" name="county_name" maxlength="25" value="<?php echo $dbData["county_name"]; ?>" /></div>
            <div class="clear"></div>
        </div>
    </div>
</div>
<!-- Widget Ends -->

<div class="form_buttons">
    <input type="submit" name="submit" value="Update" />&nbsp;&nbsp;<a href="counties_view.php">Back To View</a>
    <input type="hidden" name="country_existing" value="<?php echo $dbData["country"]; ?>" />
    <input type="hidden" name="county_name_existing" value="<?php echo $dbData["county_name"]; ?>" />
    <?php $form->passValuesToNextPage("GET"); ?>
</div>
</form>
<!-- Form Ends -->

and this is my code for saving / updating the record in edit page

$id = stripslashes($_POST["id"]);

// For Already Exist Checks
$country = stripslashes($_POST["country"]);
$country_existing = stripslashes($_POST["country_existing"]);
$county_name = stripslashes($_POST["county_name"];
$county_name_existing = stripslashes($_POST["county_name_existing"]);

// County Already Exist Check

    <--- Issue is here in the sql to check for already exist. Please read the end of the post to understand my question  --->

$sql = "SELECT county_id FROM ". TABLE_COUNTIES ." WHERE (county_name='$county_name' AND county_name!='$county_name_existing') AND (country='$country' AND country!='$country_existing') LIMIT 1";
$query = $mysql->query($sql);

if($mysql->rowCount($query) > 0)
{
    header("location:counties_edit.php?id=$id&case=edit&type=warning&msg=" .urlencode("County With This Country Already Exist"));
    exit();
}

$sql = "UPDATE ". TABLE_COUNTIES ." SET";
$sql .= " country='". $country ."',";
$sql .= " county_name='". $county_name ."',";
$sql .= " datemodified=now()";
$sql .= " WHERE county_id=$id";
$query = $mysql->query($sql);

header("location:counties_view.php?type=success&msg=" .urlencode("County Updated Successfully"));
exit();

I am unable to code the sql logic that will check for already existing record in case of edit / updating the record, though i have tried something with passing 2 hidden variables in form (check in edit page code above) that contains the old country name and county name so that I can check compare them in sql to check whether the record already exists or not

I can use the same add page logic here as well i.e

$sql = "SELECT county_id FROM counties WHERE country='$country' AND county_name='$county_name' LIMIT 1";

but the problem is that in case i dont update the 2 values i.e country and county in edit page it still considers the record as already existing in the table. I need to handle that aspect that it should check for the already existing record only if the one of them or both the form field values are updated.

Please help me how to achieve the logic that will check for already existing record only if one of the values or both are updated in the form.

What are the county_name_existing fields used for? I think the issue is there as this is fetching everything from the table? — Dale, Nov 24 '12 at 07:50
@Dale - they are used in scenario where lets say I havent updated the form fields and need 2 values in sql to compare like select * from counties where country='newly_selected_country_in_edit_page aka $country' and country!='old_value_during_edit_page_load aka $country_existing'. I hope you get it? — Asnexplore, Nov 24 '12 at 08:01
I get it but surely that means everything else in the database? — Dale, Nov 24 '12 at 08:03
How about creating a composite unique index on country and county_name? Now the database is handling the logic for you — Dale, Nov 24 '12 at 08:04

mychalvlcek · Accepted Answer · 2012-11-24T17:49:01.623

1

in edit part:
try to compare ID of edited record instead of comparing old/new names e.g.

$sql = "SELECT county_id FROM counties WHERE country='$country' AND county_name='$county_name' AND country_id !='$id' LIMIT 1";

edited Nov 24 '12 at 17:49

answered Nov 24 '12 at 08:01

mychalvlcek

3,956
1
19
34

Thanks mate it did really worked. I was so fed up that i didnt realized that it can be so easy to get it right. Thanks a lot buddy. You really solved one of my major issues. I really appreciate that. – Asnexplore Nov 24 '12 at 10:04

score 1 · Answer 2 · answered Nov 24 '12 at 08:10

1

Personally I would add a composite unique index on country and county_name

ALTER TABLE `your_table_name` ADD UNIQUE (
`country` ,
`county_name`
);

answered Nov 24 '12 at 08:10

Dale

10,384
21
34

There might be instance where same county can be there in other country. I have to take that into consideration as well. – Asnexplore Nov 24 '12 at 09:59
This unique index would allow a same county_name with a different country – Dale Nov 24 '12 at 10:02
1

I will be combining your solution as well :) It will make my code more full proof. – Asnexplore Nov 24 '12 at 10:33

Logic for already exist record check but only in case of updated form values

2 Answers2

Linked