SQL update when you delete a pilot

[mark1million]

HI guys,

When you delete a pilot from the admin I want to be able to edit that code to also update another field in my database, basically what i have is external authentication switched on for IPB, I have pilot id and the password and the account creation for the fourm, i also have a further check retired=0 so when i delete a pilot that has never flown i also want to update that field to retired preventing forum access.

So to finally get to the point where does that code live and can i just reference it to the pilot id and update the retired column?

Thanks.

[TAV1702]

Pilots can be deleted without jacking anything up in the system? I thought that was a no no so to speak.

[mark1million]

No its fine, as long as you use the admin function within phpvms, its the last tab on the pilot admin.

What i do is pilots who have not flown for 6 months get deleted, if they want to join again then that's fine they get issued another number.

[simpilot]

It starts in the PilotAdmin.php controller in /admin/modules/PilotAdmin/ on lines 70 - 83

case 'deletepilot':

			$pilotid = $this->post->pilotid;
			$pilotinfo = PilotData::getPilotData($pilotid);

			PilotData::DeletePilot($pilotid);

			$this->set('message', Lang::gs('pilot.deleted'));
			$this->render('core_success.tpl');


			LogData::addLog(Auth::$userinfo->pilotid, 'Deleted pilot '.PilotData::getPilotCode($pilotinfo->code, $pilotinfo->pilotid).' '.$pilotinfo->firstname .' ' .$pilotinfo->lastname);

			break;

and the data model is in PilotData.class.php on lines 507 - 531

public static function deletePilot($pilotid)
{
	$sql = array();
	unset(self::$pilot_data[$pilotid]);

	$sql[] = 'DELETE FROM '.TABLE_PREFIX.'acarsdata WHERE pilotid='.$pilotid;
	$sql[] = 'DELETE FROM '.TABLE_PREFIX.'bids WHERE pilotid='.$pilotid;
	$sql[] = 'DELETE FROM '.TABLE_PREFIX.'pireps WHERE pilotid='.$pilotid;
	$sql[] = 'DELETE FROM '.TABLE_PREFIX.'pilots WHERE pilotid='.$pilotid;

	# These SHOULD delete on cascade, but incase they don't
	$sql[] = 'DELETE FROM '.TABLE_PREFIX.'fieldvalues WHERE pilotid='.$pilotid;
	$sql[] = 'DELETE FROM '.TABLE_PREFIX.'groupmembers WHERE pilotid='.$pilotid;
	$sql[] = 'DELETE FROM '.TABLE_PREFIX.'pirepcomments WHERE pilotid='.$pilotid;

	foreach($sql as $query)
	{
		$res = DB::query($query);
	}

	if(DB::errno() != 0)
		return false;

	return true;
}

[mark1million]

Thanks Dave, I will start tinkering with that lot later.

For example then, if i was to just add a line to the PilotData.Class.php that should update the table as its in the same database.

$sql[] = 'DELETE FROM '.TABLE_PREFIX.'mytable WHERE pilotid='.$pilotid;

[simpilot]

That should work Mark, as long as the table is in the same database as your phpVMS install. If not wou will have to connect to the correct one and run a separate query, but could do it within that overall function.

[mark1million]

Thanks for that Dave, Just one more query if i may, when a pilot registers they are set as retired =1 in the pilots database, when they are accepted by admin that table is updated with the following code,

/**
   * Accept the pilot (allow them into the system)
   */
  public static function AcceptPilot($pilotid)
  {
     return self::updateProfile($pilotid, array('confirmed'=>PILOT_ACCEPTED, 'retired'=>'0'));
  }

I have a separate table in the same db that has a flag when pilots register set to 1, what i need to do is update that to 0 when they are accepted, can i just add the query below that line?

example,

/**
   * Accept the pilot (allow them into the system)
   */
  public static function AcceptPilot($pilotid)
  {
     return self::updateProfile($pilotid, array('confirmed'=>PILOT_ACCEPTED, 'retired'=>'0'));
     $sql = "UPDATE `mydb`.`mytable` SET `retired` = \'0\' WHERE `mytable`.`myrow` = \'$pilotid\';";
  }

Or is it a bit more complex than that?, I know you shouldn't mess with the code as it can get messy on a update.

[mark1million]

Ok so i have had another idea, instead of modding all the files i shouldn't be maybe i should go about it a bit more simpler, would i be able to add a button to the pilots profile titled "Activate Forum Account" and have the button update the field in the db?

Could i also put that in to an if else statement so it will disappear once pressed or change to activated, all this thinking is hurting my little brain :lol

Im no expert but i have come a long way with php and mysql since phpVMS, All help greatly appreciated though

[simpilot]

I think your second theory may be closer to a solution than the first, as you are correct that once you start changing core files updates can become daunting. What about a seperate module with a hook to catch any activation, deactivation, or deletion. That way the module would not be affected by an update.

If I am following correctly, for starters you want to register and activate the pilot in the forum when his application is approved. I used the registration_accepted hook to tell the SMF register module to register the pilot with the forum.

Something like this may be the start

class ForumRegister extends CodonModule
{
       public function __construct() {
           CodonEvent::addListener('ForumRegister', array('registration_complete'));
       }
       public function EventListener($eventinfo) {
           if($eventinfo[0] == 'registration_complete') {
               $this->forum_register($eventinfo);
           }
       }

       public function forum_register($eventinfo)    {
              ForumRegisterData::forum_register($eventinfo);
      }
}

then include your db functions in a data class

function forum_register($eventinfo)    {
     $sql = "sql call to insert pilot in forum table"; 
     DB::query($sql);
}

[mark1million]

Yep, thats what i use now to insert the data in to the db.

my head is starting to hurt now lol.

OK so this is where i am at now.

In the pilot profile main i have added a button that needs to update a record in my database when pushed by the pilot, so here is the code im using,

<form action="../../insert.php" method="post">
<input name="pilotid" type="hidden" value="<?php echo $pilotcode; ?>" />
<input name="Username" type="hidden" value="<?php echo $userinfo->firstname . ' ' . $userinfo->lastname; ?> <?php echo $pilotcode; ?>" />
<input type="submit" value="Create Forum Account" />
</form> 

Insert works fine but i want it to update a record where the pilot id is the pilots id.

When i hit the button i get "Error: Query was empty" here is also my insert.php cpde,

<code>

<?php

$con = mysql_connect("localhost","removed","removed");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("mydatabase", $con);

mysql_query("UPDATE mydatabase SET fullid_name = '$_POST[username]'

WHERE s_client_name = '$_POST[pilotid]'");

if (!mysql_query($sql,$con))

{

die('Error: ' . mysql_error());

}

echo "1 record added";

mysql_close($con)

?>

</code>

Any help would be appreciated to fix this sucker as all i seem to do now is go round in circles and start to confuse myself even more

Thanks in advance.

[simpilot]

It looks like the script is thinking you have not run a query because when it gets to

if (!mysql_query($sql,$con))

there is no value assigned to $sql. Is the data showing up in the database? I think you need to change

mysql_query("UPDATE mydatabase SET fullid_name = '$_POST[username]'
WHERE s_client_name = '$_POST[pilotid]'");

to

$ sql = "UPDATE mydatabase SET fullid_name = '$_POST[username]'
WHERE s_client_name = '$_POST[pilotid]'";

[mark1million]

It works ok it was me not doing something right

Just one more thing i need conformation with, when a pilot registers all the data is put in to the database using the function forum_register($eventinfo), now i need to update a row when a pilot is approved, in the PilotData.class.php line 448 can i add my custom $sql query in to that loop?

/**
 * Accept the pilot (allow them into the system)
 */
public static function AcceptPilot($pilotid)
{
	return self::updateProfile($pilotid, array('confirmed'=>PILOT_ACCEPTED, 'retired'=>'0'));
               //My custom sql update HERE
}

As i understand there is no hook for when a pilot is accepted just when he/she registers?

Just got to finish this off and its all complete.

[mark1million]

Is there a hook when a pilot is accepted in to the system, what triggers the sending of the email once they are accepted, could i tap in to that?

[simpilot]

It is on the admin side, PilotAdmin.php on line 535 is the dispather for the hook.

CodonEvent::Dispatch('pilot_approved', 'PilotAdmin', $pilot);

The catch here is that the hook is only available on the admin side, so you will need to build another module to reside in the admin tree to catch that hook, it can however share the data class.