Re: Inserting data without a reference in the row.

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: [PHP-DB] 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 (�����`0�5 > �Yݝ�!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 >> >>