[Resolved] erors from mysql_num_rows and mysql_fetch_array

[voll]

I get the following errors:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource
AND:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
I am searching 2 tables from a form (jobs and employers). Here is the code for the form:
<form action="search_results.php" method="post" name="job_search">
<select name="state" class="formfield">
<option value="ALL">All</option>
<option value="New Jersey">New Jersey</option>
<option value="New York">New York</option>
<option value="Pennsylvania">Pennsylvania</option>
<option value="Connecticut">Connecticut</option>
</select>
<select name="job_type" class="formfield">
<option value="ALL">All</option>
<option value="Technician">Technician</option>
<option value="Sales">Sales</option>
<option value="Administration">Administration</option>
<option value="Labor/Helper">Labor/Helper</option>
<option value="Driver">Driver</option>
</select>
<select name="pos_type" class="formfield">
<option value="ALL">All</option>
<option value="Full-Time">Full-Time</option>
<option value="Part-Time">Part-Time</option>
<option value="Contract">Contract</option>
</select>
<select name="exp_level" class="formfield" id="exp_level">
<option value="ALL">All</option>
<option value="Internship">Internship</option>
<option value="Entry-level">Entry-level</option>
<option value="5 - 10 Years Experience">5-10 Years Experience</option>
<option value="Supervisory/Management">Supervisory/Management</option>
</select>
<input type='submit' class="formbutton" value="Search">
</form>

I am searching the jobs and employers tables using a JOIN. Here is the code for the results page.

<?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
//WHERE 1 = '1' is here so we can use the AND if a user searches a specific field
$sql = "SELECT * FROM jobs j FULL JOIN employees e WHERE 1 = '1'"; 
if ($job_type != "ALL") $sql .= " AND j.job_type = '".mysql_escape_string($job_type)."'"; 
if ($pos_type != "ALL") $sql .= " AND j.pos_type = '".mysql_escape_string($pos_type)."'";
if ($exp_level != "ALL") $sql .= " AND j.exp_level = '".mysql_escape_string($exp_level)."'";
if ($state != "ALL") $sql .= " AND e.state = '".mysql_escape_string($state)."'";
$result = mysql_query($sql);
$numrows = mysql_num_rows($result);
// get results for printing to page
$sql = "SELECT * FROM jobs j FULL JOIN employees e WHERE 1 = '1'"; 
if ($job_type != "ALL") $sql .= " AND j.job_type = '".mysql_escape_string($job_type)."'"; 
if ($pos_type != "ALL") $sql .= " AND j.pos_type = '".mysql_escape_string($pos_type)."'";
if ($exp_level != "ALL") $sql .= " AND j.exp_level = '".mysql_escape_string($exp_level)."'";
if ($state != "ALL") $sql .= " AND e.state = '".mysql_escape_string($state)."'";
$sql .= " ORDER BY j.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["j.job_title"]);
printf("<td width=\"21%%\" align=\"left\" valign=\"top\"><font color=\"#003366\" size=\"1\" face=\"Arial\">%s</font></td>\n", $myrow["e.city"]);
printf("<td width=\"17%%\" align=\"left\" valign=\"top\"><font color=\"#003366\" size=\"1\" face=\"Arial\">%s</font></td>\n", $myrow["e.state"]);
printf("<td width=\"16%%\" align=\"left\" valign=\"top\"><font color=\"#003366\" size=\"1\" face=\"Arial\">%s</font></td>\n", $myrow["j.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["j.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> &nbsp; \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 &nbsp; </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> &nbsp; </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";
   }
?>

Hopefully someone can help me with this, I have tried everything I know so far and I have had no luck. Thanks in advance for any help.

-Mike

[devinemke]

your query is failing. echo out $sql before you run it to make sure it is a valid query.

[voll]

I echoed out $sql and get this:

SELECT * FROM jobs j FULL JOIN employees e WHERE 1 = '1' AND j.job_type = 'Technician' AND j.pos_type = 'Full-Time' AND j.exp_level = 'Entry-level' AND e.state = 'Pennsylvania'

As far as I can tell this is OK, but I am not very familiar with doing JOIN or SQL in general.

If you have any suggestions it would really help me.

Thanks,
Mike

[devinemke]

SELECT * FROM jobs j

i think that's your problem. don't you mean "jobs, j"? are those your 2 table names?

[voll]

No, the tables are jobs and employees.
jobs j and employees e just let me refer to the jobs and employees tables as simply j and e in the rest of the SQL.

I tried it without the abbreviations too and still get the same errors, so I don't think that is the problem.

-Mike

[broc7]

Try WHERE 1 = 1.

devin: jobs j assigns an alias. The AS is optional.

[voll]

Just tried WHERE 1 = 1. Still getting the same errors. :>(

[voll]

I just noticed I had employees instead of employers for the table name. That still was not the problem. If anyone else has suggestions, it would be great.
-Mike

[broc7]

What's a full join?

You're searching from two tables, jobs and employers, but you've got jobs and employeEs in the sql.

Presumably, a job position record has the employer id as a foreign key. I'd make the sql statement more like:

SELECT * from employers e INNER JOIN jobs j on e.id = j.employer_id WHERE....

If what you're trying to do here is list all available employers/positions based on potential employee search criteria, that is.

[voll]

It works!

Thanks for all of your help.
-Mike