[SOLVED] Searchable Results

[greens85]

Hi all,

 

I currently have a situation where I have some jobs dragged out of a database and displayed on a page in my site.

 

I'm just wondering how i would go about making these jobs searchable, i.e. have a search box on the homepage which would then drag up whichever jobs were requested. i.e. if a person enters a keyword of 'PHP developer', only relevant jobs would be returned.

 

Not sure how to even begin this, so any help or advice would be greatly appreciated.

 

The code below shows how I am 'dragging' the jobs from the database.

 

<?php
$connection = mysql_connect("host", "user", "pass") or die (mysql_error());
$db = mysql_select_db("mydb", $connection) or die (mysql_error());

$sql = "SELECT * FROM jobs WHERE CS = 'Yes' ORDER BY jobid";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_array ($result)) {
	echo '<font color="#246494"><strong>Job Title:</strong></font> ';
	echo $row['position'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Description:</strong></font> ';
	echo $row['description'];
	echo '<br/>';
	echo '<br/>';
	echo '<font color="#246494"><strong>Job Reference:</strong></font> ';
	echo $row['jobref'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Hours:</strong></font> ';
	echo $row['hour'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Location:</strong></font> ';
	echo $row['subcounty'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Working Term:</strong></font> ';
	echo $row['contract'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Salary:</strong></font> ';
	echo $row['salary'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Application Deadline:</strong></font> ';
	echo $row['deadline'];
      	echo '<br />';
	?>
	<div align="right"><a href="http://www.educationvacancies.com/jobseekers/info.php?jobid=<?=$row['jobid']?>" target="_blank">Apply Now</a></div>
	<?
        echo '<br/>';
	echo '<hr style="background-color: rgb(204, 204, 204);" width="100%" size="1" noshade="noshade" color="#246494">';
    }
  }
}

?>

[Zhadus]

A search system can get very indepth.

 

You'll want to use the MySQL LIKE term.

i.e.

<?php
$term = $_GET['searchterm'];
$query = "SELECT * FROM jobs WHERE position LIKE '%$term%'";
$results = mysql_query($query) or die();
?>

 

I hope that puts you on the right track.

[greens85]

I understand the basics of how that works,

 

All i really need is a location field, as the site is aimed a certain type of market anyway. Would there be a way to have a drop down location field, that dragged the data i.e. all the locations from my database?

 

Then say if they selected london, it wud filter the results for just london jobs?

 

[Zhadus]

Many ways to do that and it's very possible. I'd recommend just creating an array with all the locations, but you can pull it from your database as well.

 

<?php
$query = "SELECT subcounty FROM jobs";
$qres = mysql_query($query) or die();
$loclist = mysql_fetch_array($qres);
$locations = array_unique($loclist);
echo '<select name="location">';
for ($x = 0; $x < sizeof($locations); $x++)
echo "<option value='$locations[$x]'>$locations[$x]</option>";
echo '</select>';
?>

 

Then from there put it into a form with a submit, have it check for jobs with subcounty equal to the search.

[greens85]

I have this so far:

 

searchjobs.php

 

<table width="100%" border="0">
<form action="results.php" method="post"/>
 <tr>
   <td><label>Category:</label></td>
 </tr>
 <tr>
   <td><select>
	<option>Specified Job Type</option>
	</select>
   </td>
 </tr>
 <tr>
   <td>Location:</td>
 </tr>
 <tr>
   <td><input type="text" name="location" id="location"></td>
 </tr>
 <tr>
   <td align="left"><input type="submit" name="submit" id="submit" value="submit"/></td>
 </tr>
</form>
</table>

 

results.php

 

<?php

$connection = mysql_connect("host", "user", "pass") or die (mysql_error());
$db = mysql_select_db("my_db", $connection) or die (mysql_error());

$location = $_GET['location'];
$sql = "SELECT * FROM jobs WHERE subcounties LIKE '%location%'";
	if ($result = mysql_query($sql)) {
		if (mysql_num_rows($result)) {
			while ($row = mysql_fetch_array ($result)) {

	echo '<font color="#246494"><strong>Job Title:</strong></font> ';
	echo $row['position'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Description:</strong></font> ';
	echo $row['description'];
	echo '<br/>';
	echo '<br/>';
	echo '<font color="#246494"><strong>Job Reference:</strong></font> ';
	echo $row['jobref'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Hours:</strong></font> ';
	echo $row['hour'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Location:</strong></font> ';
	echo $row['subcounty'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Working Term:</strong></font> ';
	echo $row['contract'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Salary:</strong></font> ';
	echo $row['salary'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Application Deadline:</strong></font> ';
	echo $row['deadline'];
     	echo '<br />';
	?>
	<div align="right"><a href="http://www.educationvacancies.com/jobseekers/info.php?jobid=<?=$row['jobid']?>" target="_blank">Apply Now</a></div>
	<?
       echo '<br/>';
	echo '<hr style="background-color: rgb(204, 204, 204);" width="100%" size="1" noshade="noshade" color="#246494">';
   }
 }
}
?>

 

However this just returns a blank page, any ideas what im doing wrong?

 

I'm new to PHP so appologies for the lack of knowledge, i realise i may be overlooking something really simple!

[saint959]

Hi,

 

In your mySQL query you left out the $ for location. So its searching the field where it is like "location" and not the variable "$location".

 

 

 

[Zhadus]

Additionally your query uses 'subcounties' when it looks like the fieldname is 'subcounty'. And also you're using $_GET[] while you're POSTing the variable.

$location = $_POST['location'];

[greens85]

Zhadus,

 

I have used your code for the form on the front end:

 

i.e.

 

<?php
$query = "SELECT subcounty FROM subcounties";
$qres = mysql_query($query) or die();
$loclist = mysql_fetch_array($qres);
$locations = array_unique($loclist);
echo '<select name="location">';
for ($x = 0; $x < sizeof($locations); $x++)
echo "<option value='$locations[$x]'>$locations[$x]</option>";
echo '</select>';
?>

 

This is exactly what i mean, so thanks for that. The problem is, the drop down is only returning one row from the database when there are several. Can you see what I have done wrong here?

[Zhadus]

You're doing the query from a table 'subcounties'. The code I wrote was to draw it from your 'jobs' table.

[greens85]

I have a table called subcounties and then 3 fields of:

 

ID | subcounty | toplevelcountry

 

what i need to do is drag all the data from the subcounty row.

 

Would this be achieved by any of the following?

 

SELECT * FROM subcounty

 

or

 

SELECT * FROM subcounties

 

Thanks for your replies, as you can see im really stuck on this.

[Zhadus]

<?php
$query = "SELECT subcounty FROM subcounties";
$qres = mysql_query($query) or die();
echo '<select name="location">';
while ($info = mysql_fetch_array($qres)
echo '<option value=' . $locations[$x] . '>$locations[$x]</option>";
echo '</select>';
?>

[greens85]

Hi,

 

This returns an error of:

 

Parse error: syntax error, unexpected T_ECHO in /path/to/file/searchjobs.php on line 27

 

???

[Zhadus]

Oops, I apologize:

 

<?php
$query = "SELECT subcounty FROM subcounties";
$qres = mysql_query($query) or die();
echo '<select name="location">';
while ($info = mysql_fetch_array($qres)
echo '<option value="' . $info['subcounty'] . '">' . $info['subcounty'] . '</option>';
echo '</select>';
?>

[greens85]

Hi mate,

 

Its still returning the same error.

 

Would it help if i attached the files, as I may have some rouge tag in there that I am unable to spot?

[Zhadus]

It would help, I'll be able to take a closer look. Either attach here or you can email me, should be in my profile.

[greens85]

Both files are attached:

 

searchjobs.php = form

results.php = is the script that should be processing the data sent

 

 

[attachment deleted by admin]

[Zhadus]

Still my error. My apologies again, got wisdom teeth out a few days ago and still on some medication that makes me loopy

 

<?php
$query = "SELECT subcounty FROM subcounties";
$qres = mysql_query($query) or die();
echo '<select name="location">';
while ($info = mysql_fetch_array($qres))
echo '<option value="' . $info['subcounty'] . '">' . $info['subcounty'] . '</option>';
echo '</select>';
?>

 

Change it to that inside 'searchjobs.php'.

 

There was no closing parenthesis for the 'while' statement, and the array was indexed wrong.

[greens85]

"Still my error. My apologies again, got wisdom teeth out a few days ago and still on some medication that makes me loopy"

 

Doesnt seem to affect your coding skills, thats done the trick mate, thanks very much!

 

Although it still returns a blank page, even though it should be returning a job! would you mind checking results.php just to make sure i havnt made any stupid newbie mistakes?

[Zhadus]

Change the following:

<?php
$sql = "SELECT * FROM jobs WHERE subcounty LIKE '%$locations%'";
	if ($result = mysql_query($sql)) {
		if (mysql_num_rows($result)) {
			while ($row = mysql_fetch_array ($result)) {
?>

 

To this, and then fix your ending brackets

<?php
$sql = "SELECT * FROM jobs WHERE subcounty = '$locations'";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{
?>

[greens85]

I've now got it to return results - BUT it returns all the jobs in the database opposed to just the ones relating to that location.

 

???

[greens85]

Thats done it mate.

 

I cant thank you enough for your help and patience in this.

 

Thanks once again.