[SOLVED] Help with "DB Error: already exists" (looped INSERT)

[c4onastick]

I'm having a bit of trouble with database inserts. I've got a bunch of data coming in, it all looks good. I can get one insert to work but the second one fails. "DB Error: already exists" is what I get out of it. I tried to figure out what this means but to no avail. Anyone? Here's my code:
[code]foreach($matches as $match)
{
        $cleandata = clean_data_scrape($match);

        print_r($cleandata);

        $res =& $db->query("SELECT count(id) FROM items WHERE id=?", array($cleandata[0]));
        if (PEAR::isError($res)) {die($res->getMessage());}

        $res->fetchRow($row);
        $res->free();

        if($row[0] == 0)
        {
                $action = $db->query("INSERT INTO items VALUES(?, ?, ?, ?, ?, ?, ?, ?)", $cleandata);
                if (PEAR::isError($action)) {die($action->getMessage());}
        }
}
[/code]Basically it takes the data from further up in the script, looks up the id to see if its already in the DB. If not it inserts it, otherwise it goes to the next one.

It puts one entry in just fine, but then gives me a DB Error: already exists on the second try.

Thanks in advance. I can't figure this one out for the life of me, I've got a similar thing working except I use a for loop instead of a foreach. Other than that, the guts of the loop are pretty much identical.

[trq]

What does the $matches array look like. Can you add this before your foreach()?

[code=php:0]
print_r($matches);
exit();
[/code]

[c4onastick]

$matches is huge, I wont fill up your screens with all of it. Here's the basic structure:
[code]Array(
  [0] => Array(
      [0] => long match
      [1] => link
      [2] => title
      [3] => image tag or text
      [4] => text
      [5] => float
      [6] => float
      [7] => date
  [1] => Array(
      [0] => long match
      [1] => ...[/code]Its the output from a preg_match_all with the PREG_SET_ORDER flag.

[trq]

Its pretty hard to tell what is happening really but your SELECT is allways selecting the same record based on $cleandata[0] is the id is it not?

[c4onastick]

[quote author=thorpe link=topic=119477.msg489437#msg489437 date=1166682515]
Its pretty hard to tell what is happening really but your SELECT is always selecting the same record based on $cleandata[0] is the id is it not?
[/quote]It shouldn't be (and isn't) $cleandata is created from the new $match variable through each iteration. Just threw in a print_r($cleandata) and commented out the rest of the loop, it looks good, the ids are different each iteration (as they should be). I think there's some quirk going on with MySQL here. I'm terrible sorry, but all I have to give you/go on is the error it burped out at me. DB Error: already exists.

[trq]

Im really not sure, never worked with PEARs db so that error meens nothing to me.

Are you maybe trying to manually insert a value to a primary key field? One which allready exists?

It might be a good idea to try and print your query each iteration and lets see what that looks like.

[c4onastick]

I think you're right. I just recreated what I want to happen on a little test DB and it worked fine. I should be able to insert into a primary key field right? (its not auto_increment or anything, I defined it as "id int(14) not null primary key") I know that all these id's I'm getting should be unique.

I tracked down that LOUSY error message. Actually a 1022 error code from mysql, which, as you suspected, is a duplicate key entry. This is really weird since I [b]know[/b] that the id is changing and they all [b]are[/b] unique.

What's even funnier, is I check for that with the previous query! It only gets to the insert if there [b]isn't[/b] a id that is the same as the one I'm trying to put in.

[c4onastick]

Ok, I figured it out. I set my id field to 'bigint', the id's I was getting were out of the range of 'int' so I'm guessing that they were rounded and caused some to look like duplicates. Thanks for the help, got me rolling down the right path.