Search
-
Recent Posts
Tags
adwords amazon analytics api apple aws blog chrome chromium cloud Design dropbox ec2 email error facebook firefox google google-apps homebrew ipad javascript jQuery linux lion mac microsoft mysql os x osx paypal php plugin quicksilver raspberry pi scam social spam twitter ubuntu unix video windows woo wordpress
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 drag-and-drop, html5, mysql, phpMyAdmin, sql, upload
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.
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)
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 blog, categories, mysql, query, sql, wordpress
7 Comments
