CanMan2004 Posted October 24, 2006 Share Posted October 24, 2006 Hi allI 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 likeuserscode name23 D Halls456 S Smalls2312 H Wicks6543 J Browns23234 V Gailthe 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 likeuserscode name00023 D Halls00456 S Smalls02312 H Wicks06543 J Browns23234 V GailSee 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 greatRegardsEd Quote Link to comment Share on other sites More sharing options...
alpine Posted October 24, 2006 Share Posted October 24, 2006 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]<?phpfunction 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] Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted October 25, 2006 Author Share Posted October 25, 2006 Thanks Alpine, the only problem is I get aFatal error: Call to undefined function: str_split() in /home/webs/public_html/controlpanel/dbchange.php on line 10I dont think I have php5 installed on the server and str_split() isnt supportedAny help would be greatThanksEd Quote Link to comment Share on other sites More sharing options...
alpine Posted October 29, 2006 Share Posted October 29, 2006 Sorry about that, i've added a string_split function to this - working in PHP4 --> now[code]<?phpfunction 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] Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2006 Share Posted October 29, 2006 Or you could define the usercode column in the mysql table asusercode INT(5) ZEROFILL Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2006 Share Posted October 29, 2006 Or format on output withprintf ('%05d', $usercode); Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.