[SOLVED] Complex Search Query

[coolphpdude]

Hi there,

 

I am doing a (in my view) complex search query.

its a school site where you can search a database to see what grades each student got in a selected subject.

i have gave the user the options to select a grade from one drop down menu and select a subject from another drop down menu, the user can also select 'no preference' which will return a value of '0'. My problem is this... how do you program the script to return all records when value '0' is returned. The user might have no preference to what grade was acheived but want all the grades from maths, the user might want to see all students which received an 'A Grade' with 'no preference' to what subject, OR, the user might want to see all students that recieved 'A Grade' but only for 'maths' subject.

 

Although i have only mentioned 2 possible search options above (grade and suject) I will be adding more criteria for the user to choose from to help them narrow results. This is where the search becomes complex to me. I have thought of two possible ways to do this:

1) build a search query as i go

2) do an overall search then delete records that do not match the criteria chosen by the user

 

if i use option 1 then as far as im aware i will have to do 'if statements' for every eventuality. If i use option 2 im a little confused as to how to do it. Does any1 have any idea's how i can make this simple??

 

I personally am thinking option 2 might be best but i would need to take all records within the database into an array 1st and then delete out only the records that DO NOT match what the user selected (so if the user selected 'A Grade' the code would remove all records from the array where grade IS NOT EQUAL to A). I also have the problem with doing it this way how i would program the script to return all the results if the no preference value was returned.

 

ANY help would be really appreciated.

[trq]

Number 1 is the only viable option. I would never even consider option two as its very inefficient.

[coolphpdude]

Number 1 is the only viable option. I would never even consider option two as its very inefficient.

 

is that due to the fact your pulling out all records n then deleting as you go which would make the query time longer??

 

Im even more stuck with option 1!! The way i was considering doing it was the following:

 

^{if ($grade != '0')}

^{

^{$gradestring = "= '$grade'";}

 

^{if ($subject != '0')}

^{

^{$subjectstring = "= '$subject";}

 

^{$query = mysql_query("SELECT * FROM grades WHERE grade $gradestring AND subject $subjectstring");}

^{$my_query_row = mysql_fetch_array($query);}

 

^{printf ("query result: %s", $my_query_row["grade"]);}

^{echo "<p>";}

^}

 

^else

^{

^{$query = mysql_query("SELECT * FROM grades WHERE grade $gradestring");}

^{$my_query_row = mysql_fetch_array($query);}

 

^{printf ("query result: %s", $my_query_row["grade"]);}

^}

 

 

^}

 

^{else if (subject != '0')}

^{

^{$subjectstring = "= '$subject'";}

 

^{$query = mysql_query("SELECT * FROM grades WHERE subject $subjectstring");}

^{$my_query_row = mysql_fetch_array($query);}

 

^{printf ("query result: %s", $my_query_row["grade"]);}

 

^}

 

^else

^{

^{echo "Please select at least one search criteria from our search options.";}

^}

 

 

my problem is that basically there is 2 possible outcome for every criteria that i give the user to search, these being 'yes something was chosen' or 'no the user had no preference'. If i give a user 6 search criteria thats 128 possibles outcomes and i would have to do 'if statements' to produce a query for EVERY possible outcome. Am i thinking of this right or is there a simpler way??

 

[MrManager]

You should create the query dynamically by generating the WHERE part of the query from the form values. Also, remember to use mysql_real_escape_string on user-defined (GET/POST) variables to protect your scripts from hackers.

 

Here is a quick example (untested):

$querystring = "SELECT * FROM table WHERE 1";

if(isset($_GET['grade']) && $_GET['grade'] != 0) {
$querystring .= " AND grade = '".mysql_real_escape_string($_GET['grade'])."'";
}

if(isset($_GET['subject']) && $_GET['subject'] != 0) {
$querystring .= " AND subject = '".mysql_real_escape_string($_GET['subject'])."'";
}

echo "DEBUG: query = $querystring";

$result = mysql_query($querystring);
while($data = mysql_fetch_assoc($result)) {
var_dump($data);
}

[coolphpdude]

You should create the query dynamically by generating the WHERE part of the query from the form values. Also, remember to use mysql_real_escape_string on user-defined (GET/POST) variables to protect your scripts from hackers.

 

Here is a quick example (untested):

$querystring = "SELECT * FROM table WHERE 1";

if(isset($_GET['grade']) && $_GET['grade'] != 0) {
$querystring .= " AND grade = '".mysql_real_escape_string($_GET['grade'])."'";
}

if(isset($_GET['subject']) && $_GET['subject'] != 0) {
$querystring .= " AND subject = '".mysql_real_escape_string($_GET['subject'])."'";
}

echo "DEBUG: query = $querystring";

$result = mysql_query($querystring);
while($data = mysql_fetch_assoc($result)) {
var_dump($data);
}

 

hi thanks for your reply.

 

I was using post for the passing of variables from the form to the code. im pretty new to strings so the code is a little hard for me to understand. the '.' does this add whatever is typed after it to the end of the string??

 

As far as i can see from that code it doesnt return all records for the options where the user selects no preference?

[MrManager]

If you're using POST just replace all occurrences of $_GET with $_POST.

 

About the dot operator: 'string1' . 'string2' . 'string3' will turn into 'string1string2string3'. This is called string concatenation. If you're not familiar with this, you should probably read some PHP tutorials before you try to write a script.

[coolphpdude]

got a problem with what your suggesting. I understand now how to build a string which will form a query, theres 1 problem, we dont know how many criteria the user will choose to search from. if i give the user 6 search criteria and the user searches only 1 of these then there will be no need for the 'AND' command. however, if the user does choose more than 1 option then the 'AND' would be needed, how can i build this query?!?!

[coolphpdude]

ive copied ur code, its working to an extent. it builds the first part (for the grade but it doesnt even do anything for the subject. any idea's?

[coolphpdude]

right i kno y it dun it now because the i was returning a numerical value for grades (0=no preference, 1=A grade, 2=B Grade), problem is the drop down menu for the subject, 0=no preference but the value for the other options is corrosponding to the subject (0=no preference, maths=maths, english=english, etc...). i's there anyway i can use the same method but with text instead of only with numerical value's?

 

thanks alot for your help.

[coolphpdude]

Sorted!! Thanks for your help you lot!