So I've had no issues with writing simple queries and pulling the data through that I need (mostly using fetchOne(), but this is my first time trying it with a little bit more comprehensive query.
I'm having trouble displaying any of the data that I want, period. This is what I have right now.
<?php
$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_read');
$userTable = $resource->getTableName('ced_csmarketplace_vendor_varchar');
$salesTable = $resource->getTableName('ced_csmarketplace_vendor_sales_order');
$query = mysql_query("SELECT $userTable.value,
SUM(DISTINCT(base_order_total))
FROM $salesTable
INNER JOIN $userTable ON $salesTable.vendor_id=$userTable.entity_id
WHERE payment_state='2'
GROUP BY vendor_id
ORDER BY SUM(base_order_total) DESC
LIMIT 5");
?>
What I'm trying to do is fetch the top 5 vendor usernames (value), ordered by their total sales amounts. In order to do so, I have to pull from two different tables. I assume I'll have to use a while loop, but am still a little new to fetching data from two different tables in PHP. I know the query works, as I've tested it, it's just a matter of displaying it.
Any tips would be greatly appreciated!
Adding the following code:
$results = $readConnection->fetchAll($query);
foreach($results as $result)
{
echo "<pre>";
print_r($result);
}
returns this:
Array
(
[value] => User 1
[SUM(DISTINCT(base_order_total))] => 10027.4500
)
Array
(
[value] => User 2
[SUM(DISTINCT(base_order_total))] => 6796.8500
)
Array
(
[value] => User 3
[SUM(DISTINCT(base_order_total))] => 6179.2000
)
Array
(
[value] => User 4
[SUM(DISTINCT(base_order_total))] => 5897.1100
)
Array
(
[value] => User 5
[SUM(DISTINCT(base_order_total))] => 4178.6100
)
So I'm almost there, I just need to get rid of the unnecessary text so that I can then style it myself in the .phtml
