CODEWITHSUNDEEP
phpmysqlsqlinsert
stergosz
stergosz

Reputation: 5860

sql which is better to insert row if not exist before

I am currently using the following method to insert a row in a table when a column does not exist.. otherwise i generate new column id and try again

    $item_id = $this->misc_model->generateId(18);

    while ($flag == false){

        $this->db->where('item_id', $item_id);
        $sql = $this->db->get($this->db->dbprefix('table'));

        if ($sql->num_rows()){
            $item_id = $this->misc_model->generateId(18);
        }else{
            $flag = true;
        }
    }

can this be done faster this way?

 IF NOT EXISTS(SELECT 1 FROM table WHERE item_id = $item_id)
    INSERT INTO table (item_id) VALUES ($item_id)

Upvotes: 0

Views: 262

Answers (3)

Junaid
Junaid

Reputation: 2094

if you are good to go with a new item_id always then you can try this in your insert statement

INSERT INTO table_name (item_id) value (RIGHT(CONCAT(UNIX_TIMESTAMP(),UNIX_TIMESTAMP()),18))

this will always generate new item_id for you

Upvotes: 1

Diego
Diego

Reputation: 36156

you should have your columns as auto increment, but if for some reason you cant, that would be faster, yes!

Upvotes: 0

liquorvicar
liquorvicar

Reputation: 6106

If item_id is your primary key then you're much better off using an AUTO INCREMENT and letting the db handle this for you.

Upvotes: 2

Related Questions