sql query

[CanMan2004]

Hi all

I have a question about using a query in phpMyAdmin.

Currently I have a table called `myuserstable`, inside this table there is a field called `userscode`, if I want to update the field `userscode`, I use

[code]UPDATE `myuserstable` SET `userscode`='12345';[/code]

Now my problem. basically I have loads of rows of data in my table `myuserstable`, for each row, there is a number inside the field `userscode`, a sample looks like

userscode        name
23                  D Halls
456                S Smalls
2312              H Wicks
6543              J Browns
23234            V Gail

the problem is that all the number sequences I have stored in `userscode` should be prefixed with as many 0's as possible until the total characters stored is 5, for example, the sample data above, should look like

userscode        name
00023                  D Halls
00456                S Smalls
02312              H Wicks
06543              J Browns
23234            V Gail

See all the 0's before the code, if a code is 5 characters long, then it doesnt need a 0.

Is there a query I can use, which will add 0's to the start of each code and keep adding 0's until there is a total of 5 numbers.

The problem is that I have 7,500 records, and it will take a huge amount of time to do all this by hand.

Any help of guidence would be great

Regards

Ed

[alpine]

Study and try this, note that the provided SELECT and UPDATE not nessesarily fit your table construct.

[b]AND remember to backup your table data FIRST[/b]  ;)

[code]

<?php

function number_length($number,$length)
{
$number = str_split($number);
$number_rev = array_reverse($number);
$i=0;
while($i < $length)
{
  if($number_rev[$i]==false)
  {
    $number_rev[$i] = 0;
  }
  $i++;
}
$number = array_reverse($number_rev);
return implode("",$number);
}


$select = mysql_query("select id,userscore from users");
while($row=mysql_fetch_array($select))
{
$id = $row["id"];
$userscore = $row["userscore"]; // example: value is 55

// start manipulating userscore using the function number_length
$new_userscore = number_length($userscore,5);

// done manipulating, update with new userscore 00055
$update = mysql_query("update users set userscore = '$new_userscore' where id = 'id'");
}

?>

[/code]

[CanMan2004]

Thanks Alpine, the only problem is I get a

Fatal error: Call to undefined function: str_split() in /home/webs/public_html/controlpanel/dbchange.php on line 10

I dont think I have php5 installed on the server and str_split() isnt supported

Any help would be great

Thanks

Ed

[alpine]

Sorry about that, i've added a string_split function to this - working in PHP4 --> now
[code]

<?php

function string_split($string)
{
$piece = array();
$length = strlen($string);
for($i=0;$i<$length;$i++) $piece[] = $string{$i};
return $piece;
}

function number_length($number,$length)
{
$number = string_split($number);
$number_rev = array_reverse($number);
$i=0;
while($i < $length)
{
  if($number_rev[$i]==false)
  {
    $number_rev[$i] = 0;
  }
  $i++;
}
$number = array_reverse($number_rev);
return implode("",$number);
}

$select = mysql_query("select id,userscore from users");
while($row=mysql_fetch_array($select))
{
$id = $row["id"];
$userscore = $row["userscore"]; // example: value is 55

// start manipulating userscore using the function number_length
$new_userscore = number_length($userscore,5);

// done manipulating, update with new userscore 00055
$update = mysql_query("update users set userscore = '$new_userscore' where id = 'id'");
}

?>

[/code]

[Barand]

Or you could define the usercode column in the mysql table as

usercode INT(5) ZEROFILL

[Barand]

Or format on output with

printf ('%05d', $usercode);