PHP while loop maakt pagina traag
Ik ben bezig met een berichten systeem maar ik stuit op een probleem... Ik vermoed dat het probleem ligt bij de whileloop waarbij de berichten worden opgehaald, echter weet ik niet hoezo de pagina maarliefst 2.5 seconde nodig heeft om 7 resultaten te laten zien. Ik hoor graag hoe jullie dit zouden aanpakken.
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
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
public function listConversationsInbox()
{
$sql = "
SELECT `conversation_id`, `important`
FROM `" . dbPrefix . "conversation_participants`
INNER JOIN `" . dbPrefix . "conversations`
ON `" . dbPrefix . "conversation_participants`.`conversation_id` = `" . dbPrefix . "conversations`.`id`
WHERE `user_id` = '" . $this->getConnection()->escape($_SESSION['id']) . "'
AND (`status` = '0'
OR `status` = '1')
ORDER BY `" . dbPrefix . "conversations`.`created_on` DESC
";
$result = $this->getConnection()->query($sql);
$data = array();
if ($this->getConnection()->num_rows($result) > 0)
{
while($convo = $this->getConnection()->fetch_assoc($result))
{
$sql2 = "
SELECT `created_by`,
`title`,
`status`,
`created_on`
FROM `" . dbPrefix . "conversations`
WHERE `id` = '" . $this->getConnection()->escape($convo['conversation_id']) . "'
LIMIT 1
";
$result2 = $this->getConnection()->query($sql2);
while (
list(
$created_by,
$title,
$status,
$created_on
) = $this->getConnection()->fetch_row($result2)
)
{
$i = count($data);
$data[$i]['number'] = $i;
$data[$i]['id'] = $convo['conversation_id'];
$data[$i]['created_by'] = $this->_user->idToUsername($created_by);
$data[$i]['title'] = $title;
$data[$i]['important'] = $convo['important'];
$data[$i]['status'] = $status;
$data[$i]['created_on'] = $created_on;
if ($status == 1)
{
$data[$i]['icon'] = "fas fa-lock";
}
$sql3 = "
SELECT *
FROM `" . dbPrefix . "conversation_messages`
WHERE `conversation_id` = '" . $this->getConnection()->escape($convo['conversation_id']) . "'
ORDER BY `created_on` ASC
LIMIT 1
";
$result3 = $this->getConnection()->query($sql3);
$data[$i]['message'] = $this->getConnection()->fetch_row($result3);
}
}
}
else
{
$data = false;
}
return $data;
}
{
$sql = "
SELECT `conversation_id`, `important`
FROM `" . dbPrefix . "conversation_participants`
INNER JOIN `" . dbPrefix . "conversations`
ON `" . dbPrefix . "conversation_participants`.`conversation_id` = `" . dbPrefix . "conversations`.`id`
WHERE `user_id` = '" . $this->getConnection()->escape($_SESSION['id']) . "'
AND (`status` = '0'
OR `status` = '1')
ORDER BY `" . dbPrefix . "conversations`.`created_on` DESC
";
$result = $this->getConnection()->query($sql);
$data = array();
if ($this->getConnection()->num_rows($result) > 0)
{
while($convo = $this->getConnection()->fetch_assoc($result))
{
$sql2 = "
SELECT `created_by`,
`title`,
`status`,
`created_on`
FROM `" . dbPrefix . "conversations`
WHERE `id` = '" . $this->getConnection()->escape($convo['conversation_id']) . "'
LIMIT 1
";
$result2 = $this->getConnection()->query($sql2);
while (
list(
$created_by,
$title,
$status,
$created_on
) = $this->getConnection()->fetch_row($result2)
)
{
$i = count($data);
$data[$i]['number'] = $i;
$data[$i]['id'] = $convo['conversation_id'];
$data[$i]['created_by'] = $this->_user->idToUsername($created_by);
$data[$i]['title'] = $title;
$data[$i]['important'] = $convo['important'];
$data[$i]['status'] = $status;
$data[$i]['created_on'] = $created_on;
if ($status == 1)
{
$data[$i]['icon'] = "fas fa-lock";
}
$sql3 = "
SELECT *
FROM `" . dbPrefix . "conversation_messages`
WHERE `conversation_id` = '" . $this->getConnection()->escape($convo['conversation_id']) . "'
ORDER BY `created_on` ASC
LIMIT 1
";
$result3 = $this->getConnection()->query($sql3);
$data[$i]['message'] = $this->getConnection()->fetch_row($result3);
}
}
}
else
{
$data = false;
}
return $data;
}
Gewijzigd op 01/08/2019 17:46:29 door - Rob -
Probeer in zulke gevallen JOINS te gebruiken.
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
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
public function listConversationsInbox()
{
$sql = "
SELECT a.`important`,
a.`trash`,
b.`id`,
b.`created_by`,
b.`title`,
b.`status`,
b.`created_on`,
c.`written_by`,
c.`message`
FROM `" . dbPrefix . "conversation_participants` AS a
LEFT JOIN `" . dbPrefix . "conversations` AS b ON a.`conversation_id` = b.`id`
LEFT JOIN `" . dbPrefix . "conversation_messages` AS c ON c.`conversation_id` = b.`id`
WHERE a.`conversation_id` = b.`id`
AND a.`user_id` = '" . $_SESSION['id'] . "'
AND (`status` = '0'
OR `status` = '1')
ORDER BY b.`created_on` DESC
";
$result = $this->getConnection()->query($sql);
$data = array();
if ($this->getConnection()->num_rows($result) > 0)
{
$i = 0;
while($convo = $this->getConnection()->fetch_assoc($result))
{
$data[$i]['number'] = $i;
$data[$i]['id'] = $convo['id'];
$data[$i]['created_by'] = $this->_user->idToUsername($convo['created_by']);
$data[$i]['title'] = $convo['title'];
$data[$i]['important'] = $convo['important'];
$data[$i]['message'] = $convo['message'];
$data[$i]['created_on'] = $convo['created_on'];
if ($convo['status'] == 1)
{
$data[$i]['icon'] = "fas fa-lock";
}
$i++;
}
}
else
{
$data = false;
}
return $data;
}
{
$sql = "
SELECT a.`important`,
a.`trash`,
b.`id`,
b.`created_by`,
b.`title`,
b.`status`,
b.`created_on`,
c.`written_by`,
c.`message`
FROM `" . dbPrefix . "conversation_participants` AS a
LEFT JOIN `" . dbPrefix . "conversations` AS b ON a.`conversation_id` = b.`id`
LEFT JOIN `" . dbPrefix . "conversation_messages` AS c ON c.`conversation_id` = b.`id`
WHERE a.`conversation_id` = b.`id`
AND a.`user_id` = '" . $_SESSION['id'] . "'
AND (`status` = '0'
OR `status` = '1')
ORDER BY b.`created_on` DESC
";
$result = $this->getConnection()->query($sql);
$data = array();
if ($this->getConnection()->num_rows($result) > 0)
{
$i = 0;
while($convo = $this->getConnection()->fetch_assoc($result))
{
$data[$i]['number'] = $i;
$data[$i]['id'] = $convo['id'];
$data[$i]['created_by'] = $this->_user->idToUsername($convo['created_by']);
$data[$i]['title'] = $convo['title'];
$data[$i]['important'] = $convo['important'];
$data[$i]['message'] = $convo['message'];
$data[$i]['created_on'] = $convo['created_on'];
if ($convo['status'] == 1)
{
$data[$i]['icon'] = "fas fa-lock";
}
$i++;
}
}
else
{
$data = false;
}
return $data;
}
Is dit wat je bedoelde? Hij blijft namelijk heel traag laden?
En heb je jouw query al met EXPLAIN ervoor uitgevoerd?
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
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
<?php
public function listConversationsInbox()
{
$sql = "
SELECT a.`important`,
a.`trash`,
b.`id`,
b.`created_by`,
b.`title`,
b.`status`,
b.`created_on`,
c.`written_by`,
c.`message`
FROM `" . dbPrefix . "conversation_participants` AS a
LEFT JOIN `" . dbPrefix . "conversations` AS b ON a.`conversation_id` = b.`id`
LEFT JOIN `" . dbPrefix . "conversation_messages` AS c ON c.`conversation_id` = b.`id`
WHERE a.`conversation_id` = b.`id`
AND a.`user_id` = '" . $_SESSION['id'] . "'
AND (`status` = '0'
OR `status` = '1')
ORDER BY b.`created_on` DESC
";
$result = $this->getConnection()->query($sql);
$data = array();
if ($this->getConnection()->num_rows($result) > 0)
{
$i = 0;
while($convo = $this->getConnection()->fetch_assoc($result))
{
?>
<tr>
<td>
<div class="icheck-primary">
<input type="checkbox" class="checkbox" value="<?php echo $convo['id']; ?>" id="check<?php echo $convo['id']; ?>">
<label for="check<?php echo $convo['id']; ?>"></label>
</div>
</td>
<td class="mailbox-star"><a href="#" class="convo-important"><input class="convo-id" type="hidden" value="<?php echo $convo['id']; ?>"><i class="fas fa-star <?php if ($convo['important'] == 1) { echo 'text-warning'; } ?>"></i></a></td>
<td class="mailbox-name"><a href="<?php echo _URL_ . '/' . lang['panel-page-url'] . '/' . lang['profile-page-url'] . '/' . convo['created_by']; ?>"><?php echo $this->_user->idToUsername($convo['created_by']); ?></a></td>
<td class="mailbox-subject" style="cursor: pointer;" onclick="window.location.href='<?php echo _URL_ . '/' . lang['panel-page-url'] . '/' . lang['conversations-page-url'] . '/' . lang['conversations-page-view-url'] . '/' . $convo['id']; ?>'"><b><?php echo $convo['title']; ?></b> - <?php echo substr_replace($convo['message'][3], '', 45); if (strlen($convo['message']) > 45) { echo '...'; } ?>
</td>
<td class="mailbox-attachment"></td>
<td class="mailbox-date">
<?php
echo rewriteDate($convo['created_on']) . ' '. substr_replace(substr($convo['created_on'], 10), '', 6);
?>
</td>
</tr>
<?php
$i++;
}
}
}
?>
public function listConversationsInbox()
{
$sql = "
SELECT a.`important`,
a.`trash`,
b.`id`,
b.`created_by`,
b.`title`,
b.`status`,
b.`created_on`,
c.`written_by`,
c.`message`
FROM `" . dbPrefix . "conversation_participants` AS a
LEFT JOIN `" . dbPrefix . "conversations` AS b ON a.`conversation_id` = b.`id`
LEFT JOIN `" . dbPrefix . "conversation_messages` AS c ON c.`conversation_id` = b.`id`
WHERE a.`conversation_id` = b.`id`
AND a.`user_id` = '" . $_SESSION['id'] . "'
AND (`status` = '0'
OR `status` = '1')
ORDER BY b.`created_on` DESC
";
$result = $this->getConnection()->query($sql);
$data = array();
if ($this->getConnection()->num_rows($result) > 0)
{
$i = 0;
while($convo = $this->getConnection()->fetch_assoc($result))
{
?>
<tr>
<td>
<div class="icheck-primary">
<input type="checkbox" class="checkbox" value="<?php echo $convo['id']; ?>" id="check<?php echo $convo['id']; ?>">
<label for="check<?php echo $convo['id']; ?>"></label>
</div>
</td>
<td class="mailbox-star"><a href="#" class="convo-important"><input class="convo-id" type="hidden" value="<?php echo $convo['id']; ?>"><i class="fas fa-star <?php if ($convo['important'] == 1) { echo 'text-warning'; } ?>"></i></a></td>
<td class="mailbox-name"><a href="<?php echo _URL_ . '/' . lang['panel-page-url'] . '/' . lang['profile-page-url'] . '/' . convo['created_by']; ?>"><?php echo $this->_user->idToUsername($convo['created_by']); ?></a></td>
<td class="mailbox-subject" style="cursor: pointer;" onclick="window.location.href='<?php echo _URL_ . '/' . lang['panel-page-url'] . '/' . lang['conversations-page-url'] . '/' . lang['conversations-page-view-url'] . '/' . $convo['id']; ?>'"><b><?php echo $convo['title']; ?></b> - <?php echo substr_replace($convo['message'][3], '', 45); if (strlen($convo['message']) > 45) { echo '...'; } ?>
</td>
<td class="mailbox-attachment"></td>
<td class="mailbox-date">
<?php
echo rewriteDate($convo['created_on']) . ' '. substr_replace(substr($convo['created_on'], 10), '', 6);
?>
</td>
</tr>
<?php
$i++;
}
}
}
?>
Zo worden de resultaten wel weer snel weergegeven :D
Gewijzigd op 01/08/2019 23:14:31 door - Ariën -
