Construct mySQL query from dynamic select boxes

Question

I've already been able to construct a mySQL query using select boxes. Starting with ajax to PHP like so.

html/js (relevant parts)

var call = true;
  switch(parseInt($(this).val())) {
    case 1:
   cat_code ="ager";
        sortv = "database_percent";
        sorto = "asc";
    break;
    case 2:
   cat_code ="ager";
        sortv = "database_percent";
        sorto = "desc";
    break;
    default:
     cat_code ="ager";
        sortv = "value";
        sorto = "asc";

    break;
}
if(call) {
$.ajax({
        url: 'all_get_2.php',
        type: 'GET',
        dataType: 'JSON',
        data: {cat_code: cat_code, sortvalue: sortv ,sortorder:sorto},
        success: function(data) {
//stuff
}
})

PHP (relevant parts...just an example)

$whereCategory = isset($_GET['cat_code'])? "{$_GET['cat_code']}" : '';
$sortvalue = isset($_GET['sortvalue'])? "{$_GET['sortvalue']}" : '';
$sortorder = isset($_GET['sortorder'])? "{$_GET['sortorder']}" : '';
$sql = "select count(guid) from full_db2 where '{$whereCategory}' = '{$sortvalue}'' and example = '{$sortorder};";

$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
header('Content-type: application/json');
echo json_encode($result);

All's well. I mixed up some variables in this illustration, but that's not the issue. This returns the count for the attributes that the user selected from the selects. The selects are dynamically generated based on what the user selected in the previous select.

What if I wanted users to be able to select a bunch more attributes to add to the query. So, for example, instead of finding the count for the number of entries in the database (users) who's age is more than 40, they could add on more to this like users with age greater than 40 and married and have children.

I considered basically adding an option to add another set of select boxes (e.g., click a "+" and new set appears), but I quickly realized that I wouldn't be able to figure out how many selects were out there and a logical way to create a query.

What's the best possible way to allow users to select a (theoretically) unlimited number of attributes to construct in an single mySQL query?

Side note. Have you heard about [`SQL Injenction`](http://php.net/manual/en/security.database.sql-injection.php)? — PM 77-1, Jan 13 '15 at 20:51
@PM77-1 Unfortunately, yes, and was just reading about them. I'm completely new with PHP, so I still haven't quite figured out how to update (for example) the above code to prevent this. Any suggestions would be appreciated. — jonmrich, Jan 13 '15 at 20:52
You don't know how many are the elements... the first thing that comes to mind up is `array`. You can give every checkbox the same name, using `name="checkbox[]"`. That way you send a list with values to the server and then using php get the needed values. In php you can use `$data = $_GET['field_name'];` to get an array with the values. — Al.G., Jan 13 '15 at 20:56

score 0 · Answer 1 · answered Jan 13 '15 at 22:04

I would use the HTML <select> & <option> tags to present the various SQL conditional operators (AND, OR, >, <, =, !=) along with your field/table names, then concatenate them together to build your query.

As mentioned in the comments, you could utilize the array capabilities like name="checkbox[]" and submit it as an HTML form (POST or GET) to simplify concatenation.

You will want to escape and sanitize anything sent to the database to avoid SQL injection as HTML pages can be edited client side. Without sanitization your entire database will be accessible to a savvy user. This can be as simple as verifying that the array contents matches actual table names or one of the conditional operators and then discarding if it doesn't match. Escaping using mysqli::escape_string (http://php.net/manual/en/mysqli.real-escape-string.php) is also a good idea.

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

0

I ended up having to redo this question after I was able to figure out a few things. Here's how I progressed and check the accepted answer to see how it all is done together:

Create array for PDO from variables passed from jquery

edited May 23 '17 at 12:12

Community

1
1

answered Jan 21 '15 at 13:45

jonmrich

4,233
5
42
94

Construct mySQL query from dynamic select boxes

2 Answers2