So I am running a SQL query via PHP which is returning two rows in an array with two columns "Suppler" and "ViewAll".
I need to get the values per row and check view all is yes so send "Supplier" ID's into a variable to use in another SQL statement.
So the matching statement should be IF "View all" = 1 THEN append Supplier to variable $va for example
I've already tried the below code but can only get it to bring the first row back
protected function getAllInidents($c_id) {
//Getting view all incidents for retilaer
$contact_id = (int) $c_id;
// print_r($contact_id);
try {
//This query may bring back one row or multiple
$q1 = RNCPHP\ROQL::query("SELECT * FROM Retail.RetailerContactRel where Contact = {$contact_id}")->next();
//getting values from returned query
while ($q1r = $q1->next()) {
$ret = $q1r['Retailer'];
$va = $q1r['ViewAllTickets'];
}
//This if statemnet should evaluate the results from above and if matched the $ret id(s) needs to be passed to the ROQL statment , there could be many $ret results if there are more than one result
if ($va != "0") {
$newIncidentArray = array();
//First check if contact has logged any tickets yet if not diplsay message and exit
$roql_result = RNCPHP\ROQL::query("SELECT Incident.ID FROM Incident where Incident.PrimaryContact.Contact=" . $contact_id . " AND Incident.CustomFields.Retail.RetailerDetails is not null")->next();
if (!empty($roql_result)) {
//if user has logged tickets retrieve them and also retrieve any retailer tickets where the user has view all tickets for the retailer set to yes
$roql_result = RNCPHP\ROQL::query(
"SELECT Incident.StatusWithType.Status.LookupName as Status, Incident.ID, Incident.customfields.retail.RetailerDetails.RetailerName, LookupName, Subject, Severity, Incident.PrimaryContact.ParentContact.Emails.EmailList.Address AS CreatedByUser, Incident.CreatedTime AS CreatedTime, Incident.ClosedTime AS ClosedTime
FROM Incident
WHERE incident.customfields.retail.RetailerDetails = {$ret} AND Incident.PrimaryContact.ParentContact.Emails.EmailList.Address is not null
OR Incident.PrimaryContact.Contact = {$contact_id} AND Incident.PrimaryContact.ParentContact.Emails.EmailList.Address is not null"
)->next();
//changing the date format from unix time staff to a readable front end format
while ($inc = $roql_result->next()) {
if ($inc['CreatedTime']) {
$date = $inc['CreatedTime'];
$time = strtotime($date);
$inc['CreatedTime'] = date("m/d/Y", $time);
}
if ($inc['ClosedTime']) {
$date = $inc['ClosedTime'];
$time = strtotime($date);
$inc['ClosedTime'] = date("m/d/Y", $time);
}
$newIncidentArray[] = $inc;
}
//returning list of incidents in an array this is then passed to logic.js
return $newIncidentArray;
} else {
return "You don't have any tickets created.";
}
} else {
$newArray = array();
$newIncidentArray = array();
$a = 0;
$roql_result = RNCPHP\ROQL::query(
"SELECT Incident.ID
FROM Incident
WHERE Incident.PrimaryContact.Contact=" . $contact_id . " AND Incident.CustomFields.Retail.RetailerDetails is not null"
)->next();
if (!empty($roql_result)) {
//do same here
$roql_result = RNCPHP\ROQL::query(
"SELECT Incident.StatusWithType.Status.LookupName as Status, Incident.ID, Incident.customfields.retail.RetailerDetails.RetailerName, LookupName, Subject, Severity, Incident.PrimaryContact.ParentContact.Emails.EmailList.Address AS CreatedByUser, Incident.CreatedTime AS CreatedTime, Incident.ClosedTime AS ClosedTime
FROM Incident
WHERE Incident.PrimaryContact.Contact = {$contact_id} AND Incident.PrimaryContact.ParentContact.Emails.EmailList.Address is not null"
)->next();
while ($inc = $roql_result->next()) {
if ($inc['CreatedTime']) {
$date = $inc['CreatedTime'];
$time = strtotime($date);
$inc['CreatedTime'] = date("m/d/Y", $time);
}
if ($inc['ClosedTime']) {
$date = $inc['ClosedTime'];
$time = strtotime($date);
$inc['ClosedTime'] = date("m/d/Y", $time);
}
$newIncidentArray[] = $inc;
}
return $newIncidentArray;
} else {
return "You don't have any tickets created.";
}
}
} catch (Connect\ConnectAPIErrorBase $e) {
$warnings[] = $e->getMessage();
}
}
Just brings back the first row and cant append using .=
SQL will be something like this SELECT * FROM table WHERE supplier = (id1,id2)
