Adding column - simple for some - impossible for me

Here's my db shot...

At the bottom of the 'boxvalue' column (text) you will see the values separated by ;
I need to add those values together, up them into the 'monetary' column (decimal 10,2), then add the rows in the monetary column together for a Total. Any help would be greatly appreciated, I'm just not that good yet.

EDIT: I changed the value separator to + instead of ; if that helps but the last post ends in +...

 

This will be a fairly complicated process. I see in the boxvalue that you are allowing for non-numeric data. I can see why you did that, but it makes your life harder.

If this is still in the development process, I would suggest making the boxvalue column its own table. Then you could have one record for each value.

Some questions.
Does the monetary column have a defualt value or not?
When there are two or more entries in the boxvalue cell, does the last item end with '+' (i.e. is it 1 + 2 + 3 or 1 + 2 + 3 +)?
Will all of the values in boxvalue be X.XX or might they be XX.XX or even XXX.XX?
Are there any other characters besides numbers, ., and the separator (such as carriage returns, spaces, etc...)?

Here is the process in pseudo code: (the actual code could vary slightly depending on what database you are using)

Check if boxvalue is NULL. (check this first, or you will get errors)
-- If boxvalue is null and monetary has a default value of 0.00 then drop out of the current record and move to the next record.

-- If boxvalue is null and monetary has no default, then set monetary = 0.00 for that record and move to the next record.

-- If boxvalue is not null then follow this procedure

--- Look for the separator in the string
--- If there is no separator check if the value is numeric
--- If the value is not numeric, either drop out of that record or enter 0.00 in monetary and then drop out of that record
--- If the value is numeric, convert the string to a float/decimal and update monetary with the converted value then drop out to the next record.

With me so far? This is where it gets a little tricky.

--- If you are still in the routine at this point, then the next step is to parse the value of boxvalue. The parsing will really depend on what other characters (if any) are in this field and whether there are limits on the value (X.XX vs XX.XX).
---- First, take out spaces, carriage returns, and any other characters besides the actual values and separators (also leave the decimal).
---- If the value size is always in the format of X.XX, then you can take the first five characters, chop off the last character, then convert to a float.
---- If the last value ends with a separator, then loop through until there are no more separators.
---- If the last value does not, loop through one additional time after the last separator.
---- Drop out to the next record. Once all records have been processed, sum the montary column. You could have been doing this all along with by storing the values in a variable.

Other Questions:
What database are you using?
Is this shot representative of your tables? (If this is test data you just threw in, this may not be a problem. If this is really what the data would look like, then you may need to restructure the tables altogether.)

I can provide a MS-SQL code example of the psuedo code if you would like.

 

Ok... I understand what you are saying in most cases but my skills are limited. This is in development and it is a miracle I've gotten this far, believe me! I am very limited (almost a newb) when it come to db stuff, my php is almost up to snuff though (don't ask how I managed one without the other, a lot of hack and paste) The code that creates these boxes is so complicated it would turn most peoples stomach and the entire process from dynamically creating the boxes to checking and posting the data spans many pages. I tried segregating the initial input of text based and int based values and was actually successful except for the "checkboxes" that's my thorn... In other words, I can seperate the two columns if need be.... boxvalue for text and monetary for ints but it still won't really solve my problem when it comes to the checkboxes. I tried (in a very juvenile way) to pull the data from the boxvalue table and used ltrim() and rtrim() but it was hopeless and way beyond my talents anyway to no avail. If you know of a way to enter the data at first into the decimal table, while separating the the checkbox values that would be helpfull... one problem though, the checkbox ids are dynamic and all the same as an array (freakin' nightmare)


OH ya' your questions... hang on, I'll go through them...

-monetary does have a default of 0.00 (but doesn't have to)
- last value in checkbox row will end in +
-boxvalue can be up to xxxxxxxxxxxxxxxxx.xx (but highly unlikely unless bill gates starts selling off his assets)
-there could be other characters ie. carage returns, whitespace, commas, etc. but only in the text or textarea boxtypes
- actual shot of one of the tables (I can give you the others if needed)
-MySQL

- Thanks a gabillion for taking the time!

 

I have code that will do exactly what you want. I will pull it up when I get home from work.

 

I can't thank you enough, I've been a day and a half on it. Much appreciated!

 

Sorry, I got a call and forgot about you last night. I will get that code tonight.

 

no problem, so far I've got rid of the white space and have it printing

0.50+1.50+1.00+2.50+

Code (markup):

but if I try to add 0.00 to the end of it ie.

0.50+1.50+1.00+2.50+0.00

Code (markup):

all I end up with is .00 and the whole thing isn't in decimal anyway.... one frustrating piece of work

I tried adding rand() to the boxid's hoping it would seperate them in the table but it didn't work, if I could just seperate the boxid array somehow it would work.... I can't really see why it groups them by looking at the tables but it DISTINCT calls everything from one table (without the id) and ups it into the table I showed you. If I add id to the call it messes up the whole works. I even tried $boxid$id to seperate them but that didn't work either.

 

OK... I finally got that row to print 0.50+1.50+1.00+2.50+0.00

now what? How do I convert it to decimal (10,2)? Man I am determined to learn sql stuff, I can't understand why it doesn't add the string (except that it is in text and not int)

 

Ignore this post...

 

You have to loop through the process, but here is what you need.

First check if boxvalue is null

IF boxvalue IS NOT NULL THEN...
(if it is null, then you are done with that row as monetary should be 0.00)

Then check to see if there is a separator

IF CHARINDEX('+', boxvalue) = 0 THEN...

If there is no separator this will return a 0, otherwise it will return the position of the first +.

Now, if the above statement returned a 0, then you need to check to see if it is a number and not 'dee.'

IF ISNUMERIC(boxvalue) THEN...

If that returns true, then you would convert the text to a numeric type, then update monetary with the value of boxvalue and add that number to Total.

SELECT monetary = CAST(boxvalue AS decimal)

If the ISNUMERIC returned false, then you are done with the current row.

Now, if the CHARINDEX returned a value other than 0 this is the tricky part.

You'll want to loop through the field until there are no + left.

WHILE CHARINDEX('+', boxvalue) != 0

To get the first value use
LEFT(boxvalue, charindex('+', boxvalue) - 1)

To get the rest of the string use
RIGHT(boxvalue, len(boxvalue) - charindex('+', boxvalue))

You may need to adjust the right and left functions to be in just the right spot.

On the resut from the LEFT(), go through the ISNUMERIC bit again. Use the portion from the RIGHT() to loop through until the condition is not met. The fact that 0.00 at the end does not result in a + is fine. You don't really need to add 0 to anything.

The methods I have here work well with stored procedures. You will need to throw some placeholder variables in there as well.

 

Ok... looks good but you totally lost me... sorry. Here is the query I'm working from, it supports all of the other boxtypes as well...
Oh ya' if it's possible to seperate the array as seperate posts, that will also solve the problem...

  	$getFields = mysql_query("SELECT DISTINCT boxid, boxname, boxtype, active FROM opfields WHERE (categoryid='".$cat."' or categoryid='0') AND (productid='$row[product]' or productid='0')") or die(mysql_error()); 
  	$isFields = mysql_num_rows($getFields);
  	if ($isFields) {
		while ($fields=mysql_fetch_array($getFields)) {
			$box_value = "";
			[COLOR="Red"]if ($fields['boxtype']=="checkbox") {
				for ($i=0; $i<count($_POST['box'.$fields['boxid']]); $i++) 
					$box_value .= $_POST['box'.$fields['boxid']][$i]."+";[/COLOR]
			} else { 
				$box_value = $_POST['box'.$fields['boxid']];
			}
if ($fields['boxtype']=="checkbox") {
if ($box_value!=='') {
			$addFieldData = mysql_query("INSERT INTO opfields_data
			(prodid, custid, boxid, boxname, boxvalue, monetary, boxtype) VALUES
			('".$_GET[product]."','".$ses."','".$fields['boxid']."','".$fields['boxname']."','".remSpecialChars($box_value)."','".remSpecialChars($box_value)."','".$fields['boxtype']."')") or die(mysql_error());

}

Code (markup):

P.S. Thanks again druidelder

 

Problem solved... thanks again druidelder for all your time and extreme patience