PHP PDO SELECT LIMIT issue

Question

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.

I've also tried use "... LIMIT 10"; and that gives me the same syntax error. — user3071888, Feb 13 '15 at 13:31
The only new syntax change from that thread was using intval() to identify it as an integer, unfortunately it hasn't fixed the problem. — user3071888, Feb 13 '15 at 13:38
possible duplicate of http://stackoverflow.com/questions/9013177/mysql-limit-clause-equivalent-for-sql-server — Jay Blanchard, Feb 13 '15 at 13:42

score 3 · Accepted Answer · answered Feb 13 '15 at 13:39

3

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

answered Feb 13 '15 at 13:39

phansen

411
3
7

That is certainly a very helpful piece of previously unknown advice. Thanks for that. Now I just need to add in some more demo rows to make sure the RAND() is working. – user3071888 Feb 13 '15 at 13:52
In case anyone else has this particular issue in the future, the MS Azure version of RAND is NEWID. – user3071888 Feb 13 '15 at 14:44

PHP PDO SELECT LIMIT issue

1 Answers1