Archive: mysql

MySQL has a nice feature that you may well not have used called Triggers. These are little SQL statements that are tied to events on table and then perform some extra work. The idea being that you will check the values for a row and if they meet some conditions you will take some further [...]

If your site has suddenly gone down and you are tearing your hair out because the people you usually call for server support aren't answering the phone then fear not - we can help you out. Along with our core business of building, customising and extending e-commerce web sites, we are also pretty handy at [...]

Heres a nice easy one, installing the latest phpMyAdmin directly using SSH PLAIN TEXT CODE: wget "http://downloads.sourceforge.net/project/phpmyadmin/phpMyAdmin/3.3.8/phpMyAdmin-3.3.8-all-languages.tar.gz?r=http%3A%2F%2Fwww.phpmyadmin.net%2Fhome_page%2Fdownloads.php&ts=1289829566&use_mirror=heanet"   tar -xvf phpMyAdmin-3.3.8-all-languages.tar.gz More Reading:Yii Setting up Authorisation – the Missing ManualMySQL Performance TuningVery High Speed Batch Update Multiple Rows of a Table in Single QueryUsing PHP to Stream SQL results dynamically as CSV, even to IEEmergency [...]

If you are struggling to get some foreign keys set up on your MySQL InnoDB database then perhaps this is your problem and a pretty simple solution. If you are getting errno 150 then take a look at the data types for the two fields that you are trying to relate to each other. I [...]

One of the advantages of using InnoDB as your storage engine in MySQL is that you can create a database structure that will automatically keep itself clean and tidy, by having deletes cascade through your tables as soon as you delete the main entity. However, if you need to empty a large table, you will [...]

If you need to change the character set and collation for all columns and tables on an entire database then check out this little PHP script. It's currently Zend Framework based, though only for the database adapter - you could easily modify this to suit your own database connection. PLAIN TEXT PHP: <?php /* collationChange [...]

Check out this little query for checking for obviously invalid EANs. Note this is not checking the check digit for validity, it is purely looking for data that is in no way possibly an EAN number. PLAIN TEXT SQL: UPDATE products SET ean = '' WHERE ean != '' AND ean NOT REGEXP '^[0-9]{13}$' assuming [...]

Just found this MySQL snippet for validating EAN numbers. http://snipplr.com/view.php?codeview&id=17928 I have modified it a bit to suit my requirements (namely totally corrupted EAN data). PLAIN TEXT SQL: SELECT ean FROM products WHERE (LENGTH(ean) != 13) || (SUBSTRING((10 - (((( SUBSTRING(ean FROM 2 FOR 1) + SUBSTRING(ean FROM 4 FOR 1) + SUBSTRING(ean FROM 6 [...]

If you are using XAMPP on Ubuntu (or probably other) Linux and are also trying to use the MySQL Administrator and MySQL Query Browser GUI tools available in the repo's, you have probably hit an error message like this: PLAIN TEXT CODE: Could not connect to host 'localhost'. MySQL Error Nr. 2002 Can't connect to [...]

This is a nice little script I just knocked together that helps to synchronise databases when the table structures might not be exactly the same (for example different versions of the same system). You would need to edit the top section to put the correct DB credentials etc. This is definitely not to be used [...]