Member Avatar for johndohmen1963

Hi how do i update a selected column with numbers ?

001
002
003
004
005
and so on

when i delete a row shall we say 003
i have then the numbers
001
002
004
005
thats not the problem

the problem is when i insert a new image

then i have the numbers
001
002
004
005
005
how can i update after delete

so that the numbers wil be 001, 002, 003, 004, 005, and so on

i have the folowwing code but it doesn't work proper.

$query = "SELECT nummer FROM narr_fotos_kostumball WHERE albumdescription='$album_descr'"; 
	 
$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){
		
			$tafel = "00";	
				for ($i=$row['nummer']+1;$i<=$row['nummer']+1;$i++){
		if($row['nummer']>='9'){
		$tafel = "0";
	}	
	if($row['nummer']>='99'){
		$tafel = "";
	}
			mysql_query(" UPDATE narr_fotos_kostumball SET nummer=('$tafel$i') WHERE albumdescription='$album_descr'"); 
echo $row['nummer'];
	echo "<br />";
			}	}

echo $row; i have put in to test
the output on this wil be

005, 005, 005, 005, 005 and so on depends how many rows there are.

thanks i advice John

  • 3 Contributors
  • 9 Replies
  • 115 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by johndohmen1963

Dani AI

Generated

A compact, reliable way to resequence the per‑album numbers is to do it entirely in SQL (one statement) instead of looping in PHP. This avoids race conditions and the “all rows updated to the same value” problem. Building on ideas from and , use a user variable with an ordered subquery so numbering follows the display order (use albumid rather than text albumdescription where possible).

Example (run after a backup; replace the album id placeholder):

SET @n := 0;

UPDATE narr_fotos_kostumball AS t
JOIN (
  SELECT id, LPAD(@n := @n + 1, 3, '0') AS newnum
  FROM (SELECT id FROM narr_fotos_kostumball WHERE albumid = 123 ORDER BY id) s
) seq ON t.id = seq.id
SET t.nummer = seq.newnum
WHERE t.albumid = 123;

Notes and next steps:

  • Pick the ORDER BY column that defines the intended sequence (usually id or a timestamp).
  • If you prefer numeric storage, convert nummer to INT and generate @n := @n + 1 (no LPAD) then format with LPAD(nummer,3,'0') when selecting or in the UI.
  • To prevent future duplicates per album, add a composite unique key on (albumid, nummer) — but only after cleaning duplicates (the ALTER will fail otherwise).
  • Always back up the table before mass updates. If using InnoDB, wrap changes in a transaction; for MyISAM take a dump first.
  • While fixing this, consider moving off deprecated mysql_* functions to mysqli or PDO for security and maintainability.

This approach keeps operations atomic, is fast for large albums, and centralizes logic in SQL so you do not need complex PHP loops to keep numbers contiguous.

Recommended Answers

All 9 Replies

Member Avatar for xylude

Try putting this in your loop:

if($row['nummer']!=$i) {
//You have a blank space, put your mysql query here
mysql_query(" UPDATE narr_fotos_kostumball SET nummer=('$tafel$i') WHERE albumdescription='$album_descr'"); 
}
Edited by xylude because: I had a better answer?
Member Avatar for xylude

You could also set the 'nummer' field to be unique, then mysql will prevent you from inserting duplicate items.

Member Avatar for johndohmen1963

Sorry it cannot because there are more than one albums in that table
if i do that all the images from all different albums have the numbers from 001 002 and so on to the last

here i have the code so you can see what i mean
first the insert

$error = "Es ist ein probleem mit der data verbindung, probieren sie später nochmals. Oder nim contact mit dem webmaster.";

$connect = mysql_connect("localhost", "bla", "bla") or die ($error);
mysql_select_db("narrengilde",$connect) or die($error);

$sql1 ="CREATE TABLE IF NOT EXISTS $weinachtfeier (
id int(11) NOT NULL auto_increment,
albumdescription VARCHAR(75) NOT NULL,
name VARCHAR(25) NOT NULL,
type VARCHAR(5) NOT NULL,
size INT,
location VARCHAR(150) NOT NULL,
location1 VARCHAR(150) NOT NULL,
PRIMARY KEY (id) )";
mysql_query($sql1) or die(mysql_error());

$sql="INSERT INTO $weinachtfeier (albumdescription, name, type, size, location, location1)	VALUES ('$mapbeschrijving','$thename','$imagetype','$imagesize','$loc','$loc1')";

if (!mysql_query($sql,$connect))
{
die('Error: ' . mysql_error());
						}
include "includes/table_kostumball.php";
mysql_close($connect);

$weinachtfeier = $_POST; created by <input type='hidden' name='aname' value='narr_kostumball'>

$mapbeschrijving = $_POST; created by <TEXTAREA NAME="expl" COLS=30 ROWS=2></TEXTAREA>

both from a form with a submit button

thenext
include "includes/table_kostumball.php";

<?php
$conn = mysql_connect("localhost", "bla", "bla") or die ($error);
mysql_select_db("bla",$conn) or die($error);				
$quey1="select id from narr_kostumball WHERE albumdescription = '$mapbeschrijving'";
$result=mysql_query($quey1) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)){
  $idalbum = $row["id"];
$sql2 ="CREATE TABLE IF NOT EXISTS narr_fotos_kostumball (
id int(11) NOT NULL auto_increment,
albumid INT NOT NULL,
albumdescription VARCHAR(75) NOT NULL,
name VARCHAR(25) NOT NULL,
thumbname VARCHAR(25) NOT NULL,
type VARCHAR(5) NOT NULL,
size INT,
location VARCHAR(150) NOT NULL,
location1 VARCHAR(150) NOT NULL,
nummer VARCHAR(4) NOT NULL,
PRIMARY KEY (id) )";
mysql_query($sql2) or die(mysql_error());						
$error = "Es ist ein probleem mit der data verbindung, probieren sie später nochmals. Oder nim contact mit dem webmaster.";
$conn = mysql_connect("localhost", "bla", "bla") or die ($error);
mysql_select_db("bla") or die($error);
$admin=mysql_query("SELECT * FROM narr_fotos_kostumball WHERE albumdescription ='$mapbeschrijving'");	
	$num_rows = mysql_num_rows($admin);
			If ($num_rows ==0){
				$sq2="INSERT INTO narr_fotos_kostumball (albumid, albumdescription, name, thumbname, type, size, location, location1, nummer)
						VALUES ('$idalbum','$mapbeschrijving','$thename','$thenames','$imagetype','$imagesize','$loc','$loc1','001')";
						if (!mysql_query($sq2,$conn))
						{
						die('Error: ' . mysql_error());
						}
							}	
					else{
				$tafel = "00";	
				for ($i=$num_rows+1;$i<=$num_rows+1;$i++){
		if($num_rows>='9'){
		$tafel = "0";
	}	
	if($num_rows>='99'){
		$tafel = "";
	}	
			$sq2="INSERT INTO narr_fotos_kostumball (albumid, albumdescription, name, thumbname, type, size, location, location1, nummer)
						VALUES ('$idalbum','$mapbeschrijving','$thename','$thenames','$imagetype','$imagesize','$loc','$loc1','$tafel$i')";
						if (!mysql_query($sq2,$conn))
						{
						die('Error: ' . mysql_error());
				}}
	}}
?>

to upload an image i use the same code from
include "includes/table_kostumball.php";


as you see whe i create different albums with <TEXTAREA NAME="expl" COLS=30 ROWS=2></TEXTAREA>

you can see it on then press on - Zum album -.
there you see the numers as Foto 001, Foto 002 in different albums but thes albums are in ONE table

Edited by johndohmen1963 because: n/a
Member Avatar for xylude

From what I can gather, you're trying to get the next empty field from the db right? Like if you have rows:

001
002
004
005
007

You want to get 003 and put it back into use?

Member Avatar for johndohmen1963

yes but as i told when i upload a new image i got 001 002 004 005 006 006

with the above code

P.s. i am back in 3 hours

Member Avatar for xylude

You could use the following function to get the next number, I didn't test it or anything but it should pretty much handle the insert process.

function getNextNumber() {
$getRows = mysql_query("SELECT nummer FROM narr_fotos_kostumball ORDER BY nummer ASC");
$rows = mysql_num_rows($getRows);
$i=1; //assuming the first number is 1
if($rows>0) {
  while($rows = mysql_fetch_assoc($getRows)) {
    if(str_pad($i,2,"0",STR_PAD_LEFT)!=$rows['nummer']) {
      return str_pad($i, 2, "0", STR_PAD_LEFT);
    }
    $i++;
  }
  return str_pad(mysql_num_rows($getRows)+1, 2, "0", STR_PAD_LEFT);
} else {
  return "001";
}
}

//to use it
$next = getNextNumber();
$query="INSERT INTO narr_fotos_kostumball (albumid, albumdescription, name, thumbname, type, size, location, location1, nummer)
VALUES ('$idalbum','$mapbeschrijving','$thename','$thenames','$imagetype','$imagesize','$loc','$loc1','$next')";
Member Avatar for johndohmen1963

wel its not exactly what i need.
is there not a way to update the whole column and than start at the first cell in column nummer
something like

$query = "SELECT nummer FROM narr_fotos_kostumball WHERE albumdescription='$album_descr'"; 
	 
$result = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($result);

	
	$i=1;
while($i<=$num_rows)
  {
	$tafel = "00";	
				
		if($num_rows>='9'){
		$tafel = "0";
	}	
	if($num_rows>='99'){
		$tafel = "";
	}

	 echo "The number is $tafel$i<br />";  //here ar the number 001 002 003 and so on
  

	mysql_query(" UPDATE narr_fotos_kostumball SET nummer=('$tafel$i') WHERE albumdescription='$album_descr'"); 

  
 $i++;

  }

but i can not update the column on that way because it update nummer as the last row.
in this case if there are 6 rows nummer wil be 006 in all rows in column nummer

Member Avatar for ddymacek

you should really use an autoincrement field on your table. but this should work for you desired solution:

//change your while statement and update query:
$i=1;
// old:
// while($i<=$num_rows)
//new:  purpose to only update one row on table, use the 'nummer' retrieved in previous query
while ($row = mysql_fetch_array($result)) 
{
$nummer = $row['nummer'];  // pull the current 'nummer' out of the query.
$tafel = "00";
 
if($num_rows>='9'){
$tafel = "0";
}
if($num_rows>='99'){
$tafel = "";
}
 
echo "The number is $tafel$i<br />"; //here ar the number 001 002 003 and so on
// old
// mysql_query(" UPDATE narr_fotos_kostumball SET nummer=('$tafel$i') WHERE albumdescription='$album_descr'");
//new:  insert to that album and nummer for a distinct row.
mysql_query(" UPDATE narr_fotos_kostumball SET nummer=('$tafel$i') WHERE albumdescription='$album_descr' and nummer = $nummer"); 
$i++;
}
Member Avatar for johndohmen1963

Thanks i now i had to make an autoincrement field but than i had to rewrite all my code of all the pages a made

wel thank for your help thats just what i need.

greets John

Edited by johndohmen1963 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.