greens85 Posted April 21, 2009 Share Posted April 21, 2009 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">'; } } } ?> Quote Link to comment Share on other sites More sharing options...
Zhadus Posted April 21, 2009 Share Posted April 21, 2009 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. Quote Link to comment Share on other sites More sharing options...
greens85 Posted April 21, 2009 Author Share Posted April 21, 2009 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? Quote Link to comment Share on other sites More sharing options...
Zhadus Posted April 21, 2009 Share Posted April 21, 2009 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. Quote Link to comment Share on other sites More sharing options...
greens85 Posted April 21, 2009 Author Share Posted April 21, 2009 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! Quote Link to comment Share on other sites More sharing options...
saint959 Posted April 21, 2009 Share Posted April 21, 2009 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". Quote Link to comment Share on other sites More sharing options...
Zhadus Posted April 21, 2009 Share Posted April 21, 2009 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']; Quote Link to comment Share on other sites More sharing options...
greens85 Posted April 21, 2009 Author Share Posted April 21, 2009 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? Quote Link to comment Share on other sites More sharing options...
Zhadus Posted April 21, 2009 Share Posted April 21, 2009 You're doing the query from a table 'subcounties'. The code I wrote was to draw it from your 'jobs' table. Quote Link to comment Share on other sites More sharing options...
greens85 Posted April 21, 2009 Author Share Posted April 21, 2009 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. Quote Link to comment Share on other sites More sharing options...
Zhadus Posted April 21, 2009 Share Posted April 21, 2009 <?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>'; ?> Quote Link to comment Share on other sites More sharing options...
greens85 Posted April 21, 2009 Author Share Posted April 21, 2009 Hi, This returns an error of: Parse error: syntax error, unexpected T_ECHO in /path/to/file/searchjobs.php on line 27 ??? Quote Link to comment Share on other sites More sharing options...
Zhadus Posted April 21, 2009 Share Posted April 21, 2009 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>'; ?> Quote Link to comment Share on other sites More sharing options...
greens85 Posted April 21, 2009 Author Share Posted April 21, 2009 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? Quote Link to comment Share on other sites More sharing options...
Zhadus Posted April 21, 2009 Share Posted April 21, 2009 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. Quote Link to comment Share on other sites More sharing options...
greens85 Posted April 21, 2009 Author Share Posted April 21, 2009 Both files are attached: searchjobs.php = form results.php = is the script that should be processing the data sent [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
Zhadus Posted April 21, 2009 Share Posted April 21, 2009 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. Quote Link to comment Share on other sites More sharing options...
greens85 Posted April 21, 2009 Author Share Posted April 21, 2009 "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? Quote Link to comment Share on other sites More sharing options...
Zhadus Posted April 21, 2009 Share Posted April 21, 2009 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)) { ?> Quote Link to comment Share on other sites More sharing options...
greens85 Posted April 21, 2009 Author Share Posted April 21, 2009 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. ??? Quote Link to comment Share on other sites More sharing options...
greens85 Posted April 21, 2009 Author Share Posted April 21, 2009 Thats done it mate. I cant thank you enough for your help and patience in this. Thanks once again. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.