[PHP] SQL "limit" loopt vast
Ik ben bezig met het uitfilteren van data uit een database met producten. Wanneer ik een LIMIT toevoeg aan de SQL query gaat alles opzich goed, alleen wanneer ik bijvoorbeeld LIMIT 0, 20 vervang met LIMIT ".$startfrom.", 20 en naar pagina 2 ga, loopt de complete boel vast..
Het gaat hier overigens om lijn 47.
Na even gedebugged te hebben ben ik erachter gekomen dat het ligt aan de functies get_headcat() en breadcrumbs(). Het rare is is dat als ik LIMIT 40, 20 invul (pagina 2, 2 * 20) alles wel gewoon werkt..
Iemand enig idee waarom dit is? Of heeft iemand een efficientere manier om bijvoorbeeld die functies te schrijven?
Thanks alvast!
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
<?php
header("Content-Type: text/xml");
$link = mysqli_connect($host, $name, $pass, $db);
function get_headcat($link, $parent) {
$i = 0;
while ($parent != 0) {
$sql = mysqli_query($link, "SELECT id, parent, title, is_product FROM pages WHERE id='".$parent."'");
$line = mysqli_fetch_assoc($sql);
$parent = $line["parent"];
$headtitle = $line["title"];
$i++;
}
return $headtitle;
}
function breadcrumbs($link, $pageid) {
$i = 0;
$output = "";
while ($pageid != 0) {
$sql = mysqli_query($link, "SELECT id, parent, title, is_product FROM pages WHERE id='".$pageid."'");
$line = mysqli_fetch_assoc($sql);
if ($i == 0) {
$output = "<strong>".$line["title"]."</strong>".$output;
}else{
$output = $line["title"]." > ".$output;
}
$pageid = $line["parent"];
$i++;
}
return $output;
}
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
echo "<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\"
xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"
xsi:schemaLocation=\"http://www.sitemaps.org/schemas/sitemap/0.9
http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd\">\n";
if (isset($_GET["page"]) && $_GET["page"] != "") {
$page = $_GET["page"];
}else{
$page = 1;
}
$startfrom = intval(($page * 20) - 20);
$sql = mysqli_query($link, "SELECT pages.id as pageid, pages.title, pages.parent, products.picture, products.price FROM pages LEFT JOIN products ON products.id=pages.product_id WHERE pages.is_product='1' AND products.stock <> 'nee' ORDER BY pages.id ASC LIMIT $startfrom, 20") or die(mysqli_error($link));
if (mysqli_num_rows($sql) > 0) {
$i = ($page * 20) - 20;
while ($result = mysqli_fetch_array($sql)) {
$i++;
$details = "";
$found = false;
$detail_query = mysqli_query($link, "SELECT id, value FROM details WHERE parent='0' AND product='0' ORDER BY sort, id ASC");
while ($detail_result = mysqli_fetch_array($detail_query)) {
$detail = mysqli_fetch_array(mysqli_query($link, "SELECT value FROM details WHERE parent='".$detail_result["id"]."' AND product='".$result["id"]."'"));
if ($detail["value"] != "" && $detail["value"] != "-1.00") {
$found = true;
$details = $details."".$detail_result["value"].": ".ucfirst(strtolower($detail["value"]))."\n";
}
}
if (!$found) {
$details = "-\n";
}
$result["picture"] = explode(",", $result["picture"]);
for ($p = 0; $p < 1; $p++) {
if (isset($result["picture"][$p])) {
$result["picture"][$p] = $result["picture"][$p];
}
}
echo "<informatie><id>".$i."</id><pad>".breadcrumbs($link, $result["pageid"])."</pad><titel>".get_headcat($link, $result["parent"]).": ".$result["title"]."</titel><afbeelding>https://www.ruhl.nu/images/products/".rawurlencode($result["picture"][0])."</afbeelding><prijs>".number_format($result["price"], 2, ",", ".")."</prijs>";
}
}else{
echo "Er zijn geen resultaten gevonden.";
}
echo "</urlset>\n";
?>
header("Content-Type: text/xml");
$link = mysqli_connect($host, $name, $pass, $db);
function get_headcat($link, $parent) {
$i = 0;
while ($parent != 0) {
$sql = mysqli_query($link, "SELECT id, parent, title, is_product FROM pages WHERE id='".$parent."'");
$line = mysqli_fetch_assoc($sql);
$parent = $line["parent"];
$headtitle = $line["title"];
$i++;
}
return $headtitle;
}
function breadcrumbs($link, $pageid) {
$i = 0;
$output = "";
while ($pageid != 0) {
$sql = mysqli_query($link, "SELECT id, parent, title, is_product FROM pages WHERE id='".$pageid."'");
$line = mysqli_fetch_assoc($sql);
if ($i == 0) {
$output = "<strong>".$line["title"]."</strong>".$output;
}else{
$output = $line["title"]." > ".$output;
}
$pageid = $line["parent"];
$i++;
}
return $output;
}
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
echo "<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\"
xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"
xsi:schemaLocation=\"http://www.sitemaps.org/schemas/sitemap/0.9
http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd\">\n";
if (isset($_GET["page"]) && $_GET["page"] != "") {
$page = $_GET["page"];
}else{
$page = 1;
}
$startfrom = intval(($page * 20) - 20);
$sql = mysqli_query($link, "SELECT pages.id as pageid, pages.title, pages.parent, products.picture, products.price FROM pages LEFT JOIN products ON products.id=pages.product_id WHERE pages.is_product='1' AND products.stock <> 'nee' ORDER BY pages.id ASC LIMIT $startfrom, 20") or die(mysqli_error($link));
if (mysqli_num_rows($sql) > 0) {
$i = ($page * 20) - 20;
while ($result = mysqli_fetch_array($sql)) {
$i++;
$details = "";
$found = false;
$detail_query = mysqli_query($link, "SELECT id, value FROM details WHERE parent='0' AND product='0' ORDER BY sort, id ASC");
while ($detail_result = mysqli_fetch_array($detail_query)) {
$detail = mysqli_fetch_array(mysqli_query($link, "SELECT value FROM details WHERE parent='".$detail_result["id"]."' AND product='".$result["id"]."'"));
if ($detail["value"] != "" && $detail["value"] != "-1.00") {
$found = true;
$details = $details."".$detail_result["value"].": ".ucfirst(strtolower($detail["value"]))."\n";
}
}
if (!$found) {
$details = "-\n";
}
$result["picture"] = explode(",", $result["picture"]);
for ($p = 0; $p < 1; $p++) {
if (isset($result["picture"][$p])) {
$result["picture"][$p] = $result["picture"][$p];
}
}
echo "<informatie><id>".$i."</id><pad>".breadcrumbs($link, $result["pageid"])."</pad><titel>".get_headcat($link, $result["parent"]).": ".$result["title"]."</titel><afbeelding>https://www.ruhl.nu/images/products/".rawurlencode($result["picture"][0])."</afbeelding><prijs>".number_format($result["price"], 2, ",", ".")."</prijs>";
}
}else{
echo "Er zijn geen resultaten gevonden.";
}
echo "</urlset>\n";
?>
Gewijzigd op 12/02/2016 20:25:05 door Kevin Ruhl
Gewijzigd op 19/02/2016 22:47:10 door Peter Griffin