First post - need some ZF help!

Lewis · #1 (**permalink**) 06-04-2008, 08:20 PM

Hello

This is my first post on this forum. I am determined to learn the Zend Framework, and hope to gain much knowledge here.

Can somebody please help with the following?

/models/Clients.php

PHP Code:


			
<?php

class Clients extends Zend_Db_Table

{

protected $_name = 'webapp_clients';

}

Code:

INSERT INTO `webapp_clients` (`id`, `name`, `email`, `phone`) VALUES
(1, 'James Brown', 'james@brown.com', '01797 367586'),
(2, 'Joe Bloggs', 'joe@bloggs.com', '01797 869384'),
(3, 'Fred Flintstone', 'fred@flintstone.com', '01797 386745'),
(4, 'Barney Rubble', 'barney@rubble.com', '01797 980486');

/models/Invoices.php

PHP Code:


			
<?php

class Invoices extends Zend_Db_Table

{

protected $_name = 'webapp_invoices';

}

Code:

INSERT INTO `webapp_invoices` (`id`, `job_title`, `price`, `client_id`) VALUES
(1, 'Redesign Website', '600.00', '2'),
(2, 'Annual Hosting', '200.00', '4'),
(3, 'Domain Registration', '19.99', '4'),
(4, 'Telephone Support', '5.00', '3');

/controllers/InvoicesController.php

PHP Code:


			
<?php

class InvoicesController extends Zend_Controller_Action

{

function indexAction()

{

$this->view->title = "My Invoices";

$invoices = new Invoices();

$this->view->invoices = $invoices->fetchAll();

}

/views/scripts/invoices/index.phtml

PHP Code:


			
<table>

<tr>

<th>Job Title</th>

<th>Client Name</th>

<th>Price</th>

</tr>

<?php foreach($this->invoices as $invoice) : ?>

<tr>

<td><?php echo $this->escape($invoice->job_title);?></td>

<td><?php echo $this->escape($invoice->client_id);?></td> // This line should display the clients real name instead

<td><?php echo $this->escape($invoice->price);?></td>

</tr>

<?php endforeach; ?>

</table>

I have a simple application above that displays a list of recent invoices from the webapp_invoices table.

Both tables are linked by the (webapp_clients) id and the (webapp_invoices) client_id.

How do I grab and display the clients real name from the webapp_clients table instead of the client_id?

Thanks

Lewis

Howler9443 · #2 (**permalink**) 06-04-2008, 08:33 PM

Sounds like you want to do a join on your table.

I did something similar, I use a method in my model and create a select statement.

Here is an example of how I do it. There may be better, more efficient ways, but this works well.

Code:

	public function getItemsJoinedOnItemTypes()
	{
		$iSelect = $this->_db->select()
				->from(array('i' => 'items'), array('i.id', 'i.type_id', 'i.name', 'i.description'))
				->join(array('t' => 'item_types'), 'i.type_id = t.id', array('t.type'));
		$dbResults = $this->_db->fetchAll($iSelect);
		return $dbResults; 
	}

I hope this helps.

Lewis · #3 (**permalink**) 06-04-2008, 10:06 PM

Thank you for your reply!

That does sort of make sense, but I'm a little unsure as to how I would apply your example into my code, especially in relation to the MVC structure which is new to me.

In the past I would have used a SELECT query, ie SELECT FROM webapp_clients WHERE 'id' = '$row[client'id']' to get the relevent data from the db. The Zend Framework has a bit of a learning curve.

Any further help will be very much appreciated.

Lewis

Howler9443 · #4 (**permalink**) 06-05-2008, 03:28 PM

Hi Lewis,

I have that method in my 'Item' model. Because my 'Item' model has an item_type, which is an int pointing to a row in my 'ItemTypes' table.

So when I make a call to getItemsJoinedOnItemTypes() I get back everything I want and not just the id of the item type, but the actual item type information.

The key thing here is that I place and use this method in my model and make the call from the controller.

I hope that helps. Please let me know if I can be of further assistance.

Lewis · #5 (**permalink**) 06-05-2008, 08:54 PM

I'm sorry for being so stupid here. I've been googling this all over the place for many days and cannot find any simple explanation of this.

So in my case, would I create the method in /models/Clients.php, or in /models/Invoices.php? And how would I call it from my view?

I guess that I'm kind of asking you to spell it out for me, based on my existing code...

Thank you so much for taking the time!

Lewis

greenio · #6 (**permalink**) 06-06-2008, 10:17 AM

Invoices.php seems like the appropriate model to use here, you are fetching from webapp_invoices and doing a join to get the clients name in your query.

One thing is my models extends Zend_Db_Table_Abstract, not sure how much of a difference that makes

Howlers method seems about right, you just need to substitute his tables and fields for yours. something like this:

Code:

public function getInvoices()
{
	$select = $this->select()
			->from(array('i' => 'webapp_invoices'), array('i.job_title', 'i.price'))
			->join(array('c' => 'webapp_clients'), 'i.client_id = c.id', array('c.name'));
	return $this->fetchAll($select);
}

The from line first specifies i as an alias for webapp_invoices, and then comes an array of the fields you want to fetch.
The join line specifies c as an alias for webapp_clients, then comes the join args, and then an array of fields to fetch from the join table.

So we should have fetched the fields job_title, price and name (from the client table).

Disclaimer: There may be typos or blatant mistakes and/or misinformation in this post because im pretty new to Zf as well.

Edit: a link to this page might help: Zend Framework: Documentation

Lewis · #7 (**permalink**) 06-06-2008, 10:25 AM

Thank you for replying! I've been checking this forum regularly for updates to this post. I've spent a few days already trying to figure this out. The Zend documentation has a steep learning curve. I'm considering one of the Zend online training sessions but they are very pricey.

So I place that code in my Invoices.php model?

What code would I use in my view file to display the client name?

Lewis

greenio · #8 (**permalink**) 06-06-2008, 10:31 AM

I think you did it okay in your first example, place that code in the invoice model, call it from the controller (and assign the result to a view variable) like:

$this->view->invoices = $invoices->getInvoices();

and then echo it out like you did in your view file, you should now echo name instead of client_id tho, since thats what the query fetched.

<?php echo $this->escape($invoice->name);?>

Lewis · #9 (**permalink**) 06-06-2008, 11:37 AM

Thanks once again.

I have modified my code as you suggested. Here is the updated code :

/models/Clients.php

PHP Code:


			
<?php

class Clients extends Zend_Db_Table

{

protected $_name = 'webapp_clients';

}

Code:

INSERT INTO `webapp_clients` (`id`, `name`, `email`, `phone`) VALUES
(1, 'James Brown', 'james@brown.com', '01797 367586'),
(2, 'Joe Bloggs', 'joe@bloggs.com', '01797 869384'),
(3, 'Fred Flintstone', 'fred@flintstone.com', '01797 386745'),
(4, 'Barney Rubble', 'barney@rubble.com', '01797 980486');

/models/Invoices.php

PHP Code:


			
<?php

class Invoices extends Zend_Db_Table

{

protected $_name = 'webapp_invoices';



public function getInvoices()

{

    $select = $this->select()

            ->from(array('i' => 'webapp_invoices'), array('i.job_title', 'i.price'))

            ->join(array('c' => 'webapp_clients'), 'i.client_id = c.id', array('c.name'));

    return $this->fetchAll($select);

}

}

Code:

INSERT INTO `webapp_invoices` (`id`, `job_title`, `price`, `client_id`) VALUES
(1, 'Redesign Website', '600.00', '2'),
(2, 'Annual Hosting', '200.00', '4'),
(3, 'Domain Registration', '19.99', '4'),
(4, 'Telephone Support', '5.00', '3');

/controllers/InvoicesController.php

PHP Code:


			
<?php

class InvoicesController extends Zend_Controller_Action

{

function indexAction()

{

$this->view->title = "My Invoices";

$invoices = new Invoices();

$this->view->invoices = $invoices->getInvoices();

}

}

/views/scripts/invoices/index.phtml

PHP Code:


			
<table>

<tr>

<th>Job Title</th>

<th>Client Name</th>

<th>Price</th>

</tr>

<?php foreach($this->invoices as $invoice) : ?>

<tr>

<td><?php echo $this->escape($invoice->job_title);?></td>

<td><?php echo $this->escape($invoice->name);?></td>

<td><?php echo $this->escape($invoice->price);?></td>

</tr>

<?php endforeach; ?>

</table>

I am getting an error message :

Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty' in /home/zend.redcarrot.co.uk/public_html/webapp/library/Zend/Db/Statement/Pdo.php:68 Stack trace: #0 /home/zend.redcarrot.co.uk/public_html/webapp/library/Zend/Db/Statement.php(109): Zend_Db_Statement_Pdo->_prepare('') #1 /home/zend.redcarrot.co.uk/public_html/webapp/library/Zend/Db/Adapter/Pdo/Abstract.php(155): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Pdo_Mysql), '') #2 /home/zend.redcarrot.co.uk/public_html/webapp/library/Zend/Db/Adapter/Abstract.php(404): Zend_Db_Adapter_Pdo_Abstract->prepare('') #3 /home/zend.redcarrot.co.uk/public_html/webapp/library/Zend/Db/Adapter/Pdo/Abstract.php(205): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Table_Select), Array) #4 /home/zend.redcarrot.co.uk/public_html/webapp/library/Zend/Db/Table/Abstract.php(1184): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Table_Select)) #5 /home/zend.redcarrot.co.uk/public_html/weba in /home/zend.redcarrot.co.uk/public_html/webapp/library/Zend/Db/Statement/Pdo.php on line 68

Any ideas what could be wrong?

Lewis

Lewis · #10 (**permalink**) 06-07-2008, 10:57 AM

After days of searching Google, I have found a solution to the above error. I needed to add the following:

PHP Code:


			
->setIntegrityCheck(false)

to the select statement. So the code for /models/Invoices.php has now become:

PHP Code:


			
<?php

class Invoices extends Zend_Db_Table

{

protected $_name = 'webapp_invoices';



public function getInvoices()

{

    $select = $this->select()

    ->setIntegrityCheck(false)

            ->from(array('i' => 'webapp_invoices'), array('i.job_title', 'i.price'))

            ->join(array('c' => 'webapp_clients'), 'i.client_id = c.id', array('c.name'));

    return $this->fetchAll($select);

}

}

In all honestly, I don't know why this makes a difference, or what the code does. I'm more than a little frustrated with the Zend Framework right now. It is very hard to find support when needed, and I have spent days on this one problem alone.

Having said that, I very much appreciate the support that I have received in this thread. I hope that this forum continues to grow, as things are a little slow here right now.

OK, rant over

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode