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

$catagory = new Model_Documents_Griddyview();

$dbquery = "select NVL(MAX(DOCUMENT_ID),0)+1 MAXINNUM FROM ARC_DOCUMENT";

$result2=$catagory->rundbquery($dbquery);

$exindex .= $result2."/";

echo $exindex;public function rundbquery($dbquery) {
$dbusername = "xxx"; // DB User
$dbpass = "xxx"; // DB User password
$dbname = "xxx"; // DB Name
$conn = oci_connect ($dbusername, $dbpass, $dbname) OR die ('Unable to connect to the database. Error: <pre>'. print_r(oci_error(),1));

$query = $dbquery;

$stid2 = oci_parse($conn, $query);
$result = oci_execute($stid2);

$row2 = oci_fetch_array($stid2, OCI_BOTH);
return $row2[0];

}

Hi All,

Thank you very much for being concerned and replying to solve my problem, I truly appreciate it. Using Zend db Adapter rang a bell and i searched for it. Finally after going through few trials n errors; my requirement is complete and i am able to proceed further now. I can now send query to catch the data...for my frends who might find problem working with zend im attaching my code with this email..hope it helps some1 :)

thanx once again ya all :)

Tc of urselves :)
Regards,
YD

On Tue, Jun 15, 2010 at 2:47 AM, Bill Karwin <bill@karwin.com> wrote:

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

--
Regards,
YD