Tag archives: sql

Character Encoding Problems

03Aug10

After performing an upgrade to an earlier version of WordPress, I saw some weird characters (e.g. “ for “”) shown on my posts. It turned out that many people had the same problem as mine. So, if you encounter some character encoding problems which prevent WordPress from showing “normal” characters, there are some fixes that you can try:

Changing Config Files

One thing you can do is to change the wp-config.php file by adding or replacing these codes:

define('DB_CHARSET','');
define('DB_COLLATE', '');

It worked for me only once. When the next time I did an upgrade and the problem occurred again, it didn’t work anymore. So I tried the next fix.

Executing SQL codes

This fix is for advance users only who are quite familiar with the SQL codes. Please backup your database first before proceeding! :) Once you’ve done that, execute these SQL codes:

UPDATE wp_posts SET post_content = REPLACE (post_content, '–', '---')
UPDATE wp_posts SET post_content = REPLACE (post_content, '’', ''')
UPDATE wp_posts SET post_content = REPLACE (post_content, '“', '"')
UPDATE wp_posts SET post_content = REPLACE (post_content, '”', '"')

What these codes do is to replace the strange characters in your post content into the ones that are readable by WordPress. The codes use wp_posts table, so make sure you change these table names if you use different table names for your WordPress blog.

Hope it works!

Filed under Web Development| Tagged , , , , | No Comments

Auto-Increment in SQL

07Jun07

Auto-increment is a very useful feature that allows a unique number to be generated when a new record is inserted into a table. The DBMS will automatically increment the last number of the table and add it to the auto-incremented column. Unfortunately, different DBMSs use different ways or syntax to do this. In this post, I will show you how to include the auto-increment feature in MySQL, Microsoft SQL Server, and Oracle.

Auto-Increment in MySQL

1
2
3
4

CREATE TABLE Students (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
fullname VARCHAR(80) NOT NULL
);

As you can see from the above code, the auto-increment feature is very simple when it’s applied in MySQL. You only need to add auto_increment as shown in line 2. By default, the auto_increment value starts at 1. If you want the auto_increment to start with value other than 1, you have to set that value using ALTER TABLE:

ALTER TABLE Students AUTO_INCREMENT=2000

Continue reading

Filed under Programming| Tagged | 5 Comments

SQL: if exists

19Mar07

I collected these SQL scripts so that whenever I want to check the existency of a table, view, stored procedure, or function in a database, all I have to do is to copy and paste them to the Query Analyzer. FYI, These scripts are only used for SQL Server :) . Hope it’s useful!

Check if the table exists in a database:

1
2
3
4
5
6
7
8
9
10

IF EXISTS
(
  SELECT * FROM dbo.sysobjects
  WHERE id = object_id(N'[dbo].[enterTableNameHere]')
         AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
 
DROP TABLE [dbo].[enterTableNameHere]
 
GO

Continue reading

Filed under Programming| Tagged | 4 Comments