PHPize Online / SQLize Online  /  SQLtest Online

<?php // Execute query using emulated prepares echo "Index is being used correctly (emulated prepared statement protocol):\n"; executeWithView(true); executeWithSubQuery(true); // Execute query using native prepares echo "Index is NOT being used correctly (native prepared statement protocol):\n"; executeWithView(false); executeWithSubQuery(false); function executeWithView(bool $emulatePrepares): void { executeQuery($emulatePrepares, true); } function executeWithSubQuery(bool $emulatePrepares): void { executeQuery($emulatePrepares, false); } function executeQuery(bool $emulatePrepares, bool $useView): void { global $pdo; if ($useView) { $query = "EXPLAIN EXTENDED SELECT * FROM my_view WHERE type = ?;"; } else { $subQuery = 'SELECT type from my_table UNION ALL SELECT type from my_table'; $query = "EXPLAIN EXTENDED SELECT * FROM ($subQuery) AS test WHERE type = ?;"; } $type = 3; $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, $emulatePrepares); $stmt = $pdo->prepare($query); $stmt->bindParam(1, $type, PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); echo createTable($result); $stmt = $pdo->query('SHOW WARNINGS', PDO::FETCH_ASSOC); $result = $stmt->fetchAll(); echo createTable($result) . "\n\n"; } function createTable(array $array): string { $horizontal = '─'; $vertical = '│'; $crosses = [ 'nw' => '┌', 'n' => '┬', 'ne' => '┐', 'e' => '┤', 'se' => '┘', 's' => '┴', 'sw' => '└', 'w' => '├', 'c' => '┼', ]; // Get the headers (keys of the first element) $headers = array_keys($array[0]); // Calculate column widths $columnWidths = []; foreach ($headers as $header) { $columnWidths[$header] = strlen($header); } foreach ($array as $row) { foreach ($row as $key => $value) { $columnWidths[$key] = max($columnWidths[$key], strlen((string) $value)); } } // Build the table $firstRow = $crosses['nw']; $headerRow = $vertical; $middleRow = $crosses['w']; $lastRow = $crosses['sw']; foreach ($headers as $header) { $firstRow .= str_repeat($horizontal, $columnWidths[$header] + 2) . $crosses['n']; $headerRow .= ' ' . str_pad($header, $columnWidths[$header]) . ' ' . $vertical; $middleRow .= str_repeat($horizontal, $columnWidths[$header] + 2) . $crosses['c']; $lastRow .= str_repeat($horizontal, $columnWidths[$header] + 2) . $crosses['s']; } $firstRow = mb_substr($firstRow, 0, -1) . $crosses['ne']; $middleRow = mb_substr($middleRow, 0, -1) . $crosses['e']; $lastRow = mb_substr($lastRow, 0, -1) . $crosses['se']; $table = $firstRow . "\n" . $headerRow . "\n" . $middleRow . "\n"; foreach ($array as $row) { $rowLine = $vertical; foreach ($headers as $header) { $rowLine .= ' ' . str_pad((string) $row[$header], $columnWidths[$header]) . ' ' . $vertical; } $table .= $rowLine . "\n"; } $table .= $lastRow . "\n"; return $table; }