Category posts limit by 5

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

George mendel

george mendel

19/11/2012 12:48:38
Quote Anchor link
Ik heb hier een van een forum, waarbij het ingedeeld is in:
- categorien
- topics
- posts

Nu krijg ik bij het index.php de categorien te zien en als ik erop klik kom ik pas bij de topics.
Hoe krijg ik onder de categorien gelijk de toplics te zien(bij index.php) limited by 5?

Het ziet er nu zo uit:

index.php
Quote:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
<?php session_start(); ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="Text/html; charset=utf-8" />
<title>Login</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>

<div id="wrapper">
<h2>Forum</h2>

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
<?php
if (!isset($_SESSION['uid'])) {
    echo "<form action='login_parse.php' method='post'>
Gebruikersnaam: <input type='text' name='username'>&nbsp;
wachtwoord: <input type='password' name='password'>&nbsp;
<input type='submit' name='submit' value='Inloggen' />
";
} else {
    echo "<p>U bent ingelogd als ".$_SESSION['username']." &bull; <a href='logout_parse.php'>Uitloggen</a>";
}

?>

<hr />
<div id = "content">
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
include_once("connect.php");
$sql = "SELECT * FROM categories ORDER BY category_title ASC";
$res = mysql_query($sql) or die(mysql_error());
$categories = "";
if (mysql_num_rows($res) > 0) {
    while ($row = mysql_fetch_assoc($res)) {
        $id = $row['id'];
        $title = $row['category_title'];
        $description = $row['category_description'];
        $categories .= "<a href='view_category.php?cid=".$id."' class='cat_links'>".$title." - <font size='-1'>".$description."</font></a>";
    }
    echo $categories;
} else {
    echo "<p>Er zijn geen categorieen beschikbaar.</p>";
}
?>

</div>
</body>
</html>


view_category.php

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
<?php session_start(); ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="Text/html; charset=utf-8" />
<title>Login</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>
Quote:
<div id="wrapper">
<h2>Forum</h2>


Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
<?php
if (!isset($_SESSION['uid'])) {
    echo "<form action='login_parse.php' method='post'>
Gebruikersnaam: <input type='text' name='username'>&nbsp;
Wachtwoord: <input type='password' name='password'>&nbsp;
<input type='submit' name='submit' value='Inloggen' />
";
} else {
    echo "<p>U bent ingelogd als ".$_SESSION['username']." &bull; <a href='logout_parse.php'>Uitloggen</a>";
}

?>

<div id = "content">
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
<?php
include_once("connect.php");
$cid = $_GET['cid'];
if (isset($_SESSION['uid'])) {
    $logged = " | <a href='create_topic.php?cid=".$cid."'> Click Here to Create a Topic</a>";
} else {
    $logged = " | Login om topics te maken.";
}
$sql = "SELECT id FROM categories WHERE id='".$cid."' LIMIT 1";
$res = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($res) == 1) {
    $sql2 = "SELECT * FROM topics WHERE category_id='".$cid."' ORDER BY topic_reply_date DESC";
    $res2 = mysql_query($sql2) or die(mysql_error());
    if (mysql_num_rows($res2) > 0) {
        $topics .= "<table widht='100%' style='border-collapse: collapse;'>";
        $topics .= "<tr><td colspan='3'><a href='index.php'>Return To Forum Index</a>".$logged."<hr /></td></tr>";
        $topics .= "<tr style='background-color: #dddddd;'><td>Topics</td><td width='65' align='center'>Replies</td></tr>";
        while ($row = mysql_fetch_assoc($res2)) {
            $tid = $row['id'];
            $title = $row['topic_title'];
            $views = $row['topic_views'];
            $date = $row['topic_date'];
            $creator = $row['topic_creator'];
            $topics .= "<tr><td><a href='view_topic.php?cid=".$cid."&tid=".$tid."'>".$title."</a><br /><span class='post_info'>
            Gepubliceerd door: ".$creator." on ".$date."</span></td><td align='center'>0</td></tr>";
            
        }
        $topics .= "</table>";
        echo $topics;
    } else {
    echo "<a href='index.php'>Terug naar index</a><hr />";
    echo "<p>Er zijn nog geen topics in deze category.".$logged."</p>";
    }
} else {
    echo "<a href='index.php'>Terug naar index</a><hr />";
    echo "<p>Deze Category bestaat niet.";
}
?>

</div>
</body>
</html>
Gewijzigd op 19/11/2012 13:16:57 door George mendel
 
PHP hulp

PHP hulp

03/05/2024 17:18:44
 
Stefan WM

Stefan WM

19/11/2012 12:58:20
Quote Anchor link
Het is handiger als je laat zien wat de database structuur is.
 
George mendel

george mendel

19/11/2012 13:19:00
Quote Anchor link
CREATE TABLE IF NOT EXISTS `categories` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT,
`category_title` varchar(150) NOT NULL,
`category_description` varchar(255) NOT NULL,
`last_post_date` datetime DEFAULT NULL,
`last_user_posted` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` tinyint(4) NOT NULL,
`topic_id` int(11) NOT NULL,
`post_creator` int(11) NOT NULL,
`post_content` text NOT NULL,
`post_date` datetime NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `topics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` tinyint(4) NOT NULL,
`topic_title` varchar(150) NOT NULL,
`topic_creator` int(11) NOT NULL,
`topic_last_user` int(11) NOT NULL,
`topic_date` datetime NOT NULL,
`topic_reply_date` datetime NOT NULL,
`topic_views` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`email` varchar(255) DEFAULT NULL,
`forum_notification` enum('0','1') NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
);



Toevoeging op 19/11/2012 13:28:40:

Dit is misschien wat makkelijker.
Ik heb index.php en view_category.php samengevoegd in index.php
Nu krijg ik de categorien te zien en als ik erop click kan ik de topics zien in index.php
Ik wil dat ze getoond worden zonderdat ik op de categorieen moet klikken.
dit is de code:

Quote:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
<?php session_start(); ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="Text/html; charset=utf-8" />
<title>Login</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>

<div id="wrapper">
<h2>Zaher Tutorial | Forum Tutorial Series - Part 1</h2>
<p>Creating basic login functionality</p>


Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
<?php
if (!isset($_SESSION['uid'])) {
    echo "<form action='login_parse.php' method='post'>
Username: <input type='text' name='username'>&nbsp;
Password: <input type='password' name='password'>&nbsp;
<input type='submit' name='submit' value='Log in' />
";
} else {
    echo "<p>You are logged in as ".$_SESSION['username']." &bull; <a href='logout_parse.php'>Logout</a>";
}

?>

<hr />
<div id = "content">
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
<?php
include_once("connect.php");
$sql = "SELECT * FROM categories ORDER BY category_title ASC";
$res = mysql_query($sql) or die(mysql_error());
$categories = "";
if (mysql_num_rows($res) > 0) {
    while ($row = mysql_fetch_assoc($res)) {
        $id = $row['id'];
        $title = $row['category_title'];
        $description = $row['category_description'];
        $categories .= "<a href='index.php?cid=".$id."' class='cat_links'>".$title." - <font size='-1'>".$description."</font></a>";
    }
    echo $categories;
} else {
    echo "<p>There are no categories available yet.</p>";
}
$cid = $_GET['cid'];
if (isset($_SESSION['uid'])) {
    $logged = " | <a href='create_topic.php?cid=".$cid."'> Click Here to Create a Topic</a>";
} else {
    $logged = " | Please log in to create topics in this forum.";
}
$sql = "SELECT id FROM categories WHERE id='".$cid."' LIMIT 1";
$res = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($res) == 1) {
    $sql2 = "SELECT * FROM topics WHERE category_id='".$cid."' ORDER BY topic_reply_date DESC";
    $res2 = mysql_query($sql2) or die(mysql_error());
    if (mysql_num_rows($res2) > 0) {
        @$topics .= "<table widht='100%' style='border-collapse: collapse;'>";
        $topics .= "<tr><td colspan='3'><a href='index.php'>Return To Forum Index</a>".$logged."<hr /></td></tr>";
        $topics .= "<tr style='background-color: #dddddd;'><td>Topics</td><td width='65' align='center'>Replies</td></tr>";
        while ($row = mysql_fetch_assoc($res2)) {
            $tid = $row['id'];
            $title = $row['topic_title'];
            $views = $row['topic_views'];
            $date = $row['topic_date'];
            $creator = $row['topic_creator'];
            $topics .= "<tr><td><a href='index.php?cid=".$cid."&tid=".$tid."'>".$title."</a><br /><span class='post_info'>
            Posted by: ".$creator." on ".$date."</span></td><td align='center'>0</td></tr>";
            
        }
        $topics .= "</table>";
        echo $topics;
    } else {
    echo "<a href='index.php'>Return to Forum Index</a><hr />";
    echo "<p>There are no topics in this category yet.".$logged."</p>";
    }
} else {
    echo "<a href='index.php'>Return to Forum Index</a><hr />";
    echo "<p>You are trying to view a category that does not exist yet.";
}
?>

</div>
</body>
</html>

Gewijzigd op 19/11/2012 13:20:02 door george mendel
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

19/11/2012 19:05:33
Quote Anchor link
Eerst voer je dit querietje uit:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
SET @row_num := 1, @prev_value := '';

Dan dit querietje:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
SELECT
    c.id,
    c.category_title,
    st.topic_id,
    st.topic_title,
    st.reply_date
FROM
    categories c
JOIN
    (
    SELECT
        @row_num := IF(@prev_value=t.category_id,@row_num+1,1) row_number,
        t.topic_title,
        t.reply,
        @prev_valu := t.category_id cat_id
    FROM
        topics t
    ORDER BY
        t.category_id, t.reply_date DESC
    ) st
    ON
        c.id = st.cat_id
WHERE
    st.row_number <= 5
ORDER BY c.id, st.reply_date


Let op dat als je in de outer query meer kolommen nodig heb van topics die dan ook in de subquery selecteerd
Gewijzigd op 19/11/2012 19:36:28 door Ger van Steenderen
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.