Newbie PHP MySQL help

[alicedavidson]

Hi, I have a form that allows me to insert comments and a total, however once I delete a comment or total I can no longer re-insert a new total or comment. I simply get : SQL Insertion error: Duplicate entry 'xx for key 'PRIMARY'. I am using the myisam engine. MySQL Server version: 5.1.41

 

The table consists of 4 fields.

MID : Int (primary autonumber)

EID, : Int

Total, :text

Comment. :text

 

I have two other tables. Member, primary key is mid (member id) and Event primary key is eid (event ID). I dont think I have them relational though.

 

This is my insert function for the update causing dupe error.

 

function insert_me($mid, $eid, $total, $comments) {
        $esc_mid = mysql_real_escape_string($mid,  $this->conn);
        $esc_eid = mysql_real_escape_string($eid, $this->conn);
        $esc_total = mysql_real_escape_string($total, $this->conn);
        $esc_comments = mysql_real_escape_string($comments, $this->conn);
        $sql = "insert into memberevent (mid, eid, total, comments)
values ('{$esc_mid}', '{$esc_eid}', '{$esc_total}', '{$esc_comments}')";

        $result = mysql_query($sql, $this->conn);

        if (!$result) {
            die("SQL Insertion error: " . mysql_error());
        } else {
            $numofrows = mysql_affected_rows($this->conn);
            return $numofrows;
        }
    } 

 

This is the page side code :

 

Enter lifter total:<input type="text" name="total" id="total" /><br />
Enter comment:<input type="text" name="comments" id="total"/><br />
<input type="submit" value="Save" />
</form>

<?php
}

else//post it into database
{
$mid = $_POST['mid'];
$eid = $_POST['eid'];
$total = $_POST['total'];
$comments = $_POST['comments'];
$db1 = new dbme();
$db1->openDB();
$numofrows = $db1->insert_me($mid, $eid, $total, $comments);
echo "Success. Number of rows affected:
<strong>{$numofrows}<strong>";

$db1->closeDB();
}
?>

[kickstart]

Hi

 

Why are you trying to specify a value on an insert of an autonumber key?

 

All the best

 

Keitrh

[alicedavidson]

Hi Keith..I'm sorry if I sound dumb but I don't really know what you mean. What I think is the problem is the auto increment bit, and that is somehow conflicting with an existing value. It's as if it's trying to auto increment when I am simply trying to make a change to an existing ID and the rest of it's corrosponding field.

[kickstart]

Hi

 

OK, no probs.

 

Assuming a lower level of knowledge.

 

A table should have a primary key which is unique to each row. Sometimes this can be meaningful but often it is just a sequentially generated number. For generated numbers you can use an auto_increment value. By default this is just 1 for the first record, 2 for the next, etc. The actual value is unimportant (and you shouldn't rely on it being in any particular pattern), as long as it is unique.

 

If you are using such a primary key you do not need to specify a value for it on the INSERT. Either do not specify that columns value or specify it as NULL (which is what I tend to do). In this case mysql just uses the next available value.

 

If you do try and specify a value then you need one that hasn't been used before. And this might not be as trivial as you might think (ie, someone else could do an insert between you finding the next value and doing the insert). As it should be meaningless it is best to just let mysql determine the value itself.

 

If you have an existing record which you want to change the values in then you use an UPDATE statement rather than an INSERT. With an UPDATE you need to specify the record(s) that you want to update. If it is a single record then you can just use the primary key value to uniquely identify they record to be updated.

 

All the best

 

Keith