CODEWITHSUNDEEP
php
Satch3000
Satch3000

Reputation: 49384

PHP Select all variables which are equal to 1

I am trying to get an SQL-query to be dynamic.

These are the steps I'm taking:

Step 1:

//Here the user sets which table fields will be used:
<input type="checkbox" name="id" value="1"> ID
<input type="checkbox" name="name" value="1"> name
<input type="checkbox" name="email" value="1"> email

Step 2:

//Here I see what fields the user wants to use and set a variable to 1
<?php 
if (isset($_POST['id']) && $_POST['id'] == "1")
{
    $form_id='1';
}
//etc etc
?>

Step 3:

//And finally here's the sql query
$sql = mysql_query("select * from mytable");

Now I need to set the fields that the user selected to the query.

For example if the user checks only id and name, then it should look like this:

$sql = mysql_query("select id, name from $table");

What's the best way of doing this?

Upvotes: 1

Views: 108

Answers (3)

Jonast92
Jonast92

Reputation: 4967

The basic solution is to check which parameters are set and equal to one and push the column name into an array if that's the case.

You can then loop through the array and concatenate the fields to the query, making it dynamic.

The biggest concern here is that you're using mysql_* functions which are deprecated. I recommend using PDO since I find them comfortable and handy to use but mysqli is also viable.

With PDO and mysqli you'll be able to use prepared-statements which are the best way to prevent injections so please use them.

Solution

Using the ternary operator, we can check which parameters are set and assign the field names accordingly.

$id = isset($_POST['id']) && $_POST['id'] == 1 ? 'id' : null;
$name = isset($_POST['name']) && $_POST['name'] == 1 ? 'name' : null;
$email = isset($_POST['email']) && $_POST['email'] == 1 ? 'email' : null;

I do this becuse I find the ternary operator prettier than multiple if-statements.

Let's construct an array with the variables that we just created:

$parameters = array($id, $name, $email);

By simply checking which fields are not null we can easily construct the columns array.

$columns = array();
foreach($parameters as $column)
{
    if($column != null)
    {
        $columns[] = $column;
    }
}

Finally we can implode the columns together, separating them with a comma:

$activeColumns = implode(",", $columns);

So you can use $activeColumns as a part of your query, which is then recommend to be prepared afterwards.

Upvotes: 1

SohanLal Saini
SohanLal Saini

Reputation: 458

Simply you can use ! empty() function instead of isset() && $_post['id']==1

$select_fields = array();
if(!empty($_POST['id']))
    $select_fields[] = 'id';
if(!empty($_POST['name']))
    $select_fields[] = 'name';
if(!empty($_POST['email']))
    $select_fields[] = 'email';

$query_fields = implode(', ', $select_fields);

$sql = mysql_query("select $query_fields from $table");

Upvotes: 1

Jeff Lambert
Jeff Lambert

Reputation: 24661

Why not instead push all of the field names into an array instead of setting disparate variables?

$fields = [];
if(isset($_POST['id']) && $_POST['id'] == 1) {
    $fields[] = 'id';
}

Then you should just be able to implode the array together in order to get your select columns:

$fields = implode(', ', $fields);

Also, @h2ooooo is correct, please avoid using mysql_ functions. They are depracated and will be removed in future versions of PHP.

Upvotes: 3

Related Questions