homepage Welcome to WebmasterWorld Guest from 54.235.16.159
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP Query Language issue
stuck on this one...
mcjohnson




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

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?

 

omoutop




msg:4604960
 2:14 pm on Aug 26, 2013 (gmt 0)

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);

swa66




msg:4605283
 11:01 pm on Aug 27, 2013 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved