Category Archives: SQL

Check database tables size in Mb

Description: How can I check the size of my database tables and receive a human readaable result? This can be useful when deciding a storage architecture or a Cloud storage plan. Solution: From command line, go to mysql> terminal: SELECT … Continue reading

Posted in SQL | Tagged , | Leave a comment

Move WordPress database

Description: Moving an existing WordPress database is the easiest solution for changing website domain, duplicating content and so on, when we don’t want to lose posts, configurations and other meta data. However, this brings along also the connection to the … Continue reading

Posted in SQL, Wordpress | Tagged , , | Leave a comment

Fixing SQL errno: 150

Description: On table altering, SQL or phpMyAdmin throws an 150 error, with a message like “Error on rename of ‘./tables/#sql-efc_1’ to ‘./tables/my_table’ (errno: 150)” or “Cannot add or update a child row: a foreign key constraint fails”. Solution: Error 150 is related … Continue reading

Posted in Best practices, SQL | Tagged , , , | 2 Comments

Update database table depending on result from SELECT with CASE clause

Description: How to update a database table depending on a query on another or several tables ? Solution: If you want to make a script that does this, it’s very easy, just select all the values you need, and then … Continue reading

Posted in Best practices, SQL | Tagged , , , | Leave a comment

Find duplicate values in database table

Description: Detect fields that have duplicate values in a database table. Solution: For finding duplicate values, the COUNT condition can be used, specifying the column name that we require. Example: SELECT customer_idFROM customers_tableGROUP BY customer_idHAVING count( * ) > 1; Also … Continue reading

Posted in SQL | Tagged , | Leave a comment

Use adapter with Zend_Db

Description: Return a selection of database table fields using the Zend_Db adapter with Zend Framework Solution It’s easier to create an adapter of the database table class and use it in returning the desired values. This grants speed, clean code … Continue reading

Posted in Best practices, SQL, Zend Framework | Tagged , , | Leave a comment

Run Update SQL query with if clause

Description: Running an SQL script that performs a update depending on some given conditions. Solution: SQL query can take if clauses in the shape of cases: UPDATE table SET name = case WHEN id = 1 then ‘John’ ELSE null … Continue reading

Posted in SQL | Tagged , | Leave a comment

Save an array into database table

Description: How to save an Array variable as a regular database value? Solution: PHP offers the serialize() function, that creates a representation of the variable that can be stored everywhere. This can be used for other types of variables as … Continue reading

Posted in PhP, SQL | Tagged , , | Leave a comment

MySQL database import using Command Line under Windows

Description: Import larger databases can cause timeouts or errors in phpMyAdmin. For this situations we need to use the command line import. Solution: The file we need is mysql.exe situated in the xampp directory under \xampp\mysql\bin\ . Then 2 steps are … Continue reading

Posted in SQL | Tagged , , | Leave a comment