Jump to content


Photo

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


  • Please log in to reply
4 replies to this topic

#1 suryabuchwald

suryabuchwald
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 20 March 2003 - 09:57 PM

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!

#2 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 20 March 2003 - 10:21 PM

Why do u have to pull out data and do it.
Try to do that when u are inserting values that would reduce the work.!
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 suryabuchwald

suryabuchwald
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 20 March 2003 - 11:07 PM

Ah, the wisdom of hindsight :)
Yes, all the data is now being entered in 4-digit format. However, we\'ve already entered some 2500 items - I\'d rather not redo them one by one. Management forgot to mention they wanted everything in a 4-digit format until now.

#4 holiks

holiks
  • Members
  • PipPipPip
  • Advanced Member
  • 83 posts

Posted 21 March 2003 - 04:33 AM

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
__________________________________________________________[br]$php = array('..a stack', '...once you pop', '...you don't stop');

#5 suryabuchwald

suryabuchwald
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 25 March 2003 - 06:55 PM

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:

[code=php:0]<?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)); ?>




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users