CODEWITHSUNDEEP
phpmysqlpdo
user3071888
user3071888

Reputation: 83

PHP PDO SELECT LIMIT issue

I've been troubleshooting for hours and I keep either getting this error:

ERROR: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'LIMIT'

or not getting a return at all with the following code(this is the simplified version I've been troubleshooting with anyway):

 $userName = "currentUser";

    // getting data
    $amper = "";
    $data = "";
    $limit = 10;

    // here you go:
        $sql = "SELECT * FROM tableName WHERE playerName <> :userName ORDER BY RAND() LIMIT :limit";
        $stm = $conn->prepare($sql);
        $stm->bindParam(':userName ', $userName );
        $stm->bindParam(':limit', $limit, PDO::PARAM_INT);

    try {
        $stm->execute();    
        $results = $stm->fetchAll();
        foreach ($results as $row) {            
            $data .= $amper."userDataOne=".$row['rowName'];
        }
    } catch(PDOException $e){
        echo'ERROR: ' . $e->getMessage();
    }

        print($data);

With the follow attribute, I receive the syntax error I listed above:

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

And with this attribute setting there are no errors, but no rows are selected

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES, false);

If I remove the LIMIT from the SELECT then everything works with either setting, so I need some help figuring out what's going on.

Upvotes: 0

Views: 1358

Answers (1)

phansen
phansen

Reputation: 411

According to the error message you are using Microsoft SQL Server. SQL Server doesn't use LIMIT, instead you need to use TOP at the front of the query, e.g.

SELECT TOP 10 * FROM table_name

Upvotes: 3

Related Questions