PHPDevShell

Pagination and adding an ORDER BY

Asked by jsherk

My query looks like this:
    protected $sql = "
        SELECT *
        FROM _db_ya%u_offerings
        ORDER BY date
    ";
    protected $keyField=false;

Now when I display the $pagination->searchForm() in the template, it displays ok, when I type something in it and hit enter, I get the following error listed below.

I think it is because I included the ORDER BY in my original SQL and it is then trying to add the WHERE clause afterwards which does not work. What is the proper way to the ORDER BY clause?

Thanks

ERROR:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id LIKE '%hi%' OR unique_id LIKE '%hi%' OR date LIKE '%hi%' OR lo' at line 6
The MySQL database engine returned with an error (code 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id LIKE '%hi%' OR unique_id LIKE '%hi%' OR date LIKE '%hi%' OR lo' at line 6

The faulty query REAL SQL was:
        SELECT SQL_CALC_FOUND_ROWS *
        FROM _db_ya1_offerings
        ORDER BY date
 WHERE
 id LIKE '%hi%' OR
 unique_id LIKE '%hi%' OR
 date LIKE '%hi%' OR
 location_id LIKE '%hi%' OR
 state_province_id LIKE '%hi%' OR
 country_id LIKE '%hi%' OR
 currency_id LIKE '%hi%' OR
 cash LIKE '%hi%' OR
 other LIKE '%hi%' OR
 other_no LIKE '%hi%' OR
 foreign_cash LIKE '%hi%' OR
 foreign_other LIKE '%hi%' OR
 foreign_no LIKE '%hi%'
 LIMIT 0, 30

Question information

Language:
English Edit question
Status:
Solved
For:
PHPDevShell Edit question
Assignee:
No assignee Edit question
Solved by:
jsherk
Solved:
Last query:
Last reply:
Revision history for this message
TitanKing (titan-phpdevshell) said : 		#1

At this stage, the ORDER BY is created by the system in the <th> tags, meaning that as soon as one clicks the ascending or descending links in its column the ORDER BY will create. Would you like to use the search field without the <th> header? It is possible, I would have to enhance it slightly though.

Revision history for this message
jsherk (jeff-forerunnertv) said : 		#2

What I want is the initial results sorted by the DATE column (ORDER BY date).

I like the little ascending/descending sort links in the header... those are very cool! I just need the initial results sorted by my date column.

Revision history for this message
Greg (gregfr) said : 		#3

I don't know if this will work with the pagination plugin, but you can try this:

    protected $sql = "SELECT * FROM (
                SELECT *
                FROM _db_ya%u_offerings
                ORDER BY date
        ) as t
    ";
    protected $keyField=false;

The query system supports an optional ORDER BY field, but I'm not sure the pagination plugin uses it.

Revision history for this message
jsherk (jeff-forerunnertv) said : 		#4

I gave it a try, and no, this does not work with the pagination plugin!

Thanks

Revision history for this message
TitanKing (titan-phpdevshell) said : 		#5

Hi Jeff,

I will add this in the plugin if I understand why you want this. Remember that when using the sorting in the <th> tags it filters using the ORDER function already. If I click in the down arrow of ID for instance, it will ORDER by ID DESC. So what I dont understand is, you want to use both, do you want to group them ORDER by ID, DATE DESC, why dont you just add the DATE column too?

Revision history for this message
jsherk (jeff-forerunnertv) said : 		#6

It's just the initial database query that I want to order by date, so that the first time my page is viewed and the results are displayed, it is ordered by the date column. If the user clicks on one of the sort buttons in the header then it should get resorted by whatever they clicked on (no longer by date).

Revision history for this message
TitanKing (titan-phpdevshell) said : 		#7

Ah ok, that makes perfect sense, so, what I am going to add then is an option to add a default sorting.

Revision history for this message
jsherk (jeff-forerunnertv) said : 		#8

Yes thats it!

Revision history for this message
TitanKing (titan-phpdevshell) said : 		#9

Ok in latest RC-1 branch you now have access to these properties.

 /**
  * This simply allows you to disable the split 50 button links on pagingation.
  */
 public $showSplitLinks = true;
 /**
  * When a user did not select an order this column will be used.
  */
 public $defaultOrderColumn = '';
 /**
  * When a user did not select an order (asc|desc) order according to this.
  */
 public $defaultOrder = 'DESC';

You can test them and see if it performs as you expected.

Jason