Basic CMS Questions

[eluser]Las3r[/eluser]
Hi there,

been devving for a bit now in CI, and of course very happy about the framework.

Since I'm not a very experienced programmer, I have some questions to those who've done this before:

1) CMS Functionality :: How to show content?

I was thinking that i could make a pages.php controller with a simple function index that queries the database with uri(segment(2)) > so myweb.com/pages/bananas would do a "Select * from content where page='banana' ".

The downside of this is that if someone is willingly flooding the pages.php controllr (myweb.com/pages/$randomstringhere), he will be able to flood the database with queries.

Another thing i was thinking of to first define an array in the start of the pages.php controller that fetches all the pages (titles only) from the DB, and creates an array. Then the script will check if the uri(segment) is in the array, if yes => fetch content and show. This will relief the DB greatly, as spam is no longer possible. Is this the way I should go ?

2) Show loads of query results :: How to query?

I already built a support-ticket response script in CI, and i'm happily using it. I was wondering: Let's say there's 200 tickets in the database that are all waiting for an answer. How do I show this to the user ? I'm already using jqueries' tablesorter to sort the data rather than letting the server do this, so this is not an issue, but how would you go over /tickets/1 - /tickets/2, tickets/3 etcetera, so that the site admin won't get 500 tickets loaded in his screen, but rather 20 "per page" ? I haven't done this before, so any input is appreciated!

THanks!

Erik

[eluser]xwero[/eluser]
1 why not do a check if the page exists?

Code:

function page_exists($page)
{
  return ($this->db->where('page',$page)->count_all_results() == 0) ? false : true ;
}

2 pagination is the solution but then you can't use the jquery tablesorter anymore as it will only sort the page results and not the whole recordset. To paginate the databse you have to use the limit keyword or method if you are using CI AR to build your sql statements.

[eluser]Las3r[/eluser]
1)
Won't my database be SPAMMED if someone decides to unleash some bots on my site that force the script to search if page_exists() every time?

2) I am _not_ using AR, as I don't see myself familiar with that, but I will probably find a way, i'll research pagination in the user_guide !

Thanks Xwero

[eluser]ray73864[/eluser]
the same could be said for anything really, if you chose to use ID numbers instead of friendly names, then your database could be spammed with queries for a certain page anyway.

The quickest way to speed up a query is to index a column, so if you index the friendly name column then your search would be really quick anyway.

[eluser]xwero[/eluser]
1: The query to load all slugs and find out if the page exists or not will take more time than a query to check if the slug exists. If you really want to take the load off the database you save the slugs in a separate file and load that to find out if the page exists or not.

[eluser]jedd[/eluser]
[quote author="Las3r" date="1236609936"]I was thinking that i could make a pages.php controller with a simple function index that queries the database with uri(segment(2)) > so myweb.com/pages/bananas would do a "Select * from content where page='banana' ".

The downside of this is that if someone is willingly flooding the pages.php controllr (myweb.com/pages/$randomstringhere), he will be able to flood the database with queries.

Another thing i was thinking of to first define an array in the start of the pages.php controller that fetches all the pages (titles only) from the DB, and creates an array. Then the script will check if the uri(segment) is in the array, if yes => fetch content and show. This will relief the DB greatly, as spam is no longer possible. Is this the way I should go ?[/quote]

AFAICT you're suggesting that if someone hits your page with a random string, you'll do a DB lookup thus:

Code:

SELECT * FROM content WHERE page="randomstring";

And you're thinking that for each page hit you'd be better off with a DB call thus:

Code:

SELECT page FROM content;

The first DB call will consistently return either 0 or 1 row. The second DB call is guaranteed to return many rows. I don't follow how this will reduce your DB load?

If you want to stop people bothering your web site, you probably need to look at session management, and introducing micro delays in lookups based on IP address - that kind of thing.

[eluser]Las3r[/eluser]
K thanks guys for your replies - very usefull.

@above,

the title is defined as primary key (indexed as well), so a select page from content will only return a max of 1 row , if any at all.

I decided to go with the friendly-name lookup, as the stress is minimal eitherways (our main application uses MSSQL), and since it's a seperate web-DB, I think that's the way to go, thanks!

[eluser]jedd[/eluser]
[quote author="Las3r" date="1236617982"]
the title is defined as primary key (indexed as well), so a select page from content will only return a max of 1 row , if any at all.
[/quote]

Warning: unasked for database schema opinion impending.

You probably should consider using an auto-inc as the PK, rather than the page title. Still index the title, of course.

[eluser]charlie spider[/eluser]
i also use the jquery tablesorter for big sets of data.

did you know there is a pagination plugin that goes with that ?

http://tablesorter.com/docs/example-pager.html

i'm currently using it on a chamber of commerce site i'm building and i've got a table with 300+ members showing business name, address, contact person, phone number, email, etc,
a big ugly mess of data (but gosh, jquery sure duz make it look purty) and the pager plugin is smokin' fast. you can also customize the drop down box for how many items per page you wish to view.

super easy