How do I walk a database query and deal with it?

Ok, this seems like it has to be simpler than I feel like. All I'm trying to do right now is grab a chunk of data from my MySQL db and then foreach my way through it to create a 'pretty' array to pass to the view. It does some simple summing along the way, adding each some fields together by day.

I am getting the db return fine I think:

$clientAccountId = ClientAdAccountId::findFirst();
$myData = myModel::find("customerId = '" . $clientAccountId->accountId . "'");
//I can var_dump myData and it looks like it's all there...?
//Then I pass it to a private function to do some adding of items together by day
$viewData = $this->parseData($myData, $startDate, $endDate);

In the private function, I try to foreach through the data, but it seems to be empty?

foreach( $myData as $row )
{
    echo $row->name; //Never shows anything
}

I tried toArray, and some other things, but I think I'm just unable to understand the docs. What am I doing wrong? Thank you!

-J

edited Jun '17

use Store\Toys\Robots;

// Find record with id = 3

$robot = Robots::findFirst(3);

// Prints 'Terminator'

echo $robot->name;

FindFirst gives you a single object



1.1k
edited Jun '17

I'm trying to get all rows with a particular id (not the primary key, is just a regular field), so it'll be 'a ton' of rows. I used findFirst to get the id from ClientAdAccountId, but many rows have it in myModel. Or do I misunderstand you?

When dumping $myData you will get info from the whole DI, so maybe this is confusing you.

Do so:

$myData = myModel::findByCustomerId($clientAccountId->accountId);
echo '<pre>';
print_r($myData->toArray());
exit;

Tell us if you see the expected results first and then we will debug further :)

Notice how i used magic methods. Your code is safe in your example, but in other situation may lead to sql injections.



1.1k
edited Jun '17

Here's what I got:

Array
(
)

If I run the following in MySQL workbench, I get a ton of rows, as expected:

SELECT * FROM mytable where customerId = "123456789"

My model only has an initialize function, which sets source to the correct table. When I var dump the $myData, I do see the data in it...i think. Certainly looks like it's there.

*Edit I might be wrong about data being there. I just spit out the whole $myData again, and it looks like it's just the column names from various tables? I must be asking the model incorrectly or the model is not setup (by me) correctly?

Here's the model: use Phalcon\Mvc\Model; use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

class myModel extends Model
{  
    public function initialize()
    {
      $this->setSource('mytable');
    }
}

Are you sure $clientAccountId->accountId contains valid id? Do records exist with such id? Did you dump it ? :)



1.1k

If I recall correctly, I did dump it and all was well. I just wiped out the entire project and now I'm trying to install the dev tools so they can create the Models for me, but even THAT is failing. (Made a new discussion for that).



1.1k

Ok, devtools in and I'm creating Models using them. What could possibly go wrong? :)



1.1k

Ok, had the devtools create all the models. Things appear ok for the most part, but I'm still having an issue getting data.

The code I put in the IndexController indexAction:

$company = Company::findFirst(); //This returns something, they echo below spits out one of the company names in the database

//This returns nothing
//$company = Company::find([ 'conditions' => "name = 'Our Company Name'" ]); 

//Nor does this
//$company = Company::query()     //Nor does this
//                ->where('companyId = 178')
//                ->execute();

echo 'The company name is ', $company->name, "\n";
die();

Sigh. Phalcon makes me feel stupid.



1.1k
edited Jun '17

I tried this:

   $query = $this->modelsManager->createQuery('SELECT * FROM company WHERE name = "Our Company Name"');
    $companies  = $query->execute();
    foreach ($companies as $company) 
    {
        echo 'Name: ', $company->name, "\n";
    }

Got this:

The company name is Can't obtain model's source from models list: 'Company', when preparing: SELECT * FROM company WHERE name = "Our Company Name"

0 [internal function]: Phalcon\Mvc\Model\Query->_getQualified(Array)

1 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array, true)

2 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array)

3 [internal function]: Phalcon\Mvc\Model\Query->_prepareSelect()

4 [internal function]: Phalcon\Mvc\Model\Query->parse()

5 /var/www/html/dashboard/app/controllers/DareportController.php(24): Phalcon\Mvc\Model\Query->execute()

6 [internal function]: DareportController->indexAction()

7 [internal function]: Phalcon\Dispatcher->callActionMethod(Object(DareportController), 'indexAction', Array)

8 [internal function]: Phalcon\Dispatcher->_dispatch()

9 [internal function]: Phalcon\Dispatcher->dispatch()

10 /var/www/html/dashboard/public/index.php(42): Phalcon\Mvc\Application->handle()

11 {main}



1.1k

Ok, I was being stupid on the first find(), it returns more than one item, and I should have foreach'd.

This worked:

$companies = Company::find(
        [
            "name = 'Our Company Name'",
            'order' => 'name',
        ]
    );
    foreach ($companies as $company) 
    {
        echo 'Name: ', $company->name, "\n";
    }

I'm going to keep plugging away at this. Thank you for your help!



1.1k

If anyone ends up wondering what I did, I simply create a BaseModel in the models directory, and changin all other models to extend THAT, which itself extends the phalcon model. In the BaseModel I put:

public function rawSql($sql)
    {
        $di             = \Phalcon\DI::getDefault();
        $db             = $di['db'];
        $data           = $db->query( $sql );
        $data->setFetchMode(\Phalcon\Db::FETCH_OBJ);
        $results        = $data->fetchAll();
        return $results;
    }

And I just write normal SQL in my other controllers the feed it to:

return $this->rawSQL($sql);