DROP TABLE using variable

Luke Warm · February 19, 2011

Hi,

I'm trying to drop a table form a database using the table name as a variable. This variable is set within a session. I am using a form where you input the table name, then the DROP TABLE procedure is executed in the next page.

I know that the variable gets passed because it prints the table name variable out when I "echo".

The execution is simple (code found in deletetable.php)

$temp = $_POST['temp'];
$query = "DROP TABLE $temp";
if(mysql_query($query)){
echo "<h1 style='color:green;'>Table $temp deleted</h1>";}
else{
echo "<h1 style='color:red;'>Deletion of table '$temp' failed!</h1>";}

Here's my code for the two pages:

delete_table.php

<?php
include("includes/header.html");
session_start();
$hostname = $_SESSION['hostname'];
$user = $_SESSION['user'];
$passwd = $_SESSION['passwd'];
$database = $_SESSION['database'];
$tblname = $_SESSION['table'];

$conn = mysql_connect($hostname, $user, $passwd, $database);
/*if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}*/
?>
<div id="container">
<div id="header">Create DB</div>
<div id="breadcrumbs">
<?php
function check_port($port) {
    $conn = @fsockopen("127.0.0.1", $port, $errno, $errstr, 0.2);
    if ($conn) {
        fclose($conn);
        return true;
    }
}

function server_report() {
    $report = array();
    $svcs = array('3306'=>'MySQL');
    foreach ($svcs as $port=>$service) {
        $report[$service] = check_port($port);
    }
    return $report;
}

$report = server_report();
?>
<div id="server"><p>Server is <?php echo $report['MySQL'] ? "running" : "offline"; ?></p></div>
<?php
if(!$_SESSION['hostname']){
echo "<font color='red'>Not connected to server!</font>";
} else {
echo "<font color='green'>Server: ".$_SESSION['hostname']."</font>";
} 
if(!$_SESSION['database']){
echo "";
}else{
echo "<br><font color='green'>Database: ".$_SESSION['database']."</font>";
}
?>
</div>
<div id="nav">
<p><a href="#" onclick="javascript:window.open('connect_db.php','_self');"><img src="server_conn_ico.png" align="left" />Server Connection</a></p>
<p><a href="databases.php"><img src="databases_ico.png" align="left" />View Database</a></p>
<p><a href="create_db.php"><img src="server_folder_ico.png" align="left" />Create Database</a></p>
<p><a href="drop_db.php"><img src="drop_ico.png" align="left" />Drop Database</a></p>
<p><a href="create_table.php"><img src="status_ico.png" align="left" />Add Table</a></p>
<p><a href="add_fields.php"><img src="server_ico.png" align="left" />Add Fields</a></p>
<p class="active"><a href="delete_table.php"><img src="status_delete_ico.png" align="left" />Delete Table</a></p>
<p><a href="view_tables.php"><img src="user_admin_ico.png" align="left" />View Tables</a></p>
<p><a href="view_data.php"><img src="user_admin_ico.png" align="left" />View Table Data</a></p>
<p><a href="http://localhost/phpmyadmin/" target="_blank"><img src="phpmyadmin_ico.png" align="left" />phpMyAdmin</a></p>
<p><a href="connect_info.php"><img src="health_ico.png" align="left" />System Info</a></p>
<p><a href="error_log.php"><img src="logs_ico.png" align="left" />Server Log</a></p>
<p><a href="bug_report.php"><img src="bugs-icon.png" align="left" />Bug Reports</a></p>
</div>
<div id="controls"><a href="javascript:self.close()"><img src="controls.png" /></a></div>
<div id="main">
<div id="content">
<!--<h1>Delete Database Table</h1>-->
<?php
if(empty($database)) {
echo "<p>You must be connected to a database in order to delete a table.</p>";
}else{
//echo 'Connected successfully to \''.$database.'\'';

// Check tables
		$sql = "SHOW TABLES FROM $database";
		$result = mysql_query($sql);
		if (mysql_num_rows($result) > 0) {
			echo "<p style='text-align:left;padding-left:24px;'>Available tables:</p>\n";
			echo "<pre style='text-align:left;padding-left:24px;font-size:1.2em;'>\n";
			while ($row = mysql_fetch_row($result)) {
				echo "{$row[0]}\n";
			}
			echo "</pre>\n";
		}
?>
<form action="deletetable.php" method="post">
Enter table you'd like to delete from '<?php echo $database; ?>' database:
<input name="temp" type="text" /><br/>
<input name="Submit" type="submit" value="  Delete Table  " />   <input type='button' value='  Cancel  ' onclick="javascript:window.location='connect_db.php';"></p>
</form>
<?php
}
include("includes/footer.html");
?>

Executing file deletetable.php:

<?php
include("includes/header.html");
session_start();
$hostname = $_SESSION['hostname'];
$user = $_SESSION['user'];
$passwd = $_SESSION['passwd'];
$database = $_SESSION['database'];
//$tblname = $_SESSION['table'];

$conn = mysql_connect($hostname, $user, $passwd, $database);
?>
<div id="container">
<div id="header">Create DB</div>
<div id="breadcrumbs">
<?php
function check_port($port) {
    $conn = @fsockopen("127.0.0.1", $port, $errno, $errstr, 0.2);
    if ($conn) {
        fclose($conn);
        return true;
    }
}

function server_report() {
    $report = array();
    $svcs = array('3306'=>'MySQL');
    foreach ($svcs as $port=>$service) {
        $report[$service] = check_port($port);
    }
    return $report;
}

$report = server_report();
?>
<div id="server"><p>Server is <?php echo $report['MySQL'] ? "running" : "offline"; ?></p></div>
<?php
if(!$_SESSION['hostname']){
echo "<font color='red'>Not connected to server!</font>";
} else {
echo "<font color='green'>Server: ".$_SESSION['hostname']."</font>";
} 
if(!$_SESSION['database']){
echo "";
}else{
echo "<br><font color='green'>Database: ".$_SESSION['database']."</font>";
}
?>
</div>
<div id="nav">
<p><a href="#" onclick="javascript:window.open('connect_db.php','_self');"><img src="server_conn_ico.png" align="left" />Server Connection</a></p>
<p><a href="databases.php"><img src="databases_ico.png" align="left" />View Database</a></p>
<p><a href="create_db.php"><img src="server_folder_ico.png" align="left" />Create Database</a></p>
<p><a href="drop_db.php"><img src="drop_ico.png" align="left" />Drop Database</a></p>
<p><a href="create_table.php"><img src="status_ico.png" align="left" />Add Table</a></p>
<p><a href="add_fields.php"><img src="server_ico.png" align="left" />Add Fields</a></p>
<p class="active"><a href="delete_table.php"><img src="status_delete_ico.png" align="left" />Delete Table</a></p>
<p><a href="view_tables.php"><img src="user_admin_ico.png" align="left" />View Tables</a></p>
<p><a href="view_data.php"><img src="user_admin_ico.png" align="left" />View Table Data</a></p>
<p><a href="http://localhost/phpmyadmin/" target="_blank"><img src="phpmyadmin_ico.png" align="left" />phpMyAdmin</a></p>
<p><a href="connect_info.php"><img src="health_ico.png" align="left" />System Info</a></p>
<p><a href="error_log.php"><img src="logs_ico.png" align="left" />Server Log</a></p>
<p><a href="bug_report.php"><img src="bugs-icon.png" align="left" />Bug Reports</a></p>
</div>
<div id="controls"><a href="javascript:self.close()"><img src="controls.png" /></a></div>
<div id="main">
<div id="content">
<?php
$temp = $_POST['temp'];
$query = "DROP TABLE $temp";
if(mysql_query($query)){
echo "<h1 style='color:green;'>Table $temp deleted</h1>";}
else{
echo "<h1 style='color:red;'>Deletion of table '$temp' failed!</h1>";}

include("includes/footer.html");
?>

Your help is greatly appreciated!!

Muddy_Funster · February 19, 2011

could you change your code in deletetable.php to this and let us know what comes back :

$temp = $_POST['temp'];
$query = "DROP TABLE ".$temp;
echo $query
mysql_query($query) or die ('Deletion of table '$temp' failed! -- '.mysql_error());
echo "<h1 style='color:green;'>Table $temp deleted</h1>";

Luke Warm · February 19, 2011

I get nothing. deletetable.php won't even load.

Muddy_Funster · February 19, 2011

forgot the ; on the echo line :/

stick that in and see how we got on, you might also want to turn error reporting on for development.

Pikachu2000 · February 19, 2011

You should be developing on a sytem with:

display_errors = On

and

error_reporting = -1

Luke Warm · February 19, 2011

I did add the ";" to the echo line and error reporting is definitely on and displaying.

I'm on a Linux machine FYI.

Muddy_Funster · February 19, 2011

LAMP/WAMP/WIMP it's all the same at this level. What's the full code from the deletetable.php file (including the <?php and ?> tags)?

Luke Warm · February 19, 2011

I placed the entire page code in my original post.

Here it is again with your code included:

<?php
include("includes/header.html");
session_start();
$hostname = $_SESSION['hostname'];
$user = $_SESSION['user'];
$passwd = $_SESSION['passwd'];
$database = $_SESSION['database'];
//$tblname = $_SESSION['table'];

$conn = mysql_connect($hostname, $user, $passwd, $database);
?>
<div id="container">
<div id="header">Create DB</div>
<div id="breadcrumbs">
<?php
function check_port($port) {
    $conn = @fsockopen("127.0.0.1", $port, $errno, $errstr, 0.2);
    if ($conn) {
        fclose($conn);
        return true;
    }
}

function server_report() {
    $report = array();
    $svcs = array('3306'=>'MySQL');
    foreach ($svcs as $port=>$service) {
        $report[$service] = check_port($port);
    }
    return $report;
}

$report = server_report();
?>
<div id="server"><p>Server is <?php echo $report['MySQL'] ? "running" : "offline"; ?></p></div>
<?php
if(!$_SESSION['hostname']){
echo "<font color='red'>Not connected to server!</font>";
} else {
echo "<font color='green'>Server: ".$_SESSION['hostname']."</font>";
} 
if(!$_SESSION['database']){
echo "";
}else{
echo "<br><font color='green'>Database: ".$_SESSION['database']."</font>";
}
?>
</div>
<div id="nav">
<p><a href="#" onclick="javascript:window.open('connect_db.php','_self');"><img src="server_conn_ico.png" align="left" />Server Connection</a></p>
<p><a href="databases.php"><img src="databases_ico.png" align="left" />View Database</a></p>
<p><a href="create_db.php"><img src="server_folder_ico.png" align="left" />Create Database</a></p>
<p><a href="drop_db.php"><img src="drop_ico.png" align="left" />Drop Database</a></p>
<p><a href="create_table.php"><img src="status_ico.png" align="left" />Add Table</a></p>
<p><a href="add_fields.php"><img src="server_ico.png" align="left" />Add Fields</a></p>
<p class="active"><a href="delete_table.php"><img src="status_delete_ico.png" align="left" />Delete Table</a></p>
<p><a href="view_tables.php"><img src="user_admin_ico.png" align="left" />View Tables</a></p>
<p><a href="view_data.php"><img src="user_admin_ico.png" align="left" />View Table Data</a></p>
<p><a href="http://localhost/phpmyadmin/" target="_blank"><img src="phpmyadmin_ico.png" align="left" />phpMyAdmin</a></p>
<p><a href="connect_info.php"><img src="health_ico.png" align="left" />System Info</a></p>
<p><a href="error_log.php"><img src="logs_ico.png" align="left" />Server Log</a></p>
<p><a href="bug_report.php"><img src="bugs-icon.png" align="left" />Bug Reports</a></p>
</div>
<div id="controls"><a href="javascript:self.close()"><img src="controls.png" /></a></div>
<div id="main">
<div id="content"><br/>
<?php
$temp = $_POST['temp'];
$query = "DROP TABLE ".$temp;
echo $query;
mysql_query($query) or die ('Deletion of table '$temp' failed! -- '.mysql_error());
echo "<h1 style='color:green;'>Table $temp deleted</h1>";

include("includes/footer.html");
?>

Muddy_Funster · February 19, 2011

mysql_query($query) or die ('Deletion of table '$temp' failed! -- '.mysql_error());

my bad again...should be

mysql_query($query) or die ('Deletion of table '.$temp.' failed! -- '.mysql_error());

Luke Warm · February 19, 2011

I get the following message:

DROP TABLE smithereensDeletion of table smithereens failed! -- No database selected

HOWEVER...

This works!

$temp = $_POST['temp'];
$sql = "DROP TABLE $temp";
mysql_select_db( $database );
$retval = mysql_query( $sql );
if(!$retval)
{
  die('Could not delete table: ' . mysql_error());
}
echo "Table $temp deleted successfully\n";

I appreciate your help. Thanks.

Sign In

DROP TABLE using variable

Recommended Posts

Luke Warm

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

Luke Warm

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

Luke Warm

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

Luke Warm

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

Luke Warm

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information