run SQL query in PHP based on checked boxes

Question

I am running this SQL Query in PHP:

$stmt = $pdo_conn->prepare("SELECT * from porting order by field(status, 'Submitted', 'Rejected', 'Cancelled', 'Accepted') ");
$stmt->execute(array());
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);

But i want to be able to add checkboxes to modify the query without refreshing the page

for example,

<input type="checkbox" name="status" value="Submitted" />
<input type="checkbox" name="status" value="Rejected" />
<input type="checkbox" name="status" value="Cancelled" />
<input type="checkbox" name="status" value="Accepted" />

so if the input with a value of 'Submitted' is checked the query will change to:

SELECT * from porting where status = 'Submitted'

and if both inputs with values 'Submitted' and 'Accepted' are checked, the query will be:

SELECT * from porting where status = 'Submitted' or status = 'Accepted'

You've told us what you want. Now tell us what you've tried. — Patrick Q
– Patrick Q, Commented Apr 30, 2014 at 14:21
order by field(status, 'Submitted', 'Rejected', 'Cancelled', 'Accepted')? — Funk Forty Niner
– Funk Forty Niner, Commented Apr 30, 2014 at 14:22

Community · Accepted Answer · 2017-05-23 12:05:10Z

2

Your checkboxes should use array syntax

<input type="checkbox" name="status[]" value="Submitted" />
<input type="checkbox" name="status[]" value="Rejected" />
<input type="checkbox" name="status[]" value="Cancelled" />
<input type="checkbox" name="status[]" value="Accepted" />

From there, you implode.

$sql = "SELECT * from porting where status IN('".implode("', '", $_POST['status'])."')";

WARNING: The SQL query above IS VULNERABLE TO SQL INJECTION, but it should get you on the right track.

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Apr 30, 2014 at 14:25

user20232359723568423357842364

4,6341 gold badge20 silver badges28 bronze badges

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

5 Comments

Funk Forty Niner Over a year ago

Curious. How does this handle the where status = 'Submitted' or status = 'Accepted'? More specifically, the or clause. Does your IN clause do that?

Patrick Q Over a year ago

@Fred-ii- I believe that this answer is taking the liberty of assuming that the use of OR is not strictly required and therefore IN may be a suitable replacement.

Funk Forty Niner Over a year ago

@PatrickQ Was just stating what the OP asks in the question "and if both inputs with values 'Submitted' and 'Accepted' are checked, the query will be: SELECT * from porting where status = 'Submitted' or status = 'Accepted'"

Pinu Over a year ago

@Fred-ii Yes "IN" is best choice here as it make your answer dynamic rather than specific. And also "IN" does same think as you think need in "OR".

Funk Forty Niner Over a year ago

@Pinu Thanks for clarifying that for me. I like understand how things work, cheers.

Al.G. · Accepted Answer · 2014-04-30 14:34:04Z

0

$checkboxes = $_POST['status'];
if(count($checkboxes)) {
    $where_clause = ' where';
}
else {
    $where_clause = '';
}
foreach($checkboxes as $el) { $el = addslashes($el);
   $where_clause .= " status = '$el' or";
}
$where_clause = substr($where_clause, 0, -2);

$query = 'select * from porting' . $where_clause;

Finally, $query will contain a string like this:

select * from porting where status = 'Submitted' or status = 'Cancelled'

And you should use name="status[]" instead of name="status" if you want to send more than one values.

answered Apr 30, 2014 at 14:34

Al.G.

4,4476 gold badges34 silver badges63 bronze badges

Collectives™ on Stack Overflow

run SQL query in PHP based on checked boxes

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related