mariadb error syntax $query .= " ORDER BY s.first_name, s.last_name LIMIT ? OFFSET ?"

[titotettey]

// Add sorting and pagination
$query .= " ORDER BY s.first_name, s.last_name LIMIT ? OFFSET ?";
$params = array_merge($params, [$perPage, $offset]);
$types .= 'ii';
// Get students
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

 

[Moorcam]

// Add sorting and pagination
$query .= " ORDER BY s.first_name, s.last_name LIMIT ? OFFSET ?";
$params = array_merge($params, [$perPage, $offset]);
$types .= 'ii';
// Get students
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

If $perPage or $offset are not integers, the SQL execution may fail or return incorrect results.
Also, if the SQL query is not properly constructed before execution, it could lead to SQL injection.

Try this:

// Validate perPage and offset
$perPage = isset($perPage) && is_numeric($perPage) ? (int)$perPage : 10; // Default to 10 if not set
$offset = isset($offset) && is_numeric($offset) ? (int)$offset : 0; // Default to 0 if not set

// Add sorting and pagination
$query .= " ORDER BY s.first_name, s.last_name LIMIT ? OFFSET ?";
$params = array_merge($params, [$perPage, $offset]);
$types .= 'ii';

// Get students
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);