mySQL Super-tip! How to comma-delimit or comma-separate your query results!

| No Comments »

_Problem_: Help! I’d like to make it so my query results to be a comma delimited format!

_Answer_: No problemo! Simply use mySQL’s group_concat function and you’ll get your results in comma delimited values!

SELECT GROUP_CONCAT(id) FROM users WHERE authenticated=1 ORDER BY NULL LIMIT 1 Note: (Optional) ORDER BY NULL is used to prevent unnecessary sorting with filesort

This query would result to: 1,3,4,6,11

That’s It! This is pretty useful when you are going to use the SELECT… IN statement for your subqueries.

mySQL Super-tip! Use LIMIT and OFFSET to paginate your SELECT statements!

| No Comments »

Awesome, it seems that mySQL now supports the OFFSET clause.

This is very useful for paginating your SQL statements.

The following will return the first 10 entries of your select statement:

“SELECT field FROM table LIMIT 10”

This, on the otherhand, will OFFSET your result by 10, selecting your entries #10-20:

“SELECT field FROM table LIMIT 10 OFFSET 10”

Here’s an example of how to capture entries #10-30:

“SELECT field FROM table LIMIT 20 OFFSET 10”

Things just keeps on getting easier and easier!

A MySQL query to find & replace text in your database

| No Comments »

Problem: Help! I need to replace some words with something else in my database.

Solution: Use the replace() function to do a search and replace query.

UPDATE table_name SET table_field=REPLACE(table_field,"old_text","new_text");

Here’s an example:

UPDATE wp_posts SET post_content=REPLACE(post_content,"localhost","");

Easy as pie!

How to: Remove those nasty question marks with a diamond symbols that appears in your website


Problem: Help! I’ve pasted some text from Microsoft Word and saved it to my SQL database! Now whenever I print my text there’s a bunch of diamonds with a question mark symbols appearing!

Solution: You are trying to display characters that are outside your page’s character set, you have to tell your browser that you need to display characters from the iso-8859-1 set so it will know how to render them correctly.

Simply put the following inside your website’s <head></head>

<META http-equiv="Content-type" content="text/html; charset=iso-8859-1">

and in PHP(or any other similar language), simply put this at the top of your page(for other languages, look for an identical function):

<? header("Content-type: text/html; charset=iso-8859-1"); ?>

and voila! Your characters should now be displayed correctly.

Super Tool: (Probably) the best SQLite Editor/IDE for web development and production use

| No Comments »

I really love SQLite. It’s lightweight and got a really small footprint. I use it as my database initially when i’m creating web applications(I then move to mySQL during production), the only issue I have is the lack of IDE for working with it. Of course there’s SQLite Administrator, i’m just not into the idea of installing a software especially with SQLite being small(I prefer not to cramp up as much software possible in my Windows).

This is where sqlite-manager comes along. It’s a 100kb plugin for Firefox(which also means cross compatibility with other OS), it’s a great complement to SQLite’s lightweight and small footprint. If you need to work with the database, you simply fire up sqlite-manager from the Tools menu.

That’s It!