update selected column

[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

[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'"); 
}

[xylude]

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

[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 http://www.narrengilde-kohlscheid.de/php/sonstige.phpthen press on - Zum album -.
there you see the numers as Foto 001, Foto 002 in different albums but thes albums are in ONE table

[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?

[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

[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')";

[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

[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++;
}

[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