Merge columns to ad together

[oracle765]

Hi all

 

I have some code that I cannot figure out

 

Basically I have a variable that adds a regionid to a policytype

 

what happens is

 

if it is Region1(R1) I add this to a policytype eg single or duo so it would be SingleR1 or DuoR1 or SingleR2 etc which works fine

 

the problem is when it is more than 2 people...So if it is 3 people it would be DuoR1 + SingleR1 so I am trying to merge the columns in sql but cannot figure out how to do this or if it is even possible

 

so my query would be select 'DuoR1' + 'SingleR1' from table. this policy type would go up to a maximum of ten people

 

so I would get 'DuoR1' + 'DuoR1' for 4 people, 'DuoR1' + 'DuoR1' + 'SingleR1' for 5 people and so on.

 

I have attached my code to make this clearer

 

and thanks in advance

policyandregion.txt

[jazzman1]

Can you post the code here using [ code ] tags rather than as a file attachment?

[oracle765]

switch ($destinationCode) 

			{

   		case 'WW':

			$region = 'R1';

			break;

		case 'EU':

			$region = 'R2';	

			break;

		case 'AS':

			$region = 'R3';

			break;

		case 'PC':

			$region = 'R4';

			break;

		case 'AU':

			$region = 'R5';

   			break;

}


switch ($numberofadults) 

			{

   		case 1:

			$policyandregion = 'Single' .$region;

			break;		

   		case 2:

			$policyandregion = 'Duo' .$region;

			break;

		case 3:

			$policyandregion = 'Duo' .$region + 'Single' .$region;

			break;

			}


$rew=mysql_query("SELECT `Insurer` , `omedassistance`,`excess`,`Policy Name`, `$policyandregion` AS 'Price'

FROM `travelinsurance`

WHERE 43 

BETWEEN `Age Min` 

AND `Age Max`

AND `Duration Unit` = 'Days' 

AND $num_of_days BETWEEN `DurationDaysMin` AND `DurationDaysMax`

ORDER BY `Insurer`")





MYSQL QUERY NEEDS TO BE


SELECT `Insurer` , `omedassistance` , `excess` , `Policy Name` , `SingleR1` + `DuoR1` AS 'Price'

FROM `travelinsurance` 

WHERE 43 

BETWEEN `Age Min` 

AND `Age Max` 

AND `Duration Unit` = 'Days'

AND 2 

BETWEEN `DurationDaysMin`

[Psycho]

Try:

        case 1:
            $policyandregion = "Single{$region}";
            break;        
        case 2:
            $policyandregion = "Duo{$region}";
            break;
        case 3:
            $policyandregion = "(Duo{$region} + Single{$region})";
            break;

Edited February 10, 2014 by Psycho

[oracle765]

"SELECT `Insurer` , `Policy Name`, `SingleR1` AS 'Price'
from table"

or

"SELECT `Insurer` , `Policy Name`, `DuoR1` + `SingleR1` AS 'Price'
from table"

this needs to change and add two columns together the DuoR1 and SingleR1 because it is now a 3 person policy.

"SELECT `Insurer` , `Policy Name`, `DuoR1` + `SingleR1` AS 'Price'
from table"

the policy is (DuoR1 + SingleR1)

Hi physco

 

That gets them echoed out fine, but rather than the table initially querying 1 column it now needs to query two columns in the db and add them together.  EG 3 person policy so now needs to add two columns,  I hope I am making sense

Edited February 10, 2014 by oracle765

[jazzman1]

It's too late for me (00:00) to get into it in detail, but try to apply physco's script to the sql case operator.

Try,

SELECT `Insurer` , `Policy Name`,
CASE {$numberofadults}
  WHEN 1 THEN "Single{$region}"
  WHEN 2 THEN "Duo{$region}"
  WHEN 3 THEN "(Duo{$region} + Single{$region})"
END as Price
FROM `travelinsurance`
WHERE 43
BETWEEN `Age Min`
AND `Age Max`
AND `Duration Unit` = 'Days'
AND 2
BETWEEN `DurationDaysMin`

Does the price should be an integer or just a concatenated string, I don't get it ???

Edited February 10, 2014 by jazzman1

[Psycho]

 

Hi physco

 

That gets them echoed out fine, but rather than the table initially querying 1 column it now needs to query two columns in the db and add them together.  EG 3 person policy so now needs to add two columns,  I hope I am making sense

 

 

"Echoed"? You didn't have any code that was echoing the $policyandregion value. You only showed that it was being used in a query. You obviously didn't run it - because adding the two values is exactly what it should do.

[oracle765]

/* Pass in the region from the webform */

/* EG Region R1 so select query would be R1 */

SELECT `Insurer` , `PolicyName`, `SingleR1`
CASE {$numberofadults}
  WHEN 1 THEN "Single{R1}"
  WHEN 2 THEN "Single{R1}"
  WHEN 3 THEN "(Single{R1} x 3 x 0.95)"
  WHEN 4 THEN "(Single{R1} x 4 x 0.95)"
  WHEN 5 THEN "(Single{R1} x 5 x 0.95)"
  WHEN 6 THEN "(Single{R1} x 6 x 0.95)"
  WHEN 7 THEN "(Single{R1} x 7 x 0.95)"
  WHEN 8 THEN "(Single{R1} x 8 x 0.95)"
  WHEN 9 THEN "(Single{R1} x 9 x 0.95)"
  WHEN 10 THEN "(Single{R1} x 10 x 0.95)"
END AS 'Price'
FROM `travelinsurance`
WHERE 43    /* Here we need to pass the ages from the form, Testing on only 1 traveller at age 43, what if 5 travellers and one is age 70 */
BETWEEN `AgeMin`
AND `AgeMax`
AND `DurationUnit` = 'Days'
AND 2 BETWEEN `DurationDaysMin` AND `DurationDaysMax` 
ORDER BY `Insurer`

Hi Professionals I have an update I still cannot figure out the formula or if it is even possible, apparently I do not need the DuoR1 column it will only query the SingleR1 column or SingleR2 and so on so this is what I have back from the insurance people

 

So lets say that a 40 year old traveler is $60 and a 70 year old traveller is $70 as once the age is over 59 the policy price goes up

 

if there are more than 2 travelers then it is automatically a 5% discount

 

So if there were 3 x 40 year olds and 2 x 70 year olds it would be

 

(3 x $60 + 2 x $70) x 0.95 for the 5% discount

 

what would be the best possible approach to this

 

Im not sure if this mathematical formula is possible but if it is then thanks in advance