Hi!
Hm - i hope i got you right? Do you want to add a column or a row to your
table or do you just want to fix your primary keys?
Yes - the problem with "my" update-query is that it does only work if the
updated column is no primary key. (mysql goes through every entry from top
to bottom, and if you use an increment or decrement there may exist double
entries for the primary key)
It would be much easier if you add a second column wich contains the number
of the movie and is not a primary key...
To fix the primary key i would just suggest to do this (am i right in saying
movieid is the primkey?) - was quite hard to code this funny thing, but
interesting! :)
$qry = 'SELECT `movieid` FROM `movies` ORDER BY `id`';
$res = mysql_query($qry);
$queries = array();
$arr = array();
while($ent = mysql_fetch_array($res))
$arr[] = $ent['id'];
$old = 1; // is normally 1 if you have an autoincrement and if i am right
for($c = $old; $c < count($arr) - 1 ; $c++)
{
$diff = $arr[$c+1] - $arr[$c];
if($diff > 1)
{
$queries[] = 'UPDATE `movies` SET `movieid`= `movieid`-'.($diff-1).'
WHERE `id`>'.$arr[$c].' ORDER BY `movieid`'; // the "ORDER BY" is only
necessary if your movieids are not in the right order in your db (which is
not normal - this happens only if you edit the db yourself with phpmyadmin
or so) - i have to remark that this works only with mysql 4.0.0>
}
$old = $arr[$c];
}
$queries = array_reverse($queries);
foreach($queries as $qry)
{
$res = mysql_query($qry);
echo '<tt>'.$qry.'</tt><br>'.mysql_error();
}
Hope this code works for you?
.ma
Richard Hov <rhov@c2i.net> wrote@21.04.2003 1:58 Uhr:
> Hi and thanks for your time and knowledge. : )
>
> I think I explained a little unclear. But I'll try another time. I have to
> update all the new columns at the end of my movietable where the primarykeys
> have plenty of holes because of deleted rows. I didn't manage to get your
> code work so here is my idea : )
>
>
> $db = mysql_connect("localhost", "root");
>
> mysql_select_db("richard",$db);
>
> $result = mysql_query("SELECT movieid, artnr FROM movies");
>
> $antRader = mysql_numrows($result);
>
> $qry = 'SELECT movieid FROM movies';
>
> $n=0;
> while($rad = mysql_fetch_array(mysql_query($qry)))
> {
> $pKey[$n] = $rad[0];
> $n++;
> }
>
>
> for($i=0;$i<$antRader;$i++)
> {
> mysql_query("UPDATE movies SET artnr=$i WHERE movieid = $pKey[$i] = $i");
> }
>
>
> But I get a timeout on this code, and cant sort out whats wrong here.
>
> Richard
>
>
> ----- Original Message -----
> From: "heilo" <grillen@abendstille.at>
> To: "Raymond Lilleødegård" <rhov@c2i.net>
> Sent: Monday, April 21, 2003 1:30 AM
> Subject: Re: Inserting data without a reference in the row.
>
>
> Hi!
>
>
> Sorry, first i got you wrong - reading is quite complicated - i know :)
>
> So here is my suggestion:
>
> $primkey = 325;
>
> $qry = 'SELECT `number` FROM `movies` ORDER BY `number` DESC LIMIT 0, 1';
> $max_number = mysql_fetch_array(mysql_query($qry));
> $max_number = $max_number['number'];
>
> $qry = 'INSERT INTO `table` (`col1`, `col2`, `col3`, `col4`, `number`)
> VALUES ("val1", "val2", "val3", "val4", '.($max_number+1).')';
>
> $res = mysql_query($qry);
>
> So. this should work i thing :)
>
> .ma
>
> Raymond Lilleødegård <rhov@c2i.net> wrote@21.04.2003 1:11 Uhr:
>
>> Hi!
>>
>> I'm trying to add a new column in my moviedatabase because I want to have
> a
>> movienumber that doesn't have "holes" between the movies when I delete a
>> movie. So I have to update the new column with increasing numbers. Is it
>> possible to refer to row with a mysqlnumer of any kind?
>>
>> Best regards Richard
>>
>>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
