ORDER BY not working in postgresql

Question

This won't work and i'm getting an error from the ORDER BY param for some reason?

if($selected_radio == 'city')
{
    $query = "SELECT name FROM City WHERE name LIKE $1 LIMIT $2 OFFSET $3 ORDER BY name ASC";
    $result = pg_prepare($conn, "findCity", $query);
    $result = pg_execute($conn, "findCity", array($text, $limit, $offset));

    while($row = pg_fetch_assoc($result))
    {
        echo "<tr>";
        echo "<td>" . $i . "</td>";
        echo "<td>" . $row['name'] . "</td>";
        echo "</tr>";
        $i += 1;
    }
}

Sorry about the way it looks I'm fairly new to this site and not sure how to correctly post code :/

To format code, indent it by 4 spaces or select it and click the {} button. — Brendan Long
– Brendan Long, Commented Sep 10, 2012 at 22:31
@JoshValdivieso You should be, so you need to investigate that too. It's really important that statements that fail report error messages so you can act on them. — Craig Ringer
– Craig Ringer, Commented Sep 10, 2012 at 22:48

martin clayton · Accepted Answer · 2012-09-10 22:31:18Z

4

You've got your clauses in the wrong order - ORDER BY goes before LIMIT and OFFSET.

From the docs:

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
     * | expression [ AS output_name ] [, ...]
     [ FROM from_item [, ...] ]
     [ WHERE condition ]
     [ GROUP BY expression [, ...] ]
     [ HAVING condition [, ...] ]
     [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
     [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
     [ LIMIT { count | ALL } ]
     [ OFFSET start ]
     [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

answered Sep 10, 2012 at 22:31

martin clayton

78.6k33 gold badges220 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ORDER BY not working in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related