Insert Query In PHP OOP While Row Does Not Exist In DB

Question

I'm working on a custom CMS using PHP OOP. Basically I have made a class that can add a new row to db.

My Class:

<?php
class Navigation
{
    private $db;

    public function __construct()
    {
        $this->db = new Connection();
        $this->db = $this->db->dbConnect();
    }

    public function NewMenu($menu_name,$menu_numbers)
    {
        if (!empty($menu_name) && !empty($menu_numbers)) {
            $sql = "INSERT INTO menu_nav "
                . "(menu_name, menu_items) VALUES (?, ?)";
            $ins = $this->db->prepare($sql);

            $ins->bindParam(1,$menu_name);
            $ins->bindParam(2,$menu_numbers);
            $ins->execute();
        } else {
            header("Location: maint/php/includes/errors/009.php");
            exit();
        }
    }
}

This class works fine but the problem is that I don't know how to check if the menu_name exists already in the table or not. And if yes ,it should receive the error message that "Data can not be inserted to db" for example. So if you know how to do this feature in PHP OOP ,please let me know cause I really need it.

Possible duplicate of [Best way to avoid duplicate entry into mysql database](http://stackoverflow.com/questions/2219786/best-way-to-avoid-duplicate-entry-into-mysql-database) — Tim Biegeleisen, Oct 03 '16 at 08:45
I voted this as duplicate because your question is really just about how to handle duplicate entries in MySQL in _general_. The PHP wrapper you are using does not change this AFAIK. — Tim Biegeleisen, Oct 03 '16 at 08:46
I think OP is looking for ways to find the previously entered rows. while partly the question maybe similar but solution is quite different than the one provided. The OP has to made another request to the database to find whether the record exists or not. — Samundra, Oct 03 '16 at 08:51
Has absolotely little to do with OOP, just because you're using a class (objects) doesn't mean the problem lies with OOP. — dbf, Oct 03 '16 at 09:12

score 2 · Answer 1 · answered Oct 03 '16 at 08:53

There are two ways for this to work. For the first method you'll need to have a primary/unique key, which would cause the query to fail. Set PDO up with throwing exceptions on failures, and you can check the exceptions message for "duplicate key".
To do this you'd need to change the connection options, like this[1]:

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

Then pdo::exec() will throw an exception instead of just returning false.

For the second method you need to query the DB first, and then insert it if 0 rows are returned. The problem with this approach is that it can trigger race conditions. In which a duplicate row is inserted between the check and the INSERT query, by another, simultaneously running, script.

That's why I recommend doing it in the first manner. As you'd need to do it anyway, to catch the race conditions.

PS: As an alternative way of doing method 1, you could use ON DUPLICATE KEY in the SQL query. If you wanted to insert or update the data.

[1]: Copied from here https://phpdelusions.net/pdo

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

so you should check if row exists in table, and insert the data only if row doesn't exist(if i correctly understand your question). at first select the row with given menu name $ins = $this->db->prepare("SELECT FROM menu_nav (menu_name, menu_items) VALUES (?, ?)"); and then check if you got any data. go here , you will get the clue)

edited May 23 '17 at 10:27

Community

1
1

answered Oct 03 '16 at 08:54

nooby

294
4
17

1

This is unfortunately vulnerable to race conditions, as mentioned in my answer. – ChristianF Oct 03 '16 at 08:57

Insert Query In PHP OOP While Row Does Not Exist In DB

2 Answers2