rnumbering a table numbered 14 7 9 14 (newbe like)

[Québec]

Hi,

I have been fooling around trying to learn mySql and I have a table wich
ID's are not in continuous numbers: 1 4 8 13.

How do we renumber a table as 1 2 3 4 5 etc?

Jean Pierre
--

--
[url]http://web.jeanpierredaviau.com[/url]

[Andy Hassall]

On Wed, 8 Sep 2004 13:23:26 -0400, "Québec" <Once@WasEno.ugh> wrote:

>I have been fooling around trying to learn mySql and I have a table wich
>ID's are not in continuous numbers: 1 4 8 13.
>
>How do we renumber a table as 1 2 3 4 5 etc?

First off - why do you want to do this? If they're IDs, then gaps aren't
significant.

If you still want to, then you can't from MySQL, so do it from PHP with a
loop.

<?php
$conn = mysql_connect()
or die(mysql_error());
mysql_select_db('test')
or die(mysql_error());

mysql_query('drop table if exists t')
or die(mysql_error());

mysql_query('create table t (id int unsigned not null primary key)')
or die(mysql_error());

foreach (array(1,4,8,13) as $v)
mysql_query(sprintf('insert into t values (%d)', $v))
or die(mysql_error());

$res = mysql_query('select id from t order by id')
or die(mysql_error());
while ($row = mysql_fetch_assoc($res))
print $row['id'] . '<br>';

print "<hr>";

mysql_query('lock table t');

$res = mysql_query('select id from t order by id')
or die(mysql_error());

$id = 1;
while ($row = mysql_fetch_assoc($res))
mysql_query(
sprintf('update t set id=%d where id=%d', $id++, $row['id']));

$res = mysql_query('select id from t order by id')
or die(mysql_error());
while ($row = mysql_fetch_assoc($res))
print $row['id'] . '<br>';
?>

Outputs:

1
4
8
13
---------------
1
2
3
4

But note that this script is woefully incomplete, because presumably you'll
have foreign keys referencing this table, which would all have to be updated as
well. Which is a pretty good reason for not doing it.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Québec]

Yes.


Thank you.

Jean
"Andy Hassall" <andy@andyh.co.uk> a écrit dans le message de
news:99quj0lu6f4s6gr6ranqp70hfi1b7agdie@4ax.com...

> On Wed, 8 Sep 2004 13:23:26 -0400, "Québec" <Once@WasEno.ugh> wrote:
>

> >I have been fooling around trying to learn mySql and I have a table wich
> >ID's are not in continuous numbers: 1 4 8 13.
> >
> >How do we renumber a table as 1 2 3 4 5 etc?

>
> First off - why do you want to do this? If they're IDs, then gaps aren't
> significant.
>
> If you still want to, then you can't from MySQL, so do it from PHP with a
> loop.
>
> <?php
> $conn = mysql_connect()
> or die(mysql_error());
> mysql_select_db('test')
> or die(mysql_error());
>
> mysql_query('drop table if exists t')
> or die(mysql_error());
>
> mysql_query('create table t (id int unsigned not null primary key)')
> or die(mysql_error());
>
> foreach (array(1,4,8,13) as $v)
> mysql_query(sprintf('insert into t values (%d)', $v))
> or die(mysql_error());
>
> $res = mysql_query('select id from t order by id')
> or die(mysql_error());
> while ($row = mysql_fetch_assoc($res))
> print $row['id'] . '<br>';
>
> print "<hr>";
>
> mysql_query('lock table t');
>
> $res = mysql_query('select id from t order by id')
> or die(mysql_error());
>
> $id = 1;
> while ($row = mysql_fetch_assoc($res))
> mysql_query(
> sprintf('update t set id=%d where id=%d', $id++, $row['id']));
>
> $res = mysql_query('select id from t order by id')
> or die(mysql_error());
> while ($row = mysql_fetch_assoc($res))
> print $row['id'] . '<br>';
> ?>
>
> Outputs:
>
> 1
> 4
> 8
> 13
> ---------------
> 1
> 2
> 3
> 4
>
> But note that this script is woefully incomplete, because presumably

you'll

> have foreign keys referencing this table, which would all have to be

updated as

> well. Which is a pretty good reason for not doing it.
>
> --
> Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
> <http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Alvaro G Vicario]

*** Québec wrote/escribió (Wed, 8 Sep 2004 13:23:26 -0400):

> I have been fooling around trying to learn mySql and I have a table wich
> ID's are not in continuous numbers: 1 4 8 13.
>
> How do we renumber a table as 1 2 3 4 5 etc?

I guess you only want to do something as simple as displaying line numbers
on screen:

$res=mysql_query('SELECT * FROM table');
$line=0;
while($row=mysql_fetch_assoc($res)){
$line++;

print $line . ': ' . $row['field1'] . ', ' . $row['field2'] . '<br>';
}


If your application design depends on having consecutive IDs in your
database, you'd better redesign it from scratch.

--
-- Álvaro G. Vicario - Burgos, Spain
-- Thank you for not e-mailing me your questions
--

[Québec]

Emptying a data base set everything to zero and keep the database structure?

Jean Pierre

[Québec]

I will just try it now on a foo table
Thanks