Archive: mysql

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 find [...]

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

* Edmonds Commerce

* www.edmondscommerce.co.uk

[...]

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 you have a [...]

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 FOR 1) +

SUBSTRING(ean FROM 8 FOR 1) +

SUBSTRING(ean FROM 10 FOR 1) +

SUBSTRING(ean [...]

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 local MySQL server through socket [...]

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 on a [...]

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:
PLAIN TEXT
PHP:

$cols_query = db_query("desc table");

while($cq = mysql_fetch_assoc($cols_query)){

    $cols[]=$cq;

}

foreach($cols as $c){

    if(!empty($_POST[$c['Field']])){

      [...]

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

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){

    [...]

One of the most useful and powerful things you can do with PHP is to create a programme which will simulate a web browser and can grab data, post data to forms and generally interact with other web sites - automatically.
For PHP to be able to work like this it must have the CURL library [...]

One of the most common things that needs to be done when tidying up a database is to bulk find and replace data in MySQL tables. This can be for things like spelling mistakes, changing categories of products or any other value held in a MySQL database table.
Its an easy thing to do though and [...]