January 31, 2008

DIY Quick and Dirty MySQL query tool with PHP

We all love phpmyadmin it’s a great tool for managing databases, but it’s nice to have a web based query tool that you can allow others to access where they can get and save a query as a bookmark, and not endanger your database security too much. This is a quick and dirty way to write up a query tool that you can use over and over.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

//Function to get current time
function getmicrotime(){
@list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}
 
//Implementation like this
$start = getmicrotime();
print('<html><head><link rel="stylesheet" type="text/css" href="style.css" />');
print('<title>SQL Queries against MySQL database</title>');
print('</head><body>');
print('<div class=center>');
print('<h3>SQL Queries against MySQL database</h3>');
 
//Set a default query if no query was specified
if (isset($_GET['query'])) {
$sql = $_GET['query'];
$sql = stripslashes($sql);
 
if(strtoupper(substr($sql, 0, 6)) == 'SELECT' ||strtoupper(substr($sql, 0, 4)) == 'SHOW' || strtoupper(substr($sql, 0, 7)) == 'EXPLAIN'){
 
$sql = $sql;
 
}else{
echo "Query Failed.  Your query must begin with  SELECT, SHOW, OR EXPLAIN. ";
exit();
}
}else{
$sql="SELECT * FROM tablename LIMIT 20";
}
//Connect and select database
$link = mysql_connect('localhost', 'database_user', 'password') or die("Connection Failed");
mysql_select_db(track) or die("DB Select Failed");
//Run query
$result = mysql_query($sql, $link) or die("Query Failed.\n  " . mysql_error());
print('<div class=left70>');
print('Enter your SQL Statement:');
print('<br/>');
print('<br/>');
print('<form name=formquery method=GET action=mysqlquery.php >');
print('<textarea rows=5 cols=80 name="query" >');
print($sql);
print('</textarea>');
print('</div>');
print('<div class=right30>');
//links to resources here...
print('<input type=submit name="button" value="Submit Query" />');
print('</form>');
print('</div>');
$numfields = mysql_num_fields($result);
$numrows = mysql_num_rows($result);
print('<div class=halfcan>');
print('<hr size=1>');
print('Query results: ' . $numfields . ' field(s), ' . $numrows . ' row(s)');
print('<br/>');
print('<br/>');
//Table to display results----------------------- 
print('<table>');
print('<tr>');
for ($i=0; $i < $numfields; $i++){
$tablename = mysql_field_name($result, $i);
print('<th align=center>' . $tablename . '</th>');
}
print('</tr>'); //end header row
$j = 0; // initialize for modulo...
while( $row=mysql_fetch_row($result)){
$j++;
print('<tr>');
for ($i=0; $i < $numfields; $i++){
if($j % 2){
// I'm in an even row
$c = "";
}else{
// I'm in an odd row
$c = "class=d";
}
print('<td ' . $c . '>' . $row[$i] . '</td>'); 
}
print('</tr>');
} //end while loop
print('</table>');
mysql_free_result($result);
$end = getmicrotime();
echo '<h4>Operation took: '.($end-$start).' seconds</h4>';
print('<p><a href=index.php><i>Back to the main menu</i></a></p>');
print('</div>');
print('</div>');
print('</body></html>');

Leave a comment