Select Query Error

[stan801003]

I modified a script i downloaded to work in my web pages, the problem is, the results no longer show since i have modified the select query.  With the original script it just selected all entries from the database.  I have since tried to change the queryy to select the category and location from the index page.  However it does not show the results.  The original query still works on another page but where i have tried to update this script the query no longer works.  I would be grateful of any suggestions or pointers.  Thanks

<?
$db_addr = 'localhost'; // address of MySQL server.
$db_user = ''; // Username to access server.
$db_pass = ''; // Password access server.
$db_name = ''; // Name of database to connect to.
$connect = @mysql_connect("$db_addr", "$db_user", "$db_pass");

if (!($connect)) // If no connect, error and exit().
{
echo("<p>Unable to connect to the database server.</p>");
exit();
}

if (!(@mysql_select_db($db_name))) // If can't connect to database, error and exit().
{
echo("<p>Unable to locate the $db_name database.</p>");
exit();
}
$category = $_POST["category"] ;
$location = $_POST["location"] ;

if (!($limit)){
$limit = 10;} // Default results per-page.
if (!($page)){
$page = 0;} // Default page value.
$numresults = mysql_query("SELECT * FROM vacancies WHERE category = '$category' AND location = '$location' LIKE '%". $query ."%'"); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
if ($numrows == 0){
echo("No results found matching your query - $query"); // bah, modify the "Not Found" error for your needs.
exit();}

$pages = intval($numrows/$limit); // Number of results pages.

// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows%$limit) {
$pages++;} // has remainder so add one page

$current = ($page/$limit) + 1; // Current page number.

if (($pages < 1) || ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.

//escape from PHP mode.
?>

<title>Search Results for <?=$query?></title>

<center><h2>Search Results for <?=$query?></h2></center>
<table width="100%" border="0">
<tr>
  <td width="50%" align="left">
Results <b><?=$first?></b> - <b><?=$last?></b> of <b><?=$numrows?></b>
  </td>
  <td width="50%" align="right">
Page <b><?=$current?></b> of <b><?=$total?></b>
  </td>
</tr>
<tr>
  <td colspan="2" align="right">&nbsp;

  </td>
</tr>
<tr>
  <td colspan="2" align="right">
Results per-page: <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=5">5</a> | <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=10">10</a> | <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=20">20</a> | <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=50">50</a>
  </td>
</tr>
</table>
<?
//Go back into PHP mode.

// Now we can display results.
$results = mysql_query("SELECT * FROM vacancies WHERE category = '$category' AND location = '$location' LIKE '%". $query ."%' ORDER BY jobtitle ASC LIMIT $page, $limit");
while ($data = mysql_fetch_array($results))
{$
?>
<p><a href="<?=$data["jobtitle"]?>" title="<?=$data["location"]?>"><?=$data["salary"]?></a> - <?=$data["date"]?></p>
<?
}
?>
<p align="center">
<?
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a>    \n");}

for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
$ppage = $limit*($i - 1);
if ($ppage == $page){
echo("<b>$i</b> \n");} // If current page don't give link, just text.
else{
echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> \n");}
}

if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo("    <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>\n");}
?>

[AndyB]

[code]$numresults = mysql_query("SELECT * FROM vacancies WHERE category = '$category' AND location = '$location' LIKE '%". $query ."%'"); // the query.[/code]

That query makes no sense. $query appears to be undefined, and the LIKE clause isn't comparing anything to it.

For easy debugging, make use of error trapping. Try a syntax like this for your database queries:

[code]$query = "SELECT ..... "; // whatever your query string is
$result = mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query); // show the trouble[/code]

[stan801003]

Thank you