1

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

Improve this question

1 Answer 1

Reset to default
2

Try this

$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":

$results = $readConnection->fetchAll($query);

foreach($results as $result)
{
   echo "<pre>";
   print_r($result['value']);
}
Improve this answer
4
  • This is indeed getting me the entire list now. Thank you. However, it's printing them with text I don't want. I've updated my main post with what it's printing now. Commented Oct 29, 2017 at 16:13
  • I have updated my answer. Please check Commented Oct 29, 2017 at 16:26
  • Thank you! This is exactly what I had needed, I didn't realize it was that simple since I'm still kind of new at fetching from the database myself. Commented Oct 29, 2017 at 20:36
  • Play with programming :) Commented Oct 30, 2017 at 4:20

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.