I'm trying to get two statements to run based on the email filter when I click the buton

Question

This is the error that I get

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'anish123@gmail.com'.' GROUP BY email, date' at line 2 (SQL: SELECT email, date, min(time) AS checkedin, max(time) AS checkedout,( (TIME_TO_SEC(TIMEDIFF(max(time), min(time))) / 60) / 60) difference↵ FROM profile WHERE '. 1=1 and email like 'anish123@gmail.com'.' GROUP BY email, date)"

I am trying to filter data based on the email provided and on click of the button. The first query runs fine but I get error when I try to use the same where condition in second query.

$post = $request->all();
$email = $request->input('email');
$cond = ' 1=1 ';
if(!empty($post['email'])){
    $cond .= " and email like '".$post['email']."'";
}
$qry = 'SELECT User_id, email, status, date, time FROM profile WHERE '.$cond.' ';
$data = DB::select($qry);

$sql=" SELECT email, date, min(time) AS checkedin, max(time) AS checkedout,( (TIME_TO_SEC(TIMEDIFF(max(time), min(time))) / 60) / 60) difference
    FROM profile WHERE '.$cond.' GROUP BY email, date";
    $previousdata = DB::select($sql);

check your string concatenation. i have posted the edited version of the code below — Julius Fasema, Jun 06 '19 at 08:11

Dan · Answer 1 · 2019-06-06T08:14:15.800

You've used the wrong string concatenation for $sql:

$sql=" SELECT email, date, min(time) AS checkedin, max(time) AS checkedout,( (TIME_TO_SEC(TIMEDIFF(max(time), min(time))) / 60) / 60) difference
    FROM profile WHERE " . $cond . " GROUP BY email, date";

Using raw SQL, your query will be vulnerable to SQL injections. Read more about this problem.

Technically, you could use the Laravels query builder for both statements.

$conditions = [];

if ($email) {
    $conditions[] = ['email', 'like', $email];
}

$profile = DB::table('profile')
    ->select('user_id', 'email', 'status', 'date', 'time')
    ->where($conditions)
    ->get();

$previousData = DB::table('profile')
    ->select('email', 'date', DB:raw('min(time) checkedin'), DB:raw('max(time) checkedout'), DB::raw('((TIME_TO_SEC(TIMEDIFF(max(time), min(time))) / 60) / 60) difference'))
    ->where($conditions)
    ->groupBy('email', 'date')
    ->get();

score 1 · Accepted Answer · answered Jun 06 '19 at 08:10

i have edited the code above to the right one. the error was due to string concatenation

$sql="SELECT email, date, min(time) AS checkedin, max(time) AS checkedout,( (TIME_TO_SEC(TIMEDIFF(max(time), min(time))) / 60) / 60) difference
    FROM profile WHERE".$cond. "GROUP BY email, date";

I'm trying to get two statements to run based on the email filter when I click the buton

2 Answers2