Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Exclamation count (fieldname) returns 1 even if there are no records?

    Hi All,

    i have a query
    Code:
    SELECT * , count( dept.deptID ) AS count
    				FROM tbl_departments AS dept
    				LEFT JOIN tbl_job_department AS jdept ON dept.deptID = jdept.deptID
    				GROUP BY dept.deptID
    but if there are no jobs in a department it returns 1 instead of 0 when run in phpmyadmin.

    my department table looks like this
    deptID, deptName

    and my tbl_job_department
    jobID,DeptID

    bold fields are primary keys.

    basically i want to get the number of jobs in each department

    my php code is
    PHP Code:
                    $q = "SELECT * , count( dept.deptID ) AS count
                    FROM tbl_departments AS dept
                    LEFT JOIN tbl_job_department AS jdept ON dept.deptID = jdept.deptID
                    GROUP BY dept.deptID";
                    $query = $user->database->query($q);
                    while($row=$user->database->fetchArray($query))
                    {
                        $deptID = $row['deptID'];
                        $dept = $row['deptName'];
                        $rows = $row['count'];
                        $deptLink = "job_results.php?Dept=".$deptID;?>
                        <li><?php echo "<a href='$deptLink' title='Browse $dept Jobs'>".$dept." Jobs ($rows)</a>";?></li><?php
                    
    }?>
    the php code also echos out 1 if there are no jobs
    any ideas?

    thanks
    Luke
    Last edited by LJackson; 11-14-2013 at 04:41 PM.

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    sorted it

    this is the query i used
    Code:
    SELECT dept.deptID, dept.deptName, COUNT( job.jobID ) AS numberofjobs
    FROM tbl_departments AS dept
    LEFT JOIN tbl_job_department AS job ON dept.deptID = job.deptID
    GROUP BY dept.deptName

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,636 Times in 4,598 Posts
    Yep, it helps to count the right thing, doesn't it. <grin/>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •