How to find all your missing or broken images in Virtuemart

If your store has changed servers or you are just having some file backup issues and are missing your images here is a quick script to tell you which images are broken in virtuemart. This php script could easily be modified for other websites, stores, or tables.

Just change a few variables, upload and it will list out all of your missing product photos. Even with an edit link to help you fix them faster!

 /**
Joomla 1.5 - virtuemart 1.9
lists product large images that are missing and need to be re-uploaded
Thumbnails are auto generated so its not an issue to look for them.
could be easily modified for other datbases/content i.e. categories

*/

//database variables - copy from your configuration.php
$user = '';
$db = '';
$password = '';
$host = 'localhost';
$editurl ='https://www.DOMAIN.com/administrator/index.php?page=product.product_form&limitstart=0&keyword=&product_parent_id=&option=com_virtuemart&product_id=';
 
//make sure you edit your full path here 
$filepath ='/home/DOMAIN/public_html/components/com_virtuemart/shop_image/product';

echo "file location:".$filepath."<br><br>";

if (!$link = mysql_connect($host, $user, $password)) {
    echo 'Could not connect to mysql';
    exit;
}

if (!mysql_select_db($db, $link)) {
    echo 'Could not select database';
    exit;
}
//database query for the fields we need
$sql    = 'SELECT product_id,product_full_image, product_name FROM jos_vm_product';
$result = mysql_query($sql, $link);

if (!$result) {
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_assoc($result)) 
{
    $filename = $filepath.'/'.$row['product_full_image']; 
    //output the file name and edit link if the file is missing  
    if(!file_exists($filename))
    { 
        echo $row['product_full_image']. ' - '.$row['product_name']  ;
        echo  ' <a href="'.$editurl.$row['product_id'] .'" target="_blank">edit</a><br>';
    }
}

mysql_free_result($result);
exit();
 

Replace Test urls in Wordpress to live using phpmyadmin

Just a quick help snippet for those of us who test wp websites on a test domain before moving them to a live url.

This issue arises when using a test domain for building a site and uploaded images, pdfs etc link to the test domain. When you make the website live the links still point to the old url in each and every post and page. Here is a quick SQL snippet to replace those old urls.

    UPDATE wp_posts
    SET guid =
    REPLACE(

        guid,
        "http://TestDomain/",
        "http://www.LiveDomain.com/"

    );

 UPDATE wp_posts
    SET post_content =
    REPLACE(

        post_content,
        "http://TestDomain/",
        "http://www.LiveDomain.com/"

    ); 


update wp_options
set option_value =
replace(
       option_value,
       "http://TestDomain/",
        "http://www.LiveDomain.com/"
); 

mysql replace string

update jos_vm_product
set product_name = REPLACE(product_name,'OLD TEXT','NEW TEXT')

Copy MySQL db on the same server

Need to duplicate or backup a db for testing, give this a run.

You can specify a username and pass for both, but I had trouble with it. SO I just used root.. :)

mysqldump -h [server] -u [user] -p[password] DataBaseA | mysql -h [server]  DataBaseB

Zipcodes to go radius search MySQL table

Recently I have finished a customized zip radius search and used the one from Zipcodes to go. Unfortunately they only have csv, xls, and mdb formats for the database. To make use of their data you need to import it into mysql, but first you must create the table which phpmyadmin won't do automagically (please tell me im wrong by the way).

First you must create the table.

I tried to be as lazy as possible and tried to export the mdb to mssql then to mysql but it didn't really work and took waayy too long so i ended up having to write out the create table anyway.


SQL to create the gold table
Note: Remove the ID field until after you import all the data from the csv, otherwise the field lengths wont match!

CREATE TABLE IF NOT EXISTS `zipcodes_full` (
 `ID` int(12) unsigned NOT NULL AUTO_INCREMENT,
 `ZIP_CODE` varchar(255) NOT NULL DEFAULT '' ,
 `CITY_NAME` varchar(255) NOT NULL DEFAULT '' ,
 `STATE_NAME` varchar(255) NOT NULL DEFAULT '' ,
 `STATE_ABBREV` varchar(255) NOT NULL DEFAULT '' ,
 `STATE_FIPS` varchar(255) NOT NULL DEFAULT '' ,
 `COUNTY_FIPS` varchar(255) NOT NULL DEFAULT '' ,
 `COUNTY_NAME` varchar(255) NOT NULL DEFAULT '' ,
 `TOTAL_COUNTY_POPULATION` varchar(255) NOT NULL DEFAULT '' ,
 `TOTAL_COUNTY_HOUSING_UNITS` varchar(255) NOT NULL DEFAULT '' ,
 `LATITUDE` varchar(255) NOT NULL DEFAULT '' ,
 `LONGITUDE` varchar(255) NOT NULL DEFAULT '' ,
 `TIMEZONE` varchar(255) NOT NULL DEFAULT '' ,
 `DAYLIGHT_SAVINGS` varchar(255) NOT NULL DEFAULT '' ,
 `Returns` varchar(255) NOT NULL DEFAULT '' ,
 `$ AVG AGI` varchar(255) NOT NULL DEFAULT '' ,
 `Refunds` varchar(255) NOT NULL DEFAULT '' ,
 `$ AVG Refund` varchar(255) NOT NULL DEFAULT '' ,
 `Single` varchar(255) NOT NULL DEFAULT '' ,
 `Married Joint` varchar(255) NOT NULL DEFAULT '' ,
 `Head of Household` varchar(255) NOT NULL DEFAULT '' ,
 `<30` varchar(255) NOT NULL DEFAULT '' ,
 `30_44` varchar(255) NOT NULL DEFAULT '' ,
 `45_60` varchar(255) NOT NULL DEFAULT '' ,
 `>60` varchar(255) NOT NULL DEFAULT '' ,
 `TV_Designated Market Areas` varchar(255) NOT NULL DEFAULT '' ,
   PRIMARY KEY (`id`)
)  

Second:

Importing the data is a freaking pain through phpmyadmin. I ended up using the commandline on my local machine and now i am trying to use BigDump php to migrate it to the server.

Use the below script in the mysql commandline to import the csv into the db table. It worked wonderfully 4.6 some seconds. Great Now how do i get it to the server....

Note i dont have mysql mapped in my environment variables in windows so i needed to copy the csv file into the mysql bin directory.

load data local infile '5-DIGIT-ZIP-GOLD.csv' into table zipcodes_full fields terminated by ','  ENCLOSED BY '"' LINES TERMINATED BY '\r\n'