jeudi 31 janvier 2019

Getting Error: SQLSTATE[42S22]: Column not found: 1247

My question refers to another discussion branch - MySQL select 10 random rows from 600K rows fast.

I have a simple one column table:

+-----------------------+
| keywords              |
+-----------------------+
| Some text 1           |
| Some text 2           |
| Some text 3           |
| Some text 4           |
| Some text 5           |
| Some text 6           |
| Some text 7           |
| Some text 8           |
| Some text 9           |
| Some text 10          |
| Some text 11          |
| Some text 12          |
| ...                   |
| Some text 1200000     |
+-----------------------+

I'm trying to perform a request to some quantity of random lines using the code advised by #Riedsio on the line above, here it goes:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

My code is the following:

<?php

class TableRows extends RecursiveIteratorIterator {
    function __construct($it) {
        parent::__construct($it, self::LEAVES_ONLY);
    }

    function current() {
        return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
    }
}

$servername = "localhost";
$username = "admin";
$password = "admin_pass";
$dbname = "db_name";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//    $stmt = $conn->prepare("SELECT keywords FROM my_keywords");
    $stmt = $conn->prepare("SELECT keywords FROM my_keywords AS r1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM my_keywords)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1");
    $stmt->execute();

    // set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
            echo $v;
        }
    }

catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;

?>

As a result getting the following error: "Error: SQLSTATE[42S22]: Column not found: 1247 Reference 'id' not supported (forward reference in item list)".

I'm just starting in MySQL so please don't be offended by this stupid question and thank you for your help.




Aucun commentaire:

Enregistrer un commentaire