Re: [fw-db] Need help REGARDING ZEND_DB MAX(ID)

On Jun 14, 2010, at 9:09 AM, Y D wrote:

the thing im facing problem is ZEND_DB....i find making queries in ZEND complicated...is there a way to simply make a query as a STRING and then execute it? 

Yes, certainly.  There's no requirement to use Zend_Db_Table or Zend_Db_Select.  You can submit plain SQL queries simply using the Zend_Db_Adapter interface.  This interface is based on PDO, with a few new convenience methods for quickly getting results.  For example:

$db = Zend_Db::factory(...options...);

$query = "SELECT NVL(MAX(INBOX_NUMBER),0)+1 AS MAX_INBOX_NUMBER

    FROM ARC_DOCUMENT";

$result = $db->fetchRow($query);

$max = $result->MAX_INBOX_NUMBER;

Just be sure to define a column alias in your SELECT query.

These methods are documented here:

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

Message: Specified column "INBOX_NUMBER" is not in the row

You didn't give an alias for your MAX() expression.  In SQL, the column name of an expression does not implicitly take the name of one of the columns involved in the expression.  Besides, what if the expression were to reference more than one column?  This is not related to Zend_Db, this is standard SQL behavior and you would run into this problem in any framework or language.

You can either declare an alias explicitly, or else refer to the column of the query result by its ordinal position instead of its name.  But Zend_Db_Table_Row only indexes fields by their name.

but the table ARC_DOCUMENT has the column named INBOX_NUMBER..where as INBOX_NUMBER is not the PRIMARY KEY of the table ARC_DOCUMENT...and i don't want to put a sequence on it or anything..

Well, that's going to be a problem if two people are using your app concurrently.  Both people will see a form contain MAX(INBOX_NUMBER)+1, and they'll both try to submit this value when they get around to submitting the form.  

This is a logic problem in your application workflow, not a fault of Zend_Db.  You would have the same problem no matter what framework or language you used.

Regards,

Bill Karwin