How to use single posted parameter to run either BETWEEN or IN(SELECT parameter FROM table) type query?

Question

I have a bootstrap 4 form posting data to a PHP page. One of the variables is $_POST['age'];

I want to use this variable in a manner that when $_POST['age'] equals NULL, $age = 'IN("SELECT age FROM profiles") otherwise $minage = $age-3 and $maxage = $age+3. I have tried the following code with different variations but it doesn't work for the situation when $_POST['age'] = NULL

$age = $_POST['age'];
$minage = $age - 2;
$maxage = $age + 3;

if($age == NULL) {
  $given_age = 'IN("SELECT age FROM profiles")';
}else {

  $given_age = 'BETWEEN '. $minage.' AND '. $maxage;
}

echo $given_age;

The html form is as follows:

<div class="form-group">
   <label for="age">Preferred Age Group</label>
   <select class="form-control" name="age" id="age" required>
   <option data-hidden="true" value="Null">No Preference</option>
   <option data-hidden="true" value="20">18-22 Years</option>

   </select>
</div>

The outcome of the $given_age is to be used for a MySQL query.

You should use prepared statements for this anyway. I know it might not help in this scenario, but in general it could save you a lot of trouble. — Dharman, Jun 15 '19 at 14:07
Possible duplicate: [MySQL using BETWEEN comparison with NULL](https://stackoverflow.com/questions/26164224/mysql-using-between-comparison-with-null) — Dharman, Jun 15 '19 at 14:12

score 2 · Answer 1 · answered Jun 15 '19 at 14:08

If you try and use the value from $_POST['age'] and it was not sent to the php you will get an error of Unidentified index 'age'.

You should use isset to check and see if the value was passed and if it is valid.

Something like this:

$age = isset($_POST['age']) && !empty($_POST['age']) ? $_POST['age'] : null

and then your if check should work for if ($age == NULL) { ... }

Also like @Dharman suggested you should always try and use prepared statements for your queries as they mitigate more chances for SQL injections.

Dharman · Accepted Answer · 2019-06-15T15:22:50.247

2

You should remove the string Null from value="Null" and leave it empty i.e. value="". If you are passing integers that string has no place there.

You can use isset() to find out whether the value was entered in the HTML form, and act accordingly:

if(isset($_POST['age'])) {
  $age = (int) $_POST['age']; // make the value an integer
  $minage = $age - 2;
  $maxage = $age + 3;
  $given_age = 'BETWEEN '. $minage.' AND '. $maxage;
}else {
  $given_age = 'IN("SELECT age FROM profiles")';
}

If you are insisting on having Null as a value then apart from isset you should also check if it equals the string: $_POST['age'] === 'Null'

You should really use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, you are still in risk of corrupting your data.

edited Jun 15 '19 at 15:22

answered Jun 15 '19 at 14:16

Dharman

30,962
25
85
135

Thank you so much for editing the question and making it clearer as also for answering the question. The code is working fine as long as $_POST['age] is an integer value being passed from the form. However, when $_POST['age'] equals Null, this code doesn't produce the desired result. I am also posting the html form part being used for posting the data. Would appreciate if you may check the answer to see that it also works in the scenario when the posted data equals Null. The data will be posted using prepared statement using parameter binding. Thanks for the caution. – Muhammad Khan Jun 15 '19 at 15:18
@MuhammadKhan Updated my answer. – Dharman Jun 15 '19 at 15:24
checking for $_POST['age'] === 'Null' made it perfect for my purpose. Here is what finally worked: – Muhammad Khan Jun 15 '19 at 15:40
if(isset($_POST['age_match']) && ($_POST['age_match']) != 'NULL') { $age = (int) $_POST['age_match']; // make the value an integer $minage = $age - 2; $maxage = $age + 3; $given_age = 'BETWEEN '. $minage.' AND '. $maxage; }else { $given_age = 'IN("SELECT age_match FROM profiles")'; } – Muhammad Khan Jun 15 '19 at 15:40

How to use single posted parameter to run either BETWEEN or IN(SELECT parameter FROM table) type query?

2 Answers2