Using PHP and conditions to fill table values

[thenorman138]

I have a page of html tables that are filled by database values through PHP, and there are no problems there. However, I have an issue on one of the tables.

 

I have a pricing table, attached below.

 

 

The $575 is currently filled from a field in the database that is set from a CSV upload, but the new CSV template is not doing this because it's missing the price field.

 

This is the SQL statement that I'm using in the database, since the price is based off of meterSize, meterType and workOrderType2:

 

    UPDATE staging

    INNER JOIN pricing ON

    staging.meterType = pricing.meterType

    AND staging.meterSize = pricing.meterSize

    AND staging.workOrderType2 = pricing.workOrderType

    SET staging.onsiteTestSurveyPrice = pricing.price

 

This works, but only with the original template, so I'm looking into doing this strictly in PHP with the table/row values, if possible.

 

An example of what I'm looking for, using the html table and PHP:

 

    If  $row['meterSize'] contains 3", 

        $row['meterType'] contains COMPOUND 

        and $row['workOrderType2'] contains ONSITE SURVEY AND TEST 

 

    then $575 

 

should go in

 

    <td><? echo $row['onsiteSurveyTestCost'];?> </td>

 

Here's the code for those 3 conditional table rows:

 

    <tr style="border: none;">

    <td style="border: none; text-align: left;">Meter Type:</td>

    <td style="border: none; text-align: right;"><? echo $row['meterType'];?> </td>

    </tr>

    <tr style="border: none;">

    <td style="border: none; text-align: left;">Meter Size:</td>

    <td style="border: none; text-align: right;"><? echo $row['meterSize'];?> </td>

    </tr>

    <tr style="border: none;">

    <td style="border: none; text-align: left;">Service Preformed:</td>

    <td style="border: none; text-align: right;"><? echo $row['workOrderType2'];?> </td>

    </tr>

 

Is there a way to do this? I have multiple prices that are based on multiple conditions, but If I could figure out how to do this once then I can alter the values accordingly. Basically I want to mirror the db/SQL statement only with strictly PHP and the values in each table row. How can I do that, if possible?

 

 

[Psycho]

This doesn't make sense:

 

 

 

   If  $row['meterSize'] contains 3", 

        $row['meterType'] contains COMPOUND 
        and $row['workOrderType2'] contains ONSITE SURVEY AND TEST 
 
    then $575 
 
should go in
 

    <td><? echo $row['onsiteSurveyTestCost'];?> </td>

 

Where does $575 come from? Is it ALWAYS $575 if those three conditions are true? And, what is the value of 'onsiteSurveyTestCost' if those three conditions are not true? Rather than do the logic in PHP, I would recommend calculating the value when SELECTing the records in the query. I would provide an example, but I don't understand the condition.

Edited May 11, 2017 by Psycho

[thenorman138]

Sorry, I should have been more specific. Yes, if those 3 conditions are true it is always 575, but if it isn't ONSITE SURVEY AND TEST then that field would be blank or $0. 

 

These are all static conditions with different prices, I was just looking for an idea of how to do that in the table with PHP since the company has already changed the CSV template once. I know it may not be a great way to do it but it will get the job done until they lose the price table altogether.

[thenorman138]

I was thinking there may be a way to do it with ternary operators but I don't have much experience so I wasn't sure if that would work.

[Psycho]

OK, I don't know what your current SELECT query looks like to get the data, but here is an example of what it could look like with a dynamic value calculated in the query.

 

 

SELECT meterSize, meterType, workOrderType2,
       IF( meterSize=3 AND meterType='COMPOUND' AND workOrderType2='ONSITE SURVEY AND TEST',
           535, 0) as onsiteSurveyTestCost
FROM staging
 

[thenorman138]

Thank you, I will try this is a query at the pricing table. I have a query at the top of the page that selects everything from staging in order to fill all the other html tables, but that won't affect that, right?

Edited May 11, 2017 by Psycho

[Psycho]

Thank you, I will try this is a query at the pricing table. I have a query at the top of the page that selects everything from staging in order to fill all the other html tables, but that won't affect that, right?

 

All I am suggesting is adding a dynamic value in the query that you run to get the data for this particular table. Without knowing how all your code works, I cannot say whether the change would impact anything else - but I highly doubt it.