asked on

How to rank PHP MYSQL output and put it as PHP variable.

Hi,

I would like to request an assistant.

I have huge data, the following are some sample of it :

customer_id | total | created_at | mmobilcart_id | store_id | status_id |
=========================================================
17 | 53 | 2019-01-01 06:42:10 | 7 | 8 | 1 |
25 | 120 | 2019-02-21 06:42:10 | 7 | 8 | 1 |
19 | 225 | 2019-03-23 06:42:10 | 7 | 8 | 1 |
13 | 57 | 2019-02-01 06:42:10 | 7 | 8 | 1 |
17 | 111 | 2019-02-11 06:42:10 | 7 | 8 | 1 |
19 | 85 | 2019-02-25 06:42:10 | 7 | 8 | 1 |
17 | 654 | 2019-04-11 06:42:10 | 7 | 8 | 1 |
13 | 25 | 2019-05-08 06:42:10 | 7 | 8 | 1 |
25 | 212 | 2019-02-18 06:42:10 | 7 | 8 | 1 |
17 | 8 | 2019-03-18 06:42:10 | 7 | 8 | 1 |

I would like to have the following output :

1. Total per "customer_id" .
2. Its rank based on "total" , the bigger total should be the 1st and so forth.

I have tried the following to list it :

SELECT customer_id, SUM(total) as TOTALS FROM mmobilcart_order WHERE created_at between '2019-01-01 06:42:10' and '2019-06-30 07:42:50' AND mmobilcart_id=7 AND store_id=8 AND status_id='1' GROUP BY customer_id ORDER by TOTALS DESC ;

But, i have problem to get its rank.

Can anybody assist me on this ?

I need to have solution either via MYSQL query or PHP.

Thank you.

Robert Granlund

SELECT customer_id, SUM(total) as totals 
FROM mmobilcart_order
WHERE (created_at BETWEEN '2019-01-01 06:42:10' and '2019-06-30 07:42:50') 
AND mmobilcart_id=7 
AND store_id=8 
AND status_id='1'
GROUP BY customer_id 
ORDER by total DESC ;

Open in new window

And turn on error reporting and what do you see?

Shamsul Kamal

ASKER

No error, it produce correct sequence.

But actually i wanted to have another column with ranks that i can use.

I also want to know how to get rank for specific customer_id.

Robert Granlund

A small change:

$cid = $_POST[customer_id'];  //  Set the customer ID

SELECT customer_id, SUM(total) as totals 
FROM mmobilcart_order
WHERE customer_id = '$cid'
AND (created_at BETWEEN '2019-01-01 06:42:10' and '2019-06-30 07:42:50') 
AND mmobilcart_id=7 
AND store_id=8 
AND status_id='1'
GROUP BY customer_id 
ORDER by totals DESC ;

Open in new window

lenamtl

Hi,

I would not calculate the total within the MySQL query (SUM(total)) but instead I would do the calculation using PHP on the result page which add more flexibility and performance...

This way if you need to change the way it calculate later you won't need to change MySQL query.

Shamsul Kamal

ASKER

Hi rgranlund,

$cid = $_POST[customer_id']; // Set the customer ID

SELECT customer_id, SUM(total) as totals
FROM mmobilcart_order
WHERE customer_id = '$cid'
AND (created_at BETWEEN '2019-01-01 06:42:10' and '2019-06-30 07:42:50')
AND mmobilcart_id=7
AND store_id=8
AND status_id='1'
GROUP BY customer_id
ORDER by totals DESC ;

This will just show the "totals" for that specific customer_id but it did not tell me its rank number ?

I need to get the rank for specific customer_id , starting from the biggest "totals" as number 1 and so on.

Hope anybody can help me on this.

Robert Granlund

It should be this:

SELECT customer_id, SUM(total) as totals 
FROM mmobilcart_order
WHERE (created_at BETWEEN '2019-01-01 06:42:10' and '2019-06-30 07:42:50') 
AND mmobilcart_id=7 
AND store_id=8 
AND status_id='1'
GROUP BY customer_id 
ORDER by totals DESC ;

Open in new window

Does that not work? Can you show us the entire code?

Robert Granlund

@lenamtl How do you mean?

Shamsul Kamal

ASKER

The output from given sql query will be as follows :

customer_id | totals
=================
17 | 2500
25 | 478
13 | 352
19 | 250

My question is how to get ranking number for specific customer_id . eg : for customer_id 13 , the rank should be no 3. But how to get that rank number ?

Robert Granlund

Ahh.

<?php
$query ="SELECT customer_id, SUM(total) as totals 
FROM mmobilcart_order
WHERE (created_at BETWEEN '2019-01-01 06:42:10' and '2019-06-30 07:42:50') 
AND mmobilcart_id=7 
AND store_id=8 
AND status_id='1'
GROUP BY customer_id 
ORDER by totals DESC ";

$count=  = $conn->query($query);

$rank = array_slice($count, 3);
echo $rank;

Open in new window

since it will be returned as an array you can grab the third iteration.

Shamsul Kamal

ASKER

No, the number 3 given before is just as example.

I actually wanted to know the rank number just by query using customer_id.

I do not have that rank number and wanted to get it.

Example of my scenario.

customer_id | total | created_at | mmobilcart_id | store_id | status_id |
=========================================================
17 | 53 | 2019-01-01 06:42:10 | 7 | 8 | 1 |
25 | 120 | 2019-02-21 06:42:10 | 7 | 8 | 1 |
19 | 225 | 2019-03-23 06:42:10 | 7 | 8 | 1 |
13 | 57 | 2019-02-01 06:42:10 | 7 | 8 | 1 |
17 | 111 | 2019-02-11 06:42:10 | 7 | 8 | 1 |
19 | 85 | 2019-02-25 06:42:10 | 7 | 8 | 1 |
17 | 654 | 2019-04-11 06:42:10 | 7 | 8 | 1 |
13 | 25 | 2019-05-08 06:42:10 | 7 | 8 | 1 |
25 | 212 | 2019-02-18 06:42:10 | 7 | 8 | 1 |
17 | 8 | 2019-03-18 06:42:10 | 7 | 8 | 1 |

Question : from the above data, can i know the ranking for customer_id '17' based on totals ?

Hope this will give clear info on what i actually want.

lenamtl

I would calculate the total for each user using PHP then use Datatables to display the value and then set the ordering to what you need using Datatables settings. https://datatables.net/

I would not ordering the value using the MySQL query and I would not calculate the total using MySQL query too.
Instead I would use PHP to calculate the total and Datatables to display the result, this way this is more flexible and easier to changer later.

Shamsul Kamal

ASKER

Can anybody show me some sample script based on my scenario ?

lenamtl

Hi,

when you say his rank do you mean for example the user_id is the third on 25 users or you just need to display in order?

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shamsul Kamal

ASKER

Hi Chris Stanyon.

Thanks for the help.

SELECT @rownum:=@rownum+1 AS rank, cust, total
FROM
(SELECT SUM(total) AS total, customer_id AS cust
FROM mmobilcart_order
GROUP BY customer_id
ORDER BY total DESC) c, (SELECT @rownum:=0) r

The query seems incomplete.

Can you help to check ?

Chris Stanyon

What makes you think it's incomplete? Have you tried running it?

Shamsul Kamal

ASKER

Because it produce error in my phpmyadmin.

Chris Stanyon

Works perfectly for me. What error are you getting ?

Robert Granlund

@chris, running a query like that, what is that called exactly?

Chris Stanyon

@rgranland. Technically, it's called a cross-join. I'm using a session variable as the joined table which creates a variable and set's it's value to 0. For each row in the result set, it then adds 1 to it to give you the row number. In other flavours of SQL (MS SQL / PostGres ) etc, you have access to a function called ROW_NUMBER(), but that's not available in MySQL, so we need to fudge it with a variable (apparently it is avaiable in MySQL > 8).

Shamsul Kamal

ASKER

Hi, i test it wrong previously due to my typo.

Now its works fine.

Thanks to all who helping me.

Chris Stanyon

No worries. Glad you got it working :)

Robert Granlund

@chris and you can use that in a foreach or While statement?

Chris Stanyon

@rgranlund - yeah sure. When you run the query, it just returns a normal result set (like any other SELECT query), so you can loop over it however you like:

<?php
$sqlStr = <<< EOT
SELECT @rownum:=@rownum+1 AS rank, cust, total
FROM 
    (SELECT SUM(total) AS total, customer_id AS cust
     FROM yourTable
     GROUP BY customer_id
     ORDER BY total DESC) c, (SELECT @rownum:=0) r
EOT;

$result = $db->query($sqlStr);

while ( $row = $result->fetch() ):
    printf("<p>Customer %d has a rank of %d with a total of %d<p>", $row->cust, $row->rank, $row->total);
endwhile;

Open in new window