Location: PHPKode > projects > EXtensible Production Suite (XPS) > xpsdev/helpers/query_tool.php
<?php

/* query_tool.php ***********************************
*													*
*	author: heidtc									*
****************************************************/

Class Query_tool
{

    Private $conn 	= '';
    Private $ERROR  = '';
    
    Public $sql		= '';
    
	Public $default_dsn 	= 'mysql:host=0297-cookbt; dbname=XPS;';
    Public $dsn 			= '';
    
	Public $default_user 	= 'xpsadmin';
    Public $user 			= '';
    
	Public $default_pass 	= '';
	Public $pass 			= '';
    
/********************************************
********************************************/
    function __construct()
	{
        
		if(isset($_POST['user']))
		{
			$this->user	= $_POST['user'];
        }
        else
        {
        	$this->user	= $this->default_user;
        }
        
		if(isset($_POST['pass']))
		{
			$this->pass	= $_POST['pass'];
		}
		else
        {
        	$this->pass	= $this->default_pass;
        }
        
		if(isset($_POST['dsn']))
		{
			$this->dsn 	= $_POST['dsn'];
		}
		else
        {
        	$this->dsn	= $this->default_dsn;
        }

		if(isset($_POST['sql']))
		{
		    $this->sql	= trim($_POST['sql']);
		    $this->sql	= str_replace("\\",'',$this->sql);
		    $this->_connect();

		}
	}
	
/********************************************
********************************************/
    private function _connect()
	{
		try 
		{
            $this->conn = new PDO($this->dsn, $this->user, $this->pass);
            $this->conn->beginTransaction();
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return true;
        } 
		catch (PDOException $e) 
		{
            $this->ERROR .=  $e->getMessage() . '<br/>';
            return false;
        }
    }

/********************************************
********************************************/
	private function execute($sql) 
	{
    	try 
		{
	        $statement = $this->conn->prepare($sql);			        
			$statement->execute();
	        return true;
        }
		catch (PDOException $e) 
		{
			$this->ERROR .= $e->getMessage() . '<br />';
			$this->conn->rollBack();
			return false;
		}
    }

/********************************************
********************************************/
	private function select($sql) 
	{
        //returns recordset
        try 
		{
            $statement = $this->conn->prepare($sql);
            $statement->execute();
            return $statement->fetchAll();
        } 
		catch (PDOException $e) 
		{
            $this->ERROR .= $e->getMessage() . '<br />';
            return false;
        }

    }
	
/********************************************
********************************************/
	public function query_table()
	{
		$content = '';
		$sql = $this->sql;
		
		if($this->ERROR != '')
		{
			$content .= '<div>Unable to connect to the database due to the following error:</div><div class="error_message">' . $this->ERROR . '</div>';
		}
		elseif($sql != '')
		{
			//not expecting output
			if(eregi("^insert|^update|^delete|^alter|^commit|^rollback|^grant|^create|^drop|^administer|^execute|^become|^analyze|^audit|^comment|^force",$sql))
			{
				if($this->execute($sql))
				{
					$content .= 'update successfull';
				}
				else
				{
					$content .= '<div>Unable to execute the statement due to the following error:</div><div class="error_message">' . $this->ERROR . '</div>';
				}
			}
			else
			{
				if($record_set = $this->select($sql))
				{
					$content .= '<div>Query: ' . '<span style="color:orange">' . $sql . '</span></div><br />';
					$content .= '<table class="result">';
					$content .= '<tr>';
					
					//print record set keys
					foreach($record_set as $rows)
					{
						foreach($rows as $key => $cell)
						{
							if(!is_int($key))
							{
								$content .= '<th>' . $key . '</th>';
							}
						}
						break;
					}
					$content .= '</tr>';
					
					//print record set data
					foreach($record_set as $rows)
					{
						$content .= '<tr>';
						foreach($rows as $key => $cell)
						{
							if(!is_int($key))
							{
								$content .= '<td>' . $cell . '</td>';
							}
						}
						$content .= '</tr>';
					}
					$content .= '</table>';
				}
				else
				{
					$content .= '<div>Unable to execute the statement due to the following error:</div><div class="error_message">' . $this->ERROR . '</div>';
				}
			}
		}
		return $content;
	}

}//End class
?>




<!------------------------------------------------------------------------------------->

<?php $Query_tool = new Query_tool() ?>

<html>
<head>
	<style type="text/css">
	
	.error_message
	{
		color:red;
	}
	
	table.result
	{
		border: 1px solid red;
		border-collapse: collapse;
	}
	
	table.result th
	{
		border: 1px solid red;
	}

	table.result td
	{
		border: 1px solid red;
		text-align:center;
	}
	
	table.control
	{
		border: 1px dotted #FFFFFF;
		color:#000000;
		background-color:#999999;
		text-align:center;
		border-collapse: separate;
	}
	
	table.control td
	{
		
		text-align:center;
		font-weight:bold;
	}
	
	.title
	{
		color:#FFFFFF;
		font-size:22;
		font-style:italic;
	}
	</style>

<title>php Uber Query Tool</title>
</head>

<body bgcolor="black" text="white">

<div >
	<form name="queryForm" method="post" action="query_tool.php">
		<table align="center" class="control">
			<tbody>
				<tr>
					<td>
						<span class="title">php Uber Query Tool</span><br />
						Now with karate-chop pdo action!
					</td>
					<td style="text-align:right;">
						<label FOR="dsn">DSN</label>
						<input style="width:275px;" type="text" name="dsn" id="dsn" value="<?php echo $Query_tool->dsn; ?>">
						<input type="button" name="reset_dsn" value="Reset" onClick="document.getElementById('dsn').value = document.getElementById('default_dsn').value;"><br />
						
						<label FOR="user">USER</label>
						<input style="width:275px;" type="text" name="user" id="user" value="<?php echo $Query_tool->user; ?>">
						<input type="button" name="reset_user" value="Reset" onClick="document.getElementById('user').value = document.getElementById('default_user').value;"><br />
						
						<label FOR="pass">PASS</label>
						<input style="width:275px;" type="text" name="pass" id="pass" value="<?php echo $Query_tool->pass; ?>">
						<input type="button" name="reset_pass" value="Reset" onClick="document.getElementById('pass').value = document.getElementById('default_pass').value;"><br />
					</td>
				</tr>
				<tr>
					<td colspan=2>
						<br />
						<label FOR="template">Template</label>
						<select style="width:555px" name="template" onChange="document.getElementById('sql').value = this.options[this.selectedIndex].value;">
							<option value="" selected>None</option>
							<OPTION VALUE="SELECT * FROM _">SELECT * FROM _</option>
							<OPTION VALUE="UPDATE _ SET _ = _ WHERE _ = _">UPDATE _ SET _ = _ WHERE _ = _</option>
							<OPTION VALUE="INSERT INTO _">INSERT INTO _</option>
							<OPTION VALUE="DELETE FROM _ WHERE _ = _">DELETE FROM _ WHERE _ = _</option>
						</select>
						<br />
						<textarea style="overflow: auto; width:100%" name="sql" id="sql" rows="5"><?php echo $Query_tool->sql; ?></textarea>
						<input type="Submit" name="submit" value="Execute Query">
						&nbsp;|&nbsp;
						<input type="button" name="button" value="Clear" onClick="document.getElementById('sql').value = '';">
					</td>
				</tr>
			</tbody>
		</table>
	</form>
</div>

<div style="text-align:center;">
	<?php echo $Query_tool->query_table(); ?><br />
</div>
<input type="hidden" id="default_dsn" value="<?php echo $Query_tool->default_dsn; ?>">
<input type="hidden" id="default_user" value="<?php echo $Query_tool->default_user; ?>">
<input type="hidden" id="default_pass" value="<?php echo $Query_tool->default_pass; ?>">
</body>
</html>
Return current item: EXtensible Production Suite (XPS)