How to Drop all Databases from Mysql?

Sometimes I need to clean a mysql server so I can re-import it clean from the backups. A DROP ALL DATABASES; would have been nice, but the following does the trick. It will drop all databases except for mysql, test and information_schema


mysql -uroot -pPASS -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | gawk '{print "drop database " $1 ";"}' | mysql -uroot -pPASS

Warning:
It does what it says on the tin. It will DROP all your databases, no warnings, no confirmations!

The syntax of the DROP DATABASE statement supports only a single database name. You will need to execute a separate DROP DATABASE statement for each database.

You can run a query to return a list of database names, or maybe more helpful, to generate the actual statements you need to run. If you want to drop all databases that start with the literal string database_ (including the underscore character), then:


SELECT CONCAT('DROP DATABASE ',schema_name,' ;') AS stmt
FROM information_schema.schemata
WHERE schema_name LIKE 'database\_%' ESCAPE '\\'
ORDER BY schema_name

Copy the results from that query, and you’ve got yourself a SQL script.

(Save the results as plain text file (e.g. dropdbs.sql), review with your favorite text editor to remove any goofy header and footer lines, make sure the script looks right, save it, and then from the mysql command line tool, mysql> source dropdbs.sql.)

Obviously, you could get more sophisticated than that, but for a one-time shot, this is probably the most efficient.)

============================
How I can change prefixes in all tables in my MySQL DB?

SELECT
GROUP_CONCAT('RENAME TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`;' SEPARATOR ' ')
FROM
`TABLES` WHERE `TABLE_SCHEMA` = "test";

where “test” is expected database name

after this you can long query that will add prefixes if you execute it 😉

Posted in MySQL. Comments Off on How to Drop all Databases from Mysql? »

Zen Cart V1.5.5 Add Extra Text at Category Footer

  1. Modify mysql database, add a field called ‘categories_footer’ at the table of ‘categories_description’, same field names and description of the field called ‘categories_description’.
  2. Add the function at admin panel, to create or modify a category, we need to modify categories.php which is under the admin fold. Within this file we need to add an extra field so that we can fill in the extra text of footer. Take care there are several places need to modify or add.
  3. We need to create a function called “zen_get_category_footer()”, same as  the function of “zen_get_category_description()”, add it within the file called general.php, which is under “admin/includes/functions/”.
  4. We also need to add the new extra block name with categories.php, say “define(‘TEXT_CATEGORIES_FOOTER’, ‘Categories Footer Description:’);”, just let this line under “define(‘TEXT_CATEGORIES_DESCRIPTION’, ‘Categories Description:’);”, which will be added within the file called  categories.php which is under “admin/includes/languages/english/”.
  5.  Now let’s share how to let the  extra text at footer display at front page of a category. We need to modify 2 files. Let modify the first file which is called “tpl_index_product_list.php”, just under “includes/templates/responsive_classic/templates/”. Add the following code just above the last </div> at the end of the file:
    <?php
    // categories_description
    if ($current_categories_footer != ”) {
    ?>
    <div id=”indexProductListCatDescription” class=”content”><?php echo $current_categories_footer;  ?></div>
    <?php } // categories_description ?>
    </div>
  6. Modify the last file called “main_template_vars.php”, which is under “includes/modules/pages/index/”. Modify $sql, and add the new field, so that we can  use “$current_categories_footer” at “tpl_index_product_list.php”.
Posted in MySQL, Php Study, Zen Cart. Comments Off on Zen Cart V1.5.5 Add Extra Text at Category Footer »

How to Let X-Cart Display latest WordPress Posts?

This mod assumes that you use the WordPress blogging software and uses the same database as your xcart software.
If your wordpress and x-cart use separate database then use this code for blog_headlines.php (more…)

Posted in MySQL, Php Code, WordPress, X-Cart. Comments Off on How to Let X-Cart Display latest WordPress Posts? »

Bulk Change Mysql Database Table Prefix

We often need to change all our mysql database table prefix. If there are only one or three tables, that would be very easy for us to change. If there are too many, say 100 mysql tatabase tables. It will take us a lot of time, at the same time we may make mistakes.

Any other easy way? The answer is YES. Here let me share my php code, using html5, and mysqli! (more…)

Posted in MySQL, Php Learning. Comments Off on Bulk Change Mysql Database Table Prefix »

How to use mysql to remove the hyperlink and remain with the text?

If you have a lot of articles, and there some hyperlinks here and there, it will be very difficult for us to remove them one by one. For example, you have about 10,000 articles, if you check each articles, I am sure you will have to cost several days to remove the hyperlinks and keek the text.

There are 2 method to do this, one is using php code, we can read the article from mysql database, and using php to replace; another method, we can control mysql directly. For example, as we know, there are many people using dede cms to build sites, and all the articles is stored under body of dede_addonarticle.

Here, let share the second method with you, log in your mysql database via phpmyadmin, and choose your database, run the following sql:

update dede_addonarticle set body = replace(body,substring(body,locate(“<a “, body),locate(“>”, body,locate(“<a “, body))+1-locate(“<a “, body)),””);

Posted in MySQL. Comments Off on How to use mysql to remove the hyperlink and remain with the text? »

Skills on Display Orders of Zen-Cart.

Especially at the end of every month, we need to know how many orders we have. Here are 2 method you may like when you use Zen-Cart.

First, if you need to know certain latest new orders, say 2000, how to do then, you can use the following mysql command:
SELECT * FROM `orders` order by orders_id desc limit 0,2000;  //show latest orders;

Second is we can use the following mysql code to show all the products with a month, say display all products within March, 2012:

SELECT date_purchased, SUM(order_total) FROM `orders` where date_purchased between “2012-03-01” and “2012-03-30”;

For some phpmyadmin or mysql version, we need to use the followng code:

SELECT date_purchased, SUM(order_total) FROM orders where date_purchased between “2012-03-01” and “2012-03-31”;
If we need to know all orders in a year, say 2011, how to then? Simple:

SELECT date_purchased, SUM(order_total) FROM `orders` where date_purchased between “2011-01-01” and “2011-12-31”;

Here we can use SUM(order_total) to caculate total value within a period.

Show orders, not include unpaid and canceled orders within a month:

SELECT date_purchased, SUM(order_total) FROM orders where date_purchased between “2012-08-01” and “2012-08-31” and  order_status <> “1”  and order_status <> “9”

Posted in MySQL, Zen Cart. Comments Off on Skills on Display Orders of Zen-Cart. »

How to using PHP to delete wordpress coments?

Sometimes there are thousands wordpresss comments there, it is too difficult to delete them. Any method to delete them one time without login phpmyadmin?

The answer is yes, the follow code can help you.

include(‘wp-config.php’);
mysql_query(“truncate table wp_comments;”);

Copy the code to file delete_wp_comment.php, and put the file under your blog. Run it, all comments will be deleted!

If you only want to delete spam or trash comments, please visit http://kingphp.com/160.html , and just change some code. Very simple!

Posted in MySQL, Php Code. Comments Off on How to using PHP to delete wordpress coments? »

Mass Delete trash post from WordPress

Q:I want to mass delete my trash with 2000+ Posts in it. Is there a mysql command to do so?

A: Please backup before running the query, i’ll not take responsibility if something goes wrong as a result..

DELETE p
FROM wp_posts p
LEFT OUTER JOIN wp_postmeta pm ON (p.ID = pm.post_id)
WHERE post_status = ‘trash’

Join is optional, removes entries from the meta table (if they exist).

Check your data base table prefix also, if it is not wp_, you have to change it to your table prefix.

Tested under MySQL 5.1 in Phpmyadmin.

Posted in MySQL, WordPress. Comments Off on Mass Delete trash post from WordPress »

Zen Cart Install – #1064 – You have an error in your SQL syntax

After install zen cart v1.3.9h, the front page show:

 

#1064 – 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 ‘TYPE=MyISAM’ at line 1

 

CREATE TABLE seo_cache (`cache_id` varchar(32) NOT NULL default ”, `cache_language_id` tinyint(1) NOT NULL default ‘0’, `cache_name` varchar(255) NOT NULL default ”, `cache_data` mediumtext NOT NULL, `cache_global` tinyint(1) NOT NULL default ‘1’, `cache_gzip` tinyint(1) NOT NULL default ‘1’, `cache_method` varchar(20) NOT NULL default ‘RETURN’, `cache_date` datetime NOT NULL default ‘0000-00-00 00:00:00’, `cache_expires` datetime NOT NULL default ‘0000-00-00 00:00:00’, PRIMARY KEY (`cache_id`,`cache_language_id`), KEY `cache_id` (`cache_id`), KEY `cache_language_id` (`cache_language_id`), KEY `cache_global` (`cache_global`) ) TYPE=MyISAM

Very simples, just change “TYPE=MyISAM” to “ENGINE=MyISAM”, and login in phpmyadmin, run the mysql sentense, done!

 

Posted in MySQL. Comments Off on Zen Cart Install – #1064 – You have an error in your SQL syntax »

Using PHP to Export Mysql Data to Excel

 

<?php 
$DB_Server = “localhost”;   
$DB_Username = “put your user name here”;   
$DB_Password = “put your password here”;   
$DB_DBName = “put your database name here”;   
$DB_TBLName = “put your table name here”;   
  
$savename = date(“YmjHis”);  // excel file name
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die(“Couldn’t connect.”);   
mysql_query(“Set Names ‘utf-8′”); (more…)

Posted in MySQL, Php Code. Comments Off on Using PHP to Export Mysql Data to Excel »
12
King Php Science Welcome, you are from: 54.205.172.57