Check for entry and insert if not duplicate PHP/SQL

Question

I am running this script which looks at inputs $area1 and $area2 and checks them against my database, if they are not then it inputs them. This is what I have written but I'm new to PHP and it's not working, not only that I believe there is a better way to do this but I'm unsure of a more efficient solution.

$query1 = "SELECT location_name FROM locations WHERE (location_name = '$area1')";
$connect->query($query1);
if ($partner_result = $connect->query($query1)) {
    $partner_row = $partner_result->fetch_row();
    if(!empty($partner_row)) {
        $partners = $partner_row;
        print_r($partners);
    } else {
        $insert_location = "INSERT INTO locations (location_id, location_name) VALUES (NULL, '".$area1."')"
        $connect->query($insert_location);
        echo "Location added<br>";
        $partners = false;
    }
    $partner_result->close();
} else {
    $get_partners = false;
}
$query3 = "SELECT location_name FROM locations WHERE (location_name = '$area2')";
$connect->query($query2);
if ($partner_result = $connect->query($query2)) {
    $partner_row = $partner_result->fetch_row();
    if(!empty($partner_row)) {
        $partners = $partner_row;
        print_r($partners);
    } else {
        $insert_location = "INSERT INTO locations (location_id, location_name) VALUES (NULL, '".$area2."')"
        $connect->query($insert_location);
        echo "Location added<br>";
        $partners = false;
    }
    $partner_result->close();
} else {
    $get_partners = false;
}

Something happened when you pasted the code and it's shoved off to the right so far you can barely read it. Is this mysqli or PDO or something else? — tadman
– tadman, Commented Nov 9, 2015 at 17:05
For a start you've got syntax errors. Your insert lines don't have a semi-colon. — Jonnix
– Jonnix, Commented Nov 9, 2015 at 17:06
why are you calling ->query() twice for the top-level if() calls? All that does is waste a bunch of cpu time. and if you want to check for dupes, you're going about it wrong - you're building race-condition code. There's insert ... on duplicate key update for a reason. — Marc B
– Marc B, Commented Nov 9, 2015 at 17:07
Looking at your SQL, it would assume that location_id is your primary key and potentially using an autoincrement? If so, you'll need a unique index on location_name for the on duplicate key mentioned in comments and answers. — Jonnix
– Jonnix, Commented Nov 9, 2015 at 17:11

mv1 · Accepted Answer · 2015-11-09 17:22:44Z

3

You could achieve it on the DB side without involving too much PHP.

Firstly, you need to have UNIQUE index on the "location_name" column:

CREATE UNIQUE INDEX location_name_index ON locations (location_name)

Then you can always execute insert ignore which will add the new locations only if they do not already exist:

INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '".$area1."')
INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '".$area2."')

Additionally, from your code is not clear whether you have secured your inputs. If you have - good! If you haven't you could do it like this:

$query1 = sprintf("INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '%s')",mysql_real_escape_string($area1));

edited Nov 9, 2015 at 17:22

answered Nov 9, 2015 at 17:12

mv1

5784 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check for entry and insert if not duplicate PHP/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related