0

I am trying to write a custom sql that will provide to me data related to the Magento2 EE reward points. My issue is that my queries seem to be showing 0 as the result which can not be because when I manually look at some customer's profiles in the Magento admin and go to their reward points table I can see some customers have points.

My goal is to understand the average # of points my customers have and how many customers have at least x amount of points.

Here is my code:

$tempcon = mysqli_connect("127.0.0.1","fdghfdhg","abcdefg","foobar");
//Select Data from table
$sql = mysqli_query($tempcon, "SELECT AVG(points_balance) as avgpoints FROM magento_reward");

$sqla = mysqli_query($tempcon, "SELECT COUNT(customer_id) as customersabovefifty FROM magento_reward WHERE points_balance > 499");
$sqlb = mysqli_query($tempcon, "SELECT COUNT(customer_id) as num_customers FROM magento_reward");


$sqlrow = mysqli_fetch_assoc($sql);
$sqlarow = mysqli_fetch_assoc($sqla);
$sqlbrow = mysqli_fetch_assoc($sqlb);
$customersabovefifty = $sqlarow['customersabovefifty'];
$avgpoints = $sqlrow['avgpoints'];
$num_customers = $sqlbrow['num_customers'];
echo "<h2>REWARD POINTS</h2>
  <p>Customers have an average of $avgpoints points</p>
  <p>Total number of customers who have points: $num_customers</p>
  <p>Number of customers who have 500 or more points: $customersabovefifty";

1 Answer 1

0

It seems like you are trying to query the Magento2 EE reward points data, but the table names in your SQL queries may not be correct. In the Magento2 EE the reward points data is stored in the 'magento_reward_points_balance' table. Please try updating your queries to use the correct table name.

Here is the updated code:

$tempcon = mysqli_connect("127.0.0.1","fdghfdhg","abcdefg","foobar");

// Select Data from table
$sql = mysqli_query($tempcon, "SELECT AVG(points_balance) as avgpoints FROM magento_reward_points_balance");

$sqla = mysqli_query($tempcon, "SELECT COUNT(customer_id) as customersabovefifty FROM magento_reward_points_balance WHERE points_balance > 499");
$sqlb = mysqli_query($tempcon, "SELECT COUNT(customer_id) as num_customers FROM magento_reward_points_balance");

$sqlrow = mysqli_fetch_assoc($sql);
$sqlarow = mysqli_fetch_assoc($sqla);
$sqlbrow = mysqli_fetch_assoc($sqlb);
$customersabovefifty = $sqlarow['customersabovefifty'];
$avgpoints = $sqlrow['avgpoints'];
$num_customers = $sqlbrow['num_customers'];

echo "<h2>REWARD POINTS</h2>
  <p>Customers have an average of $avgpoints points</p>
  <p>Total number of customers who have points: $num_customers</p>
  <p>Number of customers who have 500 or more points: $customersabovefifty";

This code should now give you the correct data related to Magento2 Adobe Commerce reward points. Remember to replace the database credentials with your actual credentials.

2
  • Thank you @brentwpeterson unfortunately not my page just returns black white with no error or anything which is odd.
    – Jayreis
    Commented May 5, 2023 at 15:33
  • What if you run the query directly in MySQL? Commented May 9, 2023 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.