0

I am making a website that has jobs, and someone can filter the jobs by category. However, someone may not always filter a job, so it has to depend upon checked check-boxes on the front end. Once it gets to the PHP file, it must implode the array, then use each variable to parameterize the query. I found a few links that showed how to do a similar application, but it i couldnt find any material on how to do this with an IF statement and a prepared mysqli statement.

$o = 'o';
$sql="SELECT pid,title,description,location FROM jobs WHERE status=? ORDER BY job_date DESC";

if(isset($_POST['category'])){
    $category_filter = implode("','",$_POST['category']);
    $sql .= " AND category=? IN('".$category_filter."')";
}
$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $o,$category_filter);
$stmt->execute();
$stmt->bind_result($pid,$title,$description,$location);
$job = '';
while($stmt->fetch()){
    include '../templates/job-listing.php';
        }
Dharman
  • 30,962
  • 25
  • 85
  • 135
markusman
  • 73
  • 1
  • 7

1 Answers1

-1

Your code is almost good. The proplem is, that the constraint "IN ,..." gets added after the order by clause which will render a syntax error. change:

$o = 'o';
$sql="SELECT pid,title,description,location FROM jobs WHERE status='" . $o . "' ";

$order_clause = " ORDER BY job_date DESC";

if(isset($_POST['category'])){
    $category_filter = implode("','",$_POST['category']);
    $sql .= " AND category IN('" . $category_filter . "')";
}

$sql .= $order_clause;

$stmt = $conn->prepare($sql);
$stmt->execute();
$stmt->bind_result($pid,$title,$description,$location);

....
Axel Amthor
  • 10,980
  • 1
  • 25
  • 44
  • do i not need to bind the parameters? ive been binding parameters in every sql statement thus far – markusman Apr 06 '20 at 23:21
  • bind is only necessary, if you have `?` parameters in your query, which is not the case in my example. – Axel Amthor Apr 06 '20 at 23:26
  • i see, but should I have them to prevent SQL injection? Check this link out, this was fairly close to what I was going for, but a few parts of it i dont really understand/know if i need it to be secure. https://stackoverflow.com/questions/907806/passing-an-array-to-a-query-using-a-where-clause check Levi Morrison's answer – markusman Apr 06 '20 at 23:28
  • the linked solutions are generating a `IN` clause with as much `?` as you have array members and then pass them in as bind params. Your code is generating a full IN clause by imploding the POST array already. You have to deciide what you want, either solution works but not a mix of them. – Axel Amthor Apr 06 '20 at 23:35
  • thanks, im sorry, i dont know what a full IN clause means vs the other type. I want whats secure. My data could have 0 parameters, and could have more – markusman Apr 06 '20 at 23:39