-1

I know this question has been covered before I have read many answers and they all tell me how to write the SQL but not how to print out the results.

My dilemma is I have an orders table with a payment method field tha tlooks like this

OrderID     OrderNumber     PaymentMethod
 1234         a1b2c3          PayPal
 1235         a1b2c4          Account
 1236         a1b2c5          Account
 1237         a1b2c6          PayPal
 1238         a1b2c7          Account
 1239         a1b2c8          PayPal
 1230         a1b2c9          PayPal

I need to print out something like

Number of PayPal payments: 4

Number of Account Payments: 3

I have tried to use this answer

MySQL: Count occurrences of distinct values

and I have this code

SELECT PaymentMethod,COUNT(*) as Payment FROM tablename GROUP BY PaymentMethod ORDER BY Payment DESC;

but how do I print this out?

Community
  • 1
  • 1
Kevlar
  • 344
  • 1
  • 7
  • 25
  • Show the PHP code you already have for running that query. It should give you a result set like any other... don't understand the difficulty you are having? **Pseudo-code:** `while($row = $db->fetch()) { echo $row['PaymentMethod'].': '.$row['Payment']; }` – naththedeveloper Oct 17 '13 at 14:27
  • GO to PHP.net and see how to get result from your DB.. http://www.php.net/manual/en/mysqli-result.fetch-assoc.php try that 1st example.. – Svetoslav Oct 17 '13 at 14:32
  • I don't really understand what the question is... That query gets the pair (payment, amount) that you're looking for. What am I missing? – Mosty Mostacho Oct 17 '13 at 14:33

2 Answers2

1
$returned = mysqli_query("SELECT PaymentMethod,COUNT(distinct PaymentMethod) as Payment FROM tablename GROUP BY PaymentMethod ORDER BY Payment DESC;");
while($data = mysqli_fetch_assoc($returned)){
    echo "Number of " . $data['PaymentMethod'] . ": " . $data[0] . "<br>";
}
okawei
  • 1,447
  • 2
  • 12
  • 11
0

You should leave data representation to the VIEW.

Having said that, this is how you can extract the data formatted in that way using purely MySQL:

SELECT concat('Number of ', paymentmethod ,' payments: ', count(*)) RESULT FROM t
GROUP BY paymentmethod
ORDER BY paymentmethod DESC

This results in:

|                        RESULT |
|-------------------------------|
|  Number of PayPal payments: 4 |
| Number of Account payments: 3 |

Fiddle here.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • Don't assume that everyone's working in an MVC environment... – Marc B Oct 17 '13 at 14:41
  • Question is tagged as PHP so there is another processing level here which can/should handle that part of the process. – Mosty Mostacho Oct 17 '13 at 14:42
  • so? PHP is perfectly capable of sucking data out of a query and spitting it out to a display without having to slap on an MVC layer as well. – Marc B Oct 17 '13 at 14:43
  • yeah I need to use PHP to print this out...but it doesnt appear like a normal SELECT, loop and echo – Kevlar Oct 17 '13 at 14:44
  • @MarcB Of course. And when spitting it out to a display that is the best time to format it... not while extracting it. That *display* you mention would be the VIEW, isn't it? I never mentioned MVC so far. Only you did :) – Mosty Mostacho Oct 17 '13 at 14:49