Tag Archives: sql
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.
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.
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)