How to select rows within a specific range in sql?

Question

I am creating a simple messaging web application. People can easily send or recieve text sms by logging in. Just click on the specific contact name and you will have the chat box with all the previous messages . Now the problem is,any conversation that have lots of messages will take a long time to load the chatbox. So I want to show latest (order by by sendingtime desc) 10 messages in the chatbox for the first time. And at the top of the chat box I want to provide a button by clicking which user will get previous 10 messages (order by by sendingtime desc).And it will be continue till the user get the last msg. I am using PHP AJAX. Code I used to get first 10 messages is here

    $sql2="select * from messages where sender='$user1' and reciever='$user2' or reciever='$user1' and 
sender='$user2' order by sendingTime desc";
$res2=mysqli_query($con,$sql2);
$num=mysqli_num_rows($res2);
if($num<=10){
for($i=1;$i<=$num;$i++){
$row2=mysqli_fetch_array($res2);
$data2[]=$row2;
}
}else{
for($i=1;$i<=10;$i++){
$row2=mysqli_fetch_array($res2);
$data2[]=$row2;
}
}

But I have no idea how to select next 10 message rows. Please Help.

My 'messages' database structure 1 idPrimary int(11) No None AUTO_INCREMENT Change Change Drop Drop
More More 2 sender varchar(100) latin1_swedish_ci No None Change Change Drop Drop
More More 3 reciever varchar(100) latin1_swedish_ci No None Change Change Drop Drop
More More 4 msg varchar(5000) latin1_swedish_ci No None Change Change Drop Drop
More More 5 sendingTime varchar(100) latin1_swedish_ci No None Change Change Drop Drop
More More 6 deliveryTime varchar(100) latin1_swedish_ci No None Change Change Drop Drop
More More 7 status int(1) No

Have you tried looking at using [`OFFSET`](https://www.w3schools.com/php/php_mysql_select_limit.asp)? — rinz1er, Jun 21 '20 at 05:39
Perhaps you need [LIMIT clause with offset](https://www.guru99.com/limit.html) — krokodilko, Jun 21 '20 at 05:45
You will need to use `LIMIT` in your sql and by every click of your "load more", you simply increment the counter variable you'll use in your `LIMIT` clause by 10 each time. — Martin, Jun 21 '20 at 05:48
It's also **VERY IMPORTANT** to note that you are currently vulnerable to SQL Injections (SQLI Attacks). Please make use of Prepared Statements and Parametized Queries using either mysqli or PDO. Your database is currently at risk. — Martin, Jun 21 '20 at 05:50

Martin · Answer 1 · 2020-06-21T12:20:54.070

WARNING!: You're currently wide open to SQL Injections (SQLI Attacks). Please make use of Prepared Statements and Parametized queries, using either mysqli or PDO. Your database is at huge risk! Don't trust any user inputs!

With that said, I will be moving forward with this answer using mysqli Prepared Statements and Parametized Queries.

What you generally need in your query is the LIMIT operator. More information about the LIMIT operator can be found here.

Example:

SELECT
    *
FROM
    messages
WHERE
    sender = 1 AND receiver = 2
    OR ( receiver = 1 AND sender = 2)
LIMIT 10;

DB Fiddle here.

In your AJAX function, you need to parse a value that will serve as your variable in your LIMIT clause. By each click of "Load More", you increment that value by 10.

This is how your PHP page could look like:

<?php
// Your user variables
$user1 = $_POST['user1'];
$user2 = $_POST['user2'];

// Your amount of messages to be loaded
$loadMessages = $_POST['messageAmount'];

if(empty($loadMessages )) {
   $loadMessages = 0; 
}

// DB variables
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

// Error handling
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

/*
Declare the query.
One thing to note from a select statement,
is that it is generally considered better practice
to list the fields you want to fetch.
Even if it's considered to be all fields.
So I would recommend doing that over "SELECT *".
*/
$sql = "SELECT
            *
        FROM
            messages
        WHERE
            sender = ?
            AND receiver = ? 
            or (reciever = ? and sender = ?)
        LIMIT (10 + ?)
        ORDER BY
            sendingTime DESC";

/*
Prepare and bind 
The variable identifiers are based on assuming the sender and receiver
are int fields. Change the identifier if that's not the case.

Identifiers:
-s string
-i int
-d double
-b BLOB
*/
$stmt = $conn->prepare($sql);
$stmt->bind_param('iiiii', $user1, $user2, $user1, $user2, $loadMessages);

// Execute the query
$stmt->execute();

// Get result of query
$result = $stmt->get_result();

// Close connection
$stmt->close();
$conn->close();
?>

To loop through your results:

<?php
// Check if any returned rows?
if($result->num_rows > 0) {
    // Loop through the result(s)
    while ($data = $result->fetch_assoc()) {
        echo $data['field_name'];
    }
} else {
    // Something here?
}
?>

By doing so, I think the scenario will be, It will first load latest 10 messages. And when I will click on the 'Load More' button it will load 20 messages including the first 10 messages which are allready loaded. Am I right? — CodeSeeker, Jun 21 '20 at 07:40
Correct @CodeSeeker. You first load in however many messages you want, then upon clicking the *"Load More"* button you increment the value by 10 or however many you choose. :) — Martin, Jun 21 '20 at 07:43
I edited the answer to fit your idea of 10 initial messages, then a +10 iteration of the "Load More" logic — Martin, Jun 21 '20 at 09:49
Thank you ,but will it be effective because it is loading those rows also which are allready loaded. Has there any option to only load those messages whcih are not loaded yet. I mean at first I want to retrieve 10 messages. And by clicking the 'Load More' button I will load the next 10 messages (which are not loaded yet) not 20 . Thank you once again. — CodeSeeker, Jun 21 '20 at 13:22
There are 2 simple ways I can think of at the top of my head. You could make an `if()` statement around the query where the logic revolves around whether the load more button has iterated the `$loadMessages` value or not. Then make a query that fetches rows starting at N position and append that data to the chat box. SQL logic here: https://stackoverflow.com/questions/29894645/how-to-skip-the-first-n-rows-in-sql-query/29894850 . The other is to leave it as is in my current answer. If you are using AJAX properly, the functionality should be the same. — Martin, Jun 23 '20 at 06:56

score 0 · Answer 2 · answered Jun 21 '20 at 06:43

0

Try BETWEEN Operator

WHERE column_name BETWEEN value1 AND value2;

Set the conditions for value1 and value2

Check syntax

answered Jun 21 '20 at 06:43

Merrin K

1,602
1
16
27

How to select rows within a specific range in sql?

2 Answers2