Concatenation problem

[tommy2shoes]

I have a table (applicants) which has a number of fields including 5 for potential 'parties' - these are party1, party2, party3, party4 and party5. Any particular applicant will always have two parties but could have any number up to 5.

 

I need to export a pdf (which I can do) but I need it to include data drawn from the applicants table in the format:

 

party1, party2 and party3 OR party1 and party2 etc

 

that is, each party has a comma after it except the last but one, which has no comma but an 'and'. (I hope this is clear enough!)

 

I can't work out how to do this for a varying number of 'parties'.

 

Any suggestions/answers gratefully received.

[betterphp]

if you put the ones you have into an array, you can then do

 

implode(', ', $array);

 

to put commas between them all.

 

 

[tommy2shoes]

Thank you - but what about getting an 'and' between the last two?

 

I use a concatenation to get an address from a different query:

concat_ws(',',case when coalesce(add1,'')>''then add1 end,case when coalesce(add2,'')>''then add2 end,case when coalesce(add3,'')>''then add3 end,case when coalesce(town,'')>''then town end,case when coalesce(postcode,'')>'' then postcode end) as address

 

which (modified) would work with this party issue except for the 'and'. That's what I'm stuck on.

 

[betterphp]

well you don’t want to be doing it with sql as that will be slowwwwww.

 

start by putting all of them into an array, then check if you have more than 2, if you do remove the last one and use the implode I mentioned then add the last one back on with the and. If there are only 2 just use both with the and, and if there is just 1 just output that single one.

[Pikachu2000]

See if you can make this work for you . . .

 

<?php
$array = array('Here', 'there', 'this', 'that', 'the other');
if( is_array($array) && !empty($array) ) {
   if( count($array) < 3 ) {
      $string = implode(' and ', $array);
   } elseif( count($array > 2) ) {
      $imp = array();
      $imp[1] = array_pop($array);
      $imp[0] = implode(', ', $array);
      ksort($imp);
      $string = implode(' and ', $imp);
   }
} else {
      echo "Either \$array isn't an array, or it is empty
echo $string;
?>

Returns: Here, there, this, that and the other

[nankoweap]

how about something like:

 

<?php

// assuming you can get your values into an array
$a = array('1','2','3','4','5','6');

$count = count($a);
$result = '';
for ($i=0; $i < $count-1; $i++) {
  $result .= $a[$i] . ', ';
}
$result = trim($result, ', ');
print "$result and " . $a[$count-1] . "\n";

?>

 

some error checking in the obvious places is warranted, but this is a relatively basic approach to the problem.

[jcbones]

$array = array('party1','party2','party3','party4','party5');  //array from database.

$last = array_pop($array); //remove last value from the array.

echo implode(',',$array) . ' and ' . $last; //implode the array on comma's, then append ' and party5' to the end of it.

[tommy2shoes]

Many thanks - I'll give this a go.