RE: [PHP-DB] LIMIT
Here are some thoughts on the couple of methods shown: 1. SELECT count(*) as MyCount FROM aTable # Fast query done on server size SELECT * FROM aTable LIMIT 5 # Fast limited data return query done server side 2. SELECT * FROM aTable # Potentially slow query, lots of data stored in PHP mysql_num_rows() # Quick PHP-side function to tell how many results returned 3. SELECT SQL_CALC_FOUND_ROWS * FROM $table LIMIT 5 # Fast server side query that does the same as "SELECT count(*)" and "SELECT LIMIT 5" at the same time SELECT FOUND_ROWS() # Fast server side query Seems like #1 and #3 are your best options. Both are pretty easy on your system. #3 is definitely pretty slick, thanks to whoever originally posted that. But I'm wondering if it'll be fully compatible in the future. #1 is pretty "SQL 101" type stuff, so it should remain functional and be usable on other database platforms as well. That's the one I'd go for, just for the sake of keeping things simple if nothing else. Seems like a waste to do two database queries, but as long as you're not closing and re-opening the connection in between, it should cause very minimal impact on your system. Sometimes two queries are better than one. -TG = = = Original message = = = I see they call mysql_query twice which doesn't look like I'm gaining anything over running the 2 queries: SELECT count(*) as MyCount FROM aTable SELECT * FROM aTable LIMIT 5 But I suppose I need to time it for my app. http://us3.php.net/mysql_num_rows mancini at nextcode dot org 14-Nov-2005 02:24 here is a really fast mysql_num_rows alternative that makes use of the SELECT FOUND_ROWS() MySQL function , it only reads a single row and it is really helpfull if you are counting multiple tables with thousands of rows +++ alex dot feinberg 4t gm41l 28-Apr-2005 04:56 Re dzver at abv dot bg's note... I just ran some tests using MySQL Super Smack. Surprisingly, a SELECT * followed by a SELECT COUNT(*) actually was close in speed to a SELECT SQL_CALC_FOUND_ROWS * followed by a SELECT FOUND_ROWS(), but the SQL_CALC_FOUND_ROWS solution was still a bit faster. Perhaps it varies by table structure? Either way, it might be worth checking which is faster for your application. Regards, Dwight > -Original Message- > From: Dwight Altman [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 05, 2006 9:53 AM > To: 'php-db@lists.php.net' > Subject: RE: [PHP-DB] LIMIT > > So how do I get this information on the PHP side? > > mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name > -> WHERE id > 100 LIMIT 10; > mysql> SELECT FOUND_ROWS(); > > I count 2 queries above (unless there is some caching magic). Whatever. > I was hoping to add another column (like FOUND_ROWS) to an existing query > and be able to pull out the FOUND_ROWS when I loop over the result set. > Something like: > SELECT *, FOUND_ROWS FROM aTable LIMIT 5 > > But since that single query doesn't work, how do I apply the MySQL > solution and extract it on the PHP side? > > http://dev.mysql.com/doc/refman/4.1/en/information-functions.html > http://www.mysqlfreaks.com/statements/101.php > > > Regards, > Dwight > > > -Original Message- > > From: Chris [mailto:[EMAIL PROTECTED] > > Sent: Friday, June 30, 2006 9:46 AM > > To: php-db@lists.php.net > > Subject: Re: [PHP-DB] LIMIT > > > > If you're using MySQL then: > > > > SELECT SQL_CALC_FOUND_ROWS * FROM aTable LIMIT 5 > > SELECT FOUND_ROWS() > > > > It's in the mysql documentation under SELECT syntax I believe. > > > > Chris > > > > Dwight Altman wrote: > > > Is there a way to get the number of rows that would have been returned > > had > > > there not been a LIMIT clause in a SELECT statement? > > > > > > For example, if > > > Query #1) SELECT * FROM aTable > > > would normally return 100 rows. But > > > Query #2) SELECT * FROM aTable LIMIT 5 > > > will return 5 rows. Is there a way to find out that 100 rows would > have > > > been returned if there was no LIMIT clause, by using only Query #2 and > > maybe > > > a PHP function on the $result? > > > > > > Regards, > > > Dwight > > > > > > > > > > > > > -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] LIMIT
So how do I get this information on the PHP side? mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id > 100 LIMIT 10; mysql> SELECT FOUND_ROWS(); I count 2 queries above (unless there is some caching magic). Whatever. I was hoping to add another column (like FOUND_ROWS) to an existing query and be able to pull out the FOUND_ROWS when I loop over the result set. Something like: SELECT *, FOUND_ROWS FROM aTable LIMIT 5 But since that single query doesn't work, how do I apply the MySQL solution and extract it on the PHP side? http://dev.mysql.com/doc/refman/4.1/en/information-functions.html http://www.mysqlfreaks.com/statements/101.php Regards, Dwight > -Original Message- > From: Chris [mailto:[EMAIL PROTECTED] > Sent: Friday, June 30, 2006 9:46 AM > To: php-db@lists.php.net > Subject: Re: [PHP-DB] LIMIT > > If you're using MySQL then: > > SELECT SQL_CALC_FOUND_ROWS * FROM aTable LIMIT 5 > SELECT FOUND_ROWS() > > It's in the mysql documentation under SELECT syntax I believe. > > Chris > > Dwight Altman wrote: > > Is there a way to get the number of rows that would have been returned > had > > there not been a LIMIT clause in a SELECT statement? > > > > For example, if > > Query #1) SELECT * FROM aTable > > would normally return 100 rows. But > > Query #2) SELECT * FROM aTable LIMIT 5 > > will return 5 rows. Is there a way to find out that 100 rows would have > > been returned if there was no LIMIT clause, by using only Query #2 and > maybe > > a PHP function on the $result? > > > > Regards, > > Dwight > > > > > > > > -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] LIMIT
I see they call mysql_query twice which doesn't look like I'm gaining anything over running the 2 queries: SELECT count(*) as MyCount FROM aTable SELECT * FROM aTable LIMIT 5 But I suppose I need to time it for my app. http://us3.php.net/mysql_num_rows mancini at nextcode dot org 14-Nov-2005 02:24 here is a really fast mysql_num_rows alternative that makes use of the SELECT FOUND_ROWS() MySQL function , it only reads a single row and it is really helpfull if you are counting multiple tables with thousands of rows +++ alex dot feinberg 4t gm41l 28-Apr-2005 04:56 Re dzver at abv dot bg's note... I just ran some tests using MySQL Super Smack. Surprisingly, a SELECT * followed by a SELECT COUNT(*) actually was close in speed to a SELECT SQL_CALC_FOUND_ROWS * followed by a SELECT FOUND_ROWS(), but the SQL_CALC_FOUND_ROWS solution was still a bit faster. Perhaps it varies by table structure? Either way, it might be worth checking which is faster for your application. Regards, Dwight > -Original Message- > From: Dwight Altman [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 05, 2006 9:53 AM > To: 'php-db@lists.php.net' > Subject: RE: [PHP-DB] LIMIT > > So how do I get this information on the PHP side? > > mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name > -> WHERE id > 100 LIMIT 10; > mysql> SELECT FOUND_ROWS(); > > I count 2 queries above (unless there is some caching magic). Whatever. > I was hoping to add another column (like FOUND_ROWS) to an existing query > and be able to pull out the FOUND_ROWS when I loop over the result set. > Something like: > SELECT *, FOUND_ROWS FROM aTable LIMIT 5 > > But since that single query doesn't work, how do I apply the MySQL > solution and extract it on the PHP side? > > http://dev.mysql.com/doc/refman/4.1/en/information-functions.html > http://www.mysqlfreaks.com/statements/101.php > > > Regards, > Dwight > > > -Original Message----- > > From: Chris [mailto:[EMAIL PROTECTED] > > Sent: Friday, June 30, 2006 9:46 AM > > To: php-db@lists.php.net > > Subject: Re: [PHP-DB] LIMIT > > > > If you're using MySQL then: > > > > SELECT SQL_CALC_FOUND_ROWS * FROM aTable LIMIT 5 > > SELECT FOUND_ROWS() > > > > It's in the mysql documentation under SELECT syntax I believe. > > > > Chris > > > > Dwight Altman wrote: > > > Is there a way to get the number of rows that would have been returned > > had > > > there not been a LIMIT clause in a SELECT statement? > > > > > > For example, if > > > Query #1) SELECT * FROM aTable > > > would normally return 100 rows. But > > > Query #2) SELECT * FROM aTable LIMIT 5 > > > will return 5 rows. Is there a way to find out that 100 rows would > have > > > been returned if there was no LIMIT clause, by using only Query #2 and > > maybe > > > a PHP function on the $result? > > > > > > Regards, > > > Dwight > > > > > > > > > > > > > -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] LIMIT
Bastien Koert wrote: Your example predicates that the resultset it passed to an array which you then take the size of, which would only give you the LIMIT value if that clause is specified in the query... I see, I misunderstood the OPs question then they wanted to know that there are 25000 records that match their WHERE but only SELECT 100 of them. Gotcha ;p yeah thats pretty much got to be 2 queries, although if caching is involved it might not be that expensive... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] LIMIT
Your example predicates that the resultset it passed to an array which you
then take the size of, which would only give you the LIMIT value if that
clause is specified in the query...
Bastien
From: "JupiterHost.Net" <[EMAIL PROTECTED]>
To: php-db@lists.php.net
Subject: Re: [PHP-DB] LIMIT
Date: Sat, 01 Jul 2006 10:59:02 -0500
chris smith wrote:
On 7/1/06, Dwight Altman <[EMAIL PROTECTED]> wrote:
Thanks, but that's an additional query.
I was wondering if there may be a PHP function that can operate on the
$result or perhaps $link of the single query that uses a LIMIT clause and
I'm not sure how this would be done in PHP (simply because I am not a PHP
coder) but in Perl (as an example of the principle, so please keep your
flames at bay for th etime being ;p) I'd do this:
my $results_arrayref = $dbh->selectall_arrayref($query);
my $number_of_results = @{ $results_arrayref };
print "Number of results: $number_of_results\n";
for my $record( @{ $results_arrayref } ) {
# do what you want with each record...
}
I'm sure PHP has a way to count how many is in a given result set without
needing an additional query.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] LIMIT
I am not sure that any dbs will do thati usually run two queries (one for the limit data and one for the total rows) Bastien From: "chris smith" <[EMAIL PROTECTED]> To: "Dwight Altman" <[EMAIL PROTECTED]> CC: php-db@lists.php.net Subject: Re: [PHP-DB] LIMIT Date: Sat, 1 Jul 2006 18:51:11 +1000 On 7/1/06, Dwight Altman <[EMAIL PROTECTED]> wrote: Thanks, but that's an additional query. I was wondering if there may be a PHP function that can operate on the $result or perhaps $link of the single query that uses a LIMIT clause and have the information [count(*) had there not been a LIMIT clause "count_no_limit(*) maybe?" even though there was a LIMIT clause]. Something like mysql_info. Can the database do it like that? If the answer is no then there's no way php can do it. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] LIMIT
chris smith wrote:
On 7/1/06, Dwight Altman <[EMAIL PROTECTED]> wrote:
Thanks, but that's an additional query.
I was wondering if there may be a PHP function that can operate on the
$result or perhaps $link of the single query that uses a LIMIT clause and
I'm not sure how this would be done in PHP (simply because I am not a
PHP coder) but in Perl (as an example of the principle, so please keep
your flames at bay for th etime being ;p) I'd do this:
my $results_arrayref = $dbh->selectall_arrayref($query);
my $number_of_results = @{ $results_arrayref };
print "Number of results: $number_of_results\n";
for my $record( @{ $results_arrayref } ) {
# do what you want with each record...
}
I'm sure PHP has a way to count how many is in a given result set
without needing an additional query.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] LIMIT
On 7/1/06, Dwight Altman <[EMAIL PROTECTED]> wrote: Thanks, but that's an additional query. I was wondering if there may be a PHP function that can operate on the $result or perhaps $link of the single query that uses a LIMIT clause and have the information [count(*) had there not been a LIMIT clause "count_no_limit(*) maybe?" even though there was a LIMIT clause]. Something like mysql_info. Can the database do it like that? If the answer is no then there's no way php can do it. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] LIMIT
Thanks, but that's an additional query. I was wondering if there may be a PHP function that can operate on the $result or perhaps $link of the single query that uses a LIMIT clause and have the information [count(*) had there not been a LIMIT clause "count_no_limit(*) maybe?" even though there was a LIMIT clause]. Something like mysql_info. Regards, Dwight x2407 > -Original Message- > From: Frank M. Kromann [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 29, 2006 5:46 PM > To: Dwight Altman > Cc: php-db@lists.php.net > Subject: Re: [PHP-DB] LIMIT > > What about 'select count(*) from aTable' > > - Frank > > > Is there a way to get the number of rows that would have been returned > had > > there not been a LIMIT clause in a SELECT statement? > > > > For example, if > > Query #1) SELECT * FROM aTable > > would normally return 100 rows. But > > Query #2) SELECT * FROM aTable LIMIT 5 > > will return 5 rows. Is there a way to find out that 100 rows would > have > > been returned if there was no LIMIT clause, by using only Query #2 and > maybe > > a PHP function on the $result? > > > > Regards, > > Dwight > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] LIMIT
If you're using MySQL then: SELECT SQL_CALC_FOUND_ROWS * FROM aTable LIMIT 5 SELECT FOUND_ROWS() It's in the mysql documentation under SELECT syntax I believe. Chris Dwight Altman wrote: Is there a way to get the number of rows that would have been returned had there not been a LIMIT clause in a SELECT statement? For example, if Query #1) SELECT * FROM aTable would normally return 100 rows. But Query #2) SELECT * FROM aTable LIMIT 5 will return 5 rows. Is there a way to find out that 100 rows would have been returned if there was no LIMIT clause, by using only Query #2 and maybe a PHP function on the $result? Regards, Dwight -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] LIMIT
What about 'select count(*) from aTable' - Frank > Is there a way to get the number of rows that would have been returned had > there not been a LIMIT clause in a SELECT statement? > > For example, if > Query #1) SELECT * FROM aTable > would normally return 100 rows. But > Query #2) SELECT * FROM aTable LIMIT 5 > will return 5 rows. Is there a way to find out that 100 rows would have > been returned if there was no LIMIT clause, by using only Query #2 and maybe > a PHP function on the $result? > > Regards, > Dwight > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Limit chars from select
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gavin Amm wrote: > I'd like to avoid having to manually add all of the cols to the select > statement if possible, so if there's a universal command I can put in > that'd be best. In that case, what I would do is just do your SELECT statement, sans the SUBSTRING() function, and then use PHP substr() to manually display the first 30 characters. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFD3YLSj60GAoLuoDkRAlseAKD76nKk/heRkTqI3HPZFcgeuBkpbQCffTBE q6HiS8z3GdaghKFHcJOL284= =MA7K -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Limit chars from select
I'd like to avoid having to manually add all of the cols to the select statement if possible, so if there's a universal command I can put in that'd be best. Basically most of the fields are short.. 2 cols "action" and "comments" are text so can be a bit lengthy. The 1st page is a sumary page of a number of the rows, so what I'm wanting to do is limit the display on initial sumary page to say 100 chars per field and then if they click on the row for more info I can then display the full info. -Original Message- Sent: Monday, January 30, 2006 12:51 PM To: Gavin Amm; php-db@lists.php.net Subject: Re: [PHP-DB] Limit chars from select Gavin Amm wrote: > How do I limit the characters a Select statement returns? > In Seudo code: > SELECT * FROM myTable LIMITCHARS 30; > > I'm using MySQL. > > Thanks. > Do you want to limit the characters from an individual field? If so, then SELECT SUBSTRING(FIELD1 FROM 1 FOR 30) FROM myTable; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Limit chars from select
Gavin Amm wrote: > How do I limit the characters a Select statement returns? > In Seudo code: > SELECT * FROM myTable LIMITCHARS 30; > > I'm using MySQL. > > Thanks. > Do you want to limit the characters from an individual field? If so, then SELECT SUBSTRING(FIELD1 FROM 1 FOR 30) FROM myTable; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Limit
From: "Marie Osypian" <[EMAIL PROTECTED]> > I don't want to use the limit on this query anymore. What I want is to > display all that are dated within the last 3 months. What would be the easy > way to do that? > > > SELECT federal_development_id, UNIX_TIMESTAMP(date) AS date, topic, body > FROM federal_developments > WHERE pro_solutions = 'Y' AND date > CURDATE() - INTERVAL 3 MONTH > ORDER BY date DESC "; ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Limit
If it is a unix timestamp, why not just select rows where the date is >= 7776000 (which is 90 days) or if you want to be specific about the months, use mktime() to generate the timestamp for the date 3 months ago. -- Adam Alkins http://www.rasadam.com Quoting Marie Osypian <[EMAIL PROTECTED]>: > I don't want to use the limit on this query anymore. What I want is to > display all that are dated within the last 3 months. What would be the easy > way to do that? > > > SELECT federal_development_id, UNIX_TIMESTAMP(date) AS date, topic, body > FROM federal_developments > WHERE pro_solutions = 'Y' > ORDER BY date DESC > LIMIT 5"; > > > Thanks, > > MAO > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Limit return Size
> I would like to know how I limit my answer from a mysql database query to > display only a limited size? Is this done in the query or the php? > > i.e. Our Performance Results area includes First Quarter 2003 investment > returns for virtually every 529 savings programs. See how your 529 plan > performed... > > This question is alot longer but it was stopped and ... added when it > reached the desired size. Here is a great thread on Devshed that discusses various PHP and MySQL methods to accomplish this: http://forums.devshed.com/showthread.php?s=&threadid=23711&perpage=15&highlight=limit%20text%20from%20database&pagenumber=1 Enjoy. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Limit return Size
Or $sql = "SELECT concat(LEFT(columnName,255),"...") FROM tableName"; Gary Every Sr. UNIX Administrator Ingram Entertainment (615) 287-4876 "Pay It Forward" mailto:[EMAIL PROTECTED] http://accessingram.com > -Original Message- > From: Hutchins, Richard [mailto:[EMAIL PROTECTED] > Sent: Friday, June 20, 2003 2:46 PM > To: [EMAIL PROTECTED] > Subject: RE: [PHP-DB] Limit return Size > > > Try: > > $sql = "SELECT LEFT(columnName,255) FROM tableName"; > > Using that syntax should return the first 255 characters from > the selected > column, reading LEFT to right. I tested it in the command > line and it worked > just fine. > > Hope that helps. > > Rich > > > -Original Message- > > From: Marie Osypian [mailto:[EMAIL PROTECTED] > > Sent: Friday, June 20, 2003 3:06 PM > > To: [EMAIL PROTECTED] > > Subject: [PHP-DB] Limit return Size > > > > > > I would like to know how I limit my answer from a mysql > > database query to > > display only a limited size? Is this done in the query or the php? > > > > i.e. Our Performance Results area includes First Quarter > > 2003 investment > > returns for virtually every 529 savings programs. See how > > your 529 plan > > performed... > > > > This question is alot longer but it was stopped and ... > added when it > > reached the desired size. > > > > Thanks > > > > MAO > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >
RE: [PHP-DB] Limit return Size
Try: $sql = "SELECT LEFT(columnName,255) FROM tableName"; Using that syntax should return the first 255 characters from the selected column, reading LEFT to right. I tested it in the command line and it worked just fine. Hope that helps. Rich > -Original Message- > From: Marie Osypian [mailto:[EMAIL PROTECTED] > Sent: Friday, June 20, 2003 3:06 PM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] Limit return Size > > > I would like to know how I limit my answer from a mysql > database query to > display only a limited size? Is this done in the query or the php? > > i.e. Our Performance Results area includes First Quarter > 2003 investment > returns for virtually every 529 savings programs. See how > your 529 plan > performed... > > This question is alot longer but it was stopped and ... added when it > reached the desired size. > > Thanks > > MAO > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] "LIMIT" problem MSSQL
Also, since Sybase ASE uses a lot of the same T-SQL constructs, you can
attempt some methods located here:
http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12
--
Matt
"Noam Giladi" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> a solution offered i received from "raydan
>
> Untested:
> Untested:
> declare @num int
> set @num = 20
>
> EXEC ('select top 10 from myTable
>where ID not in (select top ' + @num + ' from myTable order by ID)
> order by ID')
>
> But beware the dangers of dynamic SQL.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] "LIMIT" problem MSSQL
a solution offered i received from "raydan
Untested:
Untested:
declare @num int
set @num = 20
EXEC ('select top 10 from myTable
where ID not in (select top ' + @num + ' from myTable order by ID)
order by ID')
But beware the dangers of dynamic SQL.
"Mark Snijders" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]
...
> $start = 10;
>
> $numbers_to_show = 25;
>
> $sql = "SELECT * FROM bla Limit $start, $numbers_to_show";
>
> or just go to mysql.com and use the manual :)
>
> -Original Message-
> From: Noam Giladi [mailto:[EMAIL PROTECTED]]
> Sent: woensdag 19 februari 2003 16:00
> To: [EMAIL PROTECTED]
> Subject: [PHP-DB] "LIMIT" problem MSSQL
>
>
> I'm trying to split results into different pages and number the pages
> accordingly. I've been able to build a fairly intelligent page numbering
> system which knows which rows it should pull (at least by numbering each
row
> numerically) but I don't know how to construct a SQL query to pull 10
> results starting at a certain number.
>
> please did anyone wrote a proc that do it?.
>
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] "LIMIT" problem MSSQL
Well if you do, (sort by id) this will work. On Wed, 2003-02-19 at 11:14, Noam Giladi wrote: i'm not using a fixed sort tnx noam "Adam Voigt" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Assuming your sorting by id, just do: SELECT TOP 50 * FROM TABLE WHERE id > $lastid And just have lastid posting to the page every time they hit next, and in lastid, put the last id of the results on that page. On Wed, 2003-02-19 at 09:59, Noam Giladi wrote: I'm trying to split results into different pages and number the pages accordingly. I've been able to build a fairly intelligent page numbering system which knows which rows it should pull (at least by numbering each row numerically) but I don't know how to construct a SQL query to pull 10 results starting at a certain number. please did anyone wrote a proc that do it?. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc signature.asc Description: This is a digitally signed message part
Re: [PHP-DB] "LIMIT" problem MSSQL
i'm not using a fixed sort tnx noam "Adam Voigt" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Assuming your sorting by id, just do: SELECT TOP 50 * FROM TABLE WHERE id > $lastid And just have lastid posting to the page every time they hit next, and in lastid, put the last id of the results on that page. On Wed, 2003-02-19 at 09:59, Noam Giladi wrote: I'm trying to split results into different pages and number the pages accordingly. I've been able to build a fairly intelligent page numbering system which knows which rows it should pull (at least by numbering each row numerically) but I don't know how to construct a SQL query to pull 10 results starting at a certain number. please did anyone wrote a proc that do it?. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc
Re: [PHP-DB] "LIMIT" problem MSSQL
Assuming your sorting by id, just do: SELECT TOP 50 * FROM TABLE WHERE id > $lastid And just have lastid posting to the page every time they hit next, and in lastid, put the last id of the results on that page. On Wed, 2003-02-19 at 09:59, Noam Giladi wrote: I'm trying to split results into different pages and number the pages accordingly. I've been able to build a fairly intelligent page numbering system which knows which rows it should pull (at least by numbering each row numerically) but I don't know how to construct a SQL query to pull 10 results starting at a certain number. please did anyone wrote a proc that do it?. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc signature.asc Description: This is a digitally signed message part
Re: [PHP-DB] "LIMIT" problem MSSQL
i assume this problem was solved in the past i didn't found a ready solution. i try to use FATCH . and i have another link with ths same problem.. http://forums.devshed.com/showthread.php?threadid=33114 tnx noam "John Krewson" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > The title suggests MSSQL, which if I am correct doesn't support Limit > like MySQL. > > I'm working on the same thing right now so I don't have a quick and > dirty working example, although it doesn't look to be too difficult to > use server side cursors in MSSQL. > > Here is a related a link to a similar question at phpbuilder: > http://www.phpbuilder.com/mail/php-db/2001062/0055.php > > Snijders, Mark wrote: > > $start = 10; > > > > $numbers_to_show = 25; > > > > $sql = "SELECT * FROM bla Limit $start, $numbers_to_show"; > > > > or just go to mysql.com and use the manual :) > > > > -Original Message- > > From: Noam Giladi [mailto:[EMAIL PROTECTED]] > > Sent: woensdag 19 februari 2003 16:00 > > To: [EMAIL PROTECTED] > > Subject: [PHP-DB] "LIMIT" problem MSSQL > > > > > > I'm trying to split results into different pages and number the pages > > accordingly. I've been able to build a fairly intelligent page numbering > > system which knows which rows it should pull (at least by numbering each row > > numerically) but I don't know how to construct a SQL query to pull 10 > > results starting at a certain number. > > > > please did anyone wrote a proc that do it?. > > > > > > > > > > -- > John Krewson > Programmer - SWORPS > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] "LIMIT" problem MSSQL
The title suggests MSSQL, which if I am correct doesn't support Limit like MySQL. I'm working on the same thing right now so I don't have a quick and dirty working example, although it doesn't look to be too difficult to use server side cursors in MSSQL. Here is a related a link to a similar question at phpbuilder: http://www.phpbuilder.com/mail/php-db/2001062/0055.php Snijders, Mark wrote: $start = 10; $numbers_to_show = 25; $sql = "SELECT * FROM bla Limit $start, $numbers_to_show"; or just go to mysql.com and use the manual :) -Original Message- From: Noam Giladi [mailto:[EMAIL PROTECTED]] Sent: woensdag 19 februari 2003 16:00 To: [EMAIL PROTECTED] Subject: [PHP-DB] "LIMIT" problem MSSQL I'm trying to split results into different pages and number the pages accordingly. I've been able to build a fairly intelligent page numbering system which knows which rows it should pull (at least by numbering each row numerically) but I don't know how to construct a SQL query to pull 10 results starting at a certain number. please did anyone wrote a proc that do it?. -- John Krewson Programmer - SWORPS -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] "LIMIT" problem MSSQL
$start = 10; $numbers_to_show = 25; $sql = "SELECT * FROM bla Limit $start, $numbers_to_show"; or just go to mysql.com and use the manual :) -Original Message- From: Noam Giladi [mailto:[EMAIL PROTECTED]] Sent: woensdag 19 februari 2003 16:00 To: [EMAIL PROTECTED] Subject: [PHP-DB] "LIMIT" problem MSSQL I'm trying to split results into different pages and number the pages accordingly. I've been able to build a fairly intelligent page numbering system which knows which rows it should pull (at least by numbering each row numerically) but I don't know how to construct a SQL query to pull 10 results starting at a certain number. please did anyone wrote a proc that do it?. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] LIMIT 0,10 retrieves 10 records, but takes forever!
thanx, I am gonna play with it Andy "Andrey Hristov" <[EMAIL PROTECTED]> schrieb im Newsbeitrag 004301c1d8ca$276af940$0b01a8c0@ANDreY">news:004301c1d8ca$276af940$0b01a8c0@ANDreY... > Another suggestion - use explicit joins. > SELECT c.*, p.province, co.country > FROM test.cities AS c LEFT JOIN test.countries AS co USING(country_c) > LEFT JOIN test.provinces AS p ON c.province_no=p.province_id > ORDER BY country , province > limit 0, 10; > > This might work. If not read the docs at http://www.mysql.com/doc/ > about joins and how to do LEFT(RIGHT) JOIN. > > Good luck. > > Andrey > > - Original Message - > From: "Andy" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Sunday, March 31, 2002 6:29 PM > Subject: [PHP-DB] LIMIT 0,10 retrieves 10 records, but takes forever! > > > > Hi there, > > > > I am having a problem understanding how limit works. As I thought limit > > restricts the amount of results > > to a given number. True.. the number of results is only that high how high I > > specify it with limit. > > > > But the querry takes as long as without limit. I am trying to avoid to long > > waiting times if a user is > > by exident searching for to many records. > > > > This is my querry: > > > > SELECT c.*, p.province, co.country > > FROM test.cities c, test.provinces p, test.countries co > > WHERE c.city like 'd%' > > AND p.province_no = c.province_id > > AND p.country_c = c.country_c > > AND c.country_c = co.country_c > > ORDER BY country , province > > limit 0, 10 > > > > This takes 30 s on a 2.5 mio entries table > > > > How could I really restrict the results to 10. So that mysql just returns > > after 10 results? > > > > Thanx, Andy > > > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] LIMIT 0,10 retrieves 10 records, but takes forever!
Another suggestion - use explicit joins. SELECT c.*, p.province, co.country FROM test.cities AS c LEFT JOIN test.countries AS co USING(country_c) LEFT JOIN test.provinces AS p ON c.province_no=p.province_id ORDER BY country , province limit 0, 10; This might work. If not read the docs at http://www.mysql.com/doc/ about joins and how to do LEFT(RIGHT) JOIN. Good luck. Andrey - Original Message - From: "Andy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, March 31, 2002 6:29 PM Subject: [PHP-DB] LIMIT 0,10 retrieves 10 records, but takes forever! > Hi there, > > I am having a problem understanding how limit works. As I thought limit > restricts the amount of results > to a given number. True.. the number of results is only that high how high I > specify it with limit. > > But the querry takes as long as without limit. I am trying to avoid to long > waiting times if a user is > by exident searching for to many records. > > This is my querry: > > SELECT c.*, p.province, co.country > FROM test.cities c, test.provinces p, test.countries co > WHERE c.city like 'd%' > AND p.province_no = c.province_id > AND p.country_c = c.country_c > AND c.country_c = co.country_c > ORDER BY country , province > limit 0, 10 > > This takes 30 s on a 2.5 mio entries table > > How could I really restrict the results to 10. So that mysql just returns > after 10 results? > > Thanx, Andy > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] LIMIT 0,10 retrieves 10 records, but takes forever!
LIMIT limits only to output. So if a SELECT creates 1 million rows and you LIMIT 10, you got the first 10 rows but the full SELECT is done. To speedup consider adding some id columnt with unique ints in ascending order. Andrey P.S. I've to go. Have a nice evening. - Original Message - From: "Andy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, March 31, 2002 6:29 PM Subject: [PHP-DB] LIMIT 0,10 retrieves 10 records, but takes forever! > Hi there, > > I am having a problem understanding how limit works. As I thought limit > restricts the amount of results > to a given number. True.. the number of results is only that high how high I > specify it with limit. > > But the querry takes as long as without limit. I am trying to avoid to long > waiting times if a user is > by exident searching for to many records. > > This is my querry: > > SELECT c.*, p.province, co.country > FROM test.cities c, test.provinces p, test.countries co > WHERE c.city like 'd%' > AND p.province_no = c.province_id > AND p.country_c = c.country_c > AND c.country_c = co.country_c > ORDER BY country , province > limit 0, 10 > > This takes 30 s on a 2.5 mio entries table > > How could I really restrict the results to 10. So that mysql just returns > after 10 results? > > Thanx, Andy > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] limit sytax error
Quoting from the MySQL manual: In MySQL Version 3.23, you can use LIMIT # to ensure that only a given number of rows are changed. So then, it's LIMIT # instead of LIMIT #,# Obviously, the way around it would be using WHERE instead of LIMIT, but I don't know your table structure so I can't provide any more details. Bogdan James Kupernik wrote: > when I try to do the update with a limit on which records, it gives me a > sytax error on the limit, is there anyway to get around this? > > UPDATE catalogs SET PROCESSED = 'Y' LIMIT $i,10 > > thanks > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] limit sytax error
Looks like LIMIT can only contain one number, when using UPATE the offset parameter cannot be added. http://www.mysql.com/doc/U/P/UPDATE.html UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1, [col_name2=expr2, ...] [WHERE where_definition] [LIMIT #] So . . . "UPDATE catalogs SET PROCESSED = 'Y' LIMIT $i,10" would have to be "UPDATE catalogs SET PROCESSED = 'Y' LIMIT $i" -Original Message- From: James Kupernik [mailto:[EMAIL PROTECTED]] Sent: Monday, January 21, 2002 11:56 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] limit sytax error when I try to do the update with a limit on which records, it gives me a sytax error on the limit, is there anyway to get around this? UPDATE catalogs SET PROCESSED = 'Y' LIMIT $i,10 thanks -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] LIMIT and get_num_rows
On Monday 14 January 2002 19:21, Markus Lervik wrote:
> Damnit.
> Forgot to cc to the list, again. Here it is.
>
> -- Forwarded Message --
>
> On Monday 14 January 2002 12:55, you wrote:
> > hi guys, just looking for verification on this, as i don't think there's
> > any way to do it
> >
> > Basically, i want to return the results for a search, but only return the
> > first 20 results from the start number given
> > LIMIT $start, 20
> > BUT... i'd like to have a page 1-whatever so if there are 65 results in
> > total, then the first 20 will be shown, but there will be options to move
> > to pages 2,3 or 4.
> > Obviously, if i use LIMIT, then it won't know that there are 65 in total,
> > so how would i get around this? do i just have to do the full query, and
> > then only use the first 20 results in the results set?
> > just becomes a bit of a problem if there are, say, 8000 results returned!
> > would it just be best to return the first 200 using LIMIT, and do my
> > pages for those, with a note that there are more than 200 results, and to
> > refine the search criteria?
> > Cheers,
> > Matt
>
> Matt,
>
> Here's how I did the "20 results per page". Messy, I know, but
> it was the only way I figured out how to do it.
>
> $query="SELECT * FROM table";
>
> $result=mysql_query($query,$database);
> $nr=mysql_num_rows($result);
> /* Here you can slap in a check for how many results
> you want, ie:
> */
> if($nr>200) {
> die("Bitch,whine and moan!");
> }
> $nr_pages=(ceil($nr/20));
>
> Or, you could, in the first SELECT statement put in a
> LIMIT 200, I suppose that would work, too.
> I'd have to dig into this myself too, as my database will
> have a tad over 100 000 records when it's done.
>
>
> Then I have two buttons, prev & next that's got a little
> javascript slapped on them;
>
> onclick="parent.location='nav.php?nav=true&go=prev';">
One potential problem (or feature, depending on which way you look at it!)
is that refreshing/reloading a page will jump to the next or previous page
depending on which action was last performed.
The way around this is to pass a position variable indicating which page of
results to view, rather than an 'action' variable.
--
Jason Wong -> Gremlins Associates -> www.gremlins.com.hk
/*
In real love you want the other person's good. In romantic love you
want the other person.
-- Margaret Anderson
*/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] LIMIT and get_num_rows
Damnit.
Forgot to cc to the list, again. Here it is.
-- Forwarded Message --
On Monday 14 January 2002 12:55, you wrote:
> hi guys, just looking for verification on this, as i don't think there's
> any way to do it
>
> Basically, i want to return the results for a search, but only return the
> first 20 results from the start number given
> LIMIT $start, 20
> BUT... i'd like to have a page 1-whatever so if there are 65 results in
> total, then the first 20 will be shown, but there will be options to move
> to pages 2,3 or 4.
> Obviously, if i use LIMIT, then it won't know that there are 65 in total,
> so how would i get around this? do i just have to do the full query, and
> then only use the first 20 results in the results set?
> just becomes a bit of a problem if there are, say, 8000 results returned!
> would it just be best to return the first 200 using LIMIT, and do my pages
> for those, with a note that there are more than 200 results, and to refine
> the search criteria?
> Cheers,
> Matt
Matt,
Here's how I did the "20 results per page". Messy, I know, but
it was the only way I figured out how to do it.
$query="SELECT * FROM table";
$result=mysql_query($query,$database);
$nr=mysql_num_rows($result);
/* Here you can slap in a check for how many results
you want, ie:
*/
if($nr>200) {
die("Bitch,whine and moan!");
}
$nr_pages=(ceil($nr/20));
Or, you could, in the first SELECT statement put in a
LIMIT 200, I suppose that would work, too.
I'd have to dig into this myself too, as my database will
have a tad over 100 000 records when it's done.
Then I have two buttons, prev & next that's got a little
javascript slapped on them;
etc.
Then, in nav.php:
if($go=="next") {
if($page < $nr_pages && $page >= 0) {
$page++;
$with= (($page) * 20).",";
$what = (($page-1) * 20).",";
$query=ereg_replace($what,$with,$query);
}
}
if($go=="prev") {
if($page>=1 && $page <= $nr_pages) {
$page--;
$with = (($page) * 20).",";
$what = (($page+1) * 20).",";
$query=ereg_replace($what,$with,$query);
}
}
$result=mysql_query($query,$database)
print_table(); //processes the query
If you got any questions regarding my code,
please do drop me a mail.
Cheers,
Markus
---
--
Markus Lervik
Linux-administrator with a kungfoo grip
Vaasa City Library - Regional Library
[EMAIL PROTECTED]
+358-6-325 3589 / +358-40-832 6709
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] LIMIT and get_num_rows
On Monday 14 January 2002 19:06, matt stewart wrote: > but presumably if i'm doing a SELECT count(*) FROM Designs WHERE Keywords > LIKE "%sport%" > followed by a SELECT * FROM Designs WHERE Keywords LIKE "%sport%" LIMIT > 0,20 > > it's still using nearly as much processing time as just returning all the > designs and just using a get_num_rows and then only using the first 20? > Possibly, I don't know. BUT if query returns 8000 records as opposed to 20, then it definitely uses up a bit more memory. This may be an issue if you have loads of such queries happening simultaneously. -- Jason Wong -> Gremlins Associates -> www.gremlins.com.hk /* Love is not enough, but it sure helps. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] LIMIT and get_num_rows
but presumably if i'm doing a SELECT count(*) FROM Designs WHERE Keywords LIKE "%sport%" followed by a SELECT * FROM Designs WHERE Keywords LIKE "%sport%" LIMIT 0,20 it's still using nearly as much processing time as just returning all the designs and just using a get_num_rows and then only using the first 20? -Original Message- From: Jonatan Bagge [mailto:[EMAIL PROTECTED]] Sent: 14 January 2002 11:00 To: matt stewart Subject: Re: [PHP-DB] LIMIT and get_num_rows First do a SELECT count(*) from tablename; This will get the number of entries... The rest should be simple... matt stewart wrote: hi guys, just looking for verification on this, as i don't think there's any way to do it Basically, i want to return the results for a search, but only return the first 20 results from the start number given LIMIT $start, 20 BUT... i'd like to have a page 1-whatever so if there are 65 results in total, then the first 20 will be shown, but there will be options to move to pages 2,3 or 4. Obviously, if i use LIMIT, then it won't know that there are 65 in total, so how would i get around this? do i just have to do the full query, and then only use the first 20 results in the results set? just becomes a bit of a problem if there are, say, 8000 results returned! would it just be best to return the first 200 using LIMIT, and do my pages for those, with a note that there are more than 200 results, and to refine the search criteria? Cheers, Matt --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system ( HYPERLINK "http://www.grisoft.com"http://www.grisoft.com). Version: 6.0.313 / Virus Database: 174 - Release Date: 02/01/02 -- PHP Database Mailing List ( HYPERLINK "http://www.php.net/"http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.313 / Virus Database: 174 - Release Date: 02/01/02 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.313 / Virus Database: 174 - Release Date: 02/01/02
Re: [PHP-DB] Limit Select Field Characters?
left(timestamp,6) - Original Message - From: "Jeff Oien" <[EMAIL PROTECTED]> To: "PHP-DB" <[EMAIL PROTECTED]> Sent: Wednesday, August 01, 2001 11:33 PM Subject: [PHP-DB] Limit Select Field Characters? > I have a timestamp field that looks like this > 0109011754 > but I only want to compare > 010801 > when doing a SELECT. Is there a way I can do this? Thanks. > Jeff Oien > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Limit Select Field Characters?
$fulldate = date("dmy");
echo $fulldate;
-Original Message-
From: Jeff Oien [mailto:[EMAIL PROTECTED]]
Sent: 01 August 2001 23:34
To: PHP-DB
Subject: [PHP-DB] Limit Select Field Characters?
I have a timestamp field that looks like this
0109011754
but I only want to compare
010801
when doing a SELECT. Is there a way I can do this? Thanks.
Jeff Oien
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] limit in informix
Hello, Luigi Barone wrote: > > I have the same problen of Fredrik > Is there any equivalent to MySQL's ?LIMIT x,y? in Informix Dynamic Server > 2000? As you may have noticed, I have just replied a similar message but about MS-SQL. The same Metabase base solution can work for Informix because Metabase also supports Informix. Manuel Lemos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] LIMIT in MS SQL
Hello. Fredrik Wahlberg wrote: > > Is there any equivalent to MySQL's ?LIMIT x,y? in Microsoft SQL-Server? You need to use server side cursors. You may want to look at how Metabase, the PHP database abstraction package does it when using MS-SQL. For Metabase users, it is as simple as calling the function MetabaseSetSelectedRowRange($database,$first,$limit) before each query. You may find Metabase here: http://phpclasses.UpperDesign.com/browse.html/package/20 You may also want to try this PHP Class that displays query results in HTML tables with links to go back and forth between pages. http://phpclasses.UpperDesign.com/browse.html/package/130 Manuel Lemos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] LIMIT in MS SQL
But with TOP I can't start on any row I want? I would like to be able to select rows 40 through 60 or something similar. /F >>>>>>>>>>>>>>>>>> Ursprungligt meddelande <<<<<<<<<<<<<<<<<< Trey Sheldon <[EMAIL PROTECTED]> skrev 2001-03-12, kl. 15:35:38 angående ämnet Re: [PHP-DB] LIMIT in MS SQL: > Yep there is- its called TOP > Example: > SELECT TOP 2 * > FROM Table > Fredrik Wahlberg wrote: > > Is there any equivalent to MySQL's ?LIMIT x,y? in Microsoft SQL-Server? > > > > /Fredrik > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] LIMIT in MS SQL
Yep there is- its called TOP Example: SELECT TOP 2 * FROM Table Fredrik Wahlberg wrote: > Is there any equivalent to MySQL's ?LIMIT x,y? in Microsoft SQL-Server? > > /Fredrik > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] LIMIT in MS SQL
I think so, but check the SS lang manual. It should be under the syntax for SELECT, maybe it's called "TOP". Haven't worked with SS for a while. MIles At 02:24 PM 3/12/01 +, Fredrik Wahlberg wrote: >Is there any equivalent to MySQL's "LIMIT x,y" in Microsoft SQL-Server? > >/Fredrik > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Limit number of persistent oci8-connections?
MaxClients inyour httpd.conf is a nice way to limit it. :) Karsten Dambekalns wrote: > > On Thu, Jan 25, 2001 at 08:22:07PM +0100, Matthias Kopolt wrote: > > Are you useing persistent connections (I had no real luck with them)? > > > > How do you connect to your DB over Multi Threaded Server (MTS) or Dedicated > > Server ? > > I am just using phplib for the dirty stuff :) So I don't know exactly how > this works under the hood.. > > > I think Oracles NET8 Config has a Parameter to limit this. > > Check out the MasterIndex at > > Well it is limited to 200 connections right now, but I would like to limit > that on the PHP side rather than on the Oracle side. Although I don't > exactly know why. It just seems right :) > > Karsten > -- > Why do we have to hide from the police, daddy? > Because we use emacs, son. They use vi. > - > mailto:[EMAIL PROTECTED] w³: http://www.k-fish.de/ > gpg: http://www.k-fish.de/mykeys.gpg > > >Part 1.2Type: application/pgp-signature -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Limit number of persistent oci8-connections?
On Thu, Jan 25, 2001 at 08:22:07PM +0100, Matthias Kopolt wrote: > Are you useing persistent connections (I had no real luck with them)? > > How do you connect to your DB over Multi Threaded Server (MTS) or Dedicated > Server ? I am just using phplib for the dirty stuff :) So I don't know exactly how this works under the hood.. > I think Oracles NET8 Config has a Parameter to limit this. > Check out the MasterIndex at Well it is limited to 200 connections right now, but I would like to limit that on the PHP side rather than on the Oracle side. Although I don't exactly know why. It just seems right :) Karsten -- Why do we have to hide from the police, daddy? Because we use emacs, son. They use vi. - mailto:[EMAIL PROTECTED] w³: http://www.k-fish.de/ gpg: http://www.k-fish.de/mykeys.gpg PGP signature
