tinyint problem

[vbthanks]

Hello,
My db stopped accepting entries at subscriber ID 127 because my db type was set to tinyint which only goes to 128.

I have changed it to an int which should accept 11 chars but everytime I try to add a new entry - i still get dupe entry for 127.....
Any ideas ?

[Paul S]

Try using the following query

ALTER TABLE your_table AUTO_INCREMENT = 128 

Paul

[vbthanks]

Doesn't work mate....

[Paul S]

Could you post your final table definition?

Paul

[vbthanks]

Hope this is what your after....
subscriberID int(9) NOT NULL auto_increment,

I've tried different type and values for the field and the db is showing the changes (via myadmin).
Just will not accept another entry.
Mike

[Paul S]

What query did you use to change the type of the field from tinyint to int?

I created a test table trying to recreate your scenario and it worked for me.

Paul

[vbthanks]

Hi,
i used what you posetd earlier....
ALTER TABLE subs AUTO_INCREMENT = 128
in phpMyAdmin - reported success.
Mike

[Paul S]

Originally posted by vbthanks
Hi,
i used what you posetd earlier....
ALTER TABLE subs AUTO_INCREMENT = 128
in phpMyAdmin - reported success.
Mike

No, that is not what I was asking for. Never mind, try the following queries

ALTER TABLE subs MODIFY subscriberID INT NOT NULL AUTO_INCREMENT

ALTER TABLE subs AUTO_INCREMENT = 128

then, try adding a new row.

Paul

[vbthanks]

Hi,
Paul - thanks for your help - mucho habenero
Tried what you said - db altered successfully - yet still cant get data in via form that was working okay up until tinyint filled-up.
insert via phpmyadmin works okay.
Mike

[Paul S]

Originally posted by vbthanks
Hi,
Paul - thanks for your help - mucho habenero
Tried what you said - db altered successfully - yet still cant get data in via form that was working okay up until tinyint filled-up.
insert via phpmyadmin works okay.
Mike

weird, probably there's a minor problem with your php code (you might want to post it here).

Paul

[vbthanks]

Okay....
Formhandler....

<?
include("conf.php");
$date = date("Y-m-d");

$db = mysql_connect($dbHost, $dbUser, $dbPass);
mysql_select_db($dbName,$db);

$query = "INSERT INTO $table (subscriberID, date, name, email, age, gender, homeAddress, hPhone, wPhone, mPhone, workplace, wpAddress, state)
VALUES
(0, '$date', '$name', '$email', '$age', '$gender', '$homeAddress', '$hPhone', '$wPhone', '$mPhone', '$workplace', '$wpAddress', '$state')";
// mysql error message was in here
include ("v_entry_ok.php");
?>

The form is okay....
Mike

[vbthanks]

Still isn't working for some reason. Does ayone have any ideas ? The db type for subscriberID is int
mike

[siteguru]

subscriberID is AUTO_INCREMENT, yes?

$query = "INSERT INTO $table (date, name, email, age, gender, homeAddress, hPhone, wPhone, mPhone, workplace, wpAddress, state)
VALUES
('$date', '$name', '$email', '$age', '$gender', '$homeAddress', '$hPhone', '$wPhone', '$mPhone', '$workplace', '$wpAddress', '$state')"; 

You don't usually need to define the name or value of an AUTO_INCREMENT field.

[vbthanks]

Tried what you said SiteGuru - No go mate....
Mike

[vbthanks]

bump

Can anyone help with this ?
Regards,
Mike

[Jump]

I had this problem just last week. I deleted the id field and created a new one. It filled all the existing fields with new id's #'s and has been working since.

[vbthanks]

Fixed.
Created a new table (same as first) but with subscriberID set to INT, and it works fine.
First table field type had changed to INT, but was trying to insert the next entry from the form handler with an ID that was already in-use, thereby giving me a dupe entry error.
Thanks all,
Mike

[Hierophant]

Could have gotten the same effect by deleting the index and re-adding it later.