mysql query, help needed, - GoFuckYourself.com

cooldude7 · 08-07-2011, 06:42 AM

this is the original query and it throws whole data,

$result = mysql_query("SELECT content.*, content_views.views AS views FROM content, content_views WHERE content_views.content = content.record_num AND content.approved = 2 AND content.enabled = 1 ORDER BY encoded_date");

so i want to limit the values from column record_num from say 1 to 20k.
record_num is kind of index so i can limit the output .

how do i do this ?

thanks for your time.

blackmonsters · 08-07-2011, 06:43 AM

The best way to fix this is to stop using mysql.

cooldude7 · 08-07-2011, 06:46 AM

Quote:

Originally Posted by blackmonsters

The best way to fix this is to stop using mysql.

okie.,

thanks for your time. have a gr8 weekend.

woj · 08-07-2011, 06:49 AM

add "AND record_num<20000" to the WHERE clause? or am I misunderstanding what you want?

redwhiteandblue · 08-07-2011, 06:50 AM

$result = mysql_query("SELECT content.*, content_views.views AS views FROM content, content_views WHERE content_views.content = content.record_num AND content.approved = 2 AND content.enabled = 1 AND record_num<20000 ORDER BY encoded_date");

BTW defining an alias for content_views.views as "views" is pretty pointless in this query.

cooldude7 · 08-07-2011, 07:00 AM

Quote:

Originally Posted by redwhiteandblue

$result = mysql_query("SELECT content.*, content_views.views AS views FROM content, content_views WHERE content_views.content = content.record_num AND content.approved = 2 AND content.enabled = 1 AND record_num<20000 ORDER BY encoded_date");

BTW defining an alias for content_views.views as "views" is pretty pointless in this query.

damn this worked like charm, thanks a lot.
so for fetching 20k to 40k should i do something like this .

$result = mysql_query("SELECT content.*, content_views.views AS views FROM content, content_views WHERE content_views.content = content.record_num AND content.approved = 2 AND content.enabled = 1 AND record_num>20000 AND record_num<40000 ORDER BY encoded_date");

thanks for your time.

cooldude7 · 08-07-2011, 07:02 AM

Quote:

Originally Posted by woj

add "AND record_num<20000" to the WHERE clause? or am I misunderstanding what you want?

thanks for your time, but i am complete noob in case of mysql,
now i got the idea of using AND,

thanks a lot.

critical · 08-07-2011, 10:07 AM

If you are trying to obtain a range, just use mysql's range function:

May times the programmer needs to find out some specific data which lies in the particular range of values but there is not any predefined function in MySQL but we can also find that particular range's data from the database in MySQL using BETWEEN and AND clauses or write your own simple query.

Syntax:

exp BETWEEN min_value AND max_value
The above syntax is equivalent to the expression (min_value <= exp AND exp <= max_value).

In the following example we will describe you that how one can find out specific data lying between some range. To fire query we must have some data into a table therefore we will first create a table and fill some data into it.

Example:

SELECT id, name, subject from mca where
mca.id BETWEEN 2 AND 5;
Above query can also be written as below:

SELECT id, name, subject from mca where
( mca.id >=2 && mca.id <=5);

cooldude7 · 08-07-2011, 10:28 AM

Quote:

Originally Posted by critical

If you are trying to obtain a range, just use mysql's range function:

May times the programmer needs to find out some specific data which lies in the particular range of values but there is not any predefined function in MySQL but we can also find that particular range's data from the database in MySQL using BETWEEN and AND clauses or write your own simple query.

Syntax:

exp BETWEEN min_value AND max_value
The above syntax is equivalent to the expression (min_value <= exp AND exp <= max_value).

In the following example we will describe you that how one can find out specific data lying between some range. To fire query we must have some data into a table therefore we will first create a table and fill some data into it.

Example:

SELECT id, name, subject from mca where
mca.id BETWEEN 2 AND 5;
Above query can also be written as below:

SELECT id, name, subject from mca where
( mca.id >=2 && mca.id <=5);

thanks for your time.,

because of ur explaination i have found something very useful for me.,

Code:

mysql> SELECT 1 BETWEEN 2 AND 3;

now my query looks like this., with start point and exnd point.,

$result = mysql_query("SELECT content.*, content_views.views AS views FROM content, content_views WHERE content_views.content = content.record_num AND content.approved = 2 AND content.enabled = 1 AND record_num BETWEEN 1000 AND 1200 ORDER BY encoded_date");

thanks a lot.

Nathan · 08-07-2011, 10:51 AM

you have a flaw in your logic though. What if encoding is not done in the row records are added? Then your record_num 2001 might be encoded before record_num 2000, and your order by logic becomes useless..

Tempest · 08-07-2011, 02:33 PM

Quote:

Originally Posted by cooldude7

this is the original query and it throws whole data,

$result = mysql_query("SELECT content.*, content_views.views AS views FROM content, content_views WHERE content_views.content = content.record_num AND content.approved = 2 AND content.enabled = 1 ORDER BY encoded_date");

so i want to limit the values from column record_num from say 1 to 20k.
record_num is kind of index so i can limit the output .

how do i do this ?

thanks for your time.

LIMIT is used to grab "pages" of data..

SELECT content.*, content_views.views AS views FROM content, content_views WHERE content_views.content = content.record_num AND content.approved = 2 AND content.enabled = 1 ORDER BY encoded_date LIMIT 0,1000

LIMIT 0,1000 - get the first 1000 entries
LIMIT 1000,1000 - get the 2nd set of 1000 entries
LIMIT 2000,1000 - get the 3rd set of 1000 entries

If you don't use LIMIT, then really, you're ORDER BY has no meaning.

08-07-2011, 06:42 AM	#1
cooldude7 Confirmed User Industry Role: Join Date: Nov 2009 Location: Heaven Posts: 4,306	mysql query, help needed, this is the original query and it throws whole data, $result = mysql_query("SELECT content., content_views.views AS views FROM content, content_views WHERE content_views.content = content.record_num AND content.approved = 2 AND content.enabled = 1 ORDER BY encoded_date"); so i want to limit the values from column record_num* from say 1 to 20k. record_num is kind of index so i can limit the output . how do i do this ? thanks for your time. Last edited by cooldude7; 08-07-2011 at 06:43 AM..

08-07-2011, 06:43 AM	#2
blackmonsters Making PHP work Industry Role: Join Date: Nov 2002 Location: 🌎🌅🌈🌇 Posts: 20,227	The best way to fix this is to stop using mysql. __________________ Make Money with Porn

08-07-2011, 06:49 AM	#4
woj <&(©¿©)&> Industry Role: Join Date: Jul 2002 Location: Chicago Posts: 47,882	add "AND record_num<20000" to the WHERE clause? or am I misunderstanding what you want? __________________ Custom Software Development, email: woj#at#wojfun#.#com to discuss details or skype: wojl2000 or gchat: wojfun or telegram: wojl2000 Affiliate program tools: Hosted Galleries Manager Banner Manager Video Manager Wordpress Affiliate Plugin Pic/Movie of the Day Fansign Generator Zip Manager

08-07-2011, 06:50 AM	#5
redwhiteandblue Bollocks Industry Role: Join Date: Jun 2007 Location: Bollocks Posts: 2,792	$result = mysql_query("SELECT content.*, content_views.views AS views FROM content, content_views WHERE content_views.content = content.record_num AND content.approved = 2 AND content.enabled = 1 AND record_num<20000 ORDER BY encoded_date"); BTW defining an alias for content_views.views as "views" is pretty pointless in this query. __________________ Interserver unmanaged AMD Ryzen servers from $73.00

08-07-2011, 10:51 AM	#10
Nathan Confirmed User Industry Role: Join Date: Jul 2003 Posts: 3,108	you have a flaw in your logic though. What if encoding is not done in the row records are added? Then your record_num 2001 might be encoded before record_num 2000, and your order by logic becomes useless.. __________________ "Think about it a little more and you'll agree with me, because you're smart and I'm right." - Charlie Munger

08-07-2011, 10:07 AM	#8
critical Confirmed User Join Date: Aug 2009 Posts: 478	If you are trying to obtain a range, just use mysql's range function: May times the programmer needs to find out some specific data which lies in the particular range of values but there is not any predefined function in MySQL but we can also find that particular range's data from the database in MySQL using BETWEEN and AND clauses or write your own simple query. Syntax: exp BETWEEN min_value AND max_value The above syntax is equivalent to the expression (min_value <= exp AND exp <= max_value). In the following example we will describe you that how one can find out specific data lying between some range. To fire query we must have some data into a table therefore we will first create a table and fill some data into it. Example: SELECT id, name, subject from mca where mca.id BETWEEN 2 AND 5; Above query can also be written as below: SELECT id, name, subject from mca where ( mca.id >=2 && mca.id <=5);