News + Custom DB Columns

[Kieran]

Hey there,

My VA has three/four separate divisions, and each of them would like their own news category.

The thing is, phpVMs does not have an option for this at the moment.

I can simply add a column to the database, but can somebody point me in the right direction as to how to edit the admin module to display a drop-down with possible menu categories, and to display news from only one category on certain pages?

If all else fails then I could use manual SQL queries but I'd prefer the proper method.

Thank you,

Kieran

[Nabeel]

Poke around in the SiteCMS.class.php in core/common. you can modify those to include a category id, or an airline id. you can add in a drop-down with the airlines list, so that would simply that. so you'd just have to pass the $this->post->airlineid variable through to the query

[Kieran]

Right I have a function like this (I have successfully coded the add-news section):

	public static function GetNewsItemByCategory($category,$limit)
{
	if (!$limit) {
	return DB::get_results('SELECT *, UNIX_TIMESTAMP(postdate) AS postdate 
								FROM '.TABLE_PREFIX.'news WHERE category=`'.$category.'`');
	}
	elseif ($limit)
	{
	return DB::get_results('SELECT *, UNIX_TIMESTAMP(postdate) AS postdate 
								FROM '.TABLE_PREFIX.'news WHERE category=`'.$category.'` LIMIT '. $limit);
	}
}

Now how would I integrate this into my page, so that I could echo all of the news from a specific category?

[Nabeel]

I simplified it a bit for you, if you don't mind.

public static function GetNewsItemByCategory($category,$limit)
{
$sql = 'SELECT *, UNIX_TIMESTAMP(postdate) AS postdate 
	FROM '.TABLE_PREFIX.'news WHERE category=`'.$category.'`';

if (!empty($limit))
{
	$sql .= ' LIMIT '.$limit;
}

return DB::get_results($sql);
}

You can call it as (assuming you put it in SiteCMS, or even better, just create a new dataclass for it so it doesnt get overwritten - to do that just follow the same format as the other classes in there, ClassName.class.php, then inside that is class ClassName { }... in fact, creating a class like that called CustomQueries (so CustomQueries.class.php, and inside class CustomQueries{ } ) to put all your custom code so it's not overwritten in an update is probably a good idea)

$news = SiteCMS::getNewsItemByCategory($category, 1);

[Kieran]

Thank you for that, I now have a Custom.class.php as well.

I have this code on my page now:

$news = Custom::GetNewsItemByCategory('notam', 1);

But I'm stuck as to what to do with it, an attempt at a For-each loop displays an 'Invalid Argument Supplied' error.

[Nabeel]

Thank you for that, I now have a Custom.class.php as well.

I have this code on my page now:

$news = Custom::GetNewsItemByCategory('notam', 1);

But I'm stuck as to what to do with it, an attempt at a For-each loop displays an 'Invalid Argument Supplied' error.

After that line, put a DB::debug() statement, like:

$news = Custom::GetNewsItemByCategory('notam', 1);
DB::debug();

There might be an error in the SQL, but it will tell you exactly what was returned from the database

[Kieran]

Got it working now, the DB::debug was empty but I relaised that the ` character should really have been " or ' .

However, it doesn't seem to be ordering the query results correctly?

It always seems to return the FIRST post and never the most recent.

[Kieran]

EDIT: Used an ORDER BY to fix that out...

Nabeel, we could do with you expertise over on the phpBB AutoRegistration Dev Thread,

Thanks...

Kieran