Mixed results generating a sql query.

[fintan]

Hi I've a class that issues surveys to people through email.
Everything works fine except for one part.

I was wondering could someone throw some light on where I'm going wrong. There's more to the class but the most relevent part is

$assignedID .= ($i == ($usersurvey_count-1))? $usersurvey_rslts[$i]['hd_id'].' ' : $usersurvey_rslts[$i]['hd_id'].', ' ;
$loggedby .= ($i == ($usersurvey_count-1))? $usersurvey_rslts[$i]['loggedby'] : $usersurvey_rslts[$i]['loggedby'].'#' ;

Which helps build the next query.

Depending on what month is passed say 2007-01 you get
IN(51507, 52075, 51886)
But if 2007-02 is passed you'll get
IN(53098, 52343, 52741, )

My problem being the extra comma. I tried using is_odd() to see if it would fix the problem with no joy.

Thanks

fintan.

$mail = new PHPMailer();

class UserSurvey{

public $year_month, $server="localhost", $port='5000', $url='/rpcProcess', $debug=true;
var $group = array('00000000000000000000000000000001' => 'Helpdesk', '00000000000000000000000000000002' => 'Infastruture', '00000000000000000000000000000003' => 'Sys Dev','00000000000000000000000000000004' => 'Security/Web', '00000000000000000000000000000005' => 'Procurement', '00000000000000000000000000000006' => 'Other');
var $notstaff = array('00000000000000000000000000000007' => 'Contractor', '00000000000000000000000000000008' => 'Other Staff');
var $months_array = array('','January','Febuary','March','April','May','June','July','August','September','October','November','December');

function emailSurvey($UserDetails){ // Mailer function
}

function getRecords(){ /* Retrieves all records for the survey */

if(empty($this->year_month)){die('Month & Year have not been supplied. Cannot continue script.');}

$year_month = $this->year_month; // Makes a local copy so it can be concatinated.

/* The variable $date should be in yyyy-mm format; 2007-06 */
$split_date = split('-', $this->year_month);
$split_month = (int) $split_date[1]; # settype(mixed &$var, string $type);
$months_with_val = array('January'=> 31, 'Febuary'=> 28, 'March'=> 31, 'April'=> 30, 'May'=> 31, 'June'=> 30, 'July'=> 31, 'August'=> 31, 'September'=> 30, 'October'=> 31, 'November'=> 30, 'December'=> 31);
$month_max_days = $months_with_val[$this->months_array[$split_month]];
$error_array= array();

/* Sets the appropriate date to filter by. */
$startdatetime = $year_month."-01 00:00:01";
$enddatetime = $year_month."-$month_max_days 23:59:59";

/* Retrieve records of calls logged during the required period */
$rsUserSurvey = "SELECT h.hd_id, h.loggedby, h.datelogged, h.details, MAX(hh.historydate) AS historydate, c.category, sc.subcat
FROM helpdesk h LEFT JOIN helpdeskhistory hh USING(hd_id) LEFT JOIN categories c USING(cat_id) LEFT JOIN subcat sc USING(subcat_id)
WHERE datelogged BETWEEN \"$startdatetime\" AND \"$enddatetime\" AND hh.actionid = 4 GROUP BY hd_id ORDER BY hh.historydate ASC";
$usersurvey = new Get_MYSQL_RS(); $usersurvey_rslts = $usersurvey->MYSQL_LOOP($rsUserSurvey);$usersurvey_count = $usersurvey->MYSQL_COUNT($rsUserSurvey);

function is_odd($int){ // Detects if a number is odd. Returns 0 for even & 1 for odd
return( $int & 1 );
}
$sub = (is_odd($usersurvey_count) == 1)? 1 : 2 ;

/* Builds filter
* A: to find out the appropriate ids to filter who it was assigned to
* B: who logged the call
* C: Strip out calls that are not relevent i.e. Contractors & Other Staff
* */
for($i=0;$i<$usersurvey_count;$i++){
if(array_key_exists($usersurvey_rslts[$i]['loggedby'], $this->notstaff)){// Strips out other staff and contractors
$usersurvey_unset[] = $usersurvey_rslts[$i]['hd_id'];
unset($usersurvey_rslts[$i]);
}
else{
$assignedID .= ($i == ($usersurvey_count-$sub))? $usersurvey_rslts[$i]['hd_id'].' ' : $usersurvey_rslts[$i]['hd_id'].', ' ;
$loggedby .= ($i == ($usersurvey_count-$sub))? $usersurvey_rslts[$i]['loggedby'] : $usersurvey_rslts[$i]['loggedby'].'#' ;
}
}

/* Retrieve records of calls assigned to during the required period. Ordering by historydate and in assending order is necessary. */
$rsAssignedTo = "SELECT hd_id, itstaffid, historydate FROM helpdeskhistory
WHERE hd_id IN($assignedID) AND actionid = 2 ORDER BY historydate ASC";
//$pattern = '/, \)/i';
//$replacement = ')';
//$rsAssignedTo = preg_replace($pattern, $replacement, $rsAssignedTo);
print_r($rsAssignedTo.' '.$usersurvey_count);
$assignedto = new Get_MYSQL_RS(); $assignedto_rslts = $assignedto->MYSQL_LOOP($rsAssignedTo);$assignedto_count = $assignedto->MYSQL_COUNT($rsAssignedTo);

for($x=0;$x<$assignedto_count;$x++){ /* Build a temporary array. Finding all assigned to ids. Filters out duplicates */
$temp_assigned[$assignedto_rslts[$x]['hd_id']] = array('historydate'=>$assignedto_rslts[$x]['historydate'], 'itstaffid'=>$assignedto_rslts[$x]['itstaffid']);
//$NotassignedID .= ($x == $assignedto_count-1)? $assignedto_rslts[$x]['hd_id'] : $assignedto_rslts[$x]['hd_id'].', ' ;
}

$y = 0;
die();
}
}
$x = new UserSurvey();
$x->year_month=$_SERVER['argv'][1];
$x->debug=false;
$x->buildResult();

[eelixduppy]

Would you be able to just trim it off the end of the string before you put it into the parenthesis?

#after loop
$assignedID = [url=http://www.php.net/rtrim]rtrim[/url]($assignedID,',');