Random DB Error

[eluser]Jay Logan[/eluser]
My site lets users add there school to a database. It works about 90% of the time but sometimes, users are given this error:

Code:

A Database Error Occurred

Error Number: 1062

Duplicate entry '2147483647' for key 1

INSERT INTO `schools` (`abbr`, `name`, `city`, `state`, `type`, `host_id`, `id`) VALUES ('RNE', 'Northeast High School', 'Columbia', 'SC', 'M', '4', 'd7d0e5348a4501a8b2f635d47276')

Apparently, the system sometimes gives schools the same ID. I wondering if you guys could take a look at the code I'm using and see any obvious errors.

My base model contains:

Code:

function unique_id()
    {
        $id = uniqid(dechex(rand()), true);
        $id = explode('.', $id);
        $id[1] = dechex($id[1]);
        $id = implode('', $id);
        return $id;
    }

My school model contains:

Code:

function insert($values)
    {
        $values['id'] = $this->unique_id();
        $this->db->insert('schools', $values);
        return $values['id'];
    }

Thanks for any help you can give me.

[eluser]steelaz[/eluser]
2147483647 is max for MySQL INT. I would suggest changing the field type to BIGINT.

[eluser]Jay Logan[/eluser]
Yes, it has always been set to BIGINT(11). Thanks for reply.

[eluser]steelaz[/eluser]
I don't think it's a coincidence that your error is showing exactly '2147483647'. Couple suggestions:

Double check all your field types.
If you're not using negative integer values, make them 'unsigned'.
Remove (11) next to BIGINT.

[eluser]Jay Logan[/eluser]
I have made your changes. Though I don't think it works properly. I tried to add another school and got this error:

Code:

A Database Error Occurred

Error Number: 1062

Duplicate entry '0' for key 1

INSERT INTO `schools` (`abbr`, `name`, `city`, `state`, `type`, `host_id`, `id`) VALUES ('WDSK', 'WOODSTOCK', 'WOODSTOCK', 'GA', 'H', 0, 'd6f406048b2ff09224875456449a')

I hit the refresh button, and got this error:

Code:

A Database Error Occurred

Error Number: 1062

Duplicate entry '60' for key 1

INSERT INTO `schools` (`abbr`, `name`, `city`, `state`, `type`, `host_id`, `id`) VALUES ('WDSK', 'WOODSTOCK', 'WOODSTOCK', 'GA', 'H', 0, 'd6f406048b2ff09224875456449a')

So it appears to be grabbing random ID numbers without checking to see if that ID already exists in the DB.

[eluser]steelaz[/eluser]
Do you currently have a function to check for existing IDs? If no, your insert function should be modified to something like this:

Code:

function insert($values)
{
    $exists = TRUE;
    while ($exists) {
        $values['id'] = $this->unique_id();
        $query = $this->db->get_where('schools', array('id' => $values['id']));
        $exists = ($query->num_rows() > 0) ? TRUE : FALSE;
    }
    $this->db->insert('schools', $values);
    return $values['id'];
}

[eluser]Jay Logan[/eluser]
I've implemented the code and added 4 schools without any problems. I will have users test it out for a while but it seems like it should work. I can't thank you enough for this.