Tag Archives: sql

phpmyadmin: Drag And Drop File Upload

I use phpMyAdmin to manage mySql databases quite often and I find myself uploading / importing SQL files. I’ve gotten a little spoiled with WordPress’ HTML5 drag and drop ajax style uploader. Every time I go to use phpMyAdmin I start wishing that it had a HTML5 style drag-and-drop file uploader, something like this: I think it would be fairly easy to write a plugin and implement this idea but I have not done it yet. Leave a comment if the lack of a drag and drop HTML5 file uploader for phpMyadmin bugs you too.

Posted in Server Administration, Tech Opinion, Web Development | Tagged , , , , , | Leave a comment

WordPress: CREATE TABLE `wp_redirection_404` mysql error

If you’re getting the following error: WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘KEY ‘referrer’ (‘referrer’) ) DEFAULT CHARACTER SET utf8′ at line 12] CREATE TABLE ‘wp_redirection_404’ ( ‘id’ int(11) unsigned NOT NULL AUTO_INCREMENT, ‘created’ datetime NOT NULL, ‘url’ varchar(255) NOT NULL DEFAULT ”, ‘agent’ varchar(255) DEFAULT NULL, ‘referrer’ varchar(255) DEFAULT NULL, ‘ip’ int(10) unsigned NOT NULL, PRIMARY KEY (‘id’), KEY ‘created’ (‘created’), KEY ‘url’ (‘url’), KEY ‘ip’ (‘ip’,’id’) KEY ‘referrer’ (‘referrer’) ) DEFAULT CHARACTER SET utf8; Most likely you need to edit the plugin file: wp-content/plugins/redirection/models/database.php and add a comma around line 195: KEY `ip` (`ip`,`id`) needs to be changed to: KEY `ip` (`ip`,`id`), This is a flaw with the upgrade_to_231 function which is not called very often.

Posted in Tech Tips, Web Development | Tagged , , , , | 1 Comment

MySQL UPDATE JOIN

Here is a working example of how to do an UPDATE JOIN with MySQL: MySql UPDATE LEFT / CENTER / RIGHT JOIN Example This is the correct way to update a table when using a JOIN. They key is that the SET needs to come after the JOIN. UPDATE wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) SET wp_term_taxonomy.taxonomy = ‘category’ WHERE 1 AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id IN (1) This WILL NOT WORK because the SET is in the wrong place: UPDATE wp_posts SET wp_term_taxonomy.taxonomy = ‘category’ LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1 AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id IN (1)

Posted in Tech Tips, Web Development | Tagged , , , , | Leave a comment

WordPress: SQL to get all Categories

If you’re trying to get all categories in WordPress with a real, raw, mysql sql statement, this is the type of query you can use: SELECT * FROM wp_term_relationships LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE wp_term_taxonomy.taxonomy = ‘category’ GROUP BY wp_term_taxonomy.term_id This is how you can get posts of a certain category / taxonomy. I have a few extra lines commented out that could also be used: SELECT * FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1 #AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id IN (13) #AND wp_term_taxonomy.term_id IN (1,2,3,4,5,6,7) GROUP BY wp_posts.ID ORDER BY ID Update: Omar asks: cool.. but how can I get the NAME of those categories ?? Well, try this: SELECT * FROM wp_term_relationships LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_terms on wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id WHERE wp_term_taxonomy.taxonomy = ‘category’ GROUP BY wp_term_taxonomy.term_id or just for the names: SELECT wp_terms.name FROM wp_term_relationships LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_terms on wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id WHERE wp_term_taxonomy.taxonomy = ‘category’ GROUP BY wp_term_taxonomy.term_id

Posted in Social Media, Tech Tips, Web Development | Tagged , , , , , | 6 Comments