PHP MySQL Duplicate Entries I'm going CRAZY

fivelow

Please God somebody help me I'm going crazy.
OK heres the deal. Whenever I INSERT a new record into the database I get a duplicate entry. I've uninstalled and reinstalled the database, rewritten the php.ini and written another little update script that worked fine. Whenever I try to use the real application I get duplicate entries. And the sql queries I'm using for the test and the main application are the same. I've diffed the queries to check for the usual suspects (. instead of , you know the drill) and they are identical. Have any of you run accross this sort of thing before? The really odd thing is...I could swear this was working fine when I went to sleep last night. This morning...FUBAR. I'm at the end of my rope. I have no idea what to try next. Just kinda sitting here rocking back and forth.

Any suggestions?

mrmufin

Perhaps by posting the database table structure as well as the PHP code you're using to add records, some help could be offered...

fivelow

I knew you were gonna say that.
Here goes

<?php
session_start();
require('DBConn.class');

$DBConn = new DBConn('xxx','xxx','xxx','xxx');

$retrieveSQL = "SELECT * FROM page WHERE filename='$filename'";
$retrieveResult = mysql_query($retrieveSQL);

while($query = mysql_fetch_object($retrieveResult)){

$page_id = $query->page_id;
$body = $query->body;
$filename = $query->filename;
$headImage = $query->headImage;
$rightImage = $query->rightImage;

}

$populateSQL = "INSERT INTO page_preview set page_id='$page_id', body='$body', filename='$filename', headImage='$headImage', rightImage='$rightImage', namelast='$namelast'";
$populateResult = mysql_query($populateSQL);

Heres the DB structure

CREATE TABLE page_preview (
page_id tinyint(4) default NULL,
body longtext,
filename text,
headImage text,
rightImage text,
namelast text,
KEY page_id (page_id),
KEY page_id_2 (page_id)
) TYPE=MyISAM;

This is where you guys tell me I don't know what I'm doing( I already know that). I have a very limited knowledge of databases in general and MySQL in particular. But who wants to spend a million on WebLogic and Oracle for a home inventory server, eh?

Thanks in advance.

mrmufin

Well, if that's the order that your script runs in, I see no reason why it wouldn't insert a duplicate of the last record retrieved...

Look at what it's doing:

You're selecting everything from the table, assigning the variables used in your INSERT statement from the last result object retrieved from your SELECT query. (Except for the 'namelast' column, which you're not assigning a variable from the object.) If this is the order in which your script is executing, every time you run a SELECT you're going to re-insert the last record retrieved from the select statement.

A really good way to make sure that duplicate records don't exist inside a table is to throw a UNIQUE index on any columns that you want to assure are not duplicated.

For example, if you wanted to make sure you never get duplicate records in the 'body' column, you could add:

UNIQUE body_idx (body)

into your CREATE table statement. This would create an index named 'body_idx' and never allow duplicates in that column.

If the composite of, say, the 'body' and 'filename' columns had to be unique you could create a UNIQUE index on multiple columns. For example, you could have several entries where the data in the 'body' column was 'abc' and several entries in the 'filename' column which contained 'def', but only one entry where the body colums was 'abc' AND the filename column was 'def', then you would create a unique index specifying the columns whose 'composite' values needed to be unique:

UNIQUE page_matrix (body, filename)

I've always found it best to define exactly what components must be unique in a table record and built those requirements into the table structure. At the end of the day, it can save a whole lot of error-checking on the PHP side.