Guest User

Unknown type 18 sent by the server - example

a guest
Apr 17th, 2016
29
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3. class Database {
  4.     private $host, $dbname, $user, $password, $connection = null;
  5.    
  6.     // Open database connection
  7.     public function connect($host, $dbname, $user, $password) {
  8.         $connectionString = sprintf("mysql:host=%s;dbname=%s;charset=utf8mb4", $host, $dbname);
  9.        
  10.         try {
  11.             $this->connection = new PDO($connectionString, $user, $password);
  12.            
  13.             $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  14.             $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  15.  
  16.             $this->connection->query("SET sql_mode = ''");
  17.         } catch (PDOException $pe) {
  18.             die("Database error: ".$pe->getMessage());
  19.         }
  20.     }
  21.    
  22.     public function get_board_threads() {
  23.         global $cfg;
  24.        
  25.         $query = "
  26.             SELECT posts.id, posts.thread, posts.date, posts.header, posts.text
  27.             FROM posts
  28.             WHERE posts.thread = 0
  29.             GROUP BY posts.id
  30.             ORDER BY posts.last_reply DESC";
  31.  
  32.         $q = $this->connection->prepare($query);
  33.         $q->execute();
  34.         return $q->fetchAll();
  35.     }
  36.    
  37.     // Working function but not what I want
  38.     public function get_thread_latests_replies($id) {
  39.         global $cfg;
  40.        
  41.         $query = "
  42.             SELECT posts.id, posts.thread, posts.date, posts.header, posts.text
  43.             FROM posts
  44.             WHERE posts.thread = :thread
  45.             GROUP BY posts.id
  46.             ORDER BY posts.id ASC LIMIT 3";
  47.  
  48.         $q = $this->connection->prepare($query);
  49.         $q->execute(array(":thread" => $id));
  50.         return $q->fetchAll();
  51.     }
  52.    
  53.     // Error is created here
  54.     public function get_thread_latests_replies_error($id) {
  55.         global $cfg;
  56.        
  57.         $query = "
  58.             (SELECT posts.id, posts.thread, posts.date, posts.header, posts.text
  59.             FROM posts
  60.             WHERE posts.thread = :thread
  61.             GROUP BY posts.id
  62.             ORDER BY posts.id ASC LIMIT 3) ORDER BY id DESC";
  63.  
  64.         $q = $this->connection->prepare($query);
  65.         $q->execute(array(":thread" => $id));
  66.         return $q->fetchAll();
  67.     }
  68.    
  69.     // Close the database connection
  70.     public function __destruct() {
  71.         $this->connection = null;
  72.     }
  73. }
  74.  
  75.  
  76.  
  77. /* Some "cosmetic" functions */
  78. function print_thread($thread) {
  79.     $thread_html = "<h3>".$thread[0]["header"]."</h3>";
  80.    
  81.     $thread_html .= print_post($thread[0], true)."<div class='answers'>";
  82.    
  83.     for ($i = 1; $i < count($thread); $i++) {
  84.         $thread_html .= print_post($thread[$i]);
  85.     }
  86.    
  87.     $thread_html .= "
  88.             </div>
  89.         </div>";
  90.    
  91.     return $thread_html;
  92. }
  93.  
  94. function print_post($post, $start_post = false) {
  95.     $html = "
  96.         <div style='border: 1px solid #000; margin: 3px;'>
  97.             <div>#".$post["id"]." | ".$post["date"]."</div>
  98.             <div>
  99.                 <p>".nl2br($post["text"])."</p>
  100.             </div>";
  101.    
  102.     if ($start_post == false) {
  103.         $html .= "</div>";
  104.     }
  105.    
  106.     return $html;  
  107. }
  108.  
  109. /* End of "cosmetic" functions */
  110.  
  111.  
  112.  
  113. echo "<h2>Run this SQL query in your MySQL Server first as root user</h2>
  114.  
  115. <pre>
  116. CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  117.  
  118. CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'safepw123';
  119.  
  120. GRANT ALL ON `test_db`.* TO 'test_user'@'localhost';
  121.  
  122. FLUSH PRIVILEGES;
  123.    
  124. USE test_db;
  125.  
  126. CREATE TABLE `posts` (
  127.     `id` int(11) NOT NULL,
  128.     `thread` int(11) NOT NULL DEFAULT '0',
  129.     `date` datetime NOT NULL,
  130.     `last_reply` datetime DEFAULT NULL,
  131.     `header` varchar(32) NOT NULL DEFAULT '',
  132.     `text` varchar(8000) NOT NULL
  133. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  134.  
  135. INSERT INTO `posts` (`id`, `thread`, `date`, `last_reply`, `header`, `text`) VALUES
  136.     (1, 0, '2016-04-17 14:31:28', '2016-04-17 15:52:33', 'Title', 'start post (always top/first)\r\n\r\nThe order should be 6, 7, 8 from top to bottom. So only the latest three messages should be showing'),
  137.     (2, 1, '2016-04-17 14:31:56', NULL, '', '2nd post'),
  138.     (3, 1, '2016-04-17 14:51:31', NULL, '', '3rd post'),
  139.     (4, 1, '2016-04-17 14:55:42', NULL, '', '4th post'),
  140.     (5, 1, '2016-04-17 15:34:01', NULL, '', '5th post'),
  141.     (6, 1, '2016-04-17 15:43:44', NULL, '', '6th posts'),
  142.     (7, 1, '2016-04-17 15:49:12', NULL, '', '7th posts'),
  143.     (8, 1, '2016-04-17 15:52:33', NULL, '', '8th posts');
  144.  
  145. ALTER TABLE `posts`
  146.     ADD UNIQUE KEY `id` (`id`),
  147.     ADD KEY `thread` (`thread`);
  148.  
  149. </pre>";
  150.  
  151. echo "<br><br><h2 style='color:red;'>To create a PHP error add \"?error=1\" in the url</h2>";
  152.  
  153. echo "<br><br><h2>PHP Output:</h2>";
  154.  
  155. /* Connect to database */
  156. $db = new Database();
  157. $db->connect("127.0.0.1", "test_db", "test_user", "safepw123");
  158.  
  159. $threads = $db->get_board_threads();
  160. $threads_html = "";
  161.  
  162. foreach ($threads as $thread) {
  163.     if (isset($_GET["error"]) AND $_GET["error"] == 1) {
  164.         // Creates error
  165.         $replies = $db->get_thread_latests_replies_error($thread["id"]);
  166.     } else {
  167.         // Doesn't create error
  168.         $replies = $db->get_thread_latests_replies($thread["id"]);
  169.     }
  170.    
  171.     $threads_html .= print_thread(array_merge(array($thread), $replies));
  172. }
  173.  
  174. // Print the output
  175. echo "<div style='width: 300px;'>".$threads_html."</div>";
  176.  
  177. ?>
RAW Paste Data