CODEWITHSUNDEEP
phpmysqlsql
Pam Apple
Pam Apple

Reputation: 103

Select a range of rows from a table

i am very new to PHP and MySQL. I am trying to write a php script and i am stucking with the selection of rows from a table

The code that works great is:

$q_pages = $wpdb -> prefix . 'q_pages_table';

$q_kws = $wpdb->prefix . 'q_kws_table';

$sql = 'SELECT a1.post_id, a1.kw_id, a2.kw_name ' +
       'FROM ' . $q_pages . ' as a1 ' +
       'LEFT JOIN ' . $q_kws . ' as a2 ON (a1.kw_id = a2.id)' + 
       'WHERE a1.draft_id = 4';

The third command is to select and join columns from variable $q_kws to variable $q_pages if there is a match between kw_id and id in the 2 tables and also when the draft_id = 4

Because of the large number of rows in the $sql variable (3000 rows), i want to break $sql to 3 variables, each variable contains 1000 rows, so that later i can process each variable separately.

Is there any suggestion?

Thank you

Upvotes: 1

Views: 1325

Answers (3)

buzznfrog
buzznfrog

Reputation: 100

For example:

select * from table where rownum < 1000

Upvotes: 0

Jobin
Jobin

Reputation: 8282

You can use limit in the query to retrieve a specific number of rows .

something like 
$start=0;
$end=1000;

$sql = 'SELECT a1.post_id, a1.kw_id, a2.kw_name ' +
       'FROM ' . $q_pages . ' as a1 ' +
       'LEFT JOIN ' . $q_kws . ' as a2 ON (a1.kw_id = a2.id)' + 
       'WHERE a1.draft_id = 4 limit ".$start.",".$end."';

Upvotes: 0

Jake
Jake

Reputation: 4234

To select a specific range, you have to use limit [start number], [# to select] so in your case, you'd select the first 1000 rows like:

select [your select stuff]
limit 0, 1000

The second one would be:

select [your select stuff]
limit 1000, 1000

and the third would be

select [your select stuff]
limit 2000, 1000

The first number in the limit is the row that it starts at, and the second number is the number of rows after and including that row that will be selected

Upvotes: 1

Related Questions