FanatPHP

Постраничная навигация с LEFT JOIN ничем не отличается от навигации с любым другим запросом.

Если запрос "встал колом", то его надо оптимизировать. Независимо от того, используется ли он для постраничной навигации, или нет. Начинать оптимизацию следует с выполнения запроса EXPLAIN

Потому что не выключен режим эмуляции.

Только фактически всё наоборот: при включенном режиме эмуляции PDO как раз таки подставляет данные в запрос, и в LIMIT будет ошибка. А при выключенном данные в запрос не подставляются, и следовательно никакой ошибки не будет.

Судя по тому, что в вопросе нет сообщения об ошибке, при соединении вообще не указывается ни один нужный параметр. Как правильно соединяться с PDO
При указанных настройках запрос с лимитом будет работать.

Идея с функцией query() отличная, только она не должна возвращать fetchAll(). Это все равно что добровольно отказаться от половины функциональности PDO!
Функция должна возвращать стейтмент. Тогда ее можно будет использовать для любых запросов ,и получать данные в любых из дюжины форматов, поддерживаемых PDO, а не только в виде тупого массива.

Соответственно, функция должна делать return $query; а код будет таким

$sql = 'SELECT * FROM vocabulary ORDER BY word_date DESC LIMIT :startLimit , :countLimit';
$params = ['startLimit' => $_POST['slimit'], 'countLimit' => $_POST['limit']];
$lines = $db->query($sql, $params)->fetchAll();
var_dump($lines);

К сожалению, примеры в мануале были написаны в прошлом веке, не очень сведущими людьми, и с тех пор не менялись. К примеру, оператор if в приведенном коде бессмысленный и вредный.

Ну и плюс к тому надо же еще немножечко самостоятельно рассуждать. Оператор while нужен только если надо вывести много строк. А если нужна одна, то и оператор while не нужен.

В итоге весь код с учетом исполнения запроса код можно сократить до трех строк (запихивать всё в одну нет смысла - потом будет неудобно читать)

$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 50,1";
$result = $mysqli->query($query);
$row = $result->fetch_assoc();

Освоившись с объектным синтаксисом, в дальнейшем последние две строчки можно объединять в одну
$row = $mysqli->query($query)->fetch_assoc();
Также хочу отметить, что код для соединения с базой данных в примерах также дается ужасный. Более правильный вариант будет такой

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$mysqli->set_charset("utf8mb4");

А еще лучше соединяться, как написано здесь, чтобы с одной стороны иметь нормальные сообщения об ошибках, а с другой не вывалить случайно при ошибке все параметры соединения к БД в открытый доступ.

Поскольку в современном РНР никто не пишет такую кашу из HTML и PHP, как в ответе 0xD34F, то сначала следует преобразовать массив к горизонтальному. Это можно сделать как угодно, я предпочитаю теплые ламповые циклы.

$max = 0;
foreach ($array as $title => $item) {
    $max = max($max, count($item));
}
$rows = [];
for($i = 0; $i < $max; $i++) {
    foreach ($array as $item) {
        $rows[$i][] = $item[$i] ?? '';
    }
};
$titles = array_keys($array);

И затем уже выводить $titles и $rows обычным циклом, используя любимый шаблонизатор.

На самом деле объяснение очень простое.

Если под РНР понимать колупание в стиле прошлого века - отдельные файлы с include 'database_connect.php' и while($row = mysqli_fetch_array($result)), то IDE не нужна - достаточно любого редактора, позволяющего делать операцию копировать/вставить. Подойдет хоть Блокнот Виндоус.

В современном же мире разработчик работает над проектом, зачастую основанном на фреймворке, лежащим в системе контроля версий и состоящим из множества файлов, большая часть которых содержит определения классов. В этом случае IDE становится необходимым инструментом, ускоряющим разработку в разы.

жаль что нельзя добавить else после while.

Специально для жертв видеокурсов: РНР за 20 лет приобрел некоторые новые функции. Если учить пхп не только по видеокурсам, но открыть однажды для себя мануал, то там обнаружится такая функция, как mysqli_fetch_all(). И с ее помощью конструкция while($row = mysql_fetch_array($data)) из прошлого века, (к которой доблестные видеоблогеры осилили дописать буквочку i и решили что их код стал нереально современным) превратится в тыкву.

Если хватит способностей проверить в JS на пустоту массив, то вместо "while" пишем просто
echo json_encode(mysqli_fetch_all($data));
если хочется обязательно null, то

$rows = mysqli_fetch_all($data) ?: null;
echo json_encode($rows);

Сам по себе еще один запрос - это не лишняя работа, а ерунда на постном масле.
Вопрос не в том, сколько запросов выполняется, а что именно они делают.

И в данном случае эти два запроса делают ужас, летящий на крыльях ночи. Я сейчас расскажу, что делает ваш код.
Сначала этот код просит базу данных отправить в РНР все найденные запросом строки. Все. Без лимита. Чтобы РНР мог посчитать их и потом выбросить.
После этого код еще раз просит базу отправить, только не все строки, а только часть. И теперь РНР уже не выбрасывает полученные строки, а честно выводит.
Понятное дело, что смысла в этих замысловатых телодвижениях - ноль целых, ноль десятых.

Как правильно получить общее количество найденных строк? Надо попросить базу посчитать их

Ну и разумеется, использование prepare вместе с доморощенной и небезопасной функцией "clearStr" - это вообще за гранью добра и зла. Это примерно как хранить миллион в сейфе, но не запирать его на ключ, а подпирать дощечкой.

$GLOBALS["mysqlcon"] - это отдельный ужас, но на фоне всего остального меркнет.

Правильный код будет таким

$sql = "SELECT count(*) FROM post WHERE MATCH (tags) AGAINST (? IN BOOLEAN MODE)";
$STH = $GLOBALS["mysqlcon"]->prepare($sql);
$STH->execute([$_GET['search']]);
$totalPages=ceil($STH->fetchColumn()/$numPosts); //Узнаем сколько всего страниц существует

$sql = "SELECT * FROM post WHERE MATCH (tags) AGAINST (? IN BOOLEAN MODE) LIMIT ?,?";
$STH = $GLOBALS["mysqlcon"]->prepare($sql);
$STH->execute([$_GET['search'],$start, $numPosts]);

Поскольку все советуют синглтон, вот готовый пример: https://phpdelusions.net/pdo/pdo_wrapper#singleton
С ним, кроме прочего, код будет короче

$sql = "SELECT count(*) FROM post WHERE MATCH (tags) AGAINST (? IN BOOLEAN MODE)";
$numRows = DB::run($sql, [$_GET['search']])->fetchColumn();
$totalPages=ceil($numRows/$numPosts); //Узнаем сколько всего страниц существует

$sql = "SELECT * FROM post WHERE MATCH (tags) AGAINST (? IN BOOLEAN MODE) LIMIT ?,?";
$STH = DB::run($sql, [$_GET['search'],$start, $numPosts]);

PDO никак не преобразовывает данные. Ни большие, ни маленькие. И предопределенная константа PARAM_STR тут тоже не при чем.

Никакого "преобразования" в "binary" при выполнении запроса не происходит.

В сообщениях об ошибке / логах нестандартные символы кодируются для большей совместимости. К выполненному запросу этот формат не имеет отношения.