CODEWITHSUNDEEP
phpmysql
mat
mat

Reputation: 2617

Count how many times a specific value is retrievied from a SQL QUERY

MySQL table: name, salary, childrens, brothers, age

I'm trying to retrieve the name of the person who has the max value in salary, childrens and brothers, with age>30. Note: every name is unique.

To do achieve this I loop through all columns whit this array:

$columns = array('salary','childrens','brothers')
foreach($columns as $value){
  $result=mysql_query("SELECT `name`, `$value` FROM table_name WHERE `age`>30 ORDER BY `$value` ASC LIMIT 1");
  while($rows=mysql_fetch_array($result,MYSQL_ASSOC)){
    echo $rows[name];
  };
};

Everything works fine, but I would also like to count the amount of times each name is retrived (echoed). i.e.: Max has the highest salary and the highest amount of brothers, so his name has been retrivied 2 times. Loren only has the highest amount of childrens, so his name has been retrivied 1 time. Jason has never been retrivied, so it's 0 for him.

I tried this:

$i=0;
$columns = array('salary','childrens','brothers')
foreach($columns as $value){
  $result=mysql_query("SELECT `name`, `$value` FROM table_name WHERE `age`>30 ORDER BY `$value` ASC LIMIT 1");
  while($rows=mysql_fetch_array($result,MYSQL_ASSOC)){
    echo "The person who has the max amount of $value is $rows[name]";
    $count[$rows[name]] = $i++;
  };
};

But it doesn't work as intended, it counting the number of times eache name appears in every columns without taking into account if it has the max value.

Any help would be appriciated. ps: if you also can improve the code the retrivie the max value I would be grateful.

UPDATE:

The query for each table should output this:

Name salary
Max  2000

--

Name   childrens
Loren  4

-- 

Name   brothers
Max    3

The $count array should be: $count = array('Max'=>2,'Loren'=>1,'Jason'=>0,'etc'=>0);

Upvotes: 0

Views: 271

Answers (2)

Leon Armstrong
Leon Armstrong

Reputation: 1303

I'm not sure what you up to , but in programing i will use comparing method to get highest value , but there is another MAX() function in server side language to get greatest value of all record http://www.w3schools.com/sql/sql_func_max.asp

$columns = array('salary','childrens','brothers');
$highest = array(array('salary'=>0,'childrens'=>0,'brothers'=>0));

foreach($columns as $value){
  $result=mysql_query("SELECT `name`, `$value` FROM table_name WHERE `age`>30 ORDER BY `$value` ASC LIMIT 1");
  while($rows=mysql_fetch_array($result,MYSQL_ASSOC)){

    //compare the value of each record if greater then replace
    if($highest[$value]<$rows[$value]){
    //replace with current highest value and set name into array
    $highest[$value]=$rows[$value];
    $highest[$value]=$rows[name];
    }
  };
};

print_r($highest);

Upvotes: 0

Tyranron
Tyranron

Reputation: 608

You used the same counter for all names. Try to split them. Something like that:

$count=array();
$columns = array('salary','childrens','brothers')
foreach($columns as $value) {
    $result=mysql_query(
        "SELECT `name`, `$value` 
         FROM table_name 
         WHERE `age`>30 
         ORDER BY `$value` ASC 
         LIMIT 1"
    );
    while($rows=mysql_fetch_array($result,MYSQL_ASSOC)) {
        echo "The person who has the max amount of $value is $rows[name]";
        if(!isset($count[$rows[name]]))
            $count[$rows[name]] = 0;
        ++$count[$rows[name]];
    };
};
print_r($count);

UPD: And also if you need a row with MAX value, you must use DESC instead of ASC

UPD2: To retrieve all the users, you need also execute SELECT DISTINCT name FROM table_name before previous code and fetch it into array $count[$r['name']] = 0

Upvotes: 0

Related Questions