Using PDO and MySql instead of mysqli

Question

I'm starting using PDO and MySQL instead of mysqli. So, i try to define this simple function:

public function getIntro($table) {
            $stmt = $this->db->prepare("SELECT * FROM $table ORDER BY rand() LIMIT ?");
            $stmt->execute(array(6));
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

But, in doing so, the function return an empty array. Considering that the connection with the db work correctly, where is the error?

Thanks

did you check `$stmt->rowCount` to see if any rows were matched at all? empty array = no rows available. — Marc B, Apr 28 '16 at 21:32
Using the parameter the rowCount is 0, using a query without parameter the rowCount is > 0. So, the error is in using the parameter but i can understand how using it — user2520969, Apr 28 '16 at 21:57

score 0 · Answer 1 · answered Apr 28 '16 at 21:35

0

Try this:

public function getIntro($table) {
        $stmt = $this->db->prepare("SELECT * FROM $table ORDER BY rand() LIMIT ?");
        $stmt->bindValue(1, 6);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

or this:

public function getIntro($table) {
            $stmt = $this->db->prepare("SELECT * FROM $table ORDER BY rand() LIMIT :limit");
            $stmt->execute(array(':limit' => 6));
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

answered Apr 28 '16 at 21:35

dimlucas

5,040
7
37
54

Unfortunately, both the solutions doesn't work... – user2520969 Apr 28 '16 at 21:41
@user2520969 Try removing the `ORDER BY` part. Try this query instead: `SELECT * FROM $table LIMIT ?` – dimlucas Apr 28 '16 at 21:43
No, it doesn't change – user2520969 Apr 28 '16 at 21:45
Why should the OP "try this"? A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Apr 28 '16 at 21:46
@user2520969 Try hardcoding the number 6 in the query, no binding at all and see if that solves it. If it does it clearly has to do with `LIMIT` – dimlucas Apr 28 '16 at 21:46
@JayBlanchard The OP should try this because I don't have access to the OP's database. There might be something causing an error that the OP or me or anyone else knows yet. There can be multiple sources of bugs. Right now, I'm trying to help. If I can't I'll make sure to delete my answer – dimlucas Apr 28 '16 at 21:49
Using 6 in the query it works. The problem is in using the value as parameter – user2520969 Apr 28 '16 at 21:50

Using PDO and MySql instead of mysqli

1 Answers1