|
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.
|
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!
|
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","www.cybervaldez.com");
Easy as pie!
|
3 Comments »
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.
|
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!
