CODEWITHSUNDEEP
phpmysql
stareen
stareen

Reputation: 37

using count to select total of rows from two table

I have two MySQL tables,

student = 150 (total no of rows which represent total no of student)

employer= 230 (total no of rows which represent total no of employer)

FULL CODE

<?php echo mysqli_num_rows($studempl); ?> 

$conn = new mysqli ('localhost', 'root', '', 'dashboard');
$studempl = $conn ->query ("SELECT  (SELECT COUNT(*) FROM student) + (SELECT COUNT(*) FROM employer) FROM    dual");
$tot_studempl = mysqli_num_rows($studempl);

Result: 1

How can i fix this?

I tried to use 

SELECT 'student' AS stdID, COUNT(*) FROM student
UNION
SELECT 'employer' AS emplID, COUNT(*) FROM employer*

It displays the result = 2,

I also tried to use:

SELECT  (SELECT COUNT(*) FROM   student) AS count1,(SELECT COUNT(*) FROM   employer) AS count2 FROM    dual*

It displays the result = 1,

I think I might have wrong in my code, how can I get total from student and from employer so i can view the data and display in piechart?

150, 230

Upvotes: 0

Views: 127

Answers (2)

Zubair
Zubair

Reputation: 248

You're second query should work.

    SELECT  (SELECT COUNT(*) FROM   student) AS count1,(SELECT COUNT(*) 
    FROM employer) AS count2 FROM    dual* <-- make sure you remove (*)
// so it should be like this 
    SELECT  (SELECT COUNT(*) FROM   student) AS count1,(SELECT COUNT(*) 
    FROM employer) AS count2 FROM    dual

Then you just have to fetch the array to display the results

$sql = "SELECT  (SELECT COUNT(*) FROM   playlists) AS count1,(SELECT COUNT(*) FROM   artists) AS count2 FROM    dual";

if ($result=mysqli_query($con,$sql))
  {
      $row = mysqli_fetch_array($result);
      var_dump($row); // just checking the raw array...
      echo "<br />";
      echo $row[0]. ", ". $row[1];  // got your results -> 150, 230
  }
mysqli_close($con);

Also, you're first query works if you replace the + to , like this 

$sql = "SELECT  (SELECT COUNT(*) FROM students), (SELECT COUNT(*) FROM employer) FROM    dual";

Upvotes: 0

WiLLyxVKei
WiLLyxVKei

Reputation: 113

I did the sum of the two tables in MySQL with a query like this

SELECT  ( (SELECT COUNT(*) FROM scheme.student) + 
          (SELECT COUNT(*) FROM scheme.employer) 
         ) AS 'Column' ;

In my case, i used the same table 2 times and that table has 280 rows (280 x 2 = 560).

Hope this help you

Upvotes: 1

Related Questions