sql query

[jacob21]

Hi,
I am working on a job module where i have to fetch company name having more jobs. like
abc 20 jobs
def 30 jobs
ghi 10 jobs
jkl 40 jobs

what i want to do, job order acc to job number like below
jkl 40 jobs
def 30 jobs
abc 20 jobs
ghi 10 jobs
thanks
NEED your suggestions

[phpbeginners]

as far as my knowledge is concern.

create 2 tables
company and jobs
for every (primary key)"companyid" match in jobs table (foreign key) "companyid".
from their you can count how many jobs per company.

that is one-many relationship of database.

[lyrico]

Try this idea.

<?php
// sql connection here

$query = mysql_query("select * from tbl_name order by number_of_jobs desc");
while($result=mysql_fetch_array($query))
{
   echo $result['company_name'];
   echo $result['number_of_jobs']."<br>";
}

?>

[jacob21]

thanks everyone but the problem is:
as company name is not a fixed entity.it is entered by the admin.
suppose admin post 6 jobs having job_id,company,type,emailid
1 abc HR [e-mail]
2 cde TECH [e-mail]
3 abc IT [e-mail]
4 def Bank [e-mail]
5 abc IT [e-mail]
6 def bank [e-mail]

abc has 3 jobs so it is in ist postion
def has 2 job so it is in 2nd position
cde has 1 job so it is in 1st position

[Akash Saikia]

Hi so you want sorting on your table?
And is it in mssql?

[jacob21]

its not exactly like sorting.we have to sort acc to number of jobs per category contain.

[Akash Saikia]

I don't know that whether i get your question correctly,but try this

select companyName ,count(companyName ) from company,jobs
where company.companyId=jobs.companyId
group by companyName 
order by count(companyName ) desc

Hope it helps... :)

[tomato.pgn]

check the code given by lyrico...it will fulfill ur requirement.....