Multi Selects & Search

[radar]

Okay, I currently have a query that is like:

 

$sql = "SELECT SQL_CALC_FOUND_ROWS a.ItemID, a.spec, a.description, a.sport, a.category, a.price, a.unit_qty, a.is_visible, a.AdminID,
	b.name, b.ItemID as catID, c.Name as nameSport, c.ItemID as sportID
	FROM purchase_request_category_item as a
	LEFT JOIN purchase_request_category as b ON b.itemID = a.category
	LEFT JOIN ro_school_sports as c ON c.ItemID = a.sport
	WHERE a.AdminID = '".$dist."'
	GROUP BY a.ItemID, b.name, c.Name
	ORDER BY a.ItemID DESC LIMIT ".
	$start.",".$limit;

 

I am building a custom search function which I intend to be able to change the WHERE line to:

 

WHERE a.AdminID = '".$dist."' ".$query

 

 

$query will be created via if statements for every element in the form which will be called either by the button on the form, or the links in my pagination.

 

The form would have the following:

 

Level(s): dynamic field, Multi Select

Status: Both/Enabled/Disabled single select

Sports: dynamic field, Multi Select

 

Category: dynamic field, single select.

 

the sport column is a single selected item on the add page, its of type int(11)

 

the levels table, is also a multi select on the add item page, and is of the type set('1','2','3','4','5','12','13')

 

So real question is I guess, how on earth do I work with these 2 multi selects to create this search? 

[radar]

I've done a little more research on this, I found the IN and FIND_IN_SET functions...

 

I've read that you'd use IN when you have multiple options, but only 1 value in the database (which would be the sports column)

 

I've also read that you'd use FIND_IN_SET when you have a single option to be found in the database (levels column)

 

so I'm assuming I would take my array output by the sports select box into a comma delimited string and use IN with that..

 

But then for FIND_IN_SET would I need to create a foreach to create an OR FIND_IN_ISET($row['value'], 'a.levels') ?

[joel24]

i would have an sql statement similar to this, though i'm sure there are less convoluted ways. Haven't tested it, i'll leave that up to you

if (isset($_POST['multi_sport']))
{
$where = " WHERE";

if (is_array($_POST['multi_sport'])) //have to test this if condition
{
$count = count($_POST['multi_sport']);
$i = 0;
foreach($_POST['multi_sport'] AS $sport) 
{
//count
$i++;
//if more where conditions to come, add OR
if ($i <= $count) { $or = " OR "; } else { $or = ''; }
$where .= " SportField='$sport' $or";
}
}
}
else
{
$where = '';
}
$sql = "SELECT * FROM table1 t1 JOIN table2 t2 ON t1.field = t2.field $where";

 

** EDIT **

make sure that your multi select is posting an array of values across

 

an easier way...

a foreach put the array of multi select sports into a structure like so

$multiSports = '("sport1", "sport2", "sport3")';

$sql = "SELECT * FROM table WHERE sportsColumn IN $multiSports";

[radar]

I've already tested the output of the form, both are outputting in an array as thats the only way to obtain a multi select as naming it 'name' would overwrite the initial value so you have to name is 'name[]' instead.

 

so your edit (an easier way) answers the question of the IN portion...

 

of course i'll have to do it more like:

 

<?php
foreach ($_POST['sports'] as %s) {
if($sports == '') {
$sports = $s;
} else {
$sports .= ",".$t;
}

$sql = "SELECT SQL_CALC_FOUND_ROWS a.ItemID, a.spec, a.description, a.sport, a.category, a.price, a.unit_qty, a.is_visible, a.AdminID, 
b.name, b.ItemID as catID, c.Name as nameSport, c.ItemID as sportID		
FROM purchase_request_category_item as a		
LEFT JOIN purchase_request_category as b ON b.itemID = a.category		
LEFT JOIN ro_school_sports as c ON c.ItemID = a.sport		
WHERE a.sport IN '"$sports."' AND a.AdminID = '".$dist."'		
GROUP BY a.ItemID, b.name, c.Name		
ORDER BY a.ItemID DESC LIMIT ".		
$start.",".$limit;
					}
?>

 

So now I've got to figure out the FIND_IN_SET thing....

 

Say I select 4 of them..  Do I need to create 4 seperate statements such as

 

OR FIND_IN_SET('1', a.levels)

OR FIND_IN_SET('2', a.levels)

OR FIND_IN_SET('3', a.levels)

OR FIND_IN_SET('4', a.levels)

 

and then impliment that just before my AND statement in the query above...  and if this is the case, what does this do for duplicates found.  Say I have 2 rows in the database...  one has a level(type = SET) of 1 and 4(1,4 in the DB), the other has a level of 3 both have a sport(type = INT(11) of 59

 

in the search form say I select levels 1, 2, 5 and sports 48 50 and 59.  Now I'm lost as to what i'm asking.

 

Esentially, the query would need to be setup in a way that allows the user to search by any of the values given.  There wont always be levels selected, and there wont always be sports selected.

 

so if i had a query like

$sql = "SELECT SQL_CALC_FOUND_ROWS a.ItemID, a.spec, a.description, a.sport, a.category, a.price, a.unit_qty, a.is_visible, a.AdminID, 
b.name, b.ItemID as catID, c.Name as nameSport, c.ItemID as sportID		
FROM purchase_request_category_item as a		
LEFT JOIN purchase_request_category as b ON b.itemID = a.category		
LEFT JOIN ro_school_sports as c ON c.ItemID = a.sport		
WHERE a.sport IN '"$sports."' AND a.AdminID = '".$dist."'		
GROUP BY a.ItemID, b.name, c.Name		
ORDER BY a.ItemID DESC LIMIT ".		
$start.",".$limit;

 

I imagine that would work as wanted if they only selected sports no levels (which means all levels).

 

But lets say that did select levels..  where in the query would that go?  perhaps since i'm probably going to have to use OR statements for the levels (true?) I should put the levels directly after the WHERE clause put AND a.sports IN '48,50,59' after that, and then the AND AdminID = '".$dist."' at the end of the where clause....  wouldnt that be the most logical way to take care of it?  since in essence, we want to filter out the selected levels (if any), and then filter out the selected sports from the results of selected levels, all the while only making sure that the AdminID is the same as the ID of the admin who created them, which in this case would be a school district.

 

Doing this would create an extra if statement in the sports code however, as where I would have to check if $levels != '' { $sports = "AND "; }  then run my foreach on the $_POST['sports'] to turn it into a comma delimited order..

 

can anyone tell me if my thinking is on the right track here, and if not help it to get there

[radar]

Anyone know about the find_in_set?  Still looking for that information, being the first time i've used the set type in a database, I am at a loss as to how to search it correctly and efficiently.

 

Thanks in advance.

[joel24]

depending on how you're levels are structured, you can use the same principal (the IN condition).

you need to ensure you've got the IN conditions structured properly: WHERE field IN ('apple', 'pear', 'orange')

i.e.

//this should implode the $_POST['sports'] array into the proper structure; ('sport1', 'sport2', 'etc'). You'll need to check $_POST['sports'] is set with isset()
$sports = "('" . implode("',", $_POST['sports']) . "')"; 
$levels = "('" . implode("',", $_POST['levels']) . "')"; 

$sql = "SELECT SQL_CALC_FOUND_ROWS a.ItemID, a.spec, a.description, a.sport, a.category, a.price, a.unit_qty, a.is_visible, a.AdminID, 
b.name, b.ItemID as catID, c.Name as nameSport, c.ItemID as sportID
FROM purchase_request_category_item as a
LEFT JOIN purchase_request_category as b ON b.itemID = a.category
LEFT JOIN ro_school_sports as c ON c.ItemID = a.sport
WHERE a.sport IN $sports AND a.level IN $levels
AND a.AdminID = '".$dist."'
GROUP BY a.ItemID, b.name, c.Name
ORDER BY a.ItemID DESC LIMIT ".
$start.",".$limit;

it will then select a row where the sport is IN the sport conditions AND the level is in the level conditions; WHERE a.sport IN $sports AND a.level IN $levels

[radar]

I figured that levels would need to be FIND_IN_SET for the reason that is datatype of set.  It could be a single value like 13, or it could be multiples laid out like 1,3,13

 

While IN seems to be the easier option because it does allow for comma delimted options, if in the search form they select only option 13 but in the database ive got 6 that are only level 13, and 4 that have multiple levels selected (levels are actually school grades) using IN on the levels would theoretically only get the 6, since 7,13 isnt IN the selected string if my knowledge and understanding of both IN and FIND_IN_SET are correct of course.

[joel24]

I'm sure you could create an association table rather than having a 'set' datatype. I've never use find_in_set though from what you've said it should work, just ensure you're executing it correctly.

Have a look here and I'm sure you'll find something to guide you.

[radar]

Yeah If I had designed the database, I wouldn't have used set myself.  I actually asked the client why they decided to use SET and they stated thats just what they wanted to use.  So being that the client is correct at least 50% of the time I decided to fly with it and figure it out, even if nothing more than to say I've used it so I know how to do it if I run across it again.

 

I'm working on some code currently, just finished designing the form -- so now its time to do some ajax calls and see if I can get it to work they way it should.  I'll post back here once I have a resolution, just in case anyone else comes across a situation like this and needs a little guidance.

[radar]

Alright so it's been a little while, but i've put this all together and it works...  So as promised, here I am posting my resolution just in case others face this sometime too..

 

<?php
function getItems($dist, $start, $limit, $post="undead") {
	if($post != "undead") {

		// we have post data, lets filter it somehow.
		if ($_POST['levels'] != '') {
			// levels were selected, lets filter them.
			foreach ($_POST['levels'] as $t){ 
				if ($levels == '') {
					$levels = ' FIND_IN_SET('.$t.', a.levels)';

				} else {
					$levels .= ' OR FIND_IN_SET('.$t.', a.levels)';
				}
			}
		}
		if ($post['sport'] != '') {
			// sports were selected, lets filter them.
			foreach($_POST['sport'] as $t) {
				if ($sports == '') {
					$sports = "'".$t."'";
				} else {
					$sports .= ",'".$t."'";
				}
			}
			if ($levels == '') {
				$sport = " a.sport IN (".$sports.")";
			} else {
				$sport = " AND a.sport IN (".$sports.")";
			}
		}

		if ($post['status'] != '') {
			// status was selected.

			if ($post['status'] != "2") {
				if($sport == '' && $levels == '') {
					$status = " a.is_visible = ".$post['status'];
				} else {
					$status = " AND a.is_visible = ".$post['status'];
				}
			}
		}

		if ($post['category'] != '') {
			if($sport == '' && $levels == '' && $status == '') {
				$category = " a.category = ".$post['category']." AND ";
			} else {
				$category = " AND a.category = ".$post['category'];
			}
		}

		if ($levels != '' || $sport != '' || $status != '' || $category != '') {
			$and = " AND";
		}
	}
	$sql = "SELECT SQL_CALC_FOUND_ROWS a.ItemID, a.spec, a.description, a.sport, a.category, a.price, a.unit_qty, a.is_visible, a.AdminID,
	b.name, b.ItemID as catID, c.Name as nameSport, c.ItemID as sportID
	FROM purchase_request_category_item as a
	LEFT JOIN purchase_request_category as b ON b.itemID = a.category
	LEFT JOIN ro_school_sports as c ON c.ItemID = a.sport
	WHERE".$levels.$sport.$status.$category.$and." a.AdminID = '".$dist."'
	GROUP BY a.ItemID, b.name, c.Name
	ORDER BY a.ItemID DESC LIMIT ".$start.",".$limit;
$data = mysql_query($sql);
$cnt = mysql_num_rows($data);
$_query = "SELECT FOUND_ROWS() as total";
        $_result = mysql_query($_query);
      	$_row = mysql_fetch_array($_result, MYSQL_ASSOC);
        
        Paginate::setTotal($_row['total']);	
	if ($cnt == '' || $cnt == '0') {
		$data = '<table width="90%" border="0" cellspacing="0"
		cellpadding="0" align="center"><tr><td>There are 0 
		Items listed for your district.   Please add some by clicking on \'add new\' up top.</td></tr></table>';
	} else {
		$data = sql_md_array($data, $cnt);	
	}
	return $data;
}
?>

 

I tried using the implode at the beginning, and it just didn't work right so I went through the sports using a foreach formatting them in the correct way.  Only issue I had was that in my main php file, I sent $_POST over which is called in this file by $post -- but on the levels, it would never read it, so I had to use $_POST...

 

So with the set, formatting a multiple select into multiple FIND_IN_SET works...  Since this is a dynamic query, I had to add in the lines to check if any of the items were not empty ($levels, $sports, $status, $category) and if they are empty make $and = '' otherwise $and = " AND"; this way my last where clause adminID works instead of throwing an error...  it's sloppy i'm sure, but hey it works!  I'll figure out a better way to clean it up later.