Displaying the Total Order History of Unregistered Customers

[hazael]

If you've ever needed to know how many orders a customer has placed in your store, it's possible to display the order count based on the number of times their email appears in the database.

This feature can be super useful for things like:

Offering discounts to loyal customers. Quickly identifying frequent buyers.
It's relatively easy to implement if you're comfortable with basic SQL and template modifications (e.g., in VirtueMart). A small tweak like this can save time and improve customer management. 😊

At the beginning of the file we add the function In:/administrator/templates/vmadmin/html/com_virtuemart/orders/orders.php

Code Select Expand

function getOrderCountByEmail($email) {
    $db = JFactory::getDbo();
    $email = strtolower($email);
    $query = $db->getQuery(true)
        ->select('COUNT(o.virtuemart_order_id)')
        ->from($db->quoteName('#__virtuemart_orders', 'o'))
        ->join('INNER', $db->quoteName('#__virtuemart_order_userinfos', 'u') . ' ON o.virtuemart_order_id = u.virtuemart_order_id')
        ->where('LOWER(' . $db->quoteName('u.email') . ') = ' . $db->quote($email));
    $db->setQuery($query);
    return (int) $db->loadResult();
}Behind the table header

Code Select Expand

echo $this->sort('order_email', 'COM_VIRTUEMART_EMAIL') ?></th>We add another header column

Code Select Expand

<th><?php echo vmText::_('COM_VIRTUEMART_ORDERS'); ?></th>After colum

Code Select Expand

<?php echo $order->order_email;?></td>Add column

Code Select Expand

<td><?php echo getOrderCountByEmail($order->order_email);?></td>