CODEWITHSUNDEEP
php
Pot Pot
Pot Pot

Reputation: 27

how to get the counted value

how can i get the result from my query COUNT.

this is how it looks of my query in my database

   fname  lname   mname    positionName  COUNT(tbl_votes.studId)
    jr    gwapo   is-very    chairman           2

and this is how my webpage looks

       Name           Position      Number of Votes
  jr is-very gwapo    chairman         ______

and heres my code.

<?php
  if ($result = $mysqli->query("SELECT tbl_student.fname, tbl_student.lname, tbl_student.mname, tbl_position.positionName, Count(tbl_votes.studId) FROM tbl_candidate Inner Join tbl_student ON tbl_candidate.studId = tbl_student.studId Inner Join tbl_position ON tbl_candidate.positionId = tbl_position.positionId Inner Join tbl_votes ON tbl_student.studId = tbl_votes.candId WHERE tbl_position.positionId =  '1' GROUP BY tbl_student.fname, tbl_student.lname, tbl_student.mname, tbl_position.positionName")) {

      if ($result->num_rows > 0) {
          echo "<table border='1' cellpadding='10'>";
          // set table headers
          echo "<tr><th>Name</th><th>Position</th><th>Number of Votes</th></tr>";

          while ($row = $result->fetch_object()) {
              echo "<tr>";
              echo "<td>" . $row->fname . " " . $row->mname . " " . $row->lname . " </td>";
              echo "<td>" . $row->positionName . "</td>";
              //this is where i suppose to echo the count result
            echo "<td>" . $row-> ??? . "</td>"; 
            echo"<tr>";
          }
          echo "</table>";
      } else {
          echo "No results to display!";
      }

  }
  $mysqli->close();

?>

heres my problem, how could i pass the "Count(tbl_votes.studId)" in "echo "" . $row-> ??? . ""; " ? pls help...

Upvotes: 0

Views: 73

Answers (5)

Bhuvnesh Gupta
Bhuvnesh Gupta

Reputation: 197

you should use 'as' with 'count' . So you query will become like this

"SELECT tbl_student.fname, tbl_student.lname, tbl_student.mname, tbl_position.positionName, Count(tbl_votes.studId) as no_of_votes FROM tbl_candidate Inner Join tbl_student ON tbl_candidate.studId = tbl_student.studId Inner Join tbl_position ON tbl_candidate.positionId = tbl_position.positionId Inner Join tbl_votes ON tbl_student.studId = tbl_votes.candId WHERE tbl_position.positionId =  '1' GROUP BY tbl_student.fname, tbl_student.lname, tbl_student.mname, tbl_position.positionName"

then you can get it via php as

$row->no_of_vote

for mor information see COUNT AS

Upvotes: 0

Serge Kuharev
Serge Kuharev

Reputation: 1052

instead of writing

count(some_field)

write

count(some_field) as count_some_field

You give alias to that count field. You can access it as $row->count_some_field.

Upvotes: 0

user1916076
user1916076

Reputation: 145

Try Select field1, field2, Count(tbl_votes.studId) as cnt from ...

Upvotes: 0

Vlad Preda
Vlad Preda

Reputation: 9910

You need to use an SQL alias.

Count(tbl_votes.studId) as cnt
//or
Count(tbl_votes.studId) 'cnt'

Then you can access it with $row->cnt

Some rules about aliases:

  • you can use an alias in the rest of your query (in your example you could use ORDER BY cnt). Note: as far as I know you can't use an alias you created in the select in another select statement. For example, SELECT COUNT(something) AS cnt, cnt+3 FROM ... will not work
  • you can use alias for tables, and replace the table name with the alias in future usages

Upvotes: 0

pktangyue
pktangyue

Reputation: 8524

In sql query, change Count(tbl_votes.studId) to Count(tbl_votes.studId) as stu_count.

And in php, you can use $row->stu_count

Upvotes: 1

Related Questions