PHP-Mysql WHERE is empty AND returns results

Question

I am not well versed in MYSQL, and I need some guidance here. I have read multiple MYSQL WHERE statement explanations, but have not found this exact issue. I have a table called studies that has fields like id, name, author, category, format and a few others.

I am building a filter with checkboxes to narrow down the query. I want to get all studies with a specific author AND category AND format. My problem comes when I build the query. If only the format is selected, the query returns empty because no author was selected. How do I get the results from the AND statements without the WHERE statement? Here is what I have so far...

"SELECT * FROM estudos WHERE subcategory='$subcategory' AND author='$author' AND format='$format'"

Mansfield · Accepted Answer · 2013-11-06 14:43:12Z

1

If author is empty, don't include it in the query:

SELECT * FROM estudos WHERE format='$format'

Alternatively, use OR instead of AND:

SELECT * FROM estudos WHERE subcategory='$subcategory' OR author='$author' OR format='$format'

edited Nov 6, 2013 at 14:43

answered Nov 6, 2013 at 14:42

Mansfield

15.3k20 gold badges79 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jeremy Tyler Over a year ago

So you are saying that I should build multiple queries in php with if statements, right? The problem with OR is that it will return broader results. I want more specific results.

Mansfield Over a year ago

@SableFoste That depends on what behavior he wants when both author and format are specified in the search.

Mansfield Over a year ago

@JeremyTyler Yes. You can simply check for each field being empty and append it to the query if it isn't, or do something like the other answer: stackoverflow.com/a/19815053/1324019

Jeremy Tyler Over a year ago

@Mansfield Thank you. All these answers are helpful. Now to the test to see which works best.

Mansfield Over a year ago

@JeremyTyler You're welcome. I'd recommend the answer I previously linked, it's the most flexible.

Explosion Pills · Accepted Answer · 2013-11-06 14:45:20Z

1

You need to conditionally append the conditions based on whether the item was selected or not. I suggest something like:

$where = array();
$params = array();
if (isset($format)) {
    $where[] = "format = ?";
    $params[] = $format;
}
/* other parameters */
$query = "SELECT * FROM estudos WHERE "
    . implode(' AND ', $where);

Note that the $params part is only necessary if you are using a parameterized query like with PDO (which you should be doing).

answered Nov 6, 2013 at 14:45

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Comments

Zahidul Hossein Ripon · Accepted Answer · 2013-11-06 14:48:16Z

0

if only format is selected you should only query based on format like

SELECT * FROM estudos WHERE format='$format'

if format and author is selected then

SELECT * FROM estudos WHERE author='$author' AND format='$format'

if all are selected then

SELECT * FROM estudos WHERE subcategory='$subcategory' AND author='$author' AND format='$format'

You should run your query depending on the selection

answered Nov 6, 2013 at 14:48

Zahidul Hossein Ripon

6726 silver badges17 bronze badges

Comments

rakimo · Accepted Answer · 2013-11-06 14:46:48Z

0

assign where condition to variable

for example

if($categoryCheckbox){
 $whare.="AND category='$category'";
}

and append it to your query

answered Nov 6, 2013 at 14:46

rakimo

896 bronze badges

Comments

mikaint · Accepted Answer · 2013-11-06 14:46:58Z

0

You could use he following code to generate the query

$q = "SELECT * FROM estudos WHERE format='$format'";
if ($author != '') { $q.= "AND author='$author'"; }
if ($subcategory != '') { $q.= "AND subcategory='$subcategory'"; }

answered Nov 6, 2013 at 14:46

mikaint

3531 gold badge3 silver badges12 bronze badges

Comments

jbakker · Accepted Answer · 2013-11-06 14:50:28Z

0

Well what i would do is this:

$query = sprintf("SELECT * FROM estudos");
if ($subcategory != "" || $author != "" || $format != "") {
    $query .= " WHERE ";
    $i = 0;

    if ($subcategory != "") {
        if ($i > 0) $query .= " AND ";
        $query .= "subcategory='" . $subcategory . "'";
    }
    if ($author != "") {
        if ($i > 0) $query .= " AND ";
        $query .= "author='" . $author . "'";
    }
    if ($format != "") {
        if ($i > 0) $query .= " AND ";
        $query .= "format='" . $format . "'";
    }
}

I hope that works.

answered Nov 6, 2013 at 14:50

jbakker

4571 gold badge4 silver badges9 bronze badges

Comments

Dip · Accepted Answer · 2013-11-06 15:36:51Z

0

I will suggest you to rewrite the query by using concatenation. Like:

$query= 'SELECT * FROM estudos WHERE 1';

if(isset($subcategory)) $query .= ' AND subcategory='.$subcategory;

if(isset($author)) $query .= ' AND author='.$author;

if(isset($format)) $query .= ' AND format='.$format;

answered Nov 6, 2013 at 15:36

Dip

7067 silver badges9 bronze badges

Collectives™ on Stack Overflow

PHP-Mysql WHERE is empty AND returns results

7 Answers 7

5 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

5 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related