CODEWITHSUNDEEP
phpmysqlajaxpdo
Vincent Li
Vincent Li

Reputation: 55

PHP MySQL query not returning all results

My following SQL query in PHP does not work fully. The result only contains the first row. The query works totally fine inside PHPMyadmin, which returns me all the results.

$select = "SELECT a.setID, a.setName, a.setPrimaryLanguage, a.setSecondaryLanguage
            FROM Person_Set ps, Album a
            WHERE ps.Person_username = :username
            AND ps.Set_setID = a.setID";

  try {
    $stmt = $dbh->prepare($select, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
    $stmt->bindValue(":username", $username, PDO::PARAM_STR);
    $stmt->execute();

    $result = $stmt->fetch();
    echo json_encode($result);

    unset($stmt); 
  } catch (Exception $e) {
    echo 'Exception : ' . $e->getMessage() . "\n";
  }

Besides, if I change the selection criteria to search for rows containing certain string, the result is empty (returned 'false'). Query below:

$select = "SELECT a.setID, a.setName, a.setPrimaryLanguage, a.setSecondaryLanguage
            FROM Album a, Set_Card s
            WHERE a.setName LIKE '%:searchText%'
            AND a.setID = s.Set_setID
            GROUP BY a.setID";

I have been trying different ways to connect to MySQL and get the results, like 

$results = $mysqli->query($query);

instead of using PDO. However, the results are still the same. Could anyone help to point out where my mistakes are? Thank you very much!

Upvotes: 0

Views: 2086

Answers (3)

Priyank
Priyank

Reputation: 1009

Hi you are using fetch() function which fetch only one row instead use this code,

$select = "SELECT a.setID, a.setName, a.setPrimaryLanguage, a.setSecondaryLanguage
            FROM Person_Set ps, Album a
            WHERE ps.Person_username = :username
            AND ps.Set_setID = a.setID";

  try {
    $stmt = $dbh->prepare($select, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
    $stmt->bindValue(":username", $username, PDO::PARAM_STR);
    $stmt->execute();

    $result = $stmt->fetchall();
    echo json_encode($result);

    unset($stmt); 
  } catch (Exception $e) {
    echo 'Exception : ' . $e->getMessage() . "\n";
  }

Upvotes: 1

c0nstantx
c0nstantx

Reputation: 39

PDOStatement::fetch fetches a single row and moves pointer to the next row. You will either use $results = $stmt->fetchAll() to retrieve all results or a loop like this:

while ($result = $stmt->fetch()) {
    echo json_encode($result);
}

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

PDOStatement::fetch — Fetches the next row from a result set

So when you just do a fetch it fetches the first row, unless you do it using a loop which changes the cursor to the next record.

You may get all the records using fetchAll method

http://php.net/manual/en/pdostatement.fetch.php

http://php.net/manual/en/pdostatement.fetchall.php

Upvotes: 3

Related Questions