I need help with simple data filtering code. I want to display data from DB but I have no idea how to do this when one of more inputs are empty so data will be display by the other user inputs, checkboxes list or whatever.
Usually you check if a form field is submitted and has a valid value and if so you add a condition to the query.
This post needs some code though
I don’t have a code yet. The bgger problem is that I don’t know how to use query on this example.
[php]
$sql="SELECT * FROM table WHERE first_row=‘user_input’ AND second_row=‘second_user_input’;
[/php]
but when one of this inputs are empty I’m getting nothing. I know it should be something like OR and And ( if that make any sense)
I just found exampe online like this :
[php] var sql = ‘SELECT * FROM table’;
var where = ’ WHERE’;
//category is present in userinput
if(category){
sql += where + ' category=category';
where = ' and';
}
//subject is present in userinput
if(subject){
sql += where + ' subject=subject';
where = ' and';
}
if(material){
sql += where + ' material=material';
where = ' and';
}
if(medium){
sql += where + ' medium=medium';
}
db.query(sql,function(error,result){
console.log(result);
});[/php]
do you think this is a good way to do so ?
You want an OR not an AND. So,
select column from table where this OR that
ok so what will happen if this and that is selected? lets say I have 4 inputs.
It seems noncorrect when you say it, but that is how you would go about it when questioning the database. If you have a data model, like a dump from the table, i could do a fiddle. Otherwise, test it out. And don’t do the dynamic SQL, it isn’t needed for what you are doing currently.
What kind of db class are you using?
The most efficient way of doing this is to dynamically build the sql query with just the WHERE terms that you are searching for.
If you use OR or AND depends on what you are trying to find, i.e. if any terms should match or if all terms should match.
To do this in php (the example you found is not php) and using a prepared query, you will build two arrays, by testing each search input. The first array gets the sql syntax for each term you want to add to the sql query and the second array gets the value for each term for when you execute the query.
After you add the elements to the arrays, you implode the first one with either the OR or AND keyword, to produce the sql syntax to put into the WHERE part of the query. This will work for one or more terms. If there are zero terms and nothing else for the WHERE clause, you would leave the entire WHERE clause out of the query. The second array becomes the data values for when you execute the query. I see in a previous thread that you are using the php PDO extension. You would just use the second array as the call time parameter to the ->execute(…) method.
Thank you guys so much esspecially phdr, now I know how to do this. Cheers guys