Welcome to WebmasterWorld Guest from 23.20.241.155

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

PHP Query Language issue

stuck on this one...

     
1:36 am on Aug 25, 2013 (gmt 0)

10+ Year Member



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?
2:14 pm on Aug 26, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



haven't done this before, but perhaps a FULLTEXT search might be what you are looking for.

Something like this might:
SELECT fields FROM table WHERE MATCH (fullTextField) AGAINST ('+criteria1 +criteria2 -criteria3' IN BOOLEAN MODE);
11:01 pm on Aug 27, 2013 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



I think a data model that packs properties of a product into a single field like described is seriously flawed.

I'd suggest to
- make full use the relational aspect of your database.
- normalize your data model.

Once you do that, these queries are piece of cake.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month