I am new to php and sql, and i have one tiny question about how to realize sql query , that can:
Thank you )
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
You need to have a reference to the current record, and then progressively look for the next record based on the sorted columns. The example below assumes it is sorted on
ORDER BY Active, DIN, NAME
First:
SELECT *
FROM TABLE
WHERE NAME LIKE '%X%' AND DIN LIKE '%%'
ORDER BY Active, DIN, Name
LIMIT 1;
Next: (make sure you separate the CURR.ID = 6 and the AND-ORs with proper brackets!)
SELECT *
FROM TABLE T
INNER JOIN TABLE CURR ON CURR.ID = 6 # the current ID being viewed
AND ((T.Active = Curr.Active AND T.DIN = Curr.DIN AND T.NAME > Curr.Name)
OR (T.Active = Curr.Active AND T.DIN > Curr.DIN)
OR T.Active > Curr.Active)
WHERE T.NAME LIKE '%X%' AND T.DIN LIKE '%%'
ORDER BY T.Active, T.DIN, T.Name
LIMIT 1;
A working sample presented below
create table products
(ID int, SEED int, NAME varchar(20), DIN varchar(10), ACTIVE int, DELETED int);
insert products values
(1, 0, 'Product #1', '004812', 1, 0),
(2, 0, 'Product #2', '004942', 0, 0),
(3, 0, 'Product #3', '004966', 1, 0),
(4, 0, 'Product #4', '007437', 1, 1),
(5, 2, 'Product #2', '004944', 0, 0),
(6, 2, 'Product #2', '004944', 1, 0);
SELECT *
FROM products
WHERE active = 1 AND deleted = 0
ORDER BY din DESC, ID desc;
Output:
"ID";"SEED";"NAME";"DIN";"ACTIVE";"DELETED"
"3";"0";"Product #3";"004966";"1";"0"
"6";"2";"Product #2";"004944";"1";"0"
"1";"0";"Product #1";"004812";"1";"0"
If current is the row with ID=6, the next record can be retrieved using
SELECT T.*
FROM products T
INNER JOIN products curr on curr.ID = 6
AND ((T.din = curr.din and T.ID > curr.ID)
OR (T.din < curr.din))
WHERE T.active = 1 AND T.deleted = 0
ORDER BY T.din DESC, T.ID ASC
LIMIT 1;
You need to add use:
use IlluminatePaginationLengthAwarePaginator as Paginator;
and now you can use:
$paginator = new Paginator($items, $count, $limit, $page, [
'path' => $this->request->url(),
'query' => $this->request->query(),
]);
to get data in the same format as paginating on model object;
$stmt=$myConnection->prepare('SELECT COUNT(id) FROM products');
// Don't use bind_result()...
// execute your statement
$stmt->execute();
// Get result set into a MySQLi result resource
$result = $stmt->bind_result($id);
// array to hold all rows
$rows = array();
// All results bound to output vars
while ($stmt->fetch()) {
// Append an array containing your result vars onto the rowset array
$rows[] = array(
'id' => $id
);
}
$rows=$id;
for the first part. i think you have got it
SELECT t3.a, t2.b FROM (SELECT * FROM t1 LIMIT 5) t3
LEFT JOIN t2 ON ...
Note that if you use limit without an 'order by' clause, it is not defined which 5 rows you will get. Consider adding an 'order by' clause if this is not what you want.
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.