Asked  7 Months ago    Answers:  3   Viewed   31 times

I am new to php and sql, and i have one tiny question about how to realize sql query , that can:

  • Take for example 5 entries from DB, insert them on 1st page (1-5)
  • Than take next 5 entries from same DB and insert them on another page (5-10)
    and so on :)

Thank you )

 Answers

54
SELECT col FROM table LIMIT  0,5; -- First page, rows 1-5
SELECT col FROM table LIMIT  5,5; -- Second page, rows 6-10
SELECT col FROM table LIMIT 10,5; -- Third page, rows 11-15

Read the LIMIT section on the MySQL SELECT helppage. If you want to display the total number of rows available, you can either do an extra count, or use the ROW_COUNT function.

Wednesday, March 31, 2021
 
skrilled
answered 7 Months ago
76

Here is a nice starting point:

<?php

// insert your mysql connection code here

$perPage = 10;
$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$startAt = $perPage * ($page - 1);

$query = "SELECT COUNT(*) as total FROM redirect
WHERE user_id = '".$_SESSION['user_id']."'";
$r = mysql_fetch_assoc(mysql_query($query));

$totalPages = ceil($r['total'] / $perPage);

$links = "";
for ($i = 1; $i <= $totalPages; $i++) {
  $links .= ($i != $page ) 
            ? "<a href='index.php?page=$i'>Page $i</a> "
            : "$page ";
}


$r = mysql_query($query);

$query = "SELECT * FROM 'redirect'
WHERE 'user_id'= ''.$_SESSION['user_id'].' ' 
ORDER BY 'timestamp' LIMIT $startAt, $perPage";

$r = mysql_query($query);

// display results here the way you want

echo $links; // show links to other pages
Tuesday, June 8, 2021
 
TheFrack
answered 5 Months ago
66

Random ordering in MySQL is as sticky a problem as they come. In the past, I've usually chosen to go around the problem whenever possible. Typically, a user won't ever come back to a set of pages like this more than once or twice. So this gives you the opportunity to avoid all of the various disgusting implementations of random order in favor of a couple simple, but not quite 100% random solutions.

Solution 1

Pick from a number of existing columns that already indexed for being sorted on. This can include created on, modified timestamps, or any other column you may sort by. When a user first comes to the site, have these handy in an array, pick one at random, and then randomly pick ASC or DESC.

In your case, every time a user comes back to page 1, pick something new, store it in session. Every subsequent page, you can use that sort to generate a consistent set of paging.

Solution 2

You could have an additional column that stores a random number for sorting. It should be indexed, obviously. Periodically, run the following query;

UPDATE table SET rand_col = RAND();

This may not work for your specs, as you seem to require every user to see something different every time they hit page 1.

Monday, July 19, 2021
 
Drazisil
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :