search results displaying all records, please help

Report · Apr 08, 2012

i did post this before but didnt have any luck I have used my search script before but tried it again and it is returning ALL the results from the DB..Can anyone see what i am missing?

$var_SalaryReq_Recordset1 = "%";

if (isset($_GET['SalaryReq'])) {

$var_SalaryReq_Recordset1 = $_GET['SalaryReq'];

}

$var_skills_offered_Recordset1 = "%";

if (isset($_GET['skills_offered'])) {

$var_skills_offered_Recordset1 = $_GET['skills_offered'];

}

$var_location_Recordset1 = "%";

if (isset($_GET['location'])) {

$var_location_Recordset1 = $_GET['location'];

}

$var_PositionReq_Recordset1 = "%";

if (isset($_GET['PositionReg'])) {

$var_PositionReq_Recordset1 = $_GET['PositionReg'];

}

mysql_select_db($database_hostprop, $hostprop);

$query_Recordset1 = sprintf("SELECT userid, FirstName, Surname, SalaryReq, PositionReq, location, otherComments, skills_offered FROM think_signup WHERE SalaryReq LIKE %s OR PositionReq LIKE %s OR location LIKE %s OR skills_offered LIKE %s", GetSQLValueString("%" . $var_SalaryReq_Recordset1 . "%", "text"),GetSQLValueString("%" . $var_PositionReq_Recordset1 . "%", "text"),GetSQLValueString("%" . $var_location_Recordset1 . "%", "text"),GetSQLValueString("%" . $var_skills_offered_Recordset1, "text"));

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $hostprop) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

the search feilds are

thanks in advance

Report · Apr 09, 2012

>SELECT userid, FirstName, Surname, SalaryReq, PositionReq, location, otherComments, skills_offered FROM think_signup

>WHERE SalaryReq LIKE %s OR PositionReq LIKE %s OR location LIKE %s OR skills_offered LIKE %s

That query will only work if the user enters values in all fields, right? Otherwise it will return all rows. How are you testing this?

Report · Apr 09, 2012

Thats correct, if user inputs a search into only one feild it returns all results, I want it to work if even only one search box is used, I used this logic before and it works fine, but this time it doesnt

Report · Apr 09, 2012

You could not have used the same logic successfully before - it simply will not work. You are using the LIKE predicate and OR keywords separating your conditions. And you are setting the default value to the SQL wildcard. So if a user doesn't enter a value in a field, all rows from the table will match.

As I have said before, the best way to solve this is to dynamically build the WHERE clause. You add each field to the WHERE clause only if the user entered a value. Another option, and a bit of a kludge in my option, is to set the default value to something that you know will never appear in the table values, like "XXXXXXX".

Also, not related to your problem, but I notice that your skills_offered field is not appending the wildcard to the end. Not sure if this was intentional.

Report · Apr 09, 2012

this is the statment from the previous search that works how i need it

$var_tk_job_title_Recordset1 = "%";

if (isset($_GET["tk_job_title"])) {

$var_tk_job_title_Recordset1 = $_GET["tk_job_title"];

}

$var_tk_job_location_Recordset1 = "%";

if (isset($_GET["tk_job_location"])) {

$var_tk_job_location_Recordset1 = $_GET["tk_job_location"];

}

$var_tk_job_salary_Recordset1 = "%";

if (isset($_GET["tk_job_salary"])) {

$var_tk_job_salary_Recordset1 = $_GET["tk_job_salary"];

}

mysql_select_db($database_hostprop, $hostprop);

$query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,200) as truncated_job_desc FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s", GetSQLValueString("%" . $var_tk_job_title_Recordset1 . "%", "text"),GetSQLValueString("%" . $var_tk_job_location_Recordset1 . "%", "text"),GetSQLValueString("%" . $var_tk_job_salary_Recordset1 . "%", "text"));

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $hostprop) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

and thats why i replicated it for the new search

>As I have said before, the best way to solve this is to dynamically build the WHERE clause. You add each field to the WHERE clause only if the user entered a value.

was this in a previous post?

.>Also, not related to your problem, but I notice that your skills_offered field is not appending the wildcard to the end. Not sure if this was intentional

no this was not intentional, i have amended this

Report · Apr 09, 2012

also i tried using the xxxxx as the defualt value instead of the SQL wildcard % and no results are displayed??

Report · Apr 09, 2012

Can you show me the complete script where you have the default values set to 'xxxxxxx' and tell us what values are being entered in the search criteria when no results are displayed. Also include the code from your search form.

Report · Apr 09, 2012

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_Recordset1 = 5;

$pageNum_Recordset1 = 0;

if (isset($_GET['pageNum_Recordset1'])) {

$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];

}

$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$var_SalaryReq_Recordset1 = "xxxxx";

if (isset($_GET['SalaryReq'])) {

$var_SalaryReq_Recordset1 = $_GET['SalaryReq'];

}

$var_skills_offered_Recordset1 = "xxxxx";

if (isset($_GET['skills_offered'])) {

$var_skills_offered_Recordset1 = $_GET['skills_offered'];

}

$var_location_Recordset1 = "xxxxx";

if (isset($_GET['location'])) {

$var_location_Recordset1 = $_GET['location'];

}

$var_PositionReq_Recordset1 = "xxxxx";

if (isset($_GET['PositionReg'])) {

$var_PositionReq_Recordset1 = $_GET['PositionReg'];

}

mysql_select_db($database_hostprop, $hostprop);

$query_Recordset1 = sprintf("SELECT SalaryReq, skills_offered, location, PositionReq, otherComments, userid, FirstName, Surname FROM think_signup WHERE SalaryReq LIKE %s OR PositionReq LIKE %s OR location LIKE %s OR skills_offered LIKE %s", GetSQLValueString("%" . $var_SalaryReq_Recordset1 . "%", "text"),GetSQLValueString("%" . $var_skills_offered_Recordset1 . "%", "text"),GetSQLValueString("%" . $var_location_Recordset1 . "%", "text"),GetSQLValueString("%" . $var_PositionReq_Recordset1 . "%", "text"));

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $hostprop) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {

$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];

} else {

$all_Recordset1 = mysql_query($query_Recordset1);

$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);

}

$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$queryString_Recordset1 = "";

if (!empty($_SERVER['QUERY_STRING'])) {

$params = explode("&", $_SERVER['QUERY_STRING']);

$newParams = array();

foreach ($params as $param) {

if (stristr($param, "pageNum_Recordset1") == false &&

stristr($param, "totalRows_Recordset1") == false) {

array_push($newParams, $param);

}

if (count($newParams) != 0) {

$queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));

}

$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);

the form is

examples of search criteria are job title would be say police officer, location would be say london

when using xxxx no results are being dispalyed

Report · Apr 09, 2012

Please, I need to see the entire form, everything between and including the FORM tags.

Report · Apr 09, 2012

<p>

</form>

Report · Apr 09, 2012

i have just noticed something there is a double line to the ../candidate-search-results.php

one is the form tag and the other in the search image, would this be the problem?

Report · Apr 09, 2012

Is the above what u needed, I re

moved the other button link but that di

dnt do anything

Report · Apr 09, 2012

Look at the code. What method are you using to submit the form? What method are you using to retrieve the form values !

Report · Apr 09, 2012

OK,i see that when i posted to the thread, i have now changed the form to GET but it is still not working, if i change the default value to the wildcard % it displays all the results but if i change it to xxxxx i get no results, so i know the way the form was sent was incorrect but now being changed i am still getting the same results.

Report · Apr 09, 2012

So now you need to start troubleshooting by echoing values to the screen. Start by outputting $query_Recordset1 and paste the results here.

Report · Apr 09, 2012

i put an echo at the end of the script, i then done a search on the location and the echo gave the following resutls

SELECT SalaryReq, skills_offered, location, PositionReq, otherComments, userid, FirstName, Surname FROM think_signup WHERE SalaryReq LIKE '%%%' OR PositionReq LIKE '%%%' OR location LIKE '%%%' OR skills_offered LIKE '%%%'

Report · Apr 09, 2012

Where is the submit button on your form?

Report · Apr 09, 2012

i have added the submit value as below

and still showing the same as before

Report · Apr 09, 2012

><input name="location" type="text" class="textfeilds" value="Location" size="32" />

Why are you populating a value in the text field? How are you clearning that out before submitting?

>SELECT SalaryReq, skills_offered, location, PositionReq, otherComments, userid, FirstName,

>Surname FROM think_signup WHERE SalaryReq LIKE '%%%' OR PositionReq LIKE '%%%'

>OR location LIKE '%%%' OR skills_offered LIKE '%%%'

This tells me that your default value is still '%' and you have NOT changed it to 'xxxxx'. Or, you are using some unposted method to populate it with '%'.

Report · Apr 09, 2012

>Why are you populating a value in the text field? How are you clearning that out before submitting?

this is to show the user what to input, i am not clearing it before submission (this is the same as the other search that working)

sorry i was trying something i have changed the values back and done another echo test

SELECT SalaryReq, skills_offered, location, PositionReq, otherComments, userid, FirstName, Surname FROM think_signup WHERE SalaryReq LIKE '%xxxxx%' OR PositionReq LIKE '%xxxxx%' OR location LIKE '%xxxxx%' OR skills_offered LIKE '%xxxxx%'

Report · Apr 09, 2012

Submit the form, and then copy the url from the address bar and post it here.

>this is to show the user what to input, i am not clearing it before submission (this is the same as the other search that working)

The only reason it appears to be working is because the default values you put into those fields has not yet appeared in your data. But this is obviously a very poor practice that will eventually lead to trouble. You must always clear out that data before posting.

Report · Apr 09, 2012

http://www.rerecruitment.co.uk/beta/candidate-search-results.php

it isnt passing any of the information

the other search that is working is giving the following results

current-positions.php?tk_job_title=security&tk_job_location=Location&tk_job_salary=Salary&x=87&y=5

again here is the echo

SELECT SalaryReq, skills_offered, location, PositionReq, otherComments, userid, FirstName, Surname FROM think_signup WHERE SalaryReq LIKE '%xxxxx%' OR PositionReq LIKE '%xxxxx%' OR location LIKE '%xxxxx%' OR skills_offered LIKE '%xxxxx%'

Report · Apr 09, 2012

Are you sure you changed the form method to GET? It appears that you have not.

Report · Apr 09, 2012

Report · Apr 09, 2012

ok it seem to be a problem with the submit graphic, i will change the image, i tried it with a standard button and it worked.

thanks

just a quick one though,

should i change the other search page to xxxx rather than the wildcard?

search results displaying all records, please help

1 Correct answer