DB numbering issue

[itisnot_me]

ok so just ran into a problem using tinyint instead of int i have hit the 127 max.

but i did not realize that on time and now i have about 100 entries that i need to fix with a loop to do an increment of a counter

this is what i have. but if i was to run it it only makes every row with the 127 to 128

$counter = 1;
$correct = 127+$counter;

$result1 = mysql_query("SELECT * FROM paperwork WHERE id = '127' ");
		  while($myrow1 = mysql_fetch_array($result1))
		  {
            $query = "UPDATE paperwork SET id ='$correct' WHERE id= '127'";

            $result = mysql_query($query);

            if($result){
echo "yay";
                $counter++;

            }
		  }

thanks in advanced

[Wraithmanilian]

Run this first:

<?php
$result1 = mysql_query("ALTER TABLE  `tablename` CHANGE  `paperwork`  `paperwork` INT NOT NULL");
?>

If it needs to be a larger int, let's make it a larger int. :)

[itisnot_me]

Run this first:

<?php
$result1 = mysql_query("ALTER TABLE  `tablename` CHANGE  `paperwork`  `paperwork` INT NOT NULL");
?>

If it needs to be a larger int, let's make it a larger int. :)

so what does each paperwork do. also the paper work is the table name and i am trying to change the field id

thanks

[diafol]

I really don't understand what you're trying to do here. To avoid db probs - simply change tinyint to int(4 or 5). Am I correct in thinking that you want to increment each id value by one? This is tricky if you have id as primary key. If you start incrementing primary key by one and start at lowest number - you'll probably get duplicates - not allowed, so to get around that, update in descending order:

$r = mysql_query("UPDATE table SET id = id + 1 ORDER BY id DESC");

Haven't tried an ordered update before so don't know if it will work.

[Wraithmanilian]

Ah, my misunderstanding. Then it should look like this:

<?php
$result1 = mysql_query("ALTER TABLE `paperwork` CHANGE `id` `id` INT NOT NULL");
?>

That should convert the field 'id' in the table 'paperwork' to an INT, instead of a TINYINT.

Hope this helps.

[Wraithmanilian]

Sorry, had to edit there...

[itisnot_me]

wait.

i have already have changed the tinyint to int. but i would still need to fix the duplicated (same) ids to be unique. so i tried to make a counter loop to make it so that 128 will be 129, 130, 131 and so on. it seems that then i run what i have now it only takes the 128 and add one to all of the 128s to make them 129's

[Wraithmanilian]

:$

I just figured out MY misunderstanding. Thanks for the heads up, ardav.

[Wraithmanilian]

Are you using, or have access to, phpMyAdmin?

If so, you could do a table dump to the screen, copy to text, renumber, empty the old table, reset the increment, and repopulate.

I had to do that once. A pain, yes, but better than losing data. :)

[itisnot_me]

so i figured out a way to fix the numbering issue without having to manually do it. i have add the code below. have fun with it

$counter = $_GET['counter'];
$fix = 130;
$correct = $fix+$counter;
$result0 = mysql_query("SELECT * FROM paperwork WHERE id='130' LIMIT 1");
while($myrow0 = mysql_fetch_array($result0))
	{

            $query = "UPDATE paperwork SET id ='$correct' WHERE id= '130' LIMIT 1";

            $result = mysql_query($query);

            if($result){
echo 'yay';
$counter2 = $counter+1;
echo '<meta http-equiv=Refresh content=1;url=counter.php?counter='.$counter2.'>';
                $counter++;

            }
	}