Question 20488 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Dynamically get the index position from a result to put it in OFFSET in another query, in the same consult

I have a table with an id column (_id, integer) and a post_id column (_post_id, string) in a tbl_messages table.

I would like to make a query where I can know the position of the row in a simple query without LIMIT, to put it in the OFFSET.

I could make a query and in PHP with a search_array get the position, but I would like to do it in the same query.

For example:

SELECT * FROM tbl_messages WHERE _user = '2' AND _instance = '5' ORDER BY _date ASC LIMIT 10, OFFSET (Here I would like to return the position in the result, and start the result list from post_id = 'R5G96H5D85S9' by example).

I couldn’t find anything that satisfied my interest in hours of searching, I appreciate any attempt to give me an idea;

Thanks

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Well, sorry to those who said it wasn't possible and gave up at the same time. For those who come to this question looking for answers, I got it this way:

    SELECT @a = row_num - 1 FROM (SELECT row_number() over (ORDER BY `_date` DESC) row_num, `_message_id` AS "m_id" FROM `tbl_messages` WHERE `_contact` = '2' AND `_instance` = '5' ORDER BY `_date` DESC) AS tab WHERE tab.`m_id` = 'BAE58BEA44DD7972';
PREPARE PREP FROM "SELECT * FROM `tbl_messages` WHERE `_contact` = '2' AND `_instance` = '5' ORDER BY `_date` DESC LIMIT 10 OFFSET ?";
EXECUTE PREP USING @a;

    This can be written in PHP like this:

    $sql = $conn->prepare('SELECT row_num - 1 AS num FROM (SELECT row_number() over (ORDER BY _date DESC) row_num, _message_id AS "m_id" FROM tbl_messages WHERE _contact = :contact AND _instance = :instance ORDER BY _date DESC) AS tab WHERE tab.m_id = "BAE58BEA44DD7972"');
$sql->execute(array(':contact' => 2, ':instance' => 5));
$result = $sql->fetch(PDO::FETCH_ASSOC);
$a = $result['num'];

$stmt = $conn->prepare("SELECT * FROM tbl_messages WHERE _contact = :contact AND _instance = :instance ORDER BY _date DESC LIMIT 10 OFFSET :offset");
$contact = 2;
$instance = 5;
$offset = intval($a);
$stmt->bindParam(':contact', $contact, PDO::PARAM_INT);
$stmt->bindParam(':instance', $instance, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll();

echo '<pre>';
var_dump($result);

  2. 0

    You cannot do what you describe in one query, because the arguments to LIMIT and OFFSET can’t be expressions or subqueries.

    So you must calculate the position before you run the query. You said you already know how to do this.

    You are allowed to use an integer parameter or a variable as the argument to LIMIT.

    SELECT * FROM tbl_messages 
WHERE _user = '2' AND _instance = '5' 
ORDER BY _date ASC LIMIT 10 OFFSET ?

    (notice I removed the comma before OFFSET)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search