Creating a common test environment quickly.
Whenever you set up a PHP test environment, or need to put it to use for a new project, there are a few tasks that need to be accomplished early on, whether it is for Wordpress, Moodle or even Sakai (which is written in Java rather than PHP). They are: create a new database, create a new user with a certain password and rights on that new database.
The name of the database and user varies, of course, along with the password, but everything else is pretty much the same, so I wrote this little utility that allows you to perform these tasks by doing little more than change three simple variables: database, user and password. You just update those three in a little include file and then you are ready to go.
I also include a section for displaying, dropping and creating a table. While perhaps less useful, it can be used to further customize the program.
Caveat #1 - do not make it available on the web.
The program is intended to be used in a test environment that it is shielded from the internet. Otherwise, anybody on the internet could use it to drop your databases and users.
Caveat #2 - protect the include files.
The program uses two include files, one with the credentials for acessing the MySQL database as root and the other for creating new databases, users and tables. As long as you are not allowing internet access, the security of these files may not be much of a concern, but it is good practice to not treat them any differently anyway. This means they need to be located outside any folder that the webserver is set up to serve pages from. They should be named with a php extension. They need to start with an opening php tag (but don't need a closing php tag).
DO include the OPENING php tag.
The opening php tag (<?php) will ensure that the contents do not get displayed in the browser and that the program gets access to the variables stored in them. Ironically, the exact opposite will happen if you leave out the php opening tag - the password and other sensitive data will be displayed in the browser, but the program will never get access to them!
DON'T use the CLOSING tag too early, if at all.
The closing php tag (?>) is not needed, since the end of file serves the same purpose. However, if you accidentally put it in immediatly after the opening tag (<?php?>) then the effect is as if you never put the opening tag in there at all.
With these words of caution, whether needed or not, you should now be in good shape to take a look at the code:
<?php
require_once ('..\..\where-no-webpages-go\WhoMe.php'); // Sample contents of WhoMe.php (three lines only):
// <?php // CAUTION: DO NOT omit this tag, and don't close it, at least not here - dumps content to screen!!!
// $conn = mysql_connect("localhost", "root", "password");
// mysql_select_db("mysql", $conn) or die(mysql_error());
require_once ('..\..\where-no-webpages-go\NoYou.php'); // set values for: $datab, $user1, $pass1, $table
?>
<h1 style="text-align: center">Basic Database Admin Form - ready for input.</h1>
<form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<table style="margin-left: 5%; background-color: lightblue; font-size: 24;">
<tr>
<td>Please choose an option:</td>
</tr><tr>
<td>
<input type="radio" name="opta" value="D1" >D1 - Show databases <br />
<input type="radio" name="opta" value="D2" >D2 - Drop database <?php echo $datab ?><br />
<input type="radio" name="opta" value="D3" >D3 - Create database <?php echo $datab ?><br />
--------------------------------<br />
<input type="radio" name="opta" value="U1" >U1 - Show users <br />
<input type="radio" name="opta" value="U2" >U2 - Delete user <?php echo $user1 ?><br />
<input type="radio" name="opta" value="U3" >U3 - Create user <?php echo $user1 ?><br />
--------------------------------<br />
<input type="radio" name="opta" value="T1" >T1 - Select * from <?php echo $datab.".".$table ?><br />
<input type="radio" name="opta" value="T2" >T2 - Drop table <?php echo $datab.".".$table ?><br />
<input type="radio" name="opta" value="T3" >T3 - Create table <?php echo $datab.".".$table ?><br />
--------------------------------<br />
<input type="radio" name="opta" checked value="N1" >N1 - Clear the output area
</td>
</tr>
<tr>
<td><input type="submit" name="Submit" value="Process"></td>
</tr>
</table>
</form>
<?php if ($_SERVER['REQUEST_METHOD'] == 'POST'){
// error_reporting(0);
$flds1=array('id', 'Make', 'Model', 'Year', 'Date & Time Entered');
$connY="<br />You are now connected to the database!<br /><br />";
$connN="<br />You are no longer connected to the database,<br />";
function optaHdr() { echo "<br />You are NOW CONNECTED to the database!<br />"; }
function optaFtr() {
mysql_close($GLOBALS["conn"]); echo "You are NO LONGER CONNECTED to the database!<br /><br />"; }
function tblhdr(&$fldset) {
$holdhorse = "<table border=\"1\" style=\"margin-left: 35%\";>";
$holdhorse=$holdhorse."<tr>";
foreach ($fldset as &$value) { $holdhorse=$holdhorse."<td>$value</td>"; }
$holdhorse=$holdhorse."</tr><br />";
return $holdhorse;
}
function tblftr() { echo "<br /></table>"; }
function gimmeYesOrNo (&$sql) {
global $conn;
// echo "Executing query: \"".$sql."\"<br />";
// CAUTION: line above commented out because the sql to create user includes the password...
if (mysql_query($sql, $conn)) { echo "Query: \"".$sql."\" executed successfully\n"; }
else { echo "<br />Error executing query \""."$sql"."\": <br />".mysql_error() . "<br />\n";}
}
function gimmeTable (&$sql, &$flds) {
global $conn; global $flds1; global $flds2; global $datab; global $table;
if ($result = mysql_query($sql, $conn))
{
$row_cnt = mysql_num_rows($result);
$fld_cnt = mysql_num_fields($result);
echo "Resultset of query \"".$sql."\" is this table with ".$row_cnt;
echo " row(s) and ".$fld_cnt." field(s) per row : ";
echo tblhdr($flds);
while ($row = mysql_fetch_row($result))
{ echo "<tr>";
for ($i=0;$i<$fld_cnt;$i++) printf ("<td>%s</td>", $row[$i]);
echo "</tr>"; }
tblftr();
// echo "<br />";
}
else { echo "<br />Error executing query \""."$sql"."\": <br />".mysql_error() . "<br />\n";}
}
switch($_POST['opta']){
//////////////////////////////////////////////////////////////////////////////////////////////
case 'D1':
optaHdr();
$sql = "show databases;";
$flds2=array('Database: ');
gimmeTable ($sql,$flds2);
optaFtr();
break;
//////////////////////////////////////////////////////////////////////////////////////////////
case 'D2':
optaHdr();
$sql = "DROP DATABASE $datab;";
gimmeYesOrNo($sql);
optaFtr();
break;
//////////////////////////////////////////////////////////////////////////////////////////////
case 'D3':
optaHdr();
$sql = "CREATE DATABASE $datab;";
gimmeYesOrNo($sql);
optaFtr();
break;
//////////////////////////////////////////////////////////////////////////////////////////////
case 'U1':
optaHdr();
$sql = "select user, host from user order by user;";
$flds2=array('User: ', 'Host: ');
gimmeTable ($sql,$flds2);
optaFtr();
break;
//////////////////////////////////////////////////////////////////////////////////////////////
case 'U2':
optaHdr();
$sql = "select user from user where user = '$user1';";
if ($result = mysql_query($sql, $conn))
{
if (mysql_num_rows($result) == 0)
{ echo "Unable to delete user \"".$user1."\" (user not in table) <br />"; }
else
{ $sql = "delete from user where user = '$user1';"; gimmeYesOrNo($sql); }
}
else { echo "<br />Error executing query \""."$sql"."\": <br />".mysql_error() . "<br />\n";}
optaFtr();
break;
//////////////////////////////////////////////////////////////////////////////////////////////
case 'U3':
optaHdr();
$sql="GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES";
$sql=$sql.", LOCK TABLES ON $datab.* TO '$user1'@'localhost' IDENTIFIED BY '$pass1'";
gimmeYesOrNo($sql);
optaFtr();
break;
//////////////////////////////////////////////////////////////////////////////////////////////
case 'T1':
optaHdr();
$sql = "select * from $datab.$table;";
$flds2=array('FldName1: ', 'FldName2: ', 'FldName3: ');
gimmeTable ($sql,$flds1);
optaFtr();
break;
//////////////////////////////////////////////////////////////////////////////////////////////
case 'T2':
optaHdr();
$sql = "DROP TABLE $datab.$table;";
gimmeYesOrNo($sql);
optaFtr();
break;
//////////////////////////////////////////////////////////////////////////////////////////////
case 'T3':
optaHdr();
$sql2 = "CREATE TABLE $datab.$table (`$flds1[0]` int NOT NULL AUTO_INCREMENT";
$sql2 = $sql2.", PRIMARY KEY (`$flds1[0]`), UNIQUE KEY `$flds1[0]` (`$flds1[0]`)";
$sql2 = $sql2.", `$flds1[1]` varchar(20) default NULL";
$sql2 = $sql2.", `$flds1[2]` varchar(20) default NULL, `$flds1[3]` int default NULL";
$sql2 = $sql2.", `$flds1[4]` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);";
echo "Executing query 1: \"".$sql2."\"<br />";
if (mysql_query($sql2, $conn))
{ echo "Query 1 of 3 executed successfully<br /><br />\n";
$sql3="INSERT INTO $datab.$table ($flds1[1], $flds1[2], $flds1[3]) ";
$sql3 = $sql3."VALUES ('Isuzu', 'Dunno', 1997);";
echo "Executing query 2: \"".$sql3."\"<br />";
if (mysql_query($sql3, $conn))
{ echo "Query 2 of 3 executed successfully<br /><br />\n";
$sql4="SELECT * FROM $datab.$table;";
echo "Executing query 3: \"".$sql4."\"<br />";
$result=mysql_query($sql4, $conn);
if ($result)
{
echo "Query 3 of 3 executed successfully<br /><br />\n";
$field_cnt = mysql_num_fields($result);
$row_cnt=mysql_num_rows($result);
while ($row = mysql_fetch_row($result))
{ for ($i=0;$i<$field_cnt;$i++) printf ("%s \n", $row[$i]); }
}
else { echo 'Error executing query 3 (Select): ' . mysql_error() . "\n";}
}
else { echo 'Error executing query 2 (Insert): ' . mysql_error() . "\n";}
}
else { echo 'Error executing query 1 (Create): ' . mysql_error() . "\n";}
mysql_close($conn);
echo $connN;
break;
//////////////////////////////////////////////////////////////////////////////////////////////
case 'N1':
echo " ";
break;
//////////////////////////////////////////////////////////////////////////////////////////////
default:
echo "Invalid or no option selected, please try again!";
} // end of switch module
} // end of if stmt (checking for post or not)
?>