MASS SQL - Revised | Ghettocode

MASS SQL - Revised

Submitted by victrolla on Wed, 07/29/2009 - 18:29

This is a revised version of the previous MassSQL Tool. I like this version a little better. Here we're using mysql_fetch_field() which is much better.

And most noteworthy we're displaying results vertically. I think this is much easier to read. Sorry if it looks like the smarty templates are broken or invalid HTML. This is primarily because I integrated this into a private web portal.

This particular version uses Smarty to render results. If you're not familiar with Smarty I think you should be. I don't care what anyone says, smarty is pretty gangster.

<?                      
if($_POST['mysql_hidden'] == 1) {
        $databases = array(
                            "db1.example.com",
                            "db2.example.com",
                            "db3.example.com"
                        );
        $sql_fields = array('sql_user', 'sql_pass', 'sql_query');
        $timestamp = date("l, F j, Y, g:i a");
        foreach($sql_fields as $unique) {
                if(!isset($_POST[$unique])) {
                        $smarty->display('mysql_query.tpl');
                        exit;
                } else {
                        $sql_fields[$unique] = $_POST[$unique];
                }
        }
        foreach ($databases as $thisone) {
                $mass_dsn = mysql_connect($thisone, $sql_fields['sql_user'], $sql_fields['sql_pass']) or die(mysql_error());
                if($mass_dsn) {
                        mysql_select_db('EVENTDB') or die(mysql_error());
                        $someshit = mysql_query(stripslashes($sql_fields['sql_query']), $mass_dsn) or die("Error in $thisone ($mass_dsn): ".mysql_error());
                        while($row = mysql_fetch_array($someshit, MYSQL_ASSOC)) {
                                for ($i=0 ; $i < mysql_num_fields($someshit) ; $i++) {
                                        $meta = mysql_fetch_field($someshit, $i);
                                        $database[$thisone][$meta->name] = $row[$meta->name];
                                }
                        }
                }
                mysql_free_result($someshit);
                mysql_close($mass_dsn);
        }
        $smarty->assign('query', stripslashes($sql_fields['sql_query']));
        $smarty->assign('databases', $database);
        $smarty->display('mysql_result.tpl');
} else {
        $pageTPL = 'mysql_query.tpl';
        $smarty->display($pageTPL);
}
?>

Here are the two referenced Smarty Templates

      <p><h3 align="center">&#171; Mass Mysql Query &#187;</h3><br></p>
        <p>
        <font size="2"><center>
        This tool will reach out to every Production EDB and execute the supplied Query.<br />
        It will display the results side by side so you may quickly find any flaws in <br />
        replication. <b>NOTE:</b> It would be a <b>*BAD*</b> idea to do any crazy joins <br />
        in this client, as you have the ability to slow down all production databases. <br />
        <font color="red">
        !!! For Redundancy, do <b>*NOT*</b> do things like cartesian joins !!!
        </font>
        </p>
           <form action="/index.php?page=mass-sql&sub=results" method="post"><br />
             Username: <input name="sql_user" type="text" size="20"/>
             Password: <input name="sql_pass" type="password" size="20"/><br />
 
             Query: <br />
             <textarea cols="80" rows="10" name="sql_query" size="500" maxlength="300"></textarea><br />
             <input type="hidden" name="mysql_hidden" value="1">
             <input type="submit" value="submit"/><br />
           </form>
        </center>
        <br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><font color="white">.</font>

And Most importantly, the result page.

      <p><h3 align="center">&#171; Mass Mysql Result &#187;</h3>
      <center><div class="smallprint"><b>Query: </b>{$query} <center></p>
        <p>
        <font size="2"><center>
        </font>
        </p>
<table width="100%" style="font-size: 8pt">
<tr valign="top">
        {foreach from=$databases key=database item=column}
        <td>
        <table>
                <tr><td><b>{$database}</b></td></tr>
                {foreach from=$column key=col_name item=col_value}
                <tr><td bgcolor=#cccccc>{$col_name}</td></tr>
                <tr><td>{$col_value}</td></tr>
                {/foreach}
        </table>
        </td>
        {/foreach}
</tr>
</table>

Attachment	Size
MassSQL_alt.php.txt	1.8 KB

Ghettocode

MASS SQL - Revised

Navigation

Donate