This
may belong in the database thread, if so please let me know.
This should be a simple issue. I have built a search engine for my client that queries a number of different rows in the database based on what a customer is looking for.
They have requested a new search query that will search based on THREE separate parameters - "Class", "Subject" and "Standard". However, all of these fields are looking in the same ROW.
In other words - client adds a product in her shopping cart and adds an extra field called "CCCSS". In that field, she will put a bunch of individual pieces of information, for example, Grade 1, ELA, RL, RI, Grade 2, Grade 6-12, Literacy, RW. All in one field, which gets written to a single row in the DB.
Now, the CUSTOMER will have a form with THREE DROPDOWN BOXES. One, the "Grade" (Grade 1, grade 2), two, the SUBJECT (ELA, Literacy, etc), and three, the STANDARD: RL, RI, RW, etc.
What I need is for these three fields, based on what the client selects, to query a single row and pluck out books/products where those three items are represented in that row. In other words, "if CCCSS has a row with all of the things I just selected, return that product".
Here is the code I am trying to use:
if($CCCSS_1 || $CCCSS_2 || $CCCSS_3) {
$fieldid = func_query_first_cell("SELECT fieldid FROM $sql_tbl[extra_fields] WHERE service_name='CCCSS'") ;
if($fieldid) {
$pids = func_query_column("SELECT productid FROM $sql_tbl[extra_field_values] WHERE fieldid='$fieldid' AND value LIKE '$CCCSS_1' and '$CCCSS_2' and '$CCCSS_3'") ;
}
if(is_array($pids)) {
$where[] = "p.productid IN ('" . implode("', '", $pids) . "')" ;
} else {
$where[] = "p.productid IN ('')" ; // products not found
}
}
This has worked for other variations of the search but not for this particular one, because this is the first that I am trying to query all three parameters from a single row. For example, I don't know ehether this:
if($CCCSS_1 || $CCCSS_2 || $CCCSS_3)
is proper.
Any thoughts or input?
