Sorry for all my posts lately. I wish my SS skills would be a lot better but so far you guys have been great in helping me and hopefully my posts will help some other SS newbies.

I'm wondering if anyone knows if it's possible to pass a variable from the URL into a function?

I have this function that I need to pass a variable into its mysql query.

function Alts() {
			$db = new MySQLDatabase(array("server" => "localhost","username" => "****","password" => "*****","database" => "*****"));
			
			$query = $db->query("SELECT * FROM roster WHERE Member='Alt' AND Owner=$variable ORDER BY Name ASC");
				$result = new DataObjectSet();
				foreach($query as $row) {
				$result->push(new ArrayData($row));
				}
					return $result;
			}

When I click a link like this: http://someurl.com?variable=Brig the mysql query would select on Member='Alt' and Owner='Brig'.

I've tried the normal PHP way of doing it but get an error, something like unexpected T_Variable.

Hopefully there is a way of doing this just that I haven't found it yet.

Thanks for taking the time to have a look.

Hi there Brig

Ouch! That's a really bad habit right there and a dangerous one too.
$variable will only be populated when "register_globals" is on, that's a php setting that's disabled on most hosting providers due to security issues. It has nothing to do with SilverStripe.

Usually you would access such a variable using the $_GET global array. Like so:

$variable = $_GET['variable'];

But there's still a huge security risk by doing so. Somebody could inject any SQL code in there, potentially destroying your database. You should make sure, the value you're getting is save to use.
The easiest thing would be to use the raw2sql convert function

$variable = Convert::raw2sql($_GET['variable']);

Personally I tend to even go further and validate my user input if it matches a desired pattern (where applicable)

// the following check will make sure, "variable" only consists of 
// lower- or uppercase letters, at least one, 20 at max.
if(!preg_match('/^[A-z]{1,20}$/', $_GET['variable'])){
	// doesn't match, get me out of here
	return;
}

// escape for sql, now actually redundant because of 
// our previous check, but still good practice
$variable = Convert::raw2sql($_GET['variable']);

Correction
My code-example above has a flaw. One should check if $_GET['variable'] exists, before using it, otherwise you'll get a PHP Notice when trying to access the page without the query string (?variable=xy).
Therefore, one should check first, if the desired variable exists:

if(isset($_GET['variable'])){
	// code that uses $_GET['variable'] goes here.
} else {
	// behavior when variable wasn't supplied goes here
}

Hmm yes I can see why this could be a bad idea. To be honest I didn't even think about the SQL injection risk! Thanks for pointing that out banal.

I'm not really sure how to solve this problem the best way of it's is even possible in any good way.

Please have a look at the site I'm building: http://nda.johannesengstrom.com/guild-roster/

The names in this list should be links which calls the function I showed above and the variable is the name.

The only solution I could think of was to use the URL but maybe someone can think of a different way?

If anyone got any ideas please let me know. Would be very grateful!

Hi Brig

No, using the URL for this is completely valid. Just make sure you sanitize input before using it for DB queries.
Instead of using the query string, you could also make use of SilverStripes controller actions.

Have a look at this example controller:

// RosterPage class omitted

class RosterPage_Controller extends Page_Controller {
	protected $memberData = false;
	
	public function alts(){
		// get the second url parameter (ID if using default routes)
		$var = Director::urlParam('ID');

		// check if input matches a pattern, otherwise issue an error
		if(!preg_match('/^[A-z]{1,20}$/', $var))
			return $this->httpError(404, 'Not a valid address');
			
		// get data from db. Maybe use SQLQuery for this?
		// http://doc.silverstripe.com/doku.php?id=sqlquery
		$this->memberData = // assign the DataObjectSet to $this->memberData

		// template engine needs that to render RosterPage_alts.ss
		return array();
	}
	
	// getter for the memberdata, to be used in the template
	public function getMemberData(){
		return $this->memberData;
	}
}

Using the above code, you can then call URLs like that:
http://nda.johannesengstrom.com/guild-roster/alts/MemberName

/alts is the function of the controller that's being called and MemberName is the value that will be passed to the ID url-param. What's left to do is simply checking if the ID is valid, then get the DataObjectSet for that ID (use SQLQuery?).
The beauty of this approach is, that you can even use a custom template (or layout) for that page. You have to name the template file RosterPage_alts.ss. In this template, you can then access the team-member data using $MemberData

Hope that was clear. If you got any questions, feel free to ask.

Thanks a lot banal for taking the time to help me. Really appreciate it!

That looks like a really good way of doing it. Should be able to implement this myself I think.

The only thing I would maybe need some help with if you have the time is the SQLQuery.
I read up on it and it looks quite different to the queries I've used so far so not sure where to put the database details such as database name and username/password.

I'm getting the data from an external database not the SS database.

Thanks again!

Oh I see.
Well, I never connected to a second database while using SilverStripe but something like this should work:

// get the current connection
$connection = DB::getConn();
// connect to the new DB
DB::connect(array(
	'type'		=> 'MySQLDatabase',
	'server'	=> 'localhost', 
	'username'	=> 'user', 
	'password'	=> 'pass', 
	'database'	=> 'dbname'
));

// run query here, it will use the new connection

// revert to old connection
DB::setConn($connection);

So what you do is: Store the current (SilverStripe) DB connection in a variable, connect to the new Database, run SQLQuery, after you got your data, revert to the old connection.

Thanks a lot again banal.

I've tried to create the new function but getting another error message.

Parse error: syntax error, unexpected T_RETURN

Here's my function:

class Roster_Controller extends Page_Controller {
	
	protected $memberData = false;
	
	/**
	* Alts - Returns alts of a member
	*/
	
		public function alts(){ 
		// get the second url parameter (ID if using default routes) 
		$var = Director::urlParam('ID');

		// check if input matches a pattern, otherwise issue an error 
		if(!preg_match('/^[A-z]{1,20}$/', $var)) 
			return $this->httpError(404, 'Not a valid address'); 

		// get data from db
		
		// get the current connection 
		$connection = DB::getConn(); 
		// connect to the new DB 
		DB::connect(array( 
			'type'      => 'MySQLDatabase', 
			'server'	=> 'localhost', 
			'username'   => '***', 
			'password'   => '***', 
			'database'   => '***' 
		));

		// run query here, it will use the new connection
		$sqlQuery = new SQLQuery();
		$sqlQuery->select = array(
			'Name', 'Level', 'Class', 'Owner'
		);
		
		$sqlQuery->from = array(
			"roster"
		);
		
		$sqlQuery->where = array(
			"Owner = $var"
		);
		
		$result = $sqlQuery->execute();
		var_dump($result->first()); // array
		
		$myDataObjectSet = singleton('roster')->buildDataObjectSet($result);
		var_dump($myDataObjectSet->First()); // DataObject
		
		$PlayerAlt = $myDataObjectSet->First();
		var_dump($PlayerAlt->Name);
		var_dump($PlayerAlt->Level);
		var_dump($PlayerAlt->Class);
		var_dump($PlayerAlt->Owner);

		// revert to old connection 
		DB::setConn($connection);
		
		$this->memberData = // assign the DataObjectSet to $this->memberData

		// template engine needs that to render RosterPage_alts.ss 
		return array(); 
		} 

		// getter for the memberdata, to be used in the template 
		public function getMemberData(){ 
		return $this->memberData; 
   }

Can you see anything obvious that is wrong?

Hehe yeah. You didn't assign the member data. This:

$this->memberData = // assign the DataObjectSet to $this->memberData

is invalid.. I just put that in my code as example. It should say something like:

$this->memberData = $PlayerAlt;

I assume, the var_dump statements are for debugging purposes?