Skip to main content
J
Jonathan_Fortis
Inspiring
April 8, 2012
Answered

search results displaying all records, please help

  • April 8, 2012
  • 37 replies
  • 5656 views

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

            <input name="PositionReq" type="text" class="textfeilds" value="Job Title" size="32" />

            <input name="skills_offered" type="text" class="textfeilds" value="Skills Required" size="32" />

            <input name="SalaryReq" type="text" class="textfeilds" value="Salary Offered" size="32" />

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

thanks in advance

This topic is closed to new replies. Start a new post to keep the conversation going.
Correct answer Jonathan_Fortis

I've already told you where the problem is. If you echo $query_Recordset1 you'll immediately be able to see the cause.


sorry,,,just needed a kick  . "%"

thanks for the prompt.

regards

37 replies

B
bregent
Participating Frequently
April 9, 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?

J
Jonathan_FortisAuthor
Inspiring
April 9, 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

B
bregent
Participating Frequently
April 9, 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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices