2008年7月30日星期三

Re: [fw-db] Using the Zend_Db_Table to return referenced row values

I have resulted into using the Zend_Db Adapter class: PDO_MYSQL for my
complex joins. The Zend_Db_Table isnt very useful for my complex joins
especially for those that start joining from a lookup table right up to the
parent tables and might involve some cross column joins. Like the one i have
for this query:

//where am joining event.areaid = area.areaid. Zend_Db_Table cant resolve
that!!
SELECT e.eventName, v.VenueName, c.City, e.ToDate,
DATE(e.DateAdded) AS 'DateAdded', e.IsFeatured,
(e.ToDATE < CURDATE()) AS 'Expired'
FROM EventVenue ev LEFT JOIN Event e ON
ev.eventid=e.eventid LEFT JOIN Venue v ON
ev.venueid=v.venueid LEFT JOIN Area a
ON e.areaid=a.areaid LEFT JOIN City c ON
a.cityid=c.cityid GROUP BY e.EventName ORDER BY
e.ToDate DESC;

So i have resulted into encapuslating my sql query directly in my code
rather than doing it the OOP way. It is just not giving me the desired
results. I will keep those models i have that work on direct lookup but for
cases like the above mentioned i think going this route simply solves it

I just have to quote every value to avoid injection.

So this works perfectly for me with no issues whatsoever

//get all events in the db
public function getAllEvents() {

$db = Zend_Registry::get('db');
$sql = 'SELECT e.EventID,e.EventName, v.VenueName, c.City, e.FromDate,
e.IsFeatured, e.DateAdded, e.ToDate FROM EventVenue ev LEFT
JOIN Event e ON ev.eventid=e.eventid LEFT JOIN Venue v ON
ev.venueid=v.venueid LEFT JOIN Area a ON e.areaid=a.areaid LEFT JOIN City c
ON a.cityid=c.cityid GROUP BY e.EventName ORDER BY
e.ToDate DESC;';

$result = $db->query($sql);
$listEvents = $result->fetchAll();
return $listEvents;

}

Thanks


dele454 wrote:
>
> Thanks Bill you have really been a huge help to me. I couldnt agree less
> with your wife. All i have learnt about PHP and ZF as been self thought. I
> have never worked in a development dept before. All i know till date has
> been out of zeal and curiosity. i believe in delving into new territories
> and grounds - they is so much potential in just leaping unto growth. Am a
> firm believer of that.
>
>
> How do you find out the query joinLeftUsing() spills out from all those
> function calls to your query building?
>
>
> Thanks for those tips - much appreciated.
>
>
> Bill Karwin wrote:
>>
>>
>>
>> dele454 wrote:
>>>
>>> i cant remember you suggesting that. Must have been to someone else. Do
>>> I have to check apache's log files for that? Sorry if i sound so naive.
>>> i 'll really love to know how to do this. Instead of posting stuff every
>>> now and then.
>>>
>>
>> Yes, a typical configuration is for PHP errors to be captured in Apache's
>> error log (note the difference between the error log and the access log).
>> Where these files reside depends on your local Apache configuration.
>>
>> When I'm developing in PHP (or any other web scripting language) I open
>> another terminal window, run "tail -f" on the Apache error log file, and
>> leave that running continuously as I test the PHP scripts. If there are
>> no errors, this monitoring of the error log doesn't move. If there are
>> errors, I see them immediately. Sort of the "old-school" equivalent of
>> an IDE. :-)
>>
>> It's not a problem that you don't know certain things. I never fault
>> anyone for not knowing, because everyone has to start somewhere. My
>> wife sums it up nicely, she says, "nothing about computers is
>> 'intuitive'" (she's been a programmer for many years in Oracle PL/SQL,
>> Java, Perl, & PHP).
>>
>> And also I admit the PHP documentation online is not clear about where to
>> find the error output. In fact, I just tried to search for an article on
>> troubleshooting PHP errors to give you some reading, and found very
>> little available. Lots of articles about how to configure PHP to output
>> errors to custom locations, or how to use PHP error-reporting functions,
>> but not much about where the output goes by default.
>>
>> Regards,
>> Bill Karwin
>>
>
>


-----
dee
--
View this message in context: http://www.nabble.com/Using-the-Zend_Db_Table-to-return-referenced-row-values-tp18688983p18728777.html
Sent from the Zend DB mailing list archive at Nabble.com.

没有评论: