comparing php variable in sql query

Question

I am trying to compare php variable in the sql query as follows: I have used '$a' as the way to compare. Any solution will provided will be quite helpful.

<?php
    $username=$_SESSION['alogin'];
    $sql = "SELECT kDepartment FROM USERS WHERE kUsername=:username ";
    $query= $dbh -> prepare($sql);
    $query-> bindParam(':username', $username, PDO::PARAM_STR);
    $query->execute();
    $results=$query->fetchAll(PDO::FETCH_OBJ);
    if($query->rowCount() > 0)
    {
        foreach($results as $result2)
        $a=$result2;
        {?>
  <?php }}?>

<?php

$sql2 = "SELECT BudgetInput.kBudgetAvailable FROM BudgetInput WHERE BudgetInput.kDepartment='$a' ";
$query2 = $dbh -> prepare($sql2);
$query2->execute();
$results2=$query2->fetchAll(PDO::FETCH_COLUMN, 0);
print htmlentities($a);
?>

I am unable to fetch the result.

When I remove WHERE BudgetInput.kDepartment='$a'; the UI works correctly.

First, please explain your question. What isn’t working? Second, you are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. Simply escaping your variables is not enough. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). (The first query in your code is done correctly; the second is not.) — elixenide, Aug 05 '18 at 12:02
@EdCottrell I have updated the question, I did realised I posted an incomplete question. — Sabhay Sardana, Aug 05 '18 at 12:05
@NikitaLeshchev I did realised that I have posted an incomplete question. — Sabhay Sardana, Aug 05 '18 at 12:05
A `JOIN` would reduce the code and only perform one query, which is better than sending two queries. — Qirel, Aug 05 '18 at 12:34

score 2 · Accepted Answer · answered Aug 05 '18 at 12:26

The main issue that I can see with your code is that you are assigning an object ($result2 is the last entry in the array of objects returned by your query) to $a and then trying to use that as a string (I'm not sure why you're using PDO::FETCH_OBJ for your fetchAll call when you are only retrieving a single value but that's another question). Anyway, the fetchAll will return an anonymous object (see the manual) with one property, kDepartment. To use that value, change:

$sql2 = "SELECT BudgetInput.kBudgetAvailable FROM BudgetInput WHERE BudgetInput.kDepartment='$a' ";

to

$sql2 = "SELECT BudgetInput.kBudgetAvailable FROM BudgetInput WHERE BudgetInput.kDepartment='{$a->kDepartment}' ";

The other (minor) issue that I see is that you are using rowCount. This is not guaranteed to work for SELECT statements (see the manual) and since you have already done a fetchAll you don't need it, you can simply change

if($query->rowCount() > 0)

to

if (count($results) > 0)

score 1 · Answer 2 · answered Aug 05 '18 at 12:32

All you need is a simple join - then you need one query instead of two. A query from the result of another query is rarely the best approach.

Here we prepare a query which selects the available budget from the BudgetInput table, where it has a matching kDpeartment in the USERS table, and a username matching the variable in $username.

<?php 
session_start();
$username = $_SESSION['alogin'];
$stmt = $dbh->prepare("SELECT bi.kBudgetAvailable 
                       FROM BudgetInput bi
                       JOIN USERS u ON u.kDepartment=bi.kDepartment
                       WHERE u.kUsername=?");
$stmt->execute([$username]);
if ($result = $stmt->fetch()) {
    echo $result['kBudgetAvailable'];
} else {
    echo "No results found";
}

Thank you @Qirel for your answer. – Sabhay Sardana Aug 05 '18 at 12:37 — Sabhay Sardana, Aug 05 '18 at 12:37

comparing php variable in sql query

2 Answers2