If for example you’re removing an extension and want to clear out its information from the database, you’ll need to establish the rows and tables it’s inserted, as well as some standard Magento table entries.

So you’ve removed everything, cleared your cache and now you go to the homepage to check everything is all good, and boom, PDOException:

Base table or view already exists: 1050 Table ‘*’ already exists

In my case it was `salesrule`, which made me question why it was trying to create the table afresh. The reason I got this is that I’d erroneously removed salesrule_setup from core_resource, and therefore Magento was reinstalling the module. Reinstating the row, and clearing my cache solved the issue

If you want to be able to run multiple queries in a single function call, for example doing the classic drop table blah; create table blah; then you might like this function.

The use case is for things like database migration systems which you might copy and paste chunks of SQL including multiple queries from things like phpMyAdmin

/**
 * Run multiple queries passed in as a single string
 * This is optimised for copying and pasting from phpMyAdmin
 * 
 * Handy for things like database migration systems
 * 
 * @param string $sql  multiple queries terminated with ; and a new line
 */
function multiQuery($sql)
{
    $sqls = preg_split('%;$%m', trim($sql));
    foreach ($sqls as $q) {
        if (empty($q)) {
            continue;
        }
        mysql_query($q); //suggest you replace this with your custom query function or if not throw in some extra error checking at least
    }
}

If you are puzzled as to why inline translate in Magento is not saving a particular translation, this could be your issue.

Inline translate uses the table core_translation to store the translations.

The fields that store the data are set to VARCHAR(255) fields.

If your source string is longer than 255 characters, it will fail silently to save the translation.

The simplest solution is to shorten the source string and stick to using inline translate on strings that are shorter than 255 characters.

You could alternatively modify the table but we prefer not to do that kind of thing unless we really have to.

Another workaround for text sections that need to be long is to simply use a static block instead.

If you need to extract the information from a PDF table sometimes when you copy and paste it into a text editor the formatting is incorrect making the information useless.

In cases like this, you can use pdfedit to extract the text in the correct formatting and then do what you need with it.

To use the program first make sure it’s installed. If not install it like this


sudo apt-get install pdfedit


then use it to open the PDF. Click through the the correct page and then click Page - Extract Text From Page

If you need to update a large number of rows on a single table then your first reaction may be to write a loop that updates one row at a time.

Of course if the table is large then this can result in a very large number of SQL queries.

Taking a bit of inspiration from this post I have put together this simple PHP function that will allow you to update as many rows as you want.

The trade off is basically memory usage as you build up a big array of row update information preparing for the batch. For this reason you may want to tune the number of rows you update per query.

function bulkUpdateSingleColumn($table, $id_column, $update_column, array &$idstovals){
        $sql = "update $table set $update_column = CASE $id_column ";
        foreach($idstovals as $id=>$val){
            $sql .= " WHEN '$id' THEN '$val' \n";
        }
        $sql .= " END 
        WHERE $id_column in (" . implode(',', array_keys($idstovals)) . ")";
//debugging info
        echo '<small>'.$sql.'</small>';
        $idstovals=array();        
        db_query($sql);       
        done();        
    }

If your osCommerce checkout starts behaving badly and bits of info seem to go missing eg billing address then you should definitely try this fix before you tear your hair out completely.

Most osCommerce installs store session information to a MySQL table called (suprisingly) sessions.

Sometimes (I have only seen this twice on umpteen osCommerce sites) the sessions table will become corrupted. The irritating thing is that if this happens it seems that the site will not stop working completely with a useful error message, but instead will continue to work but will behave very strangely.

If your osCommerce site is behaving strangely then try this fix.

Open phpMyAdmin and select your SQL database and then copy and paste the following into the SQL section and hit run:

REPAIR TABLE <code>sessions</code>;

This little PHP function will allow you to import a csv or tab etc delimited text file into a database table. Handy if you need it

function build_table_from_file($tablename, $filepath, $delim="\t") {
    db_query("DROP TABLE IF EXISTS $tablename");
    $fp=fopen($filepath, 'r');
    $headers=false;
    while($r=(($delim=='csv')?fgetcsv($fp):fgets($fp))) {
        if($delim!='csv'){
            $r=explode($delim, $r);
        }
        if(!$headers) {
            foreach($r as $h){
                $headers[]=trim($h);
            }
            $sql = "CREATE TABLE $tablename
                         (
                            <code>id</code> INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,";
            foreach($headers as $h) {
                $sqls[]=" <code>" . db_in($h) . "</code> TEXT NOT NULL ";
            }
            $sql .= implode(', ', $sqls) . "
                        ) ENGINE = MYISAM ";
            db_query($sql);
            continue;
        }
        $sql = "insert into $tablename set ";
        $sqls=array();
        foreach($headers as $k=>$h) {
            $sqls[] = "<code>$h</code> = '" . db_in($r[$k]) . "'";
        }
        $sql .= implode(', ', $sqls);
        db_query($sql);
        pbar();
    }
}

If you are feeling lazy, or would like to build in some future proofness into your system, you can use the MySQL Desc query to get table column information and then use this information to create dynamic SQL insertion strings.

For example:

$cols_query = db_query("desc table");
while($cq = mysql_fetch_assoc($cols_query)){
	$cols[]=$cq;
}
foreach($cols as $c){
	if(!empty($_POST[$c['Field']])){
		$sets[] = $c['Field'] . " = '" . mysql_real_escape_string($_POST[$c['Field']]) . "'";
	}
}
mysql_query("insert into table set " . implode(', ', $sets));

Sometime you need a PHP script to check for the existence of a MySQL table. This function achieves that for you.

function db_table_exists($table){
	$exists = false;
	$tables_query = db_query("SHOW TABLES FROM " . MYSQL_DB);
	while($t = mysql_fetch_assoc($tables_query)){
		foreach($t as $k=>$v){
			if($v == $table){
				$exists = true;
				break;
			}
		}
	}
	return $exists;
}