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, Apr 30 '14 at 14:21
`order by field(status, 'Submitted', 'Rejected', 'Cancelled', 'Accepted')`? — Funk Forty Niner, Apr 30 '14 at 14:22

score 2 · Answer 1 · edited May 23 '17 at 12:05

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 '17 at 12:05

Community

1
1

answered Apr 30 '14 at 14:25

user20232359723568423357842364

4,424
1
14
27

Curious. How does this handle the `where status = 'Submitted' or status = 'Accepted'`? More specifically, the `or` clause. Does your `IN` clause do that? – Funk Forty Niner Apr 30 '14 at 14:29
@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. – Patrick Q Apr 30 '14 at 14:33
@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'`"* – Funk Forty Niner Apr 30 '14 at 14:34
@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". – Pinu Apr 30 '14 at 14:38
@Pinu Thanks for clarifying that for me. I like understand how things work, cheers. – Funk Forty Niner Apr 30 '14 at 14:39

score 0 · Answer 2 · answered Apr 30 '14 at 14:34

$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.

run SQL query in PHP based on checked boxes

2 Answers2