DB search function

[sc00tz]

I have a very simple database set up where users submit a form with 1 drop-down menu (Values A, B, C, D, or E) and 20 checkboxes (values 1-20).  The checkboxes are set up as an array and then imploded.  Each entry in the DB has a userid, timestamp, drop down value, and any number of checked boxes.

 

Now I'm creating a new page where I want users to be able to search all the submitted forms for certain checkbox values (e.g. 1, 5, and 7).  But what I want to be displayed is not all the submissions for which 1, 5, and 7 were checked, but instead I just want a list of which drop-down values had a corresponding entry with all three of those boxes checked.  For instance if the only people who checked 1, 5, and 7 selected either B or D, when a user searches for 1, 5, and 7, I just want B and D to be displayed.

 

I know how to return individual submissions for which 1, 5, and 7 are checked, but I'm a little confused as to how I would just print the drop down values that apply (I also don't want to reprint them more than once).  Any help would be appreciated.  All my code is very basic as I'm pretty new to this.

[redarrow]

sounds well complected stuff and your new to this well, anyway please post the code...

[abazoskib]

you want to put the column names for B, C, D, etc in the SELECT part of the query and use the WHERE part of the query to search for checkmarks.

[sc00tz]

OK I have it almost all figured out, except when using the array, it is only returning the right A-E value if the search includes EXACTLY the same boxes that were checked.  If some checks boxes 1, 5, and 7 for value D, I want value D to appear as a result when a user checks just box 1 in the search, or 1&5, or 1&7 etc.

 

here is my code right now:

$result = mysql_query("SELECT A_through_E FROM table WHERE checkboxes LIKE '$checkboxsearch' ");

while($row = mysql_fetch_array($result))

  {

  echo $row['A_through_E'];

  }

 

Is there a command similar to "LIKE" that will display all the original forms that include the checkboxes that were searched for, even if they contain more than what was searched for?

 

does this make any sense at all?

[abazoskib]

yes sc00tz, you can use a wildcard search:

 

$result = mysql_query("SELECT A_through_E FROM table WHERE checkboxes LIKE '%$checkboxsearch%' ");
while($row = mysql_fetch_array($result))
  {
  echo $row['A_through_E'];
  }

[sc00tz]

Unfortunately, the wildcard search doesn't work because it only identifies the results of submissions where the numbers selected included selections in the same order as what was searched for (using wildcard).  For example, if someone checked 1, 5, 7 in there search, the wildcard search returns submissions where 1, 5, and 7 were checked, but not if 1, 5, 6, and 7 were checked.  If there is any value that was checked in between the values that were searched for, it does not return the search.  Is there a way to correct this?  Like code that would let me separate the checked values of the array in the search and then add a "WHERE checkboxes LIKE '%$XXX1%' AND '%$XXX2%' AND '%$XXX3%' " type function?

 

Thanks, I'd love some help!

[TeNDoLLA]

In the SQL clause use OR instead of AND. Using and searches the entries that has ALL the values that was checked. OR searches the entries that has any of the values checked.

[sc00tz]

I haven't used "OR" or "AND" though, that's my point.  I've set one array to be LIKE the wildcard of another array.  What would I have to do to pull the different array elements out in order to search for any combination of 1, 5, and 7, even if 2, 3, 4 or 6 were also checked in the matching results?

[sc00tz]

Crap I just realized another problem.  When there is more than one set of checkboxes under D that matches the search criteria, it prints "D" multiple times, once for each matching set.  I just want it to print all A-E's that match, but only once each, is there any easy way to do that?

[TeNDoLLA]

Could you maybe post the db structure and some test case data, and give a scenario what would you like to search and what would you like the results to be?

[sc00tz]

Okay I tried to do that in the first post but it probably didn't make sense.

-There is a form submission page where users pick A, B, C, D, or E from a pull down menu.  Then they check any number of boxes.  There are 20 boxes, labeled 1-20.

-Upon submission, that info goes to a DB table with just 2 rows.  A_through_E is one row, check_boxes is the other row.  A_through_E is just a single value, A-E, the check_boxes row is an imploded array of all the boxes that were checked.

-There is another page with a separate form.  This form just has the 20 checkboxes but no pulldown menu.  The function of this page is for users to search for the different checked values they are interested.  They check any number of boxes and hit submit.

-These checked values are submitted as an imploded array to a different DB with one row called search_check_boxes.

-The search results page should display all A_through_E values for which all the search_check_boxes elements are included in at least one check_boxes entry, but it should not repeat displaying the same A_through_E values.

 

For example, let's say the 1st DB has 6 submissions,  which are below:

B: 2, 4, 6, 10, 12

C: 6, 10, 12

C: 5, 6, 10, 12

D: 6, 8, 10, 15

B: 2, 8

E: 10, 15

 

Then let's say a user goes to the search function and checks the boxes for "6" and "10."  The results page SHOULD display "B," "C," and "D" (separated by table rows or something).  It shouldn't display E because I only want it to display A_through_E's that have all the selected values.  But there are TWO problems...

 

Problem 1: If I check 6 and 10 on the search page, it returns B and C as results, but not D, because there is an "8" in between the 6 and 10 for that imploded array. 

 

Problem 2: The results page will print "C" twice because there are 2 matching insert queries for that A_through_E value, but I only want it to print "C" once, no matter how many times the "C" value was submitted with matching checkboxes.

 

Does that make any sense?  If not I can try to come up with another example.  Thanks for the help!

[TeNDoLLA]

I get the point what you are trying to achieve but I would like to see the structure of your database table(s) so I can build the query for you. Do a query "SHOW CREATE TABLE table_name" in some SQL program and post the result here. Because from your example I am not sure if the letters are columns or rows, or if the letter is separate column and the numbers are in another column or how? And have you saved each check box value as a separate column or are they in database also in one field just comma separated?

[sc00tz]

This is the first DB table:

 

CREATE TABLE `tags` (

`tag_id` int(11) unsigned NOT NULL auto_increment,

`A_through_E` varchar(100) NOT NULL,

`check_boxes` varchar(600) NOT NULL,

PRIMARY KEY  (`tag_id`)

) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1

 

then the .php file that the search posts to just has this code:

$result = mysql_query("SELECT A_through_E FROM tags WHERE check_boxes LIKE '%$search_check_boxes%' ");

while($row = mysql_fetch_array($result))

  {

  echo $row['A_through_E'];

}

 

In this case, $search_check_boxes is just the imploded array posted to this page from the previous page, which includes the form and 20 check boxes for the search.  So in this case they are all just comma separated.

 

However, it just occurred to me that maybe this is just the wrong way to go about it.  Would it possible/easier to just store all the inputted search data in an entirely separate DB and then compare data from 2 distinct DBs for the purpose of generating the results page?

[sc00tz]

...any ideas?

[abazoskib]

sc00tz, why are you using a varchar column for all the values? why dont you split each checkbox into a seperate column? and then just use either a value of 0 or 1 to correspond to unchecked and checked respectively in a char(1) defined column(much faster).

[sc00tz]

I guess that would make it easier.  But what exactly would my query look like that?

 

What I'm saying is, how would I get a page to display every row name with a value of "1?"

[abazoskib]

do it through the coding language of your choice. you will be selecting all the columns that have anything to do with your checkboxes. then if you are using php for example, use a loop and only print out the values that are equal to 1.