PHP sort multiple columns

Question

With my code I can sort all columns individually but I do have an issue with data not being sorted alphabetically / chronologically apart from the 1st column. Please see below what is displayed and then what I need to see.

What is currently displayed (Payment type and Date not sorted):

| Account | Payment type |    Date    | Amount |
|:--------|:------------:|:----------:|-------:|
| A001    | Rent         | 2021-06-01 | 150.00 |
| A001    | Deposit      | 2021-04-15 | 200.00 |
| A001    | Rent         | 2021-05-02 | 150.00 |
| A002    | Deposit      | 2021-06-20 | 220.00 |
| A003    | Rent         | 2021-06-02 | 250.00 |
| A003    | Deposit      | 2021-05-25 | 300.00 |

What I want to get displayed:

| Account | Payment type |    Date    | Amount |
|:--------|:------------:|:----------:|-------:|
| A001    | Deposit      | 2021-04-15 | 200.00 |
| A001    | Rent         | 2021-05-02 | 150.00 |
| A001    | Rent         | 2021-06-01 | 150.00 |
| A002    | Deposit      | 2021-06-20 | 220.00 |
| A003    | Deposit      | 2021-05-25 | 300.00 |
| A003    | Rent         | 2021-06-02 | 250.00 |

So in short, I want to sort and display data 1stly by the Account column (which displays correctly) and then by the Date column.

My code currently (and yes I know it's open to sql injection and I am working on that ;-) ):

    $orderBy = !empty($_GET["orderby"]) ? $_GET["orderby"] : "contracts_account_nr";
    $order = !empty($_GET["order"]) ? $_GET["order"] : "asc";
    $sql = "SELECT contract.contracts_account_nr, payment.rental_payment_type, payment.rental_payment_amount, payment.rental_payment_date FROM contracts contract RIGHT JOIN rental_payments payment ON contract.contracts_id = payment.contracts_id ORDER BY " . $orderBy . " " . $order;
    $result = mysqli_query($con, $sql); 
    $contractOrder = "asc"; 
    $typeOrder = "asc";  
    $dateOrder = "asc"; 
    if($orderBy == "contract.contracts_account_nr" && $order == "asc") {
    $contractOrder = "desc";    
    }
    if($orderBy == "payment.rental_payment_type" && $order == "asc") {
    $typeOrder = "desc";    
    }
    if($orderBy == "payment.rental_payment_date" && $order == "asc") {
    $dateOrder = "desc";    
    }

Your help will be much appreciated!

Tim Biegeleisen · Accepted Answer · 2021-08-17 09:45:57Z

2

The ORDER BY clause of a prepared statement can't be bound by placeholders. That is, the ORDER BY clause needs to be fixed. So, just do that:

SELECT *
FROM ...
ORDER BY Account, Date;

If you want to allow your users the possibility of choosing how to sort, then I suggest choosing one or more statements with hard-coded ORDER BY clauses, based on the inputs into your PHP script.

answered Aug 17, 2021 at 9:45

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Comments

Azoel · Accepted Answer · 2021-08-17 12:04:53Z

0

Be careful with this code as you have a security issue : SQL injection SQL injection on php.net

$order and $orderBy are both retrived with GET method. You should at least protect them with mysqli_real_escape_string

edited Aug 17, 2021 at 12:04

Azoel

2197 silver badges20 bronze badges

answered Aug 17, 2021 at 11:50

CharlesM

194 bronze badges

1 Comment

Azoel Over a year ago

You can also create a PDO object and use $pdo->prepare() . By using prepared statements you are protected against SQL injections. The query isn't parsed in the same way as concatenation

Collectives™ on Stack Overflow

PHP sort multiple columns

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related