Hi guys.

I have a problem with an insert query that is driving me nuts! I have loads of queries running throughout my site, but because this one involves an auto increment in the table, it`s causing problem. I have scanned the other posts on this site, and though I have tried those fixes, it still wont work.

My table is:

CREATE TABLE clubstaff (
idref TINYINT AUTO_INCREMENT ,
name MEDIUMTEXT,
tel TINYINT,
email TINYTEXT,
PRIMARY KEY ( idref )
);

The code I am using (within a function) is:

$result = mysql_query( "INSERT INTO clubstaff (idref, name, tel, email) VALUES (0, '$name', '$tel', '$email'");

(and yes, I have passed all the variables to the function – I have even tried hard coding some test data in)

I then call:

printf("<br>Last inserted record has id %d\n", mysql_insert_id());

as suggested in some of the other posts, but I always get:

Last inserted record has id 0.

Using PHPmyADMIN, when I insert (successfully) the code it generates is:

$sql = 'INSERT INTO clubstaff ( idref , name , tel , email ) ';
$sql .= 'VALUES ( \'0\', \'r\', \'r\', \'r\' );';
$sql .= '';

But I have tried that, and even that doesn`t work.

Oh – I have also tried ‘’ instead of 0 for the idref – same problem, $result returns a fail from the function.

Any ideas anyone?

Thanks in advance…

    When you have autoincrement, you dont put the autoincrement field into query because it will automaticly increase by one when inserting. So just like this:

     $result = mysql_query( "INSERT INTO clubstaff (name, tel, email) VALUES ('$name', '$tel', '$email'");

    EDIT:
    Oh yeah.. Your table structure is kinda funny. Autoincrementing field is TINYINT so your maximum is 127 records(signed). How about mediumint or int so you dont run out of numbers? 🙂

    And the namefield.. MEDIUMTEXT? How long names are you gonna put there? 🙂 Usually with names you could use something like VARCHAR(255)..

      Thanks for the quick reply...

      That code is exactly how I started, but when that didn`t work I saw that a previous post said I HAD to use either '' or 0 without any '.

      Either way, I just tried that code again, and still no joy. I still get an error returned after it prints out:

      Last inserted record has id 0.

      Cheers

        To further help with the fault finding, I have added:

        echo "$name, $tel, $email, $result";

        And as suspected, it does print out the variables passed to the function - it just wont insert them into the database - there is nothing returned for $result.

          Wrote the whole lot out again, and suddenly it works. Must admit, I am none the wiser for it. I guess it might have been a glitch at the host end?? Either way, it now works with putting nothing in for the auto_increment field.

          Cheers.

            Write a Reply...