[SOLVED] MySQL query for a CMS category page

[avillanu]

I'm programming a basic CMS. Lets say there are three categories:

 

Headline News

Business News

Local News

 

I'd like there to be a category page which would display all of the content in that category. For example, on the "local news" page, I'd like it to display all the local news stories.

 

I know how to implement it so far but the one twist is that I'd like it to display only say 10 stories a page. One could click on the next or previous page to see the next or previous ten stories.

 

This is how I think I'll implement this (warnings newbie thinking! ): Have a request variable to see what page it is on. If this ID is 1, the script will display 1 to 10. If it's 2, it'll be 11 to 20.

 

For the MySQL query have some sort of loop that will display ten stories with a simple MySQL fetch array.

 

I guess my question if this is possible - a MySQL query to select all the stories in category, put it in order according to ID and select the nth one

 

 

[SnowControl]

Just use a $_GET variable in your URL to get the page number. Example:

 

http://yoursite.com/index.php?page=3

 

For the code to get the job with pages done:

 

$page=$_GET['page'];

/* Say page 1 is the first one with the most recent news. Multiply the page number with the amount of news per page. 
Thats your last LIMIT value. Subtract the same amount (10) again to find the first LIMIT value. */

$lastnews = $page * 10;
$firstnews = $lastnews - 10;

// Run the query

$query="SELECT * FROM news ORDER BY date DESC LIMIT $firstnews,$lastnews";
$result=mysql_query($query);
$num=mysql_num_rows($result);

// Output the data

for ($i=0 ; $num > $i ; $i++) {

$newstext=mysql_result($result,$i,"newstext");

echo "$newstext <br /><hr /><br />";

}

 

 

This one does have security issues. And if you try page 0 or similar you will of course get a mysql error. This is just to show you the outline.

 

[avillanu]

Thanks for the help, but what is the vulnerabilty in that code?

[SnowControl]

Any user input is a possible security breach. You have to validate every input you get. See this tutorial for a brief look at whats called SQL Injection. http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

 

When getting the $page variable, you should use a function to clear out the quotes that might come with it. I use htmlspecialchars(), as that will remove all the >,<, " and ' characters. This will stop a sql injection. (there might be other security issues too, please enlighten me if you can)

 

$page = htmlspecialchars($_GET['page'],ENT_NOQUOTES);