Webmaster forum - php constant in msql query

Author

php constant in msql query

Karl Groves

2006-08-03, 11:10 pm

The below query throws an error: Warning: mysql_num_rows(): supplied
argument is not a valid mysql result resource

I think it is unhappy about the constant in the LIMIT

What am I doing wrong?

SELECT
dms_user_logs.user_log_id,
dms_user_logs.user_log_date,
dms_users.dms_user_id,
dms_users.dms_user_first_name,
dms_users.dms_user_middle_name,
dms_users.dms_user_last_name
FROM
dms_user_logs,
dms_users
WHERE
dms_users.dms_user_id = dms_user_logs.dms_user_id
LIMIT
$start, PAGINATION_COUNT
ORDER BY
user_log_id DESC

--
Karl Groves
www.karlcore.com

Karl Groves

2006-08-03, 11:10 pm

Karl Groves <karl@NOSPAMkarlcore.com> wrote in
news:Xns9814619A56983karlkarlcorecom@216.196.97.136:

> The below query throws an error: Warning: mysql_num_rows(): supplied
> argument is not a valid mysql result resource
>
> I think it is unhappy about the constant in the LIMIT
>
> What am I doing wrong?
>
>

Correction. I was having two problems. Now I'm only having the one relating
to the constant

SELECT
dms_user_logs.user_log_id,
dms_user_logs.user_log_date,
dms_users.dms_user_id,
dms_users.dms_user_first_name,
dms_users.dms_user_middle_name,
dms_users.dms_user_last_name
FROM
dms_user_logs,
dms_users
WHERE
dms_users.dms_user_id = dms_user_logs.dms_user_id
ORDER BY
user_log_id DESC
LIMIT
$start, PAGINATION_COUNT

--
Karl Groves
www.karlcore.com

Marc Bissonnette

2006-08-03, 11:10 pm

Karl Groves <karl@NOSPAMkarlcore.com> altered the spacetime fabric by
disgorging news:Xns9814632FEE29Ekarlkarlcorecom@216.196.97.136:

> Karl Groves <karl@NOSPAMkarlcore.com> wrote in
> news:Xns9814619A56983karlkarlcorecom@216.196.97.136:
>
>
> Correction. I was having two problems. Now I'm only having the one
> relating to the constant
>
> SELECT
> dms_user_logs.user_log_id,
> dms_user_logs.user_log_date,
> dms_users.dms_user_id,
> dms_users.dms_user_first_name,
> dms_users.dms_user_middle_name,
> dms_users.dms_user_last_name
> FROM
> dms_user_logs,
> dms_users
> WHERE
> dms_users.dms_user_id = dms_user_logs.dms_user_id
> ORDER BY
> user_log_id DESC
> LIMIT
> $start, PAGINATION_COUNT
>

Have you echoed the actual query to the screen to see what the value of
$start is ?

Is `PAGINATION_COUNT` a mysql field var, or is it something supplied by
the browser ? (Doesn't look like a variable to me...)

--
Marc Bissonnette
Looking for a new ISP? http://www.canadianisp.com
Largest ISP comparison site across Canada.

Karl Groves

2006-08-03, 11:10 pm

Marc Bissonnette <dragnet\_@_/internalysis.com> wrote in
news:Xns9814669EE7B8Edragnetinternalysisc@216.196.97.131:

> Karl Groves <karl@NOSPAMkarlcore.com> altered the spacetime fabric by
> disgorging news:Xns9814632FEE29Ekarlkarlcorecom@216.196.97.136:
>
>
> Have you echoed the actual query to the screen to see what the value
of
> $start is ?

Yes. I'm setting it to 0 if it isn't set already. Everything is fine as
far as $start is concerned

> Is `PAGINATION_COUNT` a mysql field var, or is it something supplied
by
> the browser ? (Doesn't look like a variable to me...)

PAGINAGION_COUNT is a constant

--
Karl Groves
www.karlcore.com

Beauregard T. Shagnasty

2006-08-03, 11:10 pm

Karl Groves wrote:

> Marc Bissonnette <dragnet\_@_/internalysis.com> wrote:
>
> Yes. I'm setting it to 0 if it isn't set already. Everything is fine
> as far as $start is concerned

So is the value a zero? Why not set its initial value to 1, so you get
a result set with some rows in it?

SELECT ... FROM ... LIMIT 0 returns no rows, hence mysql_num_rows() is
tossing an error.

That said, if there is any chance there will/could be an empty
recordset, you should always test for that before doing anything else
with the result.

--
-bts
-Warning: I brake for lawn deer

oeb

2006-08-03, 11:10 pm

Karl Groves wrote:
> The below query throws an error: Warning: mysql_num_rows(): supplied
> argument is not a valid mysql result resource
>
> I think it is unhappy about the constant in the LIMIT
>
> What am I doing wrong?
>

See does surrounding the constant in curly braces work

>
> SELECT
> dms_user_logs.user_log_id,
> dms_user_logs.user_log_date,
> dms_users.dms_user_id,
> dms_users.dms_user_first_name,
> dms_users.dms_user_middle_name,
> dms_users.dms_user_last_name
> FROM
> dms_user_logs,
> dms_users
> WHERE
> dms_users.dms_user_id = dms_user_logs.dms_user_id
> LIMIT
> $start, {PAGINATION_COUNT}
> ORDER BY
> user_log_id DESC
>

GreyWyvern

2006-08-03, 11:10 pm

And lo, Beauregard T. Shagnasty didst speak in alt.www.webmaster:

> That said, if there is any chance there will/could be an empty
> recordset, you should always test for that before doing anything else
> with the result.

The error Karl was getting was "not a valid mysql result resource". A
record-set with zero rows is still a valid resource. The error message
means there was an error in the mysql query itself.

Karl, since you are spanning databases, it may be that your ORDER BY
expression does not reference a database name, like all your other
values. Just a first guess...

Grey

--
The technical axiom that nothing is impossible sinisterly implies the
pitfall corollary that nothing is ridiculous.
- http://www.greywyvern.com/orca#search - Orca Search: Full-featured
spider and site-search engine

GreyWyvern

2006-08-03, 11:10 pm

And lo, GreyWyvern didst speak in alt.www.webmaster:

> Karl, since you are spanning databases, it may be that your ORDER BY
> expression does not reference a database name, like all your other
> values. Just a first guess...

I mean *tables* !!! s/database/table/

Grey

--
The technical axiom that nothing is impossible sinisterly implies the
pitfall corollary that nothing is ridiculous.
- http://www.greywyvern.com/orca#search - Orca Search: Full-featured
spider and site-search engine

Rodney Pont

2006-08-03, 11:10 pm

On Thu, 03 Aug 2006 08:35:52 -0500, Karl Groves wrote:

>The below query throws an error: Warning: mysql_num_rows(): supplied
>argument is not a valid mysql result resource
>
>I think it is unhappy about the constant in the LIMIT
>
>What am I doing wrong?
>
>
>SELECT
> dms_user_logs.user_log_id,
> dms_user_logs.user_log_date,
> dms_users.dms_user_id,
> dms_users.dms_user_first_name,
> dms_users.dms_user_middle_name,
> dms_users.dms_user_last_name
>FROM
> dms_user_logs,
> dms_users
>WHERE
> dms_users.dms_user_id = dms_user_logs.dms_user_id

I don't see dms_user_id, try:
dms_users.dms_user_id = dms_user_logs.user_log_id

>LIMIT
> $start, PAGINATION_COUNT
>ORDER BY
> user_log_id DESC
>
>--
>Karl Groves
>www.karlcore.com

--
Regards - Rodney Pont
The from address exists but is mostly dumped,
please send any emails to the address below
e-mail ngpsm4 (at) infohitsystems (dot) ltd (dot) uk

Karl Groves

2006-08-03, 11:10 pm

oeb <what.will.i.do.with.a.gig@XXXXXXXXXX> wrote in news:eat4n2$cd5$1
@reader01.news.esat.net:

> Karl Groves wrote:
>
> See does surrounding the constant in curly braces work
>

Nope.
ugh

--
Karl Groves
www.karlcore.com

Beauregard T. Shagnasty

2006-08-03, 11:10 pm

GreyWyvern wrote:

> And lo, Beauregard T. Shagnasty didst speak in alt.www.webmaster:
>
>
> The error Karl was getting was "not a valid mysql result resource".
> A record-set with zero rows is still a valid resource. The error
> message means there was an error in the mysql query itself.

Yeah, that would be so. The error I was thinking about, was trying to
have code actually display a recordset that contained no records. <g>

--
-bts
-Warning: I brake for lawn deer

Karl Groves

2006-08-03, 11:10 pm

GreyWyvern <spam@greywyvern.com> wrote in
news:op.tdpsehoksl6xfd@news.nas.net:

> And lo, GreyWyvern didst speak in alt.www.webmaster:
>
>
> I mean *tables* !!! s/database/table/
>

The query works if I make a variable equivalent to the constant,
i.e. $page = PAGINATION_COUNT;
and then place $page in the query instead, but that's doing exactly what I
didn't want to do - making a variable out of something I want as a constant
value

--
Karl Groves
www.karlcore.com

Karl Groves

2006-08-03, 11:10 pm

Karl Groves <karl@NOSPAMkarlcore.com> wrote in
news:Xns981479EABBF2Bkarlkarlcorecom@216.196.97.136:

> GreyWyvern <spam@greywyvern.com> wrote in
> news:op.tdpsehoksl6xfd@news.nas.net:
>
>
> The query works if I make a variable equivalent to the constant,
> i.e. $page = PAGINATION_COUNT;
> and then place $page in the query instead, but that's doing exactly
> what I didn't want to do - making a variable out of something I want
> as a constant value
>

I forgot to add this:
If I echo the query when it is "... LIMIT $start, PAGINATION_COUNT" it
prints exactly that. IOW, 'PAGINATION_COUNT' is not making it to the query
as '20'.

If I echo the query when it is "...LIMIT $start, $page" (where $page =
PAGINATION_COUNT), it prints the query as intended: '...LIMIT 0, 20'

--
Karl Groves
www.karlcore.com

GreyWyvern

2006-08-03, 11:10 pm

And lo, Karl Groves didst speak in alt.www.webmaster:

> I forgot to add this:
> If I echo the query when it is "... LIMIT $start, PAGINATION_COUNT" it
> prints exactly that. IOW, 'PAGINATION_COUNT' is not making it to the
> query as '20'.

Try keeping it outside the quotes then:

"... LIMIT $start, ".PAGINATION_COUNT

Grey

--
The technical axiom that nothing is impossible sinisterly implies the
pitfall corollary that nothing is ridiculous.
- http://www.greywyvern.com/orca#search - Orca Search: Full-featured
spider and site-search engine

Marc Bissonnette

2006-08-03, 11:10 pm

GreyWyvern <spam@greywyvern.com> altered the spacetime fabric by disgorging
news:op.tdpttinpsl6xfd@news.nas.net:

> And lo, Karl Groves didst speak in alt.www.webmaster:
>
>
> Try keeping it outside the quotes then:
>
> "... LIMIT $start, ".PAGINATION_COUNT
>
> Grey
>

Waitasec; Maybe I'm missing something, 'cuz it's PHP and not PERL (dunno
PHP) - but how does the mysql engine know the value of PAGINATION_COUNT ?

Would it not be interpreting the string "PAGINATION_COUNT" as literally
that - a string and therefore invalid, because it's not a number ?

--
Marc Bissonnette
Looking for a new ISP? http://www.canadianisp.com
Largest ISP comparison site across Canada.

Karl Groves

2006-08-03, 11:10 pm

GreyWyvern <spam@greywyvern.com> wrote in
news:op.tdpttinpsl6xfd@news.nas.net:

> And lo, Karl Groves didst speak in alt.www.webmaster:
>
>
> Try keeping it outside the quotes then:
>
> "... LIMIT $start, ".PAGINATION_COUNT
>

SEXXY.
Works perfectly. Thanks

--
Karl Groves
www.karlcore.com