Jump to content

Archived

This topic is now archived and is closed to further replies.

CanMan2004

sql query

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
Or you could define the usercode column in the mysql table as

usercode INT(5) ZEROFILL

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.