Re: [fw-db] Help on building query please

You should read up on Zend_Db_Select. It makes working with SQL much easier and supports most operations.

http://framework.zend.com/manual/en/zend.db.select.html

The code snippet below basically recreates the same SQL query, minus the $more_if_there_is_more extra fields and $alpha_sql_1 where clauses. Fortunately, those modifications can be made directly to the SQL object at any time before the query is executed.

<?php

$select = new Zend_Db_Select();

// Select from table website_programs

$select->from('website_programs')

// Join table websites

->join(

    'websites', // table to join

    'website_programs.websiteid = websites.websiteid', // join condition

    array(

        '*', // select all fields

        'days_old' => new Zend_Db_Expr('TO_DAYS(NOW()) - TO_DAYS(websites.logged)') // select custom field

    )

)

// Left join table top_sites

->joinLeft('top_sites',

    'top_sites.siteid=websites.websiteid',

    array(

        '*'

    )

)

// Where conditions

->where('website_programs.active = ?', 1)

->where('website.websiteid != ?', 'AST')

// Sort by

->order($sort)

// Limit

->limit($perpage, $offset)

;

// Fetch results

$stmt = $db->query($select);

$result = $stmt->fetchAll();

?>

-Hector

On Wed, Oct 15, 2008 at 12:30 PM, vladimirn <nezaboravi@gmail.com> wrote:

I have to rebuild an sql query to Zend. How to build this query with Zend_DB?
[code]
"SELECT *, (TO_DAYS(NOW()) -    TO_DAYS(websites.logged)) as days_old
$more_if_there_is_more
       FROM `website_programs`, `websites`
       LEFT JOIN `top_sites`
       ON top_sites.siteid=websites.websiteid
       AND top_sites.programid=website_programs.programid
       WHERE website_programs.programid='$program_select'
       AND website_programs.active='1' AND website_programs.websiteid =
websites.websiteid
       AND websites.websiteid != 'AST' $alpha_sql_1 $sort_by_sql_1 LIMIT
$offset, $perpage
[/code]

Thanks,
V
--
View this message in context: http://www.nabble.com/Help-on-building-query-please-tp20000576p20000576.html
Sent from the Zend DB mailing list archive at Nabble.com.

--
-Hector