Mysql statement issue
Hello everyone
I am trying to fix a bug in my database and need some help with it.
Essentially, there are 2 tables: Table 1 with Registration info (people registered in various workshops) and Table 2 with Workshop info(a list of workshops). Table 2 gets information from Table 1 and in that sense are related.
What i want to happen is when I delete one person(row) from Table 1, I want to count the remaining number of registrants for that particular workshop in Table 1 and transfer that information to the Current Enrollment column of that workshop in Table 2.
i'm using phpMyAdmin 2.5.2 to interact with the database running MySQL. what statement should i be writing and in which table.
any inputs are appreciated
Busy posted this at 22:45 — 22nd September 2005.
He has: 6,151 posts
Joined: May 2001
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
Greg K posted this at 23:30 — 22nd September 2005.
He has: 2,145 posts
Joined: Nov 2003
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
subu posted this at 20:04 — 23rd September 2005.
They have: 10 posts
Joined: Aug 2005
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 posted this at 20:11 — 23rd September 2005.
He has: 2,145 posts
Joined: Nov 2003
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 posted this at 20:23 — 23rd September 2005.
They have: 10 posts
Joined: Aug 2005
thought as much.
so there are no such thing as triggers at this point.
thanks for clarifying that greg
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.