Dropping tables...
I have to re-create a table every month with a section of data from the main table. I suppose this would be an excellent situation to use views for but being as how MySQL doesn't have view capability yet I'm stuck with this. It's not a big deal but I just want to make sure that Creating, populating, using, dropping a table every month won't impact my database performance. Is there any specific optimization or maintainence I should be doing after dropping the table each month? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dropping Tables
I have researched this and can't find an answer. Maybe I am just looking in the wrong places or not putting the correct keywords into the search engines. So any help would be greatly appreciated. I am using MySQL with PHP. I have some PHP scripts that create tables in a database that are meant to be temporary but can't actually create TEMPORARY tables to do the job. That isn't the problem in itself. The problem is that sometimes if the script is stopped prematurely, it doesn't get to the drop table function in it. All of the tables that are created for this start with an a and follow with a random number. What I need to do is to create a script that I can run that will delete all tables that start with an a in the database. Again, since the table name is randomly generated except for the starting a, I will need the script to find them and drop them. Has anybody done this before? I would sure appreciate some help here. Thanks Steve -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ow3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: [PHP] Dropping Tables
Thank You so much John and Michael. SHOW TABLES LIKE 'a%' worked like a charm and was exactly what I was looking for. I guess my searches were using the wrong keywords. Kind of figures. Some of my searches were turning up 1000's of results. How do you know whether it's an active a* table or not? Will you be dropping a table that someone is actually using? I will be shutting down the script at a certain time each day to run some things like this for maintenance. So none of these should be active and if they are, it won't hurt anything. The customer can just wait until the maintenance time is over. Approximately 10 mintues. At 10:21 PM 12/29/2002 -0500, you wrote: Basically just do a a php script the sends the sql show tables; Then do a strchr() to see if a is the first letter in the resuts and if so do a delete table. Just yo play safe do a dump of your db first. Or even better, use SHOW TABLES LIKE 'a%' to just get the a* tables and drop each one. How do you know whether it's an active a* table or not? Will you be dropping a table that someone is actually using? ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ow3 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ow3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Dropping tables
Is there anyway to drop a group of tables with a wildcard in MySql? I have tried: DROP TABLE IF EXISTS division1s* and DROP TABLE IF EXISTS division1s(*) but neither one works. I am hoping that there is a good useable answer to this. Thanks in advance Steve Buehler - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Dropping tables
At 16:40 -0600 3/25/02, Steve Buehler wrote: Is there anyway to drop a group of tables with a wildcard in MySql? I have tried: DROP TABLE IF EXISTS division1s* and DROP TABLE IF EXISTS division1s(*) but neither one works. I am hoping that there is a good useable answer to this. The syntax described in the manual shows what what you want to do is not supported. You can't do it directly in SQL. But depending on the context in which you're issuing the statement, you can achieve the objective by other means. For example, if you're using some kind of API, you can use SHOW TABLES LIKE 'division1s%', then use the resulting list of tables to construct a DROP TABLES statement that drops the tables. Thanks in advance Steve Buehler - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Dropping tables
Thank you. I am still not an expert yet at MySQL and/or PHP. I learn something new everyday. At least this list doesn't knock people down for asking questions that could be found by either a 1 minute or a 1 month search on the web. I will try this out in the morning. I see no reason why your suggestion would not work. So I will thank you before I even try it. :) Thanks Steve At 09:58 PM 3/25/2002, you wrote: At 16:40 -0600 3/25/02, Steve Buehler wrote: Is there anyway to drop a group of tables with a wildcard in MySql? I have tried: DROP TABLE IF EXISTS division1s* and DROP TABLE IF EXISTS division1s(*) but neither one works. I am hoping that there is a good useable answer to this. The syntax described in the manual shows what what you want to do is not supported. You can't do it directly in SQL. But depending on the context in which you're issuing the statement, you can achieve the objective by other means. For example, if you're using some kind of API, you can use SHOW TABLES LIKE 'division1s%', then use the resulting list of tables to construct a DROP TABLES statement that drops the tables. Thanks in advance Steve Buehler - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Dropping tables
At 22:20 -0600 3/25/02, Steve Buehler wrote:
Thank you. I am still not an expert yet at MySQL and/or PHP. I
learn something new everyday. At least this list doesn't knock
people down for asking questions that could be found by either a 1
minute or a 1 month search on the web. I will try this out in the
morning. I see no reason why your suggestion would not work. So I
will thank you before I even try it. :)
If you're using PHP, you should be able to use a function like this:
function delete_tables_with_pattern ($conn_id, $pat)
{
$query = SHOW TABLES LIKE '$pat';
$result_id = mysql_query ($query, $conn_id);
if ($result_id)
{
$table = array ();
while (list ($name) = mysql_fetch_row ($result_id))
$table[] = $name;
mysql_free_result ($result_id);
if (count ($table) 0) # some tables were named
{
$query = DROP TABLES . join (,, $table);
mysql_query ($query, $conn_id);
}
}
}
Call it like this:
delete_tables_with_pattern ($conn_id, division1s%);
However, before actually using it as shown above, you probably want to
replace the second mysql_query() with a print statement. :-)
Thanks
Steve
At 09:58 PM 3/25/2002, you wrote:
At 16:40 -0600 3/25/02, Steve Buehler wrote:
Is there anyway to drop a group of tables with a wildcard in
MySql? I have tried:
DROP TABLE IF EXISTS division1s*
and
DROP TABLE IF EXISTS division1s(*)
but neither one works. I am hoping that there is a good useable
answer to this.
The syntax described in the manual shows what what you want to do is not
supported. You can't do it directly in SQL.
But depending on the context in which you're issuing the statement,
you can achieve the objective by other means. For example, if you're
using some kind of API, you can use SHOW TABLES LIKE 'division1s%',
then use the resulting list of tables to construct a DROP TABLES
statement that drops the tables.
Thanks in advance
Steve Buehler
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Dropping Tables like . . .
Is there a way to drop all tables that match a certain pattern, kind of like using a LIKE statement in DROP statement. if this worked it would be perfect drop table like 'TT%'; in other words dropping all the tables in a database that start with 'TT'. Roger Karnouk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
