Zend_Db_Adapter and UNIX_TIMESTAMP() function : how to ?

legal101205 · #1 (**permalink**) 04-19-2008, 06:16 PM

Inspired by Example 9.12. Quick creation of dates from database date values,

PHP Code:


			
// SELECT UNIX_TIMESTAMP(my_datetime_column) FROM my_table

require_once 'Zend/Date.php';



$date = new Zend_Date($unixtimestamp, Zend_Date::TIMESTAMP);

I did this:

PHP Code:


			
require_once 'Zend/Controller/Action.php';

require_once 'Zend/Db.php';

require_once 'Zend/Config/Ini.php';



class IndexController extends Zend_Controller_Action {

    protected function getDbAdapter() {

        $config = new Zend_Config_Ini ( CONFIG_PATH, 'staging' );

        $dbAdapter = Zend_Db::factory ( $config->database );

        return $dbAdapter;

    }

    

    public function indexAction() {

        $db = $this->getDbAdapter ();

        $data = $db->fetchAll ( 'SELECT UNIX_TIMESTAMP(start_date) AS date_unix * FROM tournament' );

        foreach ( $data as $row ) {

            echo $row [date_unix];

        }

    }

}

I got the following error message :

Code:

Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM tournament' at line 1

#0 C:\Program Files\Zend\Core\ZendFramework\library\Zend\Db\Statement.php(109): Zend_Db_Statement_Mysqli->_prepare('SELECT UNIX_TIM...')
#1 C:\Program Files\Zend\Core\ZendFramework\library\Zend\Db\Adapter\Mysqli.php(330): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), 'SELECT UNIX_TIM...')
#2 C:\Program Files\Zend\Core\ZendFramework\library\Zend\Db\Adapter\Abstract.php(405): Zend_Db_Adapter_Mysqli->prepare('SELECT UNIX_TIM...')
#3 C:\Program Files\Zend\Core\ZendFramework\library\Zend\Db\Adapter\Abstract.php(617): Zend_Db_Adapter_Abstract->query('SELECT UNIX_TIM...', Array)
#4 C:\Program Files\Zend\Apache2\htdocs\netabalone\application\modules\site\controllers\IndexController.php(15): Zend_Db_Adapter_Abstract->fetchAll('SELECT UNIX_TIM...')
#5 C:\Program Files\Zend\Core\ZendFramework\library\Zend\Controller\Action.php(502): IndexController->indexAction()
#6 C:\Program Files\Zend\Core\ZendFramework\library\Zend\Controller\Dispatcher\Standard.php(293): Zend_Controller_Action->dispatch('indexAction')
#7 C:\Program Files\Zend\Core\ZendFramework\library\Zend\Controller\Front.php(914): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#8 C:\Program Files\Zend\Core\ZendFramework\library\Zend\Controller\Front.php(223): Zend_Controller_Front->dispatch()
#9 C:\Program Files\Zend\Apache2\htdocs\netabalone\application\bootstrap.php(11): Zend_Controller_Front::run('../application/...')
#10 C:\Program Files\Zend\Apache2\htdocs\netabalone\web_root\index.php(2): require('C:\Program File...')
#11 {main}

Help!

Piro · #2 (**permalink**) 04-19-2008, 07:01 PM

You have an error in your query near *. I'm assuming you want to get the timestamp of a field and the rest of all the fields so you need to put a comma before the * sign.

Code:

SELECT UNIX_TIMESTAMP(start_date) AS date_unix, * FROM tournament

legal101205 · #3 (**permalink**) 04-19-2008, 08:11 PM

Thx for your reply.

Yes, my wish is well to take all the table content and the start_date column in the timestamp format.
But in this query, i was just assuming to select the start_date column.

I used your correction but i got the same error.

Ok i forgot to remove the * :

Code:

$data = $db->fetchAll ( 'SELECT UNIX_TIMESTAMP(start_date) AS date_unix FROM tournament' );

Is there a way like you said to get the timestamp of a field and the rest of all the fields without naming each other column in the query ?

Thx for your help.

Nicolas

Piro · #4 (**permalink**) 04-19-2008, 11:55 PM

Hi Nicolas,

There is

I should've put the * at the beginning of the query; the following should work:

Code:

SELECT *, UNIX_TIMESTAMP(start_date) AS date_unix FROM tournament

This way you get all the columns and the added date_unix field with the timestamps.

legal101205 · #5 (**permalink**) 04-20-2008, 12:08 AM

ush, terrible!

thx piro you helped me so much!

Best regards

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