SQL

MySQL Error Function Wrapper for PHP

by damonp on February 7, 2011

in Development,PHP,SQL

I was recently asked to supply some development and debugging functionality to a web application built for a client. The following function can be configured to popup for only your IP, and gives an error message containing the SQL, line number and file for easy debugging of the error.

I like this function over my current hobbled together debug kit because the display is positioned top center of the page via CSS no matter where the error occurs. Plus it includes a hide button to hide the majority of the error output and remove button to remove the error display altogether.

<?php
    function mysql_error_wrapper($q, $sql='', $line='', $file='')   {
        if($_SERVER['REMOTE_ADDR'] != 'YOUR LOCAL IP')  return false;
?>
<script type="text/javascript" language="JavaScript"><!--
function showhide(d) {
    if(document.getElementById(d).style.display == "none") {
        document.getElementById(d).style.display = "block";
        document.getElementById('shde').innerHTML = "hide";
    }   else {
        document.getElementById(d).style.display = "none";
        document.getElementById('shde').innerHTML = "show";    
    }
}

function remove()   {
    document.getElementById('errorblock').style.display = "none";
}
//--></script>

<?php
        echo '<div id="errorblock" style="display:block;position:absolute;top:10px;left:10%;width:80%;background:#efefef;text-align:left;border:1px solid #CD0000;padding:10px;">';
        echo '<a href="#" onclick="showhide(\'sqlerror\')"><span id="shde">hide</span></a> - <a href="#" onclick="remove();">remove</a>';
        echo '<h3 style="color:#CD0000;">MySQL error: '.mysql_errno().' <small style="font-weight:normal;">Line: #'.$line.' '.basename($file).'</small></h3>';
       
        echo '<pre id="sqlerror" style="height:250px;overflow:auto;padding:10px;">';
       
        echo wordwrap(mysql_error());
       
        if($sql != '')  {
            echo '<h4>SQL</h4>'.wordwrap($sql);
        }

        if($line != '') {
            echo '<h4>Line</h4>'.$line;
        }
       
        if($file != '') {
            echo '<h4>File</h4>'.$file;
        }
                   
        echo '</pre>';
        echo '</div>';         
   
    }
?>

Usage example:

<?php
    $sql = "YOUR SQL STATEMENT";
    $q = mysql_query($sql, $db);
    if(!$q) mysql_error_wrapper($q, $sql, __LINE__, __FILE__);
?>

Popularity: 3%

{ 1 comment }

Quickly Copy MySQL Database to Remote Host

by damonp on August 31, 2007

in Snippets,SQL

Copy an entire database:

mysqldump LOCAL_DBNAME | ssh USER@REMOTE_HOST mysql -p REMOTE_DBNAME

Copy a single table:

mysqldump LOCAL_DBNAME LOCAL_TABLE | ssh USER@REMOTE_HOST -p REMOTE_DBNAME REMOTE_TABLE

This shortcut only works if you can access the local DB without a password. If you have to login to both local and remote MySQL servers, the MySQL password prompts get mashed together. You could specify the password on the command line like

-pPASSWORD

but your shell may keep the password in its history so that anyone with access to your account could pick through your shell history and retrieve the password. Using the MySQL password prompt doesn’t do this.

Popularity: 3%

{ 0 comments }

Data Manipulation – Splitting Surname and Name

21 June 2007

For a new client project I was provided with database of users consisting of names, email addresses and managers. CREATE TABLE `users` (   `name` varchar(255) NOT NULL,   `email` varchar(255) NOT NULL,   `manager` varchar(255) NOT NULL,   `status` varchar(255) NOT NULL ) The name column contained both first and last names formatted last [...]

Read the full article →

Adding Human Readable Date to MySQL Query

15 May 2007

SELECT *, FROM_UNIXTIME( ‘t_stamp_column’) AS date FROM ‘tablename’ For formatting the date see MySQL’s date functions information.

Read the full article →

Easy MySQL Sorting Mistake

5 April 2007

If you have a compound ORDER BY clause with a direction (ASC or DESC), make sure to specify the directions of all columns. Correct: ORDER BY col1 DESC, col2 DESC Wrong: ORDER BY col1, col2 DESC The latter forces a filesort.

Read the full article →

Quick and Dirty MySQL Backup Script

14 March 2007

I previously posted a Quick and Dirty MySQL Backup snippet that has gotten a bit of traffic. It works well in a pinch, but it doesn’t have any features. I use it primarily as a safety backup when performing maintenance on a MySQL server. Several of my clients have requested a script that they can [...]

Read the full article →

ZenCart Manufacturers System Errors

12 January 2007

In ZenCart versions immediately prior to 1.3.6 using many of the public side manufacturers functions may produce this error: Warning: constant(): Couldn’t find constant in  …/includes/init_includes/init_add_crumbs.php on line 45 The error is caused by a missing database column that was added in the official 1.3.6 release. If upgrade is not immediately possible the following SQL [...]

Read the full article →

MySQL Slow Query Logging

5 December 2006

Add to /etc/my.cnf under [mysqld] stanza MySQL5 log_slow_queries  = /var/log/mysqld-slow.log long_query_time   = 1 MySQL3, MySQL4 log-slow-queries=/var/log/mysqld-slow.log set-variable    = long_query_time=1

Read the full article →

ZenCart Orders Total Weight Query

29 November 2006

SELECT op.products_quantity * SUM( products_weight ) FROM products p JOIN orders_products op WHERE p.products_id IN ( SELECT products_id FROM orders_products WHERE orders_id = ‘ORDER_ID’ ) AND op.orders_id = ‘ORDER_ID’ Replace ORDER_ID with the orders_id in question. Requires MySQL 4.1 or better for subselect support

Read the full article →

MySQL Database Performance – ZenCart Sales Report

23 October 2006

One of my client’s ZenCart installs was loading the server down so much to generate sales reports with the Sales Report v1 contribution that the public side was noticeably less responsive for the couple of minutes it took to build the report. The site has been running for over three years and contains 9k orders, [...]

Read the full article →

← Previous Entries