Jump to content

SQL update when you delete a pilot


mark1million

Recommended Posts

  • Moderators

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.

Link to comment
Share on other sites

  • Administrators

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;
}

Link to comment
Share on other sites

  • Moderators

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.

Link to comment
Share on other sites

  • Moderators

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 :)

Link to comment
Share on other sites

  • Administrators

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);
}

Link to comment
Share on other sites

  • Moderators

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 :lol:

Thanks in advance.

Link to comment
Share on other sites

  • Administrators

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]'";

Link to comment
Share on other sites

  • Moderators

It works ok it was me not doing something right :lol:

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.

Link to comment
Share on other sites

  • Administrators

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...