I have 2 tables, jobs and employers. I need to search the jobs table from a form. When the jobs are listed, I need to show the employers' city and state in the results which are stored in the employers table. The employers table has a unique field called id which identifies each employer, and the jobs table has a field called employer_id which stores the emploers' id when they post a job. I cannot get the code to work. I think I am going in the right direction using a JOIN but I am not sure.
Here is the code:
<?php
include "db/dbconnect.php";
$db = db_connect();
// To show each page by number set to Y. Set to N for prev-next links only
$showpages = "Y";
// Set number of records to be displayed per page $limit=somenumber
$limit=15;
// proceed with main script
if (empty($offset))
{
$offset=0;
}
// get results and count rows
$sql = "SELECT jobs.* WHERE 1 = '1'";
if ($job_type != "ALL") $sql .= " AND job_type = '".mysql_escape_string($job_type)."'";
if ($pos_type != "ALL") $sql .= " AND pos_type = '".mysql_escape_string($pos_type)."'";
if ($exp_level != "ALL") $sql .= " AND exp_level = '".mysql_escape_string($exp_level)."'";
if ($state != "ALL") $sql .= " LEFT JOIN employers ON employers.id = jobs.employer_id WHERE employers.state = '".mysql_escape_string($state)."'";
$result = mysql_query($sql);
$numrows = mysql_num_rows($result);
// get results for printing to page
$sql = "SELECT jobs.* WHERE 1 = '1'";
if ($job_type != "ALL") $sql .= " AND job_type = '".mysql_escape_string($job_type)."'";
if ($pos_type != "ALL") $sql .= " AND pos_type = '".mysql_escape_string($pos_type)."'";
if ($exp_level != "ALL") $sql .= " AND exp_level = '".mysql_escape_string($exp_level)."'";
if ($state != "ALL") $sql .= " LEFT JOIN employers ON employers.id = jobs.employer_id WHERE employers.state = '".mysql_escape_string($state)."'";
$sql .= " ORDER BY date limit $offset,$limit";
$result2 = mysql_query($sql);
//show results
printf("<table border=\"0\" align=\"center\" bgcolor=\"#FFFFFF\" width=\"90%%\">\n");
printf("<tr>\n");
printf("<td><table width=\"100%%\" height=\"60\" border=\"0\" align=\"center\" cellpadding=\"2\" cellspacing=\"0\" bgcolor=\"#FFFFFF\">\n");
printf("<tr bgcolor=\"#003366\">\n");
printf("<td width=\"30%%\" valign=\"top\"><p align=\"left\"><font color=\"#FFFFFF\" size=\"1\" face=\"Arial\"><b>Job Title</b></font></p></td>\n");
printf("<td width=\"21%%\" valign=\"top\"><p align=\"left\"><font color=\"#FFFFFF\" size=\"1\" face=\"Arial\"><b>City</b></font></p></td>\n");
printf("<td width=\"17%%\" valign=\"top\"><p align=\"left\"><font color=\"#FFFFFF\" size=\"1\" face=\"Arial\"><b>State</b></font></p></td>\n");
printf("<td width=\"16%%\" valign=\"top\"><p align=\"left\"><font color=\"#FFFFFF\" size=\"1\" face=\"Arial\"><b>Date Posted</b></font></p></td>\n");
printf("<td width=\"16%%\" valign=\"top\"><div align=\"left\"><font color=\"#FFFFFF\" size=\"1\" face=\"Arial\"><b>Job Details</b></font></div></td>\n");
printf("</tr>\n");
$bgcolor = TRUE; //variable for alternating row bgcolor
while ($myrow = mysql_fetch_array($result2)) {
if ($bgcolor == TRUE) {
// print the white row
printf("<tr bgcolor=\"#FFFFFF\">\n");
$bgcolor = FALSE;
}
else {
// print grey row
printf("<tr bgcolor=\"#CCCCCC\">\n");
$bgcolor = TRUE;
}
printf("<td width=\"30%%\" align=\"left\" valign=\"top\"><font color=\"#003366\" size=\"1\" face=\"Arial\">%s</font></td>\n", $myrow["job_title"]);
printf("<td width=\"21%%\" align=\"left\" valign=\"top\"><font color=\"#003366\" size=\"1\" face=\"Arial\">%s</font></td>\n", $myrow["employers.city"]);
printf("<td width=\"17%%\" align=\"left\" valign=\"top\"><font color=\"#003366\" size=\"1\" face=\"Arial\">%s</font></td>\n", $myrow["employers.state"]);
printf("<td width=\"16%%\" align=\"left\" valign=\"top\"><font color=\"#003366\" size=\"1\" face=\"Arial\">%s</font></td>\n", $myrow["date"]);
printf("<td width=\"16%%\" valign=\"top\"><div align=\"left\"><font color=\"#003366\" size=\"1\" face=\"Arial\"><a href=\"%s?id=%s\">View</a></font></div></td>\n", "record_detail.php", $myrow["id"]);
printf("</tr>\n");
}
printf("</table></td>\n");
printf("</tr>\n");
printf("</table>\n");
//determine number of pages needed
$pages=intval($numrows/$limit);
//if number of records isn't evenly divisible by the number of pages above, add another page.
if ($numrows%$limit)
{
$pages++;
}
//set value of first record to be displayed
$first_record = $offset + 1;
//begin display to browser
echo "<br><font face=\"Arial\" size=\"1\" color=\"#FF0000\"><b>Now viewing: $first_record -";
//see if we're on the last page
if (!((($offset)/$limit)+1==$pages))
{
$last_record = $offset + $limit;
//is last page - send this to browser
echo " $last_record of $numrows job posts</b></font><br>";
}
else
{
//is NOT last page - send this to browser
echo " $numrows of $numrows job posts</b></font><br>";
}
//prepare and display links to other results in browser
if ($offset != 0)
{
$prevoffset=$offset-$limit;
//create a previous link - if we're not on the first page
echo "<font face=\"Arial\" size=\"1\"><a href='$PHP_SELF?offset=$prevoffset&&job_title=$job_title&&city=$city&&state=$state&&date=$date'>Prev</a></font> \n";
}
//construct that shows just prev-next or all page numbers
if ($showpages == Y)
{
if ( $pages != 1 )
{
for ($i=1;$i<=$pages;$i++)
{
$newoffset=$limit*($i-1);
if ( ((($offset)/$limit)==($i-1)) )
{
echo "<font face=\"Arial\" size=\"1\">$i </font>\n";
}
else
{
echo "<font face=\"Arial\" size=\"1\"><a href='$PHP_SELF?offset=$newoffset&&job_title=$job_title&&city=$city&&state=$state&&date=$date'>$i</a> </font>\n";
}
}
}
}
if (!((($offset)/$limit)+1==$pages) && $pages!=1)
{
$newoffset=$offset+$limit;
//create a next link - if we're not on the last page
echo "<font face=\"Arial\" size=\"1\"><a href='$PHP_SELF?offset=$newoffset&&job_title=$job_title&&city=$city&&state=$state&&date=$date'>Next</a></font><p>\n";
}
?>
Can anyone help me with this?
Thanks in advance.
