How To show articles form just one category

Dla86

Posted on 10 Feb 2011, 11:01:52

Access: Member
Total Posts: 21
Joined: 2011-02-10

Hello,
I'm sorry for my english, I'm Italian.
I'd like to show the articles by this script:

Code:


 <?php
                require("dati.php");
                $tot = 3;
                $query = "SELECT * FROM news30_story
                          ORDER by id desc
                          LIMIT 0,$tot";
                          
                $result = mysql_query($query) 
                or die("Caricamento fallito: ".mysql_error());
                while ($row = mysql_fetch_array($result))
                {                
                echo "<a href='news_1.php?id=".$row['title']."'><b>", $row['title'], "</b></a>";
                echo "<br>";
                echo "<font>", substr ($row['story'],0,150), " (..)</font>";
                echo "<br>";
                

                }
                mysql_close($connection);                
?>

But I have a problem.I'd like to show the articles from just one category I created, not every one. Is It possible? thanks to everybody!

#3178

Chris

Posted on 12 Feb 2011, 10:09:37

Access: Admin
Total Posts: 1395
Joined: 2006-05-19

This should do the job

Code:


<?php
require_once('news/db.php');
require_once('news/config.php');

// Category to use
$category = 'Sports';

// Grab the ID of the category
$categoryid = DataAccess::fetch(sprintf("SELECT id FROM %s WHERE name = ?", NEWS_CATS), $category);
$categoryid = $categoryid['0']['id'];

// Grab all news articles assigned to the category
$newsarticles = DataAccess::fetch(sprintf("SELECT title, story, shortstory FROM %s WHERE id IN (SELECT storyid FROM %s WHERE catid = ?)", NEWS_ARTICLES, NEWS_GROUPCATS), $categoryid);

// Loop through each one
foreach($newsarticles AS $article){
    echo "<a href='news_1.php?id=".$article['title']."'><b>", $article['title'], "</b></a>";
    echo "<br>";
    echo "<font>", substr ($article['story'],0,150), " (..)</font>";
    echo "<br>";
}
?>

#3190

Last edited by Chris at 2011-02-12 10:10:11 Reason:

Dla86

Posted on 13 Feb 2011, 11:14:22

Access: Member
Total Posts: 21
Joined: 2011-02-10

Really Thanks!

but the trouble is, In the page news1.php I have this script:

Code:



<?php
                require("dati.php");
                $tot = 10;
                $query = "SELECT * FROM news_story WHERE id = " . $_GET['id'] . " ORDER by id desc";
                
                $row = mysql_fetch_array(mysql_query($query));
                
            
                echo "<h1>", $row['title'], "</h1>";
                echo "<p>";
                echo $row['story'];
                echo "</p>";
                mysql_close($connection);                
?>

When I click on the News, The script searches a page that It doesn't exist :(
How I should change the second script? thanks

#3194

Chris

Posted on 13 Feb 2011, 11:23:04

Access: Admin
Total Posts: 1395
Joined: 2006-05-19

First script

Code:


<?php
require_once('news/db.php');
require_once('news/config.php');

// Category to use
$category = 'Sports';

// Grab the ID of the category
$categoryid = DataAccess::fetch(sprintf("SELECT id FROM %s WHERE name = ?", NEWS_CATS), $category);
$categoryid = $categoryid['0']['id'];

// Grab all news articles assigned to the category
$newsarticles = DataAccess::fetch(sprintf("SELECT title, story, shortstory, id FROM %s WHERE id IN (SELECT storyid FROM %s WHERE catid = ?)", NEWS_ARTICLES, NEWS_GROUPCATS), $categoryid);

// Loop through each one
foreach($newsarticles AS $article){
    echo "<a href='news_1.php?id=".$article['id']."'><b>", $article['title'], "</b></a>";
    echo "<br>";
    echo "<font>", substr ($article['story'],0,150), " (..)</font>";
    echo "<br>";
}
?>

Second script

Code:


<?php
require_once('news/db.php');
require_once('news/config.php');

$article = DataAccess::fetch(sprintf("SELECT * FROM %s WHERE id = ?", NEWS_ARTICLES), $_GET['id']);

echo " <h1>", $article['0']['title'], "</h1>";
echo "<p>";
echo $article['0']['story'];
echo "</p>";     
?>

Also, you should NEVER do this

Code:


 $query = "SELECT * FROM news_story WHERE id = " . $_GET['id'] . " ORDER by id desc";

This is a huge SQL injection vulnerability.

#3195

Dla86

Posted on 13 Feb 2011, 12:12:20

Access: Member
Total Posts: 21
Joined: 2011-02-10

Thanks!!

#3196

Dla86

Posted on 02 Mar 2011, 17:48:55

Access: Member
Total Posts: 21
Joined: 2011-02-10

The problem is, I'd like to show the latest articles first... but with this code I see the oldest articles first...

#3282

Chris

Posted on 02 Mar 2011, 18:02:06

Access: Admin
Total Posts: 1395
Joined: 2006-05-19

Code:


<?php
include 'news/index.php';
?>

By default shows the latest articles first.

#3283

Dla86

Posted on 02 Mar 2011, 18:14:26

Access: Member
Total Posts: 21
Joined: 2011-02-10

yes but I use this code:

Code:


<?php
require_once('news/db.php');
require_once('news/config.php');

// Category to use
$category = 'Sports';

// Grab the ID of the category
$categoryid = DataAccess::fetch(sprintf("SELECT id FROM %s WHERE name = ?", NEWS_CATS), $category);
$categoryid = $categoryid['0']['id'];

// Grab all news articles assigned to the category
$newsarticles = DataAccess::fetch(sprintf("SELECT title, story, shortstory, id FROM %s WHERE id IN (SELECT storyid FROM %s WHERE catid = ?)", NEWS_ARTICLES, NEWS_GROUPCATS), $categoryid);

// Loop through each one
foreach($newsarticles AS $article){
    echo "<a href='news_1.php?id=".$article['id']."'><b>", $article['title'], "</b></a>";
    echo "<br>";
    echo "<font>", substr ($article['story'],0,150), " (..)</font>";
    echo "<br>";
}
?>

#3288

Chris

Posted on 02 Mar 2011, 18:17:54

Access: Admin
Total Posts: 1395
Joined: 2006-05-19

If all you're wanting to do is display the latest articles then doing the simple include I posted above is the best way to do this.

If you insist on using the code you posted change this line

Code:


$newsarticles = DataAccess::fetch(sprintf("SELECT title, story, shortstory, id FROM %s WHERE id IN (SELECT storyid FROM %s WHERE catid = ?)", NEWS_ARTICLES, NEWS_GROUPCATS), $categoryid);

To the following

Code:


$newsarticles = DataAccess::fetch(sprintf("SELECT title, story, shortstory, id FROM %s WHERE id IN (SELECT storyid FROM %s WHERE catid = ? ORDER BY id DESC)", NEWS_ARTICLES, NEWS_GROUPCATS), $categoryid);

Change "DESC" to "ASC" if you want the articles reversed.

#3289

Dla86

Posted on 02 Mar 2011, 18:38:12

Access: Member
Total Posts: 21
Joined: 2011-02-10

I'm sorry..but nothing changed..

Code:


<?php

require_once('news/db.php');
require_once('news/config.php');

// Category to use
$category = 'news';

// Grab the ID of the category
$categoryid = DataAccess::fetch(sprintf("SELECT id FROM %s WHERE name = ?", NEWS_CATS), $category);
$categoryid = $categoryid['0']['id'];

// Grab all news articles assigned to the category
$newsarticles = DataAccess::fetch(sprintf("SELECT title, story, shortstory, id FROM %s WHERE id IN (SELECT storyid FROM %s WHERE catid = ? ORDER BY id ASC)", NEWS_ARTICLES, NEWS_GROUPCATS), $categoryid);
// Loop through each one
foreach($newsarticles AS $article){
    echo "<div style='border-bottom:1px solid #999'>";
    echo "<a href='news1.php?id=".$article['id']."'><b>", $article['title'], "</b></a>";
    echo "<br>";
    echo "<font>", substr ($article['shortstory'],0,150), " (..)</font>";
    echo "</div>";
    echo "<br>";
}
?>

#3291