MySQL heeft geen zin om te doen wat ik vraag
Ik wilde zonet wat toevoegen aan mijn script, maar blijkbaar vind MySQL het moeilijk om de laatste reactie van een onderwerp te pakken. Ook pakt hij het verkeerde id en het begint ondertussen nogal vervelend te worden. Dit is mijn query:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
$result = mysqli_query($db, "
SELECT t.title, t.id, t.laster, t.board, u.username, u.id, t.last_msg, t.last_date
FROM " . $sfp['db_pref'] . "threads as t
LEFT JOIN " . $sfp['db_pref'] . "users as u ON t.laster = u.id
WHERE t.id='" . $id_msg . "'
ORDER BY last_msg DESC
");
SELECT t.title, t.id, t.laster, t.board, u.username, u.id, t.last_msg, t.last_date
FROM " . $sfp['db_pref'] . "threads as t
LEFT JOIN " . $sfp['db_pref'] . "users as u ON t.laster = u.id
WHERE t.id='" . $id_msg . "'
ORDER BY last_msg DESC
");
En dit geeft de tekst weer:
Code (php)
1
2
3
4
5
2
3
4
5
while($msginfo = mysqli_fetch_array($result))
return '
<a href="' . $sfp['forum_file'] . '?thread=' . $msginfo['id'] . '">' . $msginfo['title'] . '</a><br />
By <a href="' . $sfp['forum_file'] . '?act=profile&u=' . sfp_get_id($msginfo['username']) . '">' . $msginfo['username'] . '</a><br />
On ' . date("F j, Y, g:i a", $msginfo['last_date']);
return '
<a href="' . $sfp['forum_file'] . '?thread=' . $msginfo['id'] . '">' . $msginfo['title'] . '</a><br />
By <a href="' . $sfp['forum_file'] . '?act=profile&u=' . sfp_get_id($msginfo['username']) . '">' . $msginfo['username'] . '</a><br />
On ' . date("F j, Y, g:i a", $msginfo['last_date']);
Blijkbaar pakt hij alleen het bericht met het id "1" en ik kan de fout echt niet vinden. Ik denk dat het komt omdat het id van de gebruiker 1 is, maar ik kan er echt niet meer achter komen. Alvast bedankt voor de reacties!
Robert
Lijkt me sterk dat MySQL zomaar ergens iets vandaan haalt.
Code (php)
1
2
3
4
5
2
3
4
5
SELECT t.title, t.id, t.laster, t.board, u.username, u.id, t.last_msg, t.last_date
FROM sfp_threads as t
LEFT JOIN sfp_users as u ON t.laster = u.id
WHERE t.id='26'
ORDER BY last_msg DESC
FROM sfp_threads as t
LEFT JOIN sfp_users as u ON t.laster = u.id
WHERE t.id='26'
ORDER BY last_msg DESC
Gewijzigd op 05/02/2012 18:44:15 door Robert van der Leest
Robert van der Leest op 05/02/2012 19:08:22:
Die return stuurt de tekst die is aangemaakt met die functie terug naar het vakje waar de tekst weergegeven moet worden. :)
Dat is dus altijd het eerst gevonden record.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// Get the message id, user id etc
$result = mysqli_query($db, "
SELECT t.title, t.id, t.laster, t.board, u.username, u.id, t.last_msg, t.last_date
FROM " . $sfp['db_pref'] . "threads as t
LEFT JOIN " . $sfp['db_pref'] . "users as u ON t.laster = u.id
LEFT JOIN " . $sfp['db_pref'] . "boards as b ON t.board = b.id_board
WHERE t.id='" . $id_msg . "'
ORDER BY b.last_msg DESC
");
// Now return the information
while($msginfo = mysqli_fetch_array($result))
echo '
<a href="' . $sfp['forum_file'] . '?thread=' . $msginfo['id'] . '">' . $msginfo['title'] . '</a><br />
By <a href="' . $sfp['forum_file'] . '?act=profile&u=' . sfp_get_id($msginfo['username']) . '">' . $msginfo['username'] . '</a><br />
On ' . date("F j, Y, g:i a", $msginfo['last_date']);
$result = mysqli_query($db, "
SELECT t.title, t.id, t.laster, t.board, u.username, u.id, t.last_msg, t.last_date
FROM " . $sfp['db_pref'] . "threads as t
LEFT JOIN " . $sfp['db_pref'] . "users as u ON t.laster = u.id
LEFT JOIN " . $sfp['db_pref'] . "boards as b ON t.board = b.id_board
WHERE t.id='" . $id_msg . "'
ORDER BY b.last_msg DESC
");
// Now return the information
while($msginfo = mysqli_fetch_array($result))
echo '
<a href="' . $sfp['forum_file'] . '?thread=' . $msginfo['id'] . '">' . $msginfo['title'] . '</a><br />
By <a href="' . $sfp['forum_file'] . '?act=profile&u=' . sfp_get_id($msginfo['username']) . '">' . $msginfo['username'] . '</a><br />
On ' . date("F j, Y, g:i a", $msginfo['last_date']);
En dit is de query die de topics laat zien:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
$result = mysqli_query($db, "
SELECT t.id, t.title, t.thread, t.closed, t.sticky, t.last_msg,
t.replies, t.views, u.id, u.username, t.board
FROM " . $sfp['db_pref'] . "threads as t
LEFT JOIN " . $sfp['db_pref'] . "users as u on t.starter = u.id
LEFT JOIN " . $sfp['db_pref'] . "users as su on t.laster = su.id
WHERE t.board='" . $board . "' AND thread='yes'
");
SELECT t.id, t.title, t.thread, t.closed, t.sticky, t.last_msg,
t.replies, t.views, u.id, u.username, t.board
FROM " . $sfp['db_pref'] . "threads as t
LEFT JOIN " . $sfp['db_pref'] . "users as u on t.starter = u.id
LEFT JOIN " . $sfp['db_pref'] . "users as su on t.laster = su.id
WHERE t.board='" . $board . "' AND thread='yes'
");
En dit laat de topics zien:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
while($thread = mysqli_fetch_assoc($result))
echo '
<tr>
<td width="50%"><a href="' . $sfp['forum_file'] . '?thread=' . $thread['id'] . '">' . $thread['title'] . '</a><br />Posted by <a href="' . $sfp['forum_file'] . '?act=profile&u=' . sfp_get_id($thread['username']) . '">' . $thread['username'] . '</a></td>
<td width="10%" align="middle">' . $thread['replies'] . '</td>
<td width="10%" align="middle">' . $thread['views'] . '</td>
<td width="30%">' . sfp_lastreply($thread['last_msg']) . '</td>
</tr>';
echo '
<tr>
<td width="50%"><a href="' . $sfp['forum_file'] . '?thread=' . $thread['id'] . '">' . $thread['title'] . '</a><br />Posted by <a href="' . $sfp['forum_file'] . '?act=profile&u=' . sfp_get_id($thread['username']) . '">' . $thread['username'] . '</a></td>
<td width="10%" align="middle">' . $thread['replies'] . '</td>
<td width="10%" align="middle">' . $thread['views'] . '</td>
<td width="30%">' . sfp_lastreply($thread['last_msg']) . '</td>
</tr>';
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
r.reply_date,
t.title,
u.user_name,
b.board
FROM
replies AS r
INNER JOIN
threads AS t ON r.thread_id = t.thread_id
users AS u ON t.user_id = u.user_id
boards AS b ON t.board_id = b.board_id
GROUP BY
r.thread_id
ORDER BY
r.reply_date DESC
LIMIT 10
r.reply_date,
t.title,
u.user_name,
b.board
FROM
replies AS r
INNER JOIN
threads AS t ON r.thread_id = t.thread_id
users AS u ON t.user_id = u.user_id
boards AS b ON t.board_id = b.board_id
GROUP BY
r.thread_id
ORDER BY
r.reply_date DESC
LIMIT 10
*opgelost*