Jump to content

adding ZEROES to existing mysql data? Should I use PHP?


suryabuchwald

Recommended Posts

I\'ve got fields full of values like:

 

S-1

AB-128

A-85

 

I need them to be:

 

S-0001

AB-0128

A-0085

 

etc. My possible idea is to write a script that will look through the records one by one, detect a number, and make sure it\'s four digits long. If it\'s not, add zeroes to the left until it is four digits, then UPDATE the row. Not quite sure how I would do this, but it seems possible (with my limited knowledge of PHP).

 

Thanks so much everyone!

Link to comment
Share on other sites

something along the lines of....

 


$sql = \'SELECT desired_field_or_all FROM ur_table\';



$result = @mysql_query($sql) or die(mysql_error());



while($row = mysql_fetch_array($result))

{

  $field_value = $row[\'desired_field\'];

  list($part_a, $part_b) = explode(\'-\', $field_value);

  if(strlen($part_b) is less than 4)

  {

     //prepend 0\'s and implode with $part_a ...and a dash;)

     //of course this loop may go further becuase it could be 1, 2 or 3 less;

     //then update same field in db with new $field_value;

   }

 

..on a note i myself am learning php so the (pseudo)code may not be perfect :P

Link to comment
Share on other sites

For anyone that\'s interested, I\'ll print the final code here.

 

There are some extra sections - I realized there were item numbers like S-MM-3 as well as just S-3, and these had to have a different handler. I\'m sure there\'s a much cleaner way to write all this code, but at this point it works, so I\'m not complaining. Here it is:

 

<?php do { ?>

<div align=\"center\"><font color=\"#333333\" face=\"Arial, Helvetica, sans-serif\"><span>

<?php

//////////// FORMAT THE INFORMATION //////////

 

$field_value = $row_rs_mine[\'item_number\']; //take the item number into a variable

echo \"item number \" . $field_value; //echo to browser

$field_array = explode(\'-\', $field_value); //split number into array, separating on -\'s

 

///////////////////////////////////////////////////////

 

 

$array_count = count($field_array); // count the number of items in the array

 

///////////// SCRIPT FOR TWO-PART ARRAY //////////

 

 

if ($array_count == \'2\') { // if the array has only two parts, deal with the second part

list($part_a, $part_B) = explode(\'-\', $field_value); // split the array into usable parts

if(strlen($part_B) < \'4\') // check if the number is okay as-is

if(strlen($part_B) == \'1\') { //add the appropriate number of zeroes

$part_b = \"000\" . $part_b;

} elseif (strlen($part_B) == \'2\') {

$part_b = \"00\" . $part_b;

} elseif (strlen($part_B) == \'3\') {

$part_b = \"0\" . $part_b;

};

$item_number_fixed = $part_a . \"-\" . $part_b; // restore original format

echo \" was turned into: \" . $item_number_fixed . \"<br />\";

$item_id_string = $row_rs_mine[\'item_id\'];

$update_sql = \"UPDATE pieces SET item_number = \'$item_number_fixed\' WHERE item_id = \'$item_id_string\'\";

mysql_query($update_sql, $conn_candyvision) or die(mysql_error());

 

////////// SCRIPT FOR THREE_PART ARRAY //////////////

 

} elseif ($array_count == \'3\'){

list($part_a, $part_b, $part_c) = explode(\'-\', $field_value); // split the array into usable parts

if(strlen($part_c) < \'4\') // check if the number is okay as-is

if(strlen($part_c) == \'1\') { //add the appropriate number of zeroes

$part_c = \"000\" . $part_c;

} elseif (strlen($part_c) == \'2\') {

$part_c = \"00\" . $part_c;

} elseif (strlen($part_c) == \'3\') {

$part_c = \"0\" . $part_c;

};

$item_number_fixed = $part_a . \"-\" . $part_b . \"-\" . $part_c; // restore original format

echo \" was turned into:\" . $item_number_fixed . \"<br />\";

$item_id_string = $row_rs_mine[\'item_id\'];

$update_sql = \"UPDATE pieces SET item_number = \'$item_number_fixed\' WHERE item_id = \'$item_id_string\'\";

mysql_query($update_sql, $conn_candyvision) or die(mysql_error());

 

////////////////////////////////////////////////////////

 

////////// SCRIPT FOR LARGER ARRAY /////////////////

} elseif ($array_count > \'3\'){

echo \" was left alone.\";

};

/////////////////////////////////////////////////////////////

 

 

?>

</span></font> </div>

<?php } while ($row_rs_mine = mysql_fetch_assoc($rs_mine)); ?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.