Mysql – a query offset

kohanaMySQL

I saw this at the Kohana documentation:

$content = new View('pages/items');
$items = new Items_Model;

$content->items = $items->get_items($page_no, 10); // page to get starting at offset, number of items to get

As you can see, we can assume that we have get_items method for Items model that receives 2 parameters, $page_no and 10 (as shown here). I know that 10 is the number of items to get and $page_no is the page to get starting at offset

I can probably implement a limit sql statement for the 10 parameter, but what sql statement will correspond to $page_no? What does Kohana mean regarding "page to get starting at offset"

Best Answer

It corresponds to a LIMIT statement:

SELECT something FROM table LIMIT $limit OFFSET $offset;

//or alternatively
SELECT something FROM table LIMIT $offset,$limit;

In other words, select something from table, but only give me $limit records starting from record $offset.

$offset = ($page_no - 1) * $limit
_{$page_no is 1 based.}

More information in the MySQL documentation:

12.2.8 SELECT Syntax

DISCLAMER: $limit and $offset is used in this question only to aid understanding. Of course, you do not want to build a query without proper value escaping.

Related Solutions

Mysql – How to output MySQL query results in CSV format

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

MySQL – UPDATE query based on SELECT Query

You can actually do this one of two ways:

MySQL update join syntax:

UPDATE tableA a
INNER JOIN tableB b ON a.name_a = b.name_b
SET validation_check = if(start_dts > end_dts, 'VALID', '')
-- where clause can go here

ANSI SQL syntax:

UPDATE tableA SET validation_check = 
    (SELECT if(start_DTS > end_DTS, 'VALID', '') AS validation_check
        FROM tableA
        INNER JOIN tableB ON name_A = name_B
        WHERE id_A = tableA.id_A)

Pick whichever one seems most natural to you.

Best Answer

Related Solutions

Mysql – How to output MySQL query results in CSV format

MySQL – UPDATE query based on SELECT Query

Related Topic