| Subject | Handy little query tool |
|---|---|
| Author | Nigel Weeks |
| Post date | 2005-04-12T03:29:37Z |
Hi all,
I had a need to write a tiny sql script runner in PHP, and thought I'd
contribute it.
It's entirely self contained, and can run multiple queries seperated by a
semicolon.
Have fun!
Nige.
----- BEGIN CODE -----
<?php
echo "<html><head><title>DBA</title>
<style type=text/css>
BODY {
font-family:tahoma,helvetica;
font-size:10pt;
}
INPUT, TEXTAREA {
font-family:helvetica;
font-size:8pt;
}
TD {
border:1px solid #CCCCCC;
border-top:0px;
font-size:8pt;
}
.even {
border:1px solid #AAAAAA;
border-top:0px;
background-color:#DDDDDD;
}
.t {
background-color:#DDDDAA;
}
</style>
</head>
<body>
";
if($action == ""){
echo "<TABLE width=100%><TR><form method=post action='dba.php'
TARGET='dba_iframe'><TD class=even>
Server String<br><input type=text name='dbstring'></TD>
<TD class=even>Username<br><input type=text name='dbuser'></TD>
<TD class=even>Password<br><input type=text name='dbpass'></TD></TR>
<TR><TD COLSPAN=3>Query<br>
<TEXTAREA NAME=query rows=10 cols=120></TEXTAREA></TD></TR>
<TR><TD class=even COLSPAN=3 align=right><INPUT TYPE=SUBMIT NAME='action'
value='Run Query'></TD></TR>
<tr><td colspan=3><IFRAME NAME='dba_iframe' width=100%
height=300></IFRAME></TD></TR>
</TABLE><br>
</BODY></HTML>";
} else {
echo "Connecting to server...";
if(!ibase_connect($dbstring,$dbuser,$dbpass)){
echo "FAILED! Check dbstring, username, and password.";
} else {
echo "Ok<br>";
// Chop it into multiple queries
$queries = explode(";",$query);
$qcount = count($queries);
for($qc = 0;$qc<$qcount;$qc++){
$query = str_replace("\\'","'",$queries[$qc]);
$query = str_replace('\\"','"',$query);
$sanitized = str_replace("<","<",$query);
$sanitized = str_replace(">","*gt;",$sanitized);
if($query != "" && $query != " "){
echo "<b>Performing Query:</b> '".$sanitized."'...";
$start = microtime();
if(!$rec = ibase_query($query)){
echo "Failed!";
} else {
echo "Ok<br>";
echo "Query time: ";
$begin = substr($start,11).".".substr($start,2,8);
$finish = microtime();
$end = substr($finish,11).".".substr($finish,2,8);
echo number_format(substr(($end - $begin),0,8),4)."<br>";
$affected = ibase_affected_rows();
if($affected > 0){
echo "<b>No results from Updates or Inserts</b><br>";
} else {
$fc = ibase_num_fields($rec);
echo "<TABLE><TR>";
// Get the field names
for($a=0;$a<$fc;$a++){
$colinfo = ibase_field_info($rec,$a);
echo "<TD
class=t><small>".$colinfo['relation'].".</small><br>".$colinfo['name']."</td
echo "</TR>";
$rows = 0;
while($row = ibase_fetch_row($rec)){
if($rows%2 == 0){
$cl = "";
} else {
$cl = " class=even";
}
echo "<TR>";
for($a=0;$a<$fc;$a++){
echo "<TD".$cl.">".$row[$a]."</TD>";
}
echo "</TR>";
$rows++;
}
echo "<TR><TD class=t COLSPAN=".$fc."'>Record Count:
".$rows."<br>";
$begin = substr($start,11).".".substr($start,2,8);
$finish = microtime();
$end = substr($finish,11).".".substr($finish,2,8);
echo "Total Time: ";
echo number_format(substr(($end - $begin),0,8),4)."<br>";
echo "Rows per second: ";
echo number_format(substr(($rows/($end - $begin)),0,8),2);
echo "</TD></TR>";
echo "</TABLE>";
} // End of valid result set
} // End of valid query
}
}
} // End of valid connect
}
?>
----- END CODE -----
Nigel Weeks
Tech Support & Systems Developer
nweeks@...
www.examiner.com.au
71 - 75 Paterson Street
Launceston
Tasmania 7250
Australia
Phone: 03 6336 7234
[Non-text portions of this message have been removed]
I had a need to write a tiny sql script runner in PHP, and thought I'd
contribute it.
It's entirely self contained, and can run multiple queries seperated by a
semicolon.
Have fun!
Nige.
----- BEGIN CODE -----
<?php
echo "<html><head><title>DBA</title>
<style type=text/css>
BODY {
font-family:tahoma,helvetica;
font-size:10pt;
}
INPUT, TEXTAREA {
font-family:helvetica;
font-size:8pt;
}
TD {
border:1px solid #CCCCCC;
border-top:0px;
font-size:8pt;
}
.even {
border:1px solid #AAAAAA;
border-top:0px;
background-color:#DDDDDD;
}
.t {
background-color:#DDDDAA;
}
</style>
</head>
<body>
";
if($action == ""){
echo "<TABLE width=100%><TR><form method=post action='dba.php'
TARGET='dba_iframe'><TD class=even>
Server String<br><input type=text name='dbstring'></TD>
<TD class=even>Username<br><input type=text name='dbuser'></TD>
<TD class=even>Password<br><input type=text name='dbpass'></TD></TR>
<TR><TD COLSPAN=3>Query<br>
<TEXTAREA NAME=query rows=10 cols=120></TEXTAREA></TD></TR>
<TR><TD class=even COLSPAN=3 align=right><INPUT TYPE=SUBMIT NAME='action'
value='Run Query'></TD></TR>
<tr><td colspan=3><IFRAME NAME='dba_iframe' width=100%
height=300></IFRAME></TD></TR>
</TABLE><br>
</BODY></HTML>";
} else {
echo "Connecting to server...";
if(!ibase_connect($dbstring,$dbuser,$dbpass)){
echo "FAILED! Check dbstring, username, and password.";
} else {
echo "Ok<br>";
// Chop it into multiple queries
$queries = explode(";",$query);
$qcount = count($queries);
for($qc = 0;$qc<$qcount;$qc++){
$query = str_replace("\\'","'",$queries[$qc]);
$query = str_replace('\\"','"',$query);
$sanitized = str_replace("<","<",$query);
$sanitized = str_replace(">","*gt;",$sanitized);
if($query != "" && $query != " "){
echo "<b>Performing Query:</b> '".$sanitized."'...";
$start = microtime();
if(!$rec = ibase_query($query)){
echo "Failed!";
} else {
echo "Ok<br>";
echo "Query time: ";
$begin = substr($start,11).".".substr($start,2,8);
$finish = microtime();
$end = substr($finish,11).".".substr($finish,2,8);
echo number_format(substr(($end - $begin),0,8),4)."<br>";
$affected = ibase_affected_rows();
if($affected > 0){
echo "<b>No results from Updates or Inserts</b><br>";
} else {
$fc = ibase_num_fields($rec);
echo "<TABLE><TR>";
// Get the field names
for($a=0;$a<$fc;$a++){
$colinfo = ibase_field_info($rec,$a);
echo "<TD
class=t><small>".$colinfo['relation'].".</small><br>".$colinfo['name']."</td
>";}
echo "</TR>";
$rows = 0;
while($row = ibase_fetch_row($rec)){
if($rows%2 == 0){
$cl = "";
} else {
$cl = " class=even";
}
echo "<TR>";
for($a=0;$a<$fc;$a++){
echo "<TD".$cl.">".$row[$a]."</TD>";
}
echo "</TR>";
$rows++;
}
echo "<TR><TD class=t COLSPAN=".$fc."'>Record Count:
".$rows."<br>";
$begin = substr($start,11).".".substr($start,2,8);
$finish = microtime();
$end = substr($finish,11).".".substr($finish,2,8);
echo "Total Time: ";
echo number_format(substr(($end - $begin),0,8),4)."<br>";
echo "Rows per second: ";
echo number_format(substr(($rows/($end - $begin)),0,8),2);
echo "</TD></TR>";
echo "</TABLE>";
} // End of valid result set
} // End of valid query
}
}
} // End of valid connect
}
?>
----- END CODE -----
Nigel Weeks
Tech Support & Systems Developer
nweeks@...
www.examiner.com.au
71 - 75 Paterson Street
Launceston
Tasmania 7250
Australia
Phone: 03 6336 7234
[Non-text portions of this message have been removed]