MVC Query Function

Im not 100% sure how to ask the following question but I will do my best.
I'm work with MVC on Zend Framework.

I have a script that pulls invoices from a DB for a specific user at a specific location.  However, I need to have the Query to pull all of the Invoices for a location, not just the ones that a specific user created:

My Script in the Viewr:
<table class="thleft spacing sortable" width="100%">
	<tr>
		<th><a href="<?= $this->sortLink('invoice_date') ?>">Invoice Date</a></th>
		<th><a href="<?= $this->sortLink('invoice_num') ?>">Invoice #</a></th>
		<th><a href="<?= $this->sortLink('vendor_name') ?>">Vendor Name</a></th>
		<th style="text-align: right"><a href="<?= $this->sortLink('total') ?>">Total</a></th>
		<th width="2"></th>
	</tr>
	<?php foreach ($this->paginator as $current)  : ?>
	<tr>
		<td><a href="<?= $this->url(array('controller'=>'reports','action'=>'invoice','id'=>$current->t_id), null, true) ?>"><?= date('m/d/Y', strtotime($current->invoice_date)) ?></a></td>
		<td><?= $current->invoice_num ?></td>
		<td><?= stripcslashes($current->vendor_name) ?></td>
		<td align="right">$ <?= number_format($current->total, 2) ?></td>
		<td>
			<?php if ($current->getPNL()->finalized != 1) : ?>
			<a class="delete_link" href="<?= $this->url(array('action'=>'delete-invoice','id'=>$current->t_id)) ?>">Delete</a>
			<?php endif; ?>
		</td>
	</tr>
	<?php endforeach; ?>
</table>

Open in new window


Now my function in the Controller:
public function invoicesAction()
{
	if ($this->_getParam('s')) {
		$sort = $this->_getParam('s') . ' ' . $this->_getParam('dir');
	}
	
	$db = Stax_Db::getConnection();
	$where = array();
	if ($this->_getParam('search') != "") {
		$where[] = 'vendor_name LIKE "%' . $this->_getParam('search') . '%"';
		$this->view->search = $this->_getParam('search');
	}
	if (is_numeric($this->_getParam('property'))) {
		$where[] = 'property_id = ' . $db->quote($this->_getParam('property'), Zend_Db::INT_TYPE);
		$this->view->property = $this->_getParam('property');
	}

	$where = count($where) ? $where : $this->getProps($db);

	$records = $this->user->getInvoiceReports(implode(' OR ', $where), $sort);

	$paginator = Zend_Paginator::factory($records);
	$paginator->setCurrentPageNumber($this->_getParam('page'));
	$paginator->setItemCountPerPage(50);

Open in new window


So, on line 20 of the above code I need to pull the info from the DB field property_id, not from the user (user_id).

What do I need to supply you with so you can help?
LVL 7
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
It would be helpful if we could see the fully resolved query string.  I don't know how to get Zend to show us that, but there is probably some kind of debugging hook that could produce it.  Maybe var_dump($this->user)?  There has to be some place that will contain the query.   Once we can see that, we can try to go "upstream" to find where the parts of the query string are created.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rgranlundAuthor Commented:
@ray_Paseur

Thanks for the suggestion.  I have been trying to find an online video tutorial that fully explains how zend works.  How it creates Querys is the final mystery to me.  I'll get back to ya as soon as I have more information.
0
rgranlundAuthor Commented:
@Ray
This is what I got when I did the Var_Dump:\

object(Stax_User_Manager)#53 (3) { ["_fields:protected"]=> array(14) { [0]=> string(7) "user_id" [1]=> string(8) "username" [2]=> string(8) "password" [3]=> string(6) "access" [4]=> string(14) "responsibility" [5]=> string(10) "first_name" [6]=> string(9) "last_name" [7]=> string(5) "phone" [8]=> string(5) "email" [9]=> string(16) "date_last_signin" [10]=> string(10) "date_added" [11]=> string(12) "date_updated" [12]=> string(6) "active" [13]=> string(5) "photo" } ["db:protected"]=> object(Zend_Db_Adapter_Pdo_Mysql)#54 (12) { ["_pdoType:protected"]=> string(5) "mysql" ["_numericDataTypes:protected"]=> array(16) { [0]=> int(0) [1]=> int(1) [2]=> int(2) ["INT"]=> int(0) ["INTEGER"]=> int(0) ["MEDIUMINT"]=> int(0) ["SMALLINT"]=> int(0) ["TINYINT"]=> int(0) ["BIGINT"]=> int(1) ["SERIAL"]=> int(1) ["DEC"]=> int(2) ["DECIMAL"]=> int(2) ["DOUBLE"]=> int(2) ["DOUBLE PRECISION"]=> int(2) ["FIXED"]=> int(2) ["FLOAT"]=> int(2) } ["_defaultStmtClass:protected"]=> string(21) "Zend_Db_Statement_Pdo" ["_config:protected"]=> array(8) { ["host"]=> string(9) "127.0.0.1" ["username"]=> string(13) "mrstaxin_user" ["password"]=> string(4) "u53r" ["dbname"]=> string(12) "mrstaxin_dev" ["charset"]=> string(4) "utf8" ["driver_options"]=> array(1) { [1002]=> string(16) "SET NAMES 'utf8'" } ["persistent"]=> bool(false) ["options"]=> array(3) { ["caseFolding"]=> int(0) ["autoQuoteIdentifiers"]=> bool(true) ["fetchMode"]=> int(2) } } ["_fetchMode:protected"]=> int(2) ["_profiler:protected"]=> object(Zend_Db_Profiler)#56 (4) { ["_queryProfiles:protected"]=> array(3) { [0]=> object(Zend_Db_Profiler_Query)#51 (5) { ["_query:protected"]=> string(7) "connect" ["_queryType:protected"]=> int(1) ["_startedMicrotime:protected"]=> float(1379711939.09) ["_endedMicrotime:protected"]=> float(1379711939.09) ["_boundParams:protected"]=> array(0) { } } [1]=> object(Zend_Db_Profiler_Query)#60 (5) { ["_query:protected"]=> string(45) "SELECT * FROM properties WHERE manager_id = ?" ["_queryType:protected"]=> int(32) ["_startedMicrotime:protected"]=> float(1379711939.09) ["_endedMicrotime:protected"]=> float(1379711939.09) ["_boundParams:protected"]=> array(1) { [1]=> string(1) "2" } } [2]=> object(Zend_Db_Profiler_Query)#70 (5) { ["_query:protected"]=> string(45) "SELECT * FROM properties WHERE manager_id = ?" ["_queryType:protected"]=> int(32) ["_startedMicrotime:protected"]=> float(1379711939.09) ["_endedMicrotime:protected"]=> float(1379711939.09) ["_boundParams:protected"]=> array(1) { [1]=> string(1) "2" } } } ["_enabled:protected"]=> bool(true) ["_filterElapsedSecs:protected"]=> NULL ["_filterTypes:protected"]=> NULL } ["_defaultProfilerClass:protected"]=> string(16) "Zend_Db_Profiler" ["_connection:protected"]=> object(PDO)#57 (0) { } ["_caseFolding:protected"]=> int(0) ["_autoQuoteIdentifiers:protected"]=> bool(true) ["_allowSerialization:protected"]=> bool(true) ["_autoReconnectOnUnserialize:protected"]=> bool(false) } ["_properties:protected"]=> array(13) { ["user_id"]=> string(1) "2" ["username"]=> string(13) "george.miller" ["password"]=> string(32) "f585c2d5bbf6f14b307e94f44298e74e" ["photo"]=> NULL ["access"]=> string(7) "manager" ["first_name"]=> string(6) "George" ["last_name"]=> string(6) "Miller" ["phone"]=> string(0) "" ["email"]=> string(0) "" ["date_last_signin"]=> string(19) "0000-00-00 00:00:00" ["date_added"]=> string(19) "2011-06-30 11:08:46" ["date_updated"]=> string(19) "2011-06-30 11:08:46" ["active"]=> string(1) "1" } }
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Ray PaseurCommented:
Actually, that looks helpful.  Here is how to make it readable.  Please go into wherever you've got the var_dump() statement and put this right before var_dump():

echo '<pre>';

The preformatting will cause the var_dump() output to be well organized.  Then you can copy and paste the var_dump() output into the code snippet here and we should be able to read it easily.
0
rgranlundAuthor Commented:
Edited to use the code snippet feature.

Please use the code snippet feature.  Click the word "Code" in the gray header bar above the edit box.  Paste the information between the BBCode tags.  It really saves a lot of time when we have unispace fonts and line numbers.  Thanks.

object(Stax_User_Manager)#53 (3) {
  ["_fields:protected"]=>
  array(14) {
    [0]=>
    string(7) "user_id"
    [1]=>
    string(8) "username"
    [2]=>
    string(8) "password"
    [3]=>
    string(6) "access"
    [4]=>
    string(14) "responsibility"
    [5]=>
    string(10) "first_name"
    [6]=>
    string(9) "last_name"
    [7]=>
    string(5) "phone"
    [8]=>
    string(5) "email"
    [9]=>
    string(16) "date_last_signin"
    [10]=>
    string(10) "date_added"
    [11]=>
    string(12) "date_updated"
    [12]=>
    string(6) "active"
    [13]=>
    string(5) "photo"
  }
  ["db:protected"]=>
  object(Zend_Db_Adapter_Pdo_Mysql)#54 (12) {
    ["_pdoType:protected"]=>
    string(5) "mysql"
    ["_numericDataTypes:protected"]=>
    array(16) {
      [0]=>
      int(0)
      [1]=>
      int(1)
      [2]=>
      int(2)
      ["INT"]=>
      int(0)
      ["INTEGER"]=>
      int(0)
      ["MEDIUMINT"]=>
      int(0)
      ["SMALLINT"]=>
      int(0)
      ["TINYINT"]=>
      int(0)
      ["BIGINT"]=>
      int(1)
      ["SERIAL"]=>
      int(1)
      ["DEC"]=>
      int(2)
      ["DECIMAL"]=>
      int(2)
      ["DOUBLE"]=>
      int(2)
      ["DOUBLE PRECISION"]=>
      int(2)
      ["FIXED"]=>
      int(2)
      ["FLOAT"]=>
      int(2)
    }
    ["_defaultStmtClass:protected"]=>
    string(21) "Zend_Db_Statement_Pdo"
    ["_config:protected"]=>
    array(8) {
      ["host"]=>
      string(9) ""
      ["username"]=>
      string(13) ""
      ["password"]=>
      string(4) ""
      ["dbname"]=>
      string(12) ""
      ["charset"]=>
      string(4) "utf8"
      ["driver_options"]=>
      array(1) {
        [1002]=>
        string(16) "SET NAMES 'utf8'"
      }
      ["persistent"]=>
      bool(false)
      ["options"]=>
      array(3) {
        ["caseFolding"]=>
        int(0)
        ["autoQuoteIdentifiers"]=>
        bool(true)
        ["fetchMode"]=>
        int(2)
      }
    }
    ["_fetchMode:protected"]=>
    int(2)
    ["_profiler:protected"]=>
    object(Zend_Db_Profiler)#56 (4) {
      ["_queryProfiles:protected"]=>
      array(3) {
        [0]=>
        object(Zend_Db_Profiler_Query)#51 (5) {
          ["_query:protected"]=>
          string(7) "connect"
          ["_queryType:protected"]=>
          int(1)
          ["_startedMicrotime:protected"]=>
          float(1379713731.75)
          ["_endedMicrotime:protected"]=>
          float(1379713731.75)
          ["_boundParams:protected"]=>
          array(0) {
          }
        }
        [1]=>
        object(Zend_Db_Profiler_Query)#60 (5) {
          ["_query:protected"]=>
          string(45) "SELECT * FROM properties WHERE manager_id = ?"
          ["_queryType:protected"]=>
          int(32)
          ["_startedMicrotime:protected"]=>
          float(1379713731.75)
          ["_endedMicrotime:protected"]=>
          float(1379713731.75)
          ["_boundParams:protected"]=>
          array(1) {
            [1]=>
            string(1) "2"
          }
        }
        [2]=>
        object(Zend_Db_Profiler_Query)#70 (5) {
          ["_query:protected"]=>
          string(45) "SELECT * FROM properties WHERE manager_id = ?"
          ["_queryType:protected"]=>
          int(32)
          ["_startedMicrotime:protected"]=>
          float(1379713731.76)
          ["_endedMicrotime:protected"]=>
          float(1379713731.76)
          ["_boundParams:protected"]=>
          array(1) {
            [1]=>
            string(1) "2"
          }
        }
      }
      ["_enabled:protected"]=>
      bool(true)
      ["_filterElapsedSecs:protected"]=>
      NULL
      ["_filterTypes:protected"]=>
      NULL
    }
    ["_defaultProfilerClass:protected"]=>
    string(16) "Zend_Db_Profiler"
    ["_connection:protected"]=>
    object(PDO)#57 (0) {
    }
    ["_caseFolding:protected"]=>
    int(0)
    ["_autoQuoteIdentifiers:protected"]=>
    bool(true)
    ["_allowSerialization:protected"]=>
    bool(true)
    ["_autoReconnectOnUnserialize:protected"]=>
    bool(false)
  }
  ["_properties:protected"]=>
  array(13) {
    ["user_id"]=>
    string(1) "2"
    ["username"]=>
    string(13) "george.miller"
    ["password"]=>
    string(32) "f585c2d5bbf6f14b307e94f44298e74e"
    ["photo"]=>
    NULL
    ["access"]=>
    string(7) "manager"
    ["first_name"]=>
    string(6) "George"
    ["last_name"]=>
    string(6) "Miller"
    ["phone"]=>
    string(0) ""
    ["email"]=>
    string(0) ""
    ["date_last_signin"]=>
    string(19) "0000-00-00 00:00:00"
    ["date_added"]=>
    string(19) "2011-06-30 11:08:46"
    ["date_updated"]=>
    string(19) "2011-06-30 11:08:46"
    ["active"]=>
    string(1) "1"
  }
} 

Open in new window

0
Ray PaseurCommented:
Have a look at lines 126 and 142.  Are these the queries that are limiting the results with the WHERE clause?
0
rgranlundAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for rgranlund's comment #a39510570

for the following reason:

@ray

Through this brief exercise I learned that you can have a function within a function.  That never dawned on me. I found where the actual function/query was being generated.  There I was able to edit the query to include not just the user_id but the property_id also.  

All in all this was a good exercise and turned on a much needed light bulb!

Thanks for your assistance and patience.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.