select if 'greater than' help

[adammc]

Hi,

I am trying to get my head around how to write this statement and could REALLY do with some help if possible?

I have a database which stores all the details about the jobs listed on my job search site. I have a featured companies menu on the site that I want to list the employers who currently have more than 2 jobs in the database.

Up till now this has been done by hand by setting the 'featured' field to '1' after we saw that they had listed 2 or more jobs.

The code I was using is :

$xa = "SELECT * FROM job_post where featured = '1' order by ename asc LIMIT 10";
$xb = mysql_query($xa) or die(mysql_error());
 while($xc = mysql_fetch_array($xb))
 {
echo "<td bgcolor='#F1ECB4'><a href=http://www.example.com/employerview.php?ename=$xc[ename]>$xc[CompanyName]</a></td> </tr>";
 }

So i guess I need to say something like :
Select / count from Job_post where CompanyName is greater than 2?

As mentioned above, I am not sure how to accomplish this :(

[edited by: Woz at 2:30 am (utc) on Sep. 15, 2005]
[edit reason] Examplified Code [/edit]

[dkin]

$xa = "SELECT * FROM job_post where CompanyName >= '2' order by ename asc LIMIT 10";
$xb = mysql_query($xa) or die(mysql_error());
while($xc = mysql_fetch_array($xb))
{
echo "<td bgcolor='#F1ECB4'><a href=http://www.example.com/employerview.php?ename=$xc[ename]>$xc[CompanyName]</a></td> </tr>";
}

should do it

[edited by: Woz at 2:31 am (utc) on Sep. 15, 2005]
[edit reason] Examplified Code [/edit]

[adammc]

Hi dkin,
Thanks for the reply mate :)

It didnt work :(
It displayed a list of 'ALL' employers, listing them as many times as they had jobs in the database.

Example company A has 2 jobs in the database, it listed them twice.

Heres what the job_post table looks like:

`job_id` int(10) default NULL,
`ename` varchar(10) NOT NULL default '',
`Company` varchar(100) NOT NULL default '',
`CompanyCountry` varchar(200) NOT NULL default '',
`position` varchar(100) NOT NULL default '',
`JobCategory` varchar(150) NOT NULL default '',
`description` text NOT NULL,
`CompanyState` varchar(100) NOT NULL default '',
`JobIn` varchar(255) NOT NULL default '',
`postdate` varchar(200) NOT NULL default 'none',
`viewed` int(3) NOT NULL default '0',
`employment_type` varchar(150) NOT NULL default '',
`availableto` varchar(255) NOT NULL default '',
`featured` varchar(200) NOT NULL default ''

[dkin]

$xa = "SELECT Distinct CompanyName FROM job_post where CompanyName >= '2' order by ename asc LIMIT 10";
$xb = mysql_query($xa) or die(mysql_error());
while($xc = mysql_fetch_array($xb))
{
echo "<td bgcolor='#F1ECB4'><a href=http://www.example.com/employerview.php?ename=$xc[ename]>$xc[CompanyName]</a></td> </tr>";
}

Try that, if you want to display more than just the company name change this Distinct CompanyName to this Distinct CompanyName, jobamount, id, etc, etc

[adammc]

Hmmm.. still not working.

It has now listed all the employers who currently have a job on the site.

Any ideas?

[dkin]

lol I just looked at the query and we're going about this the wrong way.

I didnt even look at the column name before. You need something like this

$xa = "SELECT COUNT(ename) AS ename FROM job_post where ename = '$whatever' GROUP BY ename asc LIMIT 10";
$xb = mysql_query($xa) or die(mysql_error());
while($xc = mysql_fetch_array($xb))
{
echo "<td bgcolor='#F1ECB4'><a href=http://www.example.com/employerview.php?ename=$xc[ename]>$xc[CompanyName]</a></td> </tr>";
}

that should get you in the right direction I hope

[adammc]

Still not working :(

I got an error saying "cannot group on ename"

$xa = "SELECT COUNT(ename) AS ename FROM job_post where ename = '$ename' GROUP BY ename asc LIMIT 20"; 
$xb = mysql_query($xa) or die(mysql_error());
 while($xc = mysql_fetch_array($xb))
 {
echo "<td bgcolor='#F1ECB4'><a href=http://www.example.com/employerview.php?ename=$xc[ename]>$xc[Company]</a></td> </tr>";
 }

Thanks for al you help :)

[edited by: jatar_k at 3:21 pm (utc) on Sep. 15, 2005]
[edit reason] removed url [/edit]

[dkin]

seems like no one else is willing to help so I will try again

I tried rewriting and testing the script for you after some research and this is what Ive created, let me know how it works for you

$xb = mysql_query("SELECT ename, COUNT(*) AS num FROM job_post group by ename order by num desc", $link) or die ("query 1: " . mysql_error());
while($xc = mysql_fetch_array($xb))
{
if ($xc['num'] >= '2')
{
echo "<td bgcolor='#F1ECB4'><a href=http://www.example.com/employerview.php?ename=$xc[ename]>$xc[Company]</a></td> </tr>";
}
}

[edited by: jatar_k at 3:21 pm (utc) on Sep. 15, 2005]
[edit reason] removed url [/edit]

[adammc]

Sorry, I am starting to sound like a broken record now.

I got the following error:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/httpd/vhosts/example.com/httpdocs/main-contactus.php on line 315
query 1:

$xb = mysql_query("SELECT ename, COUNT(*) AS num FROM job_post group by ename order by num desc", $link) or die ("query 1: " . mysql_error());
while($xc = mysql_fetch_array($xb))
{
if ($xc['num'] >= '2')
{
echo "<td bgcolor='#F1ECB4'><a href=http://www.example.com/employerview.php?ename=$xc[ename]>$xc[Company]</a></td> </tr>";
}
} 
 
?>

Thanks agin for trying to help :)

[edited by: jatar_k at 3:21 pm (utc) on Sep. 15, 2005]
[edit reason] removed url [/edit]

[adammc]

I got it sorted, thanks for trying to help, much appreciated :)

$query = "SELECT CompanyName FROM job_post GROUP BY CompanyName HAVING count(*)>2";

[dkin]

see I never would have approached it that way, prolly cuz it was between 3 and 5 am, anyhow glad you got it sorted.