subu — Fri, 23 Sep 2005 20:23:51 +0000

thought as much.
so there are no such thing as triggers at this point.
thanks for clarifying that greg

Greg K — Fri, 23 Sep 2005 20:11:57 +0000

that is a statement that you will ahve to manualy execute.

from what i was seeing in the docs, looks like newer versions of mySQL allow for stored procedures, not sure how fancy they are. I know with MS SQL server you can store procedures to automatically do things like this.

-Greg

subu — Fri, 23 Sep 2005 20:04:34 +0000

thanks 'busy' and 'greg' for that info.
but I want to link the action of 'deletion' with the updating of the Current enrollment. so that when I click the delete button of a row in phpMyAdmin, the query for counting remaining rows must execute and transfer of that number to the other table should occur. will the above statement "SELECT COUNT(id) FROM table1 WHERE workshopid=ID NUMBER" achieve that?

Greg K — Thu, 22 Sep 2005 23:30:58 +0000

you would also need to limit the SELECT to be jsut the workshop you are wanting

ie
SELECT COUNT(id) FROM table1 WHERE workshopid=<strong><em>ID NUMBER</em></strong>'

If you are doing this a lot, it would probably be best to have a script update the info for all the workshops, maybe have it run as a cron job during the middle of the night, or manually run it after you have done some deleting.

<?php
  
      echo \"WORKSHOP ENROLLMENT COUNT UPDATE SCRIPT<br>\n\";
      echo \"Script Started: \" . date(\"l dS of F Y h:i:s A\") . \"<br>\n<br>\n\";
  
      // THESE SHOULD BE SET UP IN YOUR phpMyAdmin config file
      $dbH = mysql_connect('hostname','username','password')
          or die ('Could not connect to database server: ' . mysql_error());
      
      // THIS SHOULD ALSO BE SET UP IN YOUR phpMyAdmin config file
      mysql_select_db('databasename',$dbH)
          or die ('Could not access database: ' . mysql_error());
          
      // NOTE YOU WILL HAVE TO ADJUST THESE BASED ON WHAT YOU ACTUALLY
      // USED FOR YOUR TABLES. 
      
      $sql = \"SELECT workShopID, COUNT(workShopID) FROM userTable\";
      
      $dbResult = mysql_query($sql,$dbH);
      
      $resData = array();
      while($resData[] = mysql_fetch_assoc());
      
      foreach ($resData as $rowData)
      {
          list($rowID,$rowCount) = $rowData;
          $sql = \"UPDATE workshopTable SET enrollment=$rowCount WHERE workshopID=$rowID\";
          echo \"Updating Workshop ID $rowID With an Enrollment of $rowCount ... \";
          if (mysql_query($sql,$dbH))
              echo \"SUCCESS!<br>\n\";
          else 
              echo \"FAILED!<br>\n\";
      }        
      
      mysql_close($dbH);
      
      echo \"<br>\nScript Finished: \" . date(\"l dS of F Y h:i:s A\") . \"<br>\n\";
  
  
?>

Now this is just off the top of my head after a LONG day at work, so may need some tweaking...

-Greg

Busy — Thu, 22 Sep 2005 22:45:23 +0000

Select count(id) from Table 1

Insert the result into Table 2

id would be whatever you call the Table 1 member numbers, bit trickt not knowing the table names etc but hopefully it is enough for you to go on