Jump to content

lb3000

Members
  • Posts

    15
  • Joined

  • Last visited

lb3000's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Ok - I think that modification is working pretty good. I have this set up in SQL Fiddle -- http://sqlfiddle.com/#!2/c5e65/1 -- and set up the query to look like this: SELECT research.research_id AS resource_id, research.title, COUNT(DISTINCT issue_area_id)=2, COUNT(DISTINCT doctype_id)=2 FROM research JOIN link_issue_area ON link_issue_area.resource_id = research.research_id JOIN link_doctype ON link_doctype.resource_id = research.research_id WHERE issue_area_id IN ('aging','health') AND doctype_id IN ('case_study','evaluation') GROUP BY resource_id I changed some things and ran it and got the results I was looking for. I will play with this more today and let you know what happens. Thanks mac_gyver for all of your help with this. Much appreciated!
  2. Oh - got it. I'm sorry I wasn't clear about "any" and "all". If they pick "any" and then check 2 categories out of 10 available categories, then what they are saying is give me either cat1 or cat2 or both cat1 and cat2. So our query looks like: AND (issue_area.identifier = 'aging' OR issue_area.identifier ='health'). If they pick "all" and then check 2 categories out of 10 available categories, then what they are saying is give me "all" that I have checked -- not all of 10 categories. So, they want only records that include cat1 AND cat2. If they don't check anything in a category box, then we leave out any reference to that category -- which is what you are saying. If they don't check anything, then that category doesn't matter to them -- give them everything.
  3. Thanks mac_gyver, and I apologize - I'm not sure what you mean when you write "your 'all' queries should leave the entire term out of the WHERE clause (to produce the fastest query execution.)". Are you suggesting that we continue to use the original query, which works for "any" queries, but modify it so that, when someone searches for "all" terms, there is no WHERE clause? Original query modified to not include terms in WHERE clause: SELECT DISTINCT * FROM research JOIN link_resource_doctype ON link_resource_doctype.resource_id = research.research_id JOIN doctype ON doctype.id = link_resource_doctype.doctype_id JOIN link_resource_issue_area ON link_resource_issue_area.resource_id = research.research_id JOIN issue_area ON issue_area.id = link_resource_issue_area.issue_area_id WHERE approved = '1' I don't understand how this will provide a result set based on what someone searched for? Maybe you are suggesting some combination of the original query and the last query I posted which does seem to work, except for the limitation where it provides records that are categorized under only 2 categories -- and so skips any records that include the 2 categories requested but also may be categorized under additional categories. Thanks for your help.
  4. I have an advanced search form that offers a bunch of ways to filter your search. Here's a simplified idea (doesn't include keyword text input or date range searches or other select menus): Topic: <select><option>any</option><option>all</option></select> [] Aging [] Environment [] Health [] Hunger [] Poverty Document type: <select><option>any</option><option>all</option></select> [] Case Study [] Policy Brief [] Whitepaper If someone selects "any" when they choose more than one topic or document type, the query needs to include, eg., topic = "Aging" OR topic = "Health". If someone selects "all" when they choose more than one topic or document type, the query needs to include, eg., topic = "Aging" AND topic = "Health". We default to "AND" between these different filters. So when you search for all documents categorized under Aging and all documents categorized as a whitepaper, the query is: topic = "Aging" AND doctype = "whitepaper". The Problem: We have a query that is working when the search is for "any". But when the search is for "all", according to MySQL's "EXPLAIN" command, we have an "impossible WHERE". Here is the query that works when someone selects "any" for both topic and document type: SELECT DISTINCT * FROM research JOIN link_resource_doctype ON link_resource_doctype.resource_id = research.research_id JOIN doctype ON doctype.id = link_resource_doctype.doctype_id JOIN link_resource_issue_area ON link_resource_issue_area.resource_id = research.research_id JOIN issue_area ON issue_area.id = link_resource_issue_area.issue_area_id WHERE approved = '1' AND (doctype.identifier = 'case_study' OR doctype.identifier = 'whitepaper') AND (issue_area.identifier = 'aging' OR issue_area.identifier = 'health') And here's the same query which does not work when someone selects "all" for both topic and document type (this also doesn't work if someone selects just topic or just document type): SELECT DISTINCT * FROM research JOIN link_resource_doctype ON link_resource_doctype.resource_id = research.research_id JOIN doctype ON doctype.id = link_resource_doctype.doctype_id JOIN link_resource_issue_area ON link_resource_issue_area.resource_id = research.research_id JOIN issue_area ON issue_area.id = link_resource_issue_area.issue_area_id WHERE approved = '1' AND (doctype.identifier = 'case_study' AND doctype.identifier = 'whitepaper') AND (issue_area.identifier = 'aging' AND issue_area.identifier = 'health') Possible solution but there's a problem: I came across this post on Stackoverflow -- Select row belonging to multiple categories -- which contains a query that I think might solve our problem when someone selects "all". Here it is: SELECT DISTINCT * FROM research JOIN link_issue_area ON link_issue_area.resource_id = research.research_id JOIN link_doctype ON link_doctype.resource_id = research.research_id WHERE issue_area.identifier IN ('aging', 'health') AND doctype_id.identifier IN ('case_study', 'whitepaper') GROUP BY research.research_id HAVING COUNT(DISTINCT issue_area.identifier) = 2 AND COUNT(DISTINCT doctype.identifier) = 2 The Problem> This query seems to work for either "any" or "all", except for one problem: Say that a document is categorized under Aging and Health and Poverty but the person searching only checked off Aging and Health. The document that is categorized under Aging and Health and Poverty will not appear in the search result list. I think this is because of the HAVING COUNT (DISTINCT issue_area.identifier) = 2 -- the 2 excludes any document that has a COUNT that is not exactly 2. Is there a work-around for this? Or a better query to use here? Any insight, ideas, assistance much appreciated! Thanks! Here's an SQLfiddle that gets at all of this too: http://sqlfiddle.com/#!2/847362/1
  5. Sorry - I rushed through setting up your code within what exists in my script. I screwed up the implementation. Your code works great! Thanks so much for taking the time to respond, and then to respond again. Really appreciate your help and expertise!
  6. Hi Barand, and thanks for your code. I tried it and I get no output at all. I think this is because $currOrg never gets a chance to get set: $currOrg = ''; // set to nothing here foreach ($arr as $data) { if ($currOrg != $data['org']) { // $currOrg isn't set to $data['org'] since it is still set to nothing if ($currOrg) { // currOrg is still set to nothing, so this if never executes outputEmail ($currOrg, $currEmail, $titles, $ids); } I tried switching this around to start with if ($currOrg)..., but since $currOrg always starts out as being set to nothing, again the if statement never executes. Thanks again for your go at this. Any other ideas are welcome!
  7. I have an array with data that looks like this: 01 | organization_1 | email_A | publication_A1 | pubID_A1 02 | organization_1 | email_A | publication_A2 | pubID_A2 03 | organization_1 | email_A | publication_A3 | pubID_A3 04 | organization_2 | email_B | publication_B1 | pubID_B1 05 | organization_2 | email_B | publication_B2 | pubID_B2 06 | organization_3 | email_C | publication_C1 | pubID_C1 07 | organization_4 | email_D | publication_D1 | pubID_D1 08 | organization_4 | email_D | publication_D2 | pubID_D2 The array is structured like this: array('org' => organization_x, 'email' => email_x, 'title' => publication_x, 'pub_id' => pubID_x) What I need to do is output this array so that it displays as individual email messages with a submit button after each. A person viewing this page would review the displayed message and he or she would have the option to click submit to send a particular message to the appropriate email. Here's the initial display I need to create: <!-- Message #1 --> <form> <input type="hidden" name="pub_ids" value="pubID_A1;pubID_A2;pubID_A3"> <input type="hidden" name="recipient" value="email_A"> <input type="hidden" name="organization" value="organization_1"> <!-- start on-screen text display --> To: email_A Your account contains the following publication titles: * publication_A1 * publication_A2 * publication_A3 You can edit these publications after logging into our system. Your email address, email_A, serves as your username. Thanks! <!-- end on-screen text display --> <input type="submit" name="submit" value="submit"> </form> <!-- Message #2 --> <form> <input type="hidden" name="pub_ids" value="pubID_B1;pubID_B2"> <input type="hidden" name="recipient" value="email_B"> <input type="hidden" name="organization" value="organization_2"> <!-- start on-screen text display --> To: email_B Your account contains the following publications: * publication_B1 * publication_B2 You can edit these publications after logging into our system. Your email address, email_B, serves as your username. Thanks! <!-- end on-screen text display --> <input type="submit" name="submit" value="submit"> </form> ...and on until all of the items in the array have been processed. I've tried some version of the following code for too long now. The idea is to loop through each item, concatenating the pub_id and title data into text strings to be passed through the form or shown on screen as needed. When the script hits a new organization name, it should put all the pieces together and then get started on the next organizaton's email message. The Code: $current_org = ''; $pub_ids = ''; $titles = ''; foreach ($array AS $value) { $pub_ids .= $value['pub_id'] . ';'; $titles .= '* ' . $value['title'] . '<br />'; if ($current_org != $value['org']) { echo '<form>'; echo '<input type="hidden" name="pub_ids" value="' . $pub_ids . '">'; echo '<input type="hidden" name="recipient" value="' . $value['email'] . '">'; echo '<input type="hidden" name="organization" value="' . $value['org'] . '">'; echo 'To: ' . $value['email']; echo '<br /><br />'; echo 'Your account contains the following publication titles:'; echo '<blockquote>'; echo $titles; echo '</blockquote>'; echo 'You can edit these publications after logging into our system. Your email address, ' . $value['email'] . ', serves as your username.'; echo '<br /><br />'; echo 'Thanks!'; echo '<input type="submit" name="submit" value="submit">'; echo '</form>'; echo '<br /><br />'; $pub_ids = ''; $titles = ''; $current_org = $value['org']; } } The Problem: The problem seems to be the first organization with more than one publication only gets one publication listed out. Then the next organization gets the last org's publication listed as well as their own. Then the rest are thrown off. Thinking this has something to do with my "if ($current_org != $value['org'])". Since $current_org starts off as NULL the first loop will never be set to $value['org'], so I get one loop through that gives me a pub_id, a title, and an email message -- then the old_org "changes" from NULL to the first $value['org'] value and the loop starts over again. I think this is what is going on. I can't figure out how to fix this though. Where/how would I check to see if the org value has changed? Any insight into this problem is much appreciated!
  8. I am trying to format form text input for use in a mysql fulltext boolean mode search. If someone wants to match "all" words in the input box, I need to put a "+" sign in front of every word. If they choose to match "any" words, we can ignore this step. If they enter a phrase -- indicated by wrapping the phrase in quotes (eg., "search me") I need to include that quoted string as is. Easier to see with an example: Input: one two "test test" Assuming someone has asked to get a result matching "all" words/phrases, I should format this input like so: +one +two "test test". Here's my code to do this formatting. It works great up until, of course, the last step. I end up with this output: +one +two +"test +test". The + inside of the quoted phrase -- "test +test" -- is the problem. It seems preg_replace() is just skipping over doing the replacing I need. Anyone see anything weird here? $keywords = 'one two "test test"'; // Checking that the $keywords string doesn't start with a quotation mark. If not - this is a word, add a + if (substr($keywords, 0,1) != '"') { $keywords = '+' . $keywords; } // Rewriting $keywords so that every space in the string now includes a + -- will use this in a sec to replace the quoted phrase with "+" with the original quoted phrase - no + $prep_string = str_replace(' ', ' +', $keywords); // now $keywords = +one +two +"test +test" // Finding any quoted phrases in the original $keywords string here $pattern = "/\"[^\"]*\"/"; preg_match_all($pattern, $keywords, $matches); // the match = "test test" if (!empty($matches[0])) // $matches[0] is the returned array that holds any matches found in the original $keywords string { $prepped = array(); foreach ($matches[0] AS $value) { // Looping through and preparing each value (here the value is just "test test") to include a + // This is so that I have a way to match values in the $prep_string string $prepped[] = '/' . str_replace(' ', ' +', $value) . '/'; // this outputs '/"test +test"/' } $new_string = preg_replace($prepped, $matches[0], $prep_string); $new_string = str_replace('+"', ' "', $new_string); // The rest here is just to dump all of this to screen, you'll see that preg_replace didn't replace "test +test" found in $prepped with "test test" found in $matches[0] 'original matches: ' . var_dump($matches[0]); echo '<br />'; 'prepped matches: ' . var_dump($prepped); echo '<br />'; echo 'preg_replace output: ' . $new_string; // this is } If you var_dump matches[0] and $prepped, and then echo $new_string, here's what you see: original matches: array(1) { [0]=> string(11) ""test test"" } prepped matches: array(1) { [0]=> string(14) "/"test +test"/" } preg_replace output: +one +two +"test +test" ====== this should be +one +two "test test"
  9. Jessica - thank you so much for all of your insight, and challenges. Seriously. I work over here all day long (and sometimes nights and weekends) alone, making this stuff up as I go along. I really appreciate that you took this on and kept coming back at me. Makes me want to work in an office or something, with colleagues who know what they're doing! Thanks again! And glad to know that my entire query isn't a fraud.
  10. Hmmmm but it does return something. It returns a title that has a row in the link_audience table that matches the id/type in question. Audience table id | type ======= 1 | advocates 2 | legislators 3 | researchers Link table pub_id | audience_id ================ 1 | 1 1 | 2 2 | 1 2 | 3 So if someone requests a title that has been indexed 1 and 2 from the audience table, then they get back title id 1. If they ask for something indexed 1 or 2, they would get back title id 1 and 2. I'm no guru! I could be wrong. But when i run a query I do get back what I expect. Which brings me back to my original post re: expectations.
  11. Oh - sorry - right. A little background on this project. We collect publications from all over the world. When someone adds a publication they can index it in a number of ways. Substitute "audience" for "something". So someone might index their publication as serving "advocates", "policy professionals", "legislators". When someone else uses our search form -- which is what all of the above posts refer to -- they can choose to get a result set that focuses on publications for audience type: advocates AND legislators. Or they might want to be less restrictive and choose to get back a match on either advocates OR legislators. Here's a more accurate query, based on a "match ANY" request and then a "match ALL" request complete with joins: SELECT DISTINCT publications.title, publications.date, publications.description FROM publications JOIN link_audience ON link_audience.pub_id = publications.pub_id JOIN audience ON audience.id = link_audience.audience.id JOIN link_doctype ON link_doctype.pub_id = publications.pub_id JOIN doctype ON doctype.id = link_doctype.doctype.id WHERE (audience.type = 'advocates' OR audience.type = 'legislators) AND (doctype.type = 'casestudy' AND doctype.type = 'whitepaper') ORDER BY publications.title
  12. Yes - I am going to use your query: SELECT DISTINCT * FROM table WHERE (something = 'this' OR something = 'that' OR something = 'the other') AND (thingamajig = 'one' OR thingamajig = 'two') Here's the thing - I would LOVE to use IN. But we do need to capture peoples preference that their search return a match on ANY versus a match on ALL. If they choose ANY, I need to deploy a query like (something = 'this' OR something = 'that' OR something = 'other'). If they choose ALL, I need to deploy a query like (something = 'this' AND something = 'that' AND something = 'other'). If I use IN, it would be as though I am running an "ANY" query all the time. Unless I'm thinking about that wrong.
  13. Ok - that makes sense. I'll drop that idea and go back to the way I've always written a query like this, with everything spelled out: SELECT DISTINCT * FROM table WHERE something = 'this OR something = 'that' OR something = 'other' AND thingamajig = 'one' OR thingamajig = 'two' I just had a quick look around for operator precedence and see what you are getting at there. For my purposes the query written like the one above does return what I need - I'm confident of that. This query works with data that comes from a form with a mix of checkbox data (which provides this/that/other data in an array) and select pull-down menu data (just returns one value). Our search form doesn't let people do much more than check off or select the data they want returned. They don't have any preferences to indicate except for "any/all" on the checkbox data. (If they choose "any" we run the query with OR in between each value; choose "and" - we run query with AND in between each value). Thanks again for replying, and for providing clarification. Much appreciated.
  14. Hi Jessica - thanks for your quick reply. I just noticed I have a missing apostrophe in my example - should be: SELECT DISTINCT * FROM table WHERE something = ('this' OR 'that' OR 'other') AND thingamajig = ('one' OR 'two') I have tried running this query and it does return what I would expect. No errors are returned. But like I said, I am just running the one query against a small set of data. I want to ensure that I'm not getting what I expect because I expect it - if that makes any sense. If it's not too much trouble, can you explain a little more, or point me to more info, about what you suggest re "use the parens to organize your precedence". Thanks a bunch!
  15. I am using MySQL v5.5.27. I usually would write this SELECT statement like this: SELECT DISTINCT * FROM table WHERE something = 'this OR something = 'that' OR something = 'other' AND thingamajig = 'one' OR thingamajig = 'two' But would this SELECT statement provide the exact same result? SELECT DISTINCT * FROM table WHERE something = ('this OR 'that' OR 'other') AND thingamajig = ('one' OR 'two') I have tried running this and it *seems* to be working. Just want to make sure this second way of doing things won't return errant data in some way that I can't think of. Thanks for any insight, assistance!
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.