By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

if exists in php with UDB Linux

P: n/a
I need to see if some rows exist, but do not need to read them. The best
I could come up with is:

$sql = "SELECT 1 FROM $schema.auction_owners
WHERE auction_id=$ai
AND item_id=$item_id
FETCH FIRST 1 ROW ONLY
FOR READ ONLY
";
dosql($sql, "S AO4", -1, "", $n2, $res2);
if(odbc_fetch_array($res)) {
....
}
Feb 27 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
On Feb 27, 10:16 am, Bob Stearns <rstearns1...@charter.netwrote:
I need to see if some rows exist, but do not need to read them. The best
I could come up with is:

$sql = "SELECT 1 FROM $schema.auction_owners
WHERE auction_id=$ai
AND item_id=$item_id
FETCH FIRST 1 ROW ONLY
FOR READ ONLY
";
dosql($sql, "S AO4", -1, "", $n2, $res2);
if(odbc_fetch_array($res)) {
...

}- Hide quoted text -

- Show quoted text -
Your method certainly works. If you want to do something SQL-related
after you find if the rows exist or not, you may want to consider
using BEGIN ATOMIC:

BEGIN ATOMIC
IF ((SELECT 1 FROM X WHERE ... FETCH FIRST 1 ROW ONLY) = 1) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;

You could also combine your method with BEGIN ATOMIC, i.e.,

BEGIN ATOMIC
IF (NOT EXISTS(SELECT 1 FROM X WHERE ...)) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;
--Jeff

Feb 27 '07 #2

P: n/a
jefftyzzer wrote:
On Feb 27, 10:16 am, Bob Stearns <rstearns1...@charter.netwrote:
>>I need to see if some rows exist, but do not need to read them. The best
I could come up with is:

$sql = "SELECT 1 FROM $schema.auction_owners
WHERE auction_id=$ai
AND item_id=$item_id
FETCH FIRST 1 ROW ONLY
FOR READ ONLY
";
dosql($sql, "S AO4", -1, "", $n2, $res2);
if(odbc_fetch_array($res)) {
...

}- Hide quoted text -

- Show quoted text -


Your method certainly works. If you want to do something SQL-related
after you find if the rows exist or not, you may want to consider
using BEGIN ATOMIC:

BEGIN ATOMIC
IF ((SELECT 1 FROM X WHERE ... FETCH FIRST 1 ROW ONLY) = 1) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;

You could also combine your method with BEGIN ATOMIC, i.e.,

BEGIN ATOMIC
IF (NOT EXISTS(SELECT 1 FROM X WHERE ...)) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;
--Jeff
Thanks. I'm doing different things at the PHP level depending on the result.
Feb 27 '07 #3

P: n/a
Ray
On Feb 27, 12:48 pm, Bob Stearns <rstearns1...@charter.netwrote:
jefftyzzer wrote:
On Feb 27, 10:16 am, Bob Stearns <rstearns1...@charter.netwrote:
>I need to see if some rows exist, but do not need to read them. The best
I could come up with is:
>$sql = "SELECT 1 FROM $schema.auction_owners
WHERE auction_id=$ai
AND item_id=$item_id
FETCH FIRST 1 ROW ONLY
FOR READ ONLY
";
dosql($sql, "S AO4", -1, "", $n2, $res2);
if(odbc_fetch_array($res)) {
...
>}- Hide quoted text -
>- Show quoted text -
Your method certainly works. If you want to do something SQL-related
after you find if the rows exist or not, you may want to consider
using BEGIN ATOMIC:
BEGIN ATOMIC
IF ((SELECT 1 FROM X WHERE ... FETCH FIRST 1 ROW ONLY) = 1) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;
You could also combine your method with BEGIN ATOMIC, i.e.,
BEGIN ATOMIC
IF (NOT EXISTS(SELECT 1 FROM X WHERE ...)) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;
--Jeff

Thanks. I'm doing different things at the PHP level depending on the result.
Why not use SELECT COUNT (*) ?

Feb 28 '07 #4

P: n/a
On Feb 28, 7:23 am, "Ray" <raymmailbox-n...@yahoo.comwrote:
On Feb 27, 12:48 pm, Bob Stearns <rstearns1...@charter.netwrote:


jefftyzzer wrote:
On Feb 27, 10:16 am, Bob Stearns <rstearns1...@charter.netwrote:
>>I need to see if some rows exist, but do not need to read them. The best
>>I could come up with is:
>>$sql = "SELECT 1 FROM $schema.auction_owners
> WHERE auction_id=$ai
> AND item_id=$item_id
> FETCH FIRST 1 ROW ONLY
> FOR READ ONLY
> ";
>>dosql($sql, "S AO4", -1, "", $n2, $res2);
>>if(odbc_fetch_array($res)) {
>>...
>>}- Hide quoted text -
>>- Show quoted text -
Your method certainly works. If you want to do something SQL-related
after you find if the rows exist or not, you may want to consider
using BEGIN ATOMIC:
BEGIN ATOMIC
IF ((SELECT 1 FROM X WHERE ... FETCH FIRST 1 ROW ONLY) = 1) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;
You could also combine your method with BEGIN ATOMIC, i.e.,
BEGIN ATOMIC
IF (NOT EXISTS(SELECT 1 FROM X WHERE ...)) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;
--Jeff
Thanks. I'm doing different things at the PHP level depending on the result.

Why not use SELECT COUNT (*) ?- Hide quoted text -

- Show quoted text -
Please see the section titled "Tuning existential predicates" in the
following TinyURL link for an opinion on COUNT(*): http://tinyurl.com/2t3gac

--Jeff

Feb 28 '07 #5

P: n/a
Ray
On Feb 28, 12:40 pm, "jefftyzzer" <jefftyz...@sbcglobal.netwrote:
Please see the section titled "Tuning existential predicates" in the
following TinyURL link for an opinion on COUNT(*):http://tinyurl.com/2t3gac
I agree that irrelevant existential tests are a waste of time but I
don't see where that's the case when the whole reason for the query is
simply to establish existence to control program flow at a later time.
That said, I'm sure I'm overlooking something obvious to the more
experienced so what am I missing?

Mar 1 '07 #6

P: n/a
On Mar 1, 7:23 am, "Ray" <raymmailbox-n...@yahoo.comwrote:
On Feb 28, 12:40 pm, "jefftyzzer" <jefftyz...@sbcglobal.netwrote:
Please see the section titled "Tuning existential predicates" in the
following TinyURL link for an opinion on COUNT(*):http://tinyurl.com/2t3gac

I agree that irrelevant existential tests are a waste of time but I
don't see where that's the case when the whole reason for the query is
simply to establish existence to control program flow at a later time.
That said, I'm sure I'm overlooking something obvious to the more
experienced so what am I missing?
Hi, Ray.

I'm not saying you're missing anything. I think Serge's argument (the
author of the opinion I included) is that there's no sense counting up
a bunch of things only to throw this number away.

The better thing about EXISTS is that the minute a single row tests
true, the search stops, so there's the potential for a much faster
test.

Anyway, I think we're splitting hairs here. As in all things,
individual results will vary. Do what works :-)

Regards,

--Jeff

Mar 1 '07 #7

P: n/a
Ray
On Mar 1, 12:58 pm, "jefftyzzer" <jefftyz...@sbcglobal.netwrote:
I'm not saying you're missing anything. I think Serge's argument (the
author of the opinion I included) is that there's no sense counting up
a bunch of things only to throw this number away.
Thanks, Jeff. In fact I WAS missing a couple of things. First was the
explicit optimization of the select, which I now understand.
The better thing about EXISTS is that the minute a single row tests
true, the search stops, so there's the potential for a much faster
test.
Does that mean that DB2 will actually short circuit the select in the
infocenter example for exists?

EXISTS (SELECT * FROM TEMPL WHERE SALARY < 10000)

Mar 1 '07 #8

P: n/a
On 1 Mar 2007 12:52:04 -0800, "Ray" <ra**************@yahoo.com>
wrote:
>On Mar 1, 12:58 pm, "jefftyzzer" <jefftyz...@sbcglobal.netwrote:
>I'm not saying you're missing anything. I think Serge's argument (the
author of the opinion I included) is that there's no sense counting up
a bunch of things only to throw this number away.

Thanks, Jeff. In fact I WAS missing a couple of things. First was the
explicit optimization of the select, which I now understand.
>The better thing about EXISTS is that the minute a single row tests
true, the search stops, so there's the potential for a much faster
test.

Does that mean that DB2 will actually short circuit the select in the
infocenter example for exists?

EXISTS (SELECT * FROM TEMPL WHERE SALARY < 10000)
I think it should. EXISTS (SELECT * is the classic example of
short-circuiting.

B.
Mar 1 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.