Skip navigation
Currently Being Moderated

search results displaying all records, please help

Apr 8, 2012 11:06 AM

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

 
Replies
  • Currently Being Moderated
    Apr 9, 2012 10:07 AM   in reply to Jonathan Fortis

    >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?

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 10:41 AM   in reply to Jonathan Fortis

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 11:06 AM   in reply to Jonathan Fortis

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 11:22 AM   in reply to Jonathan Fortis

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

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 1:29 PM   in reply to Jonathan Fortis

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

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 1:46 PM   in reply to Jonathan Fortis

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

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 1:59 PM   in reply to Jonathan Fortis

    Where is the submit button on your form?

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 2:23 PM   in reply to Jonathan Fortis

    ><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 '%'.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 2:57 PM   in reply to Jonathan Fortis

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 3:01 PM   in reply to Jonathan Fortis

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

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 3:38 PM   in reply to Jonathan Fortis

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

     

    I can't comment without knowing all of the details. If you are using AND to combine conditions, then you probably want to continue using the wildcard. In any case, I wouldn't put default text in the form fields. Use labels to help the user complete the form.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 3:40 PM   in reply to Jonathan Fortis

    >its strange becuase some of the feilds are working the job title isnt working

    >and neither is the skills required but the salary offered and location are working now

     

    When you are not getting the expected results, we need to see the SQL submitted - every time.

    Echo the $query_Recordset1  variable and post the results.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 10, 2012 10:30 AM   in reply to Jonathan Fortis

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

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

    >LIKE '%Skills Required%' OR location LIKE '%Location%' OR skills_offered LIKE '%xxxxx%'

    >

    >this has no results

     

    Right. Are you expecting a result? You have no search criteria entered, other than the default values you have in the form fields.

     

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

    >'%Salary Offered%' OR PositionReq LIKE '%fireman%' OR location LIKE '%Location%' OR skills_offered LIKE '%xxxxx%'

    >

    >this has no result

     

    Right. You are searching for 'fireman' in the PositionReq column, but you stated above that it is in the skills_offered column.

     

    Why does PositionReq  SalaryReq use proper case naming, while skills_offered uses lower case with an underscore?  Things are a real mess! We've already found about a dozen errors in your code for this relatively simple search form.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 11, 2012 9:43 AM   in reply to Jonathan Fortis

    >Is is a problem ( i know its not good practice) to  have an underscore

    >or should i go back and start again

     

    You can use underscores, proper case, camel case -anything that you want. The key is consistency; it makes your code easier to read which results in less coding errors and easier maintenance. Pick a naming convention for your objects and stick with it 100% of the time. You don't need to use the same convention for database objects as you do for php objects - but within each object type (table name, column name, local variable, etc) be consistent. Search the web for 'database naming conventions' and 'php naming conventions' for some ideas.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 16, 2012 9:07 AM   in reply to 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.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points