[RESOLVED] Traffic Lights

[BarryBKS]

Hi,

Setup:
PHP 5.4
MS SQL using SQLSRV driver
Running on Windows Server 2012

I have a database table that contains records for different online services.

Each record contains a status code of the following,

SC001 - Green

SC002 or SC003 - Amber

SC004 or SC005 - Red

I need to set up a php script that looks at this status code list and picks up on the higher status to issue a single image of importance.

ie:
All at SC001 = Green image.
Most at SC001, but 1 at SC002 = Amber mage.
Most at SC001, but 1 at SC005 = Red image.

I've got my db lookup and basic SQL query which is just extracting all of the SC codes into a single array.

My issue is getting the script to pick out the priority code and apply the correct image.

I have tried IF.. Else and I've also tried SWITCH... CASE, but the last code in the list is always applied, which isn't always the highest priority. 🙁

Existing code:

 // Reset Image variable
  $dis_image = "";

  $s_sql = "SELECT Current_Status_ID FROM IT_t_services";

  $params = array();
  $cursorType = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
  $getServices = sqlsrv_query($conn, $s_sql, $params, $cursorType);
  {
  if(sqlsrv_has_rows($getServices))
    {
    $rowCounter = sqlsrv_num_rows($getServices);

 while( $row = sqlsrv_fetch_array( $getServices, SQLSRV_FETCH_ASSOC))
    {
   foreach ($row as $field)

   echo $field;
      {     
   if ( $field ='SC005 ')
      {
      $dis_image = "isams_red.jpg";

      }
   else if ( $field ='SC004 ')
      {
      $dis_image = "isams_red.jpg";

      }
   else if ( $field ='SC003 ')
      {
      $dis_image = "isams_amber.jpg";

      }
   else if ( $field ='SC002 ')
      {
      $dis_image = "isams_amber.jpg";

      }  
   else if ( $field ='SC001')
      {
      $dis_image = "isams_green.jpg";
       } 
     }
   } 
   displayImage($dis_image, $rowCounter);
  }

}
function displayImage($dis_image, $rowCounter) 

{
echo "<br/>".$rowCounter." rows.<br/>";
echo "Error Status : <img src='/images/status/".$dis_image."'>";
}

/* Free the statement and connection resources. */
sqlsrv_free_stmt( $getServices );
sqlsrv_close( $conn );

Any ideas?

Cheers,

Barry.

[NogDog]

$dis_image will always end up being based on whichever DB row happens to be read last. Maybe you could just grab the "highest" status with something like:

SELECT Current_Status_ID
FROM IT_t_services
ORDER BY Current_Status_ID DESC
LIMIT 1

[BarryBKS]

NogDog,

Thanks for the tip. Unfortunately I can not use LIMIT as it is a MySQL function and I'm using MS SQL. 🙁

However, you did send me in the right direction and I am now getting the correct information back from my DB. 🙂

SELECT TOP 1 Current_Status_ID 
FROM IT_t_services 
ORDER by Current_Status_ID DESC

This returns the highest status id as a resource.

I have worked out how to get the string for the Current_Status_ID, but I am having trouble with what should be the easiest part, the IF.. ELSE.. 😕

I have got the db returning SC002, which is an amber alert, but the script still displays the red image, which is the first on my IF.. ELSE.. list.

Would you mind running your eyes over the code for me.

  $s_sql = "SELECT TOP 1 Current_Status_ID FROM IT_t_services ORDER by Current_Status_ID DESC";

  //$params = array();
  //$cursorType = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
  $getServices= sqlsrv_query($conn, $s_sql);


  sqlsrv_fetch($getServices);

  $field = sqlsrv_get_field($getServices, 0);

   echo $field;

   if ( $field ="SC005")
      {
      $dis_image = "isams_red.jpg";
      }
   else if ( $field ="SC004")
      {
      $dis_image = "isams_red.jpg";
      }
   else if ( $field ="SC003")
      {
      $dis_image = "isams_amber.jpg";
      }
   else if ( $field ="SC002")
      {
      $dis_image = "isams_amber.jpg";
      }  
   else if ( $field ="SC001")
      {
      $dis_image = "isams_green.jpg";
       } 
    else
      {
      $dis_image = "No image";
      }


displayImage($dis_image);



function displayImage($dis_image) 

{
//   echo "<br/>".$rowCounter." rows.<br/>";
     echo "Error Status : <img src='/images/status/".$dis_image."'>";
}

Cheers,

Barry.

[Weedpacket]

In

       if ( $field ="SC005") 

and similar lines you're confusing the [font=monospace]=[/font] (assignment) operator with the [font=monospace]==[/font] (equality) operator.

Consider also the [man]switch[/man] statement.

[BarryBKS]

Thanks WeedPacket,

I knew it would be something stupid! :rolleyes:

I've changed the code so many times over the last couple of days, that I couldn't see where I was going wrong.

Sometimes it helps to have a fresh set of eyes.

Cheers,

Barry.