CODEWITHSUNDEEP
phpmysqlgroup-by
user12538154
user12538154

Reputation:

How to select how many users has different row values

I have ROW named "Vacancy" - it shows how many times someone went to holiday.

Some has value "0". Some has value "1" or even "30". It doesn't matter.

But, I want to select every user who has "vacancy" row NOT 0. How can I do that?

    User1: Vacancy row 0
    User2: Vacancy row 0
    User3: Vacancy row 5
    User4: Vacancy row 8
    User5: Vacancy row 6000
    User6: Vacancy row 100
    User7: Vacancy row 0
    _______
    Total users who went to vacancy: 4.  How can I echo number 4? 

$sql = "SELECT Vacancy FROM users WHERE Vacancy > 0";
?

My current code:

$sql25 = "SELECT Vacancy AS value_vacancy FROM users WHERE Vacancy > 0";
$result = $con->query($sql24);

if ($result->num_rows > 0) {
    while($totalvacancy = $result->fetch_assoc()) {
$output25 = $totalvacancy ["value_vacancy"];    }


<html><p><?php echo number_format("$output25",0,",",".");?> people who went to vacancy</p>
</html>

With error 

Warning: number_format() expects parameter 1 to be float, string given in on line

Upvotes: 0

Views: 84

Answers (2)

GMB
GMB

Reputation: 222482

Your code is looping through the results of the query and assigning the value of vacancy to a variable (which, possibly, was not declared outside the loop). This is not what you want (you should actually just count the number of rows that your query returns).

Anyway, I would recommend using an aggregate query that computes the total number of users in vacancy. Such a query (aggregate without a group by clause) is guaranteed to always return just one row, with the expected count. This avoids the need to loop through the results. It is also far more efficient to let your database do the aggregation work for you rather than fetching and looping in php.

Your code could look like:

$sql25 = "SELECT COUNT(*) AS cnt_vacancy FROM users WHERE Vacancy > 0";
if ($result = $con->query($sql24)) {
    $row = $result->fetch_assoc();
    <html><p><?php echo $row['cnt_vacancy']; ?> people went to vacancy</p>
</html>
} else {
    # error handling...
}

Upvotes: 1

Kiran Maniya
Kiran Maniya

Reputation: 8979

You can simply use the != as,

SELECT * FROM user WHERE vacancy != 0;

for your issue, give a try to this.

$query = "SELECT sum(vacancy) as total FROM user WHERE vacancy != 0";
$result = $con->query($query);
if(mysqli_num_rows($result) > 0){
    $total = mysqli_fetch_assoc($result);
    $total = floatval($totle['total']);
}
<html>
    <p><?php echo number_format($total,0,",",".");?> people who went to vacancy
    </p>
</html>

Upvotes: 0

Related Questions