CODEWITHSUNDEEP
phpdatabase
Victoria Cho
Victoria Cho

Reputation: 49

PHP numerical descending in database?

I have table person. 

id, name    product_A  product_B
--  -----   ---------  --------- 
1   Joe        1         4        // result product A + Product B = 5
2   Leo        5         1        // result product A + Product B = 6
3   Lia        2         2        // result product A + Product B = 4

First, I need to count product A + Product B. 

function count($a,$b){
$total = $a+$b;
return $total;
}

Then call value in database

    $count_row = mysql_query("SELECT * FROM person ");
    $result_row= mysql_num_rows($count_row);
$i=0;
$j=0;
$k=0;
    $query = "SELECT * FROM person "
    $result = mysql_query($query) or die ("Query error: " . mysql_error());
    while($row = mysql_fetch_array($result))
        {
          $array_a[$i++]=$row['product_A'];
          $array_b[$j++]=$row['product_B'];
          $array_name[$j++]=$row['name'];
        }
for ($i = 0 ; $i < $count_row ; $i++){
$tot[i] = count($array_a,$array_a);
$array_name[i];
}

// sorting

   function desc($result_row){
    $array = array();
    global $tot;
    for ($n = 0 ; $n <$result_row ; $n++){
        array_push($array,$tot[$n]);
    }
    for($i = 0 ; $i < sizeof($array) ; $i++)
        rsort($array);
                for ($n = 0 ; $n <$result_row ; $n++){
                echo $array_name[$n]."<br>";
                            echo $array[$n]."<br>";
                }
            }

   desc($result_row);

Then I want to echoing name based on the highest value or descending of product A + Product B. 'The output = Leo , Joe, Lia'. How can I do that in PHP code? Help me please...

Upvotes: 1

Views: 145

Answers (4)

bestprogrammerintheworld
bestprogrammerintheworld

Reputation: 5520

Try this: 

<?php
//product array ($products is the array fetched from the db)
$products = array();
$products[0] = array('id'=>1, 'name'=>'Joe', 'productA'=>1, 'productB'=>4);
$products[1] = array('id'=>2, 'name'=>'Leo', 'productA'=>5, 'productB'=>1);
$products[2] = array('id'=>3, 'name'=>'Lia', 'productA'=>2, 'productB'=>2);

//Create a single-column array with sums
$productSum = array();
foreach ($products as $product) {
    $productSum[] = (int)$product['productA'] + (int)$product['productB'];
}
//Do the magic, sort DESCENDING by $productSum-array into $products-array
array_multisort($productSum, SORT_DESC, $products);

//Output
print_r($products);
?>

Output:

Array (
[0] => Array ( [id] => 2 [name] => Leo [productA] => 5 [productB] => 1 )
[1] => Array ( [id] => 1 [name] => Joe [productA] => 1 [productB] => 4 )
[2] => Array ( [id] => 3 [name] => Lia [productA] => 2 [productB] => 2 )
)

For more info about array_multisort(), take a look at: http://php.net/manual/en/function.array-multisort.php

Upvotes: 0

arma
arma

Reputation: 4124

Easiest way to do this is to use suggested SQL that people wrote for you. Doing this in php is not practical for your case. Rather do it in SQL and save yourself some code writing and memory on the server.

Example:

In SQL:

SELECT *, product_A  + product_B AS product_sum FROM persons ORDER BY product_sum DESC

Then in PHP: 

while($record = mysql_fetch_assoc($result_set))
{
    // Results Already will be sorted for you you just have to output them.
    echo $record['name'].' has '.$record['product_sum'].' product sum.';
    echo '<br/>'; 
}

And you can output now name or product counts or totals or anything you want.

Upvotes: 1

Rubin Porwal
Rubin Porwal

Reputation: 3845

As a solution to your problem please try executing the following query.

select *,product_A + product_B as total from person order by total desc

Upvotes: 0

sAnS
sAnS

Reputation: 1163

Yoy may try like this 

SELECT  (tp.product_A + tp.product_B) AS total,tp.name
FROM table_person tp
ORDER BY total DESC

Upvotes: 1

Related Questions