Is there a way to get most bought package in php?

Question

I have a web application in php. where users can buy packages and admin can see how many sales each package have. right now I'm getting total sales by counting the rows from the payments table. to get the most bought package I think I have to count the rows in payments table in the database and return the id of of most bought package. but I don't to go with this?

Current Statement Which Get Package Sales from Id:

$sql = "SELECT COUNT(*) AS totalSales FROM ph_paypal_payments WHERE packageId = ?";
$stmt = mysqli_stmt_init($connection);
if (!mysqli_stmt_prepare($stmt, $sql)) die ("Prepare failed: " . mysqli_stmt_error($stmt));

mysqli_stmt_bind_param($stmt, "i", $packageId);
mysqli_stmt_execute($stmt);
return mysqli_fetch_assoc(mysqli_stmt_get_result($stmt))['totalSales'];

It is a very bad idea to use `mysqli_stmt_error($stmt)` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Feb 15 '20 at 15:28

score 0 · Accepted Answer · answered Feb 15 '20 at 13:33

0

You can use this

SELECT 
  packageId
FROM
  (SELECT packageId, COUNT(*) AS totalSales FROM ph_paypal_payments 
  GROUP BY packageId) t1
ORDER BY totalSales DESC
LIMIT 1;

Ir gets you one packageId which has the highest totalSales . Even if there are more it still gets you only one of them.

this is based on the number of rows and not the quantity sold.

$sql = "SELECT packageId FROM (SELECT packageId, COUNT(*) AS totalSales FROM ph_paypal_payments GROUP BY packageId) t1 ORDER BY totalSales DESC LIMIT 1;";
$stmt = mysqli_stmt_init($connection);
if (!mysqli_stmt_prepare($stmt, $sql)) die ("Prepare failed: " . mysqli_stmt_error($stmt));

mysqli_stmt_bind_param($stmt, "i", $packageId);
mysqli_stmt_execute($stmt);
return mysqli_fetch_assoc(mysqli_stmt_get_result($stmt))['packageId'];

answered Feb 15 '20 at 13:33

nbk

45,398
8
30
47

Thank you. I think you should remove `mysqli_stmt_bind_param` for future users. because there's no need to bind anything, – Feb 15 '20 at 13:44
It is a very bad idea to use `mysqli_stmt_error($stmt)` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Feb 15 '20 at 15:27
But it only dies when it can't be prepared, this would only happen during debugging, but in my case we don't need a prepared statement ergo it can't fail at all. – nbk Feb 15 '20 at 15:39

Is there a way to get most bought package in php?

1 Answers1