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

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|||||
  • 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. – taskle Oct 29 '17 at 16:13
  • I have updated my answer. Please check – Dinesh Yadav Oct 29 '17 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. – taskle Oct 29 '17 at 20:36
  • Play with programming :) – Dinesh Yadav Oct 30 '17 at 4:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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