0

I want to display the from selected in a database based on the number of applications jobs in this form job Name(number of applications) for example Accountant(4),Driver(2) Here is the query of selecting from a database $sql=mysql_query("SELECT applications.*, jobs.jname FROM applications LEFT JOIN jobs ON jobs.jobID = applications.jobID ");

I have tried 

$jobApplication_list="";
$sql=mysql_query("SELECT  applications.*, jobs.jname FROM applications 
LEFT JOIN jobs  ON jobs.jobID = applications.jobID ");
$jobCount= mysql_num_rows($sql);//count the output amount
global $jobCount;
if($jobCount > 0){
while($row=mysql_fetch_array($sql)){
$jobname=$row["jname"];

$countt=(count($appID= $row["id"]));
$jobApplication_list .='
        <tr><td>'.$jobname.'('.$countt.')</td></tr> ';  

        }
}`

But im not getting the required results What I'm getting is this

Accountant(1) Programmers(1) Programmers(1) Not that I don't want the job name e'g programmer to repeat i want job name to repeat i want the number to increase eg programmer(2)..(3)..on and on Any Assistance will be appreciated

Nicholas Macharia
  • 124
  • 1
  • 9
  • Please give [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/13944956/the-mysql-extension-is-deprecated-and-will-be-removed-in-the-future-use-mysqli) a read - it's deprecated, insecure and no longer exists in new versions of PHP. – h2ooooooo Feb 22 '16 at 09:50

1 Answers1

0

I think what you are looking for is something like this: 

SELECT COUNT(jobID), jobs.jname 
FROM jobs
GROUP BY jobs.jname

This query should return you a table with the job name and the number of rows with that job name in your table.

As tip I would tell you to get rid of mysql functions, they are deprecated, you'd better use mysqli or PDO, for further information read this.

Community
  • 1
  • 1
Asur
  • 379
  • 3
  • 20
  • there two tables not one. That is **jobs** and **applications** I think did'nt understand the assistance i needed. – Nicholas Macharia Feb 22 '16 at 10:41
  • I know there are two tables, and maybe I'm mistaken but why do you need the applications table in this case? – Asur Feb 22 '16 at 10:44
  • the application table stores the the applicantID, jobID and and the jobTable stores the available jobs. I need now to format the applications in the format [link] (https://www.brightermonday.co.ke/search/jobs-in-accounting) in that link – Nicholas Macharia Feb 22 '16 at 10:49
  • `CREATE TABLE IF NOT EXISTS `applications` ( `id` int(11) NOT NULL AUTO_INCREMENT, `jobID` int(11) NOT NULL, `userID` int(11) NOT NULL, `salary` varchar(89) NOT NULL, `cletter` text NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=63 ; ` – Nicholas Macharia Feb 22 '16 at 10:53
  • jobs table CREATE TABLE IF NOT EXISTS `jobs` ( `jobID` int(11) NOT NULL AUTO_INCREMENT, `jname` varchar(24) NOT NULL, `jcategory` varchar(24) NOT NULL, `jdetails` text NOT NULL, `expiry_day` varchar(80) NOT NULL, `expiry_month` varchar(80) NOT NULL, `expiry_year` varchar(80) NOT NULL, `date_added` varchar(80) NOT NULL, PRIMARY KEY (`jobID`), UNIQUE KEY `jname` (`jname`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ; – Nicholas Macharia Feb 22 '16 at 10:54
  • Edit your post aswell with your table structure to make it more clear – Asur Feb 22 '16 at 10:54
  • Well, as far as I have seen in that link you don't need the application table for anything. As I see it you should use the query I posted to find out the number of jobs with that name and then asign the field using the name. – Asur Feb 22 '16 at 10:57