Evaluating variables stored inside an array variable and re-sorting that array according to the evaluated values?

Question

So I have a MySQL database table that stores order ID and prices. The price can either be dynamic or static.

So for example:

Order ID | Price

1|205.99

2|215.95

3|217.88

4|$marketrate

5|$marketrate*1.02

6|212.99

On my php page I have

include "prices.php";
$sql="Select `Order ID`, `Price` from `table` ORDERBY `price`";
$result=mysql_query($sql);

while($row = mysql_fetch_assoc($result)){

        echo "<tr ".$tr_class.">\n";
        foreach ($row as $field=>$value) {


            eval('$value = ' . $value . ';');
            echo "<td>" . $value . "</td>\n";

        }
        echo "</tr>\n";



    }

So you see I use eval() to evaluate the price variable and display it and prices.php defines all the prices like $marketrate and whatnot.

My question is, how can I orderby the evaluated prices? When I use orderby in my mysql statement, it sorts the array based on static numbers and then the variable names $marketrate, which obviously is wrong because $marketrate is not a number. I need it to sort the array according to what the evaluated value of $marketrate is.

Is it possible to somehow evaluate all the variables in the $result array, and then re-sort the array using a PHP sorting function?

edit: tried to put eval('$result['Price'] = ' . $result['Price'] . ';'); in there but it did not work.

Answer 1

Loop throurgh your MySQL results first, put the products in an array, sort the array and then loop through the array to output your HTML table

$priceValues = array(); // create an empty array to hold your prices
while($row = mysql_fetch_assoc($result)){

    foreach ($row as $field=>$value) {

        eval( '$priceValues[] = ' . $value . ';' ); // append the new value to the array
    }
}

sort( $priceValues ); // sort values from lowest to highest

// Then output the HTMl table
echo "<tr ".$tr_class.">\n";

foreach ($priceValues as $price) {
    echo "<td>" . $price . "</td>\n";
}

echo "</tr>\n";

However resorting to eval is usually a bad sign and in your case there is no need to do it. You should really change your table structure and include a column for the marketrate multiplier. That way you could select the product (marketrate * multiplier) directly and sort by product.

Answer 2

You can retrieve the mysql results as a clean array with full data in rows, and then use an usort() function. It would look like follows, but I've not tested it:

include "prices.php";
$sql="Select `Order ID`, `Price` from `table` ORDERBY `price`";
$result=mysql_query($sql);

$data = array();
while ($row = mysql_fetch_assoc($result)) {
    
    foreach ($row as $field => $value) {
    
        eval('$value = ' . $value . ';');
        $row[$field] = $value; // replace row value
        
    }
    $data[] = $row; // append row to our array
}

// sorting function
usort($data, "order_by_price");

// usort callback
function order_by_price($a, $b) {
    $aprice = intval($a['price']);
    $bprice = intval($b['price']);

    if ($aprice == $bprice)
        return 0;
        
    return ($aprice < $bprice) ? -1 : 1;    
}

// now you have a sorted array
foreach ($data as $row) {
    echo "<tr ".$tr_class.">\n";
    foreach ($row as $field => $value)
        echo "<td>" . $value . "</td>\n";
    echo "</tr>\n";
}

Answer 3

This isn't really an answer as it doesn't address the actual questioned asked, but have you considered having the database do the evaluation and then the sort?

SELECT
    OrderId,
    CASE
      -- handle market rate (could be handled below) e.g. OrderId 4 above
      WHEN Price = '$marketrate' THEN market.rate
      -- handle $marketrate*number
      WHEN LEFT(Price,LENGTH('$marketrate*')) = '$marketrate*' 
        THEN RIGHT(price,LENGTH(Price)-LENGTH('$marketrate*')) * market.rate  
      -- handle static prices
      ELSE Price 
    END as Price  
  FROM 
    rates, 
    -- replace this with the table that has the rate if its in the database; 
    --   just make sure it only return one row or add a join condition
    -- if not in a table (you use a web service call etc) you could use a parameter,
    --   remove this line, and replace market.rate above
    (SELECT 202 as rate) market
  ORDER BY
    Price

You might want to replace some of the LENGTH surrounding static strings, but I was trying to avoid using "magical numbers" and keep it as clear as possible.

Edit:

I tried quickly adding comments to explain how this works, but as I've received feedback of not understanding, I thought I'd expand a bit.

Case Statement Explanation

Basically the CASE statement takes whatever is in the price field and tries to convert it to a numeric price (you can of think of the WHEN as an IF).

WHEN Price = '$marketrate' THEN market.rate

says if the price is listed as market rate (e.g. OrderId 4) then just return the marketrate.

WHEN LEFT(Price,LENGTH('$marketrate')) = '$marketrate'

says if the price starts with market rate $marketrate

THEN RIGHT(price,LENGTH(Price)-LENGTH('$marketrate*')) * market.rate

then take everything to the right of $marketrate* and multiple it by the market rate.

Let's look at this using the example of Order Id 5 above: $marketrate*1.02 This works by taking the length of price (16) and subtracting the length of $marketrate* (12) to get 4. It then takes the right four characters (1.02) and multiplies it by the market rate

ELSE Price

Finally, it assumes if the string isn't $marketrate and doesn't start with $marketrate, that it will be a number representing a static price. This could be a bad assumption (e.g. $marketrate+1.02), but with the provided data it works and this was meant primarily as a suggestion and not a canned solution

Test Database Configuration

I created a table called rates and populated it with data:

select OrderId,Price from rates;

+---------+------------------+
| OrderId | Price            |
+---------+------------------+
|       1 | 205.99           |
|       2 | 215.95           |
|       3 | 217.88           |
|       4 | $marketrate      |
|       5 | $marketrate*1.02 |
|       6 | 212.99           |
+---------+------------------+

Add The Rate

In order to use the case statement, the query needs to know the current market rate. Since I was lazy and split the cases of $marketrate and $marketrate*x, I need the rate twice. Rather then hardcoding it twice, I'm joining an ad hoc table with one column and one row containing my fake current rate.

SELECT OrderId, Price, market.rate  FROM rates, (SELECT 202 as rate) market ORDER BY Price;
+---------+------------------+------+
| OrderId | Price            | rate |
+---------+------------------+------+
|       4 | $marketrate      |  202 |
|       5 | $marketrate*1.02 |  202 |
|       1 | 205.99           |  202 |
|       6 | 212.99           |  202 |
|       2 | 215.95           |  202 |
|       3 | 217.88           |  202 |
+---------+------------------+------+

CASE Statement Example

If you append the case statement to the select clause of the above query then you get:

SELECT
    OrderId,
    Price,
    market.rate,
    CASE
      -- handle when its just the market rate
      WHEN Price = '$marketrate' THEN market.rate

      -- handle cases of $marketrate*number
      WHEN LEFT(Price,LENGTH('$marketrate')) = '$marketrate'
         THEN RIGHT(price,LENGTH(Price)-LENGTH('$marketrate')-1) * market.rate  

      -- handle static prices
      ELSE Price 
    END as Price2  
  FROM
    rates,
    (SELECT 202 as rate) market;

+---------+------------------+------+--------+
| OrderId | Price            | rate | Price2 |
+---------+------------------+------+--------+
|       1 | 205.99           |  202 | 205.99 |
|       2 | 215.95           |  202 | 215.95 |
|       3 | 217.88           |  202 | 217.88 |
|       4 | $marketrate      |  202 | 202    |
|       5 | $marketrate*1.02 |  202 | 206.04 |
|       6 | 212.99           |  202 | 212.99 |
+---------+------------------+------+--------+

Add Sorting

Finally, the point of all of this was to obtain the data in a sorted fashion, so sort it by appending ORDER BY Price2

SELECT
    OrderId,
    Price,
    market.rate,
    CASE
      -- handle when its just the market rate
      WHEN Price = '$marketrate' THEN market.rate

      -- handle cases of $marketrate*number
      WHEN LEFT(Price,LENGTH('$marketrate')) = '$marketrate'
         THEN RIGHT(price,LENGTH(Price)-LENGTH('$marketrate')-1) * market.rate  

      -- handle static prices
      ELSE Price 
    END as Price2  
  FROM
    rates,
    (SELECT 202 as rate) market
  ORDER BY
    Price2;

+---------+------------------+------+--------+
| OrderId | Price            | rate | Price2 |
+---------+------------------+------+--------+
|       4 | $marketrate      |  202 | 202    |
|       1 | 205.99           |  202 | 205.99 |
|       5 | $marketrate*1.02 |  202 | 206.04 |
|       6 | 212.99           |  202 | 212.99 |
|       2 | 215.95           |  202 | 215.95 |
|       3 | 217.88           |  202 | 217.88 |
+---------+------------------+------+--------+

To try to make it easier to read I cheated in my Order by; MySql will let you reference a calculated column (e.g. Price2) in the Order By, but not every RDBMS will.

If the above SQL doesn't work, then copy the CASE statement into the ORDER BY (e.g.)

SELECT
    OrderId,
    Price,
    market.rate,
    CASE
      -- handle when its just the market rate
      WHEN Price = '$marketrate' THEN market.rate

      -- handle cases of $marketrate*number
      WHEN LEFT(Price,LENGTH('$marketrate')) = '$marketrate'
         THEN RIGHT(price,LENGTH(Price)-LENGTH('$marketrate')-1) * market.rate  

      -- handle static prices
      ELSE Price 
    END as Price2  
  FROM
    rates,
    (SELECT 202 as rate) market
  ORDER BY
    CASE
      -- handle when its just the market rate
      WHEN Price = '$marketrate' THEN market.rate

      -- handle cases of $marketrate*number (e.g. OrderId 5 above)
      WHEN LEFT(Price,LENGTH('$marketrate')) = '$marketrate'
         THEN RIGHT(price,LENGTH(Price)-LENGTH('$marketrate')-LENGTH('*')) * market.rate  

      -- handle static prices (e.g. OrderId 1, 2, 3, 6)
      ELSE Price 
    END