I have a form that collects search filters as text input and stores them in an array. I want to write a SQL query to select all rows except those where a particular field is equal to one of the users filters. I found documentation for excluding things from a query, but Im not sure how to do this if the user enters a lot of filters. Would I use a loop to create multiple queries for each array item? Can someone be able to point me in the direction to achieve this?
Thanks!
If I understand well what you want, you will have to use the array to construct the appropriate criteria.
Let’s seach that your Array is (value1, value2, value3). And the way I’m understanding your question, that means that those values need to be excluded.
Your basic query would be “SELECT field1, field2… FROM table1”. You would keep this basic query in a string variable.
So you would need to create the WHERE part " WHERE field1<>" value1 “AND field2<>” value2 " AND field3<>" value3
Be aware that because I don’t know which programming you’re using, I haven’t included any concatenation operators. Also that there is a white space before the word WHERE, and that I am assuming that all those values need to be exluded at the same time.
You definitely understand what Im trying to do, the only thing is im not going to know how many filters the user might enter. So I cant just write WHERE field1<>“value1” etc.I would need some sort of way to loop through the array of filters the user enters and build one query to unclude all of the filters in the WHERE section
What language are you using? PHP?
And do all filters apply to the same column?
I’m using PHP and AJAX. I’m messing around trying to send an array using POST through ajax but the for each loop in php is giving me an invalid argument error when trying to pass it the array. And yes all of the filters will apply to the same cilumn. I have a dB of recipes and a table of ingredients with a foreign key constraint to match the ids for each ingredient to the id of the corresponding recipe. So I want a join statement that selects all recipes except those where one of its ingredients matches one of the filters the user entered
Please post the PHP code you have so far.
And do a var_dump($_POST) to see the actual content you’re sending to the PHP script.
Okay, I’ll get that up tonight. Thanks a lot guys
HTML
<div id="wrapper" class="section group">
<form>
<input type="text" name="filter" placeholder="Add Filters">
</form>
<button id="clear">Clear</button>
</div>
javaScript
$(document).ready(function() {
console.log( "Document ready!" );
$('form').submit(function(event) {
event.preventDefault();
var input = [];
var filter = $('input[name=filter]').val();
input.push(filter);
$('<span class="filters">'+filter+'</span>').appendTo(this).hide().fadeIn(500);
$('input').val('');
$.post('getRecipes.php', input, function(data) {
$('#wrapper').append(data);
});
});
$('button').click(function() {
$('#wrapper span').remove();
});
});
PHP
$filters = $_POST['input'];
foreach($filters as $value) {
echo $filters[$value];
}
Did you try the var_dump($_POST) ?
Could you post the result here?
Yes definitely. Im at work rn Ill be home at around 6 (EST) so stay posted and thanks again for the help!
This is what I get out of var_dump($_POST)
array(1) { ["undefined"]=> string(0) "" }
Here’s the live version:
http://www.darren-segal.com/recipeSchedule/index.php
It seems like your javascript doesn’t do what you want it to do? It never actually sends an array of filter values to the PHP script.
I’m going to move it to the JS forum for now. Once that problem has been solved, if you still have problems with the PHP part, we can return it here.
Hey so any word on this? Is there a link to the JS thread for it? Thanks a lot
This is the JS thread. I moved it 
Let’s hope someone will give you some help
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.