Making It Up As I Go Along - mySQL

Add a value to a stored list in mySQL using FIND_IN_SET() and CONCAT()

Fri, 12 Mar 2010 15:04:00 -0600

For example, a column in a candy store database contains a list of flavors that the different shapes of candy come in. We have another table of flavors, but rather than a third relative table of flavors and product relationships, the IDs of the flavors are simply stored in a column in the products table. So, you might have a value like 12,14,35,36,78,105 where each number represents the value of a form checkbox in the product admin for this site, and and ID for a flavor in the flavors table. The store admin checks the boxes for the flavors that product comes in, and those IDs get stored as a list in a specific column. For the purpose of example, suppose the column is simply called 'flavors', and you need to add the flavor 'wowzaberry' (id 199) to all of the products that currently have the flavor 'razzoberry' (id 105) - with the added logic so that if a candy product already has the new flavor, we don't add it twice.


UPDATE tbl_products
SET flavors = CONCAT(flavors,',199')
WHERE FIND_IN_SET('105',flavors)
AND NOT FIND_IN_SET('199',flavors)

This says Add (concat) a comma (,) and 199 (the new ID number) To any record that already has ID 105 in the flavors column And that does not already have ID 199 in that same column

How to find lowercase (no caps) values in mySQL (and Capitalize them with ColdFusion)

Thu, 04 Feb 2010 18:30:00 -0600

I have a request from a client to help clean up some customer-entered data in a mySQL database, specifically, we need to capitalize all of the values in a column called 'user_city' For the actual capitalization, I am using the very handy CapFirstTitle() function from CFlib : http://www.cflib.org/index.cfm?event=page.udfbyid&udfid=116 But first, I need to find all of the entries that are not already capitalized. This dataset has tens of thousands of records , including some with a blank value in the 'city' field - no need to loop all of that just to fix our caps! So, here's the SQL query code to find all of the records with no capital letters at all, by comparing to a lower() all lowercase version of the same value:


SELECT  user_city, ID 
FROM site_users
WHERE user_city = lower(user_city)
AND NOT user_city = ''
ORDER  by user_city

And here is the full CFoutput / CFquery code for the loop and update with Coldfusion, and the CapFirstTitle() function in the head of the page:



	
	UPDATE site_users
	SET user_city = '#capFirstTitle(myQueryAbove.user_city)#'
	WHERE ID = #myQueryAbove.ID#

for other columns you could use OR in the first query and an additional statement in the second, or just change the queries, run the page again, or even, make the column name a variable and just change it once in the head of the page... easy.

mySQL str_to_date converts text formatted dates and times to real SQL timestamps

Sun, 10 Jan 2010 14:00:00 -0600

I am working on a site where dates have been stored as text strings in a mySQL database, such as '2009-10-17' , and I need to create a 'search by date' function where a given start and end date can be compared to the date stored in the database. In other words, i need to convert the text-formatted date to a real 'datetime' value , that can be compared with (greater than) and (less than) logic within the query itself.

Like all things CF/mySQL , there are usually multiple ways to get things done. After kicking it around a few different ways, I settled on mySQL's built-in "str_to_date" function as the easiest and most efficient solution.

The str_to_date() function takes two arguments: the string that is to be converted, and a format string, which uses the same syntax as mysql date_format() and other related mySQL date functions.

Since the dates are entered in 'yyyy-mm-dd' format, my format string is" %Y-%m-%d "
(Note the capital Y which signifies a 4-digit year)

My query now looks something like this - note the second line:

SELECT
str_to_date(aa.date_published,'%Y-%m-%d') as articleDate,
aa.title, aa.ID,
aa.author,
aa.article
FROM articles_table ...

This converts the date, 2009-10-17,
to a datetime stamp,
{ts '2009-09-17 00:00:00'},
which can now be used for any type of date comparisons.

To keep things clean, if an article happens to be entered with a different format that does not match our format pattern (i.e. somebody types in '10/17/09'), str_to_date() will simply return an empty string in that column, rather than throwing an error.

The mySQL specification for this and other functions is here:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

How to find Specific Column Names or Types in a mySQL Database

Sun, 06 Dec 2009 02:29:00 -0600

I'm working on a rather extensive CMS admin application, and need to make some adjustments to all of the "sort" fields in the database. Many of the tables have either a 'sort' or 'sort_order' (and in one case, I think I saw 'SortOrder'), and I need hunt them down, creating a list of column names and table names so i can quickly find the columns I want to edit. I could open each table one by one, and look for those column names... but of course, there's a much better way:


SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE lower(column_name) LIKE '%sort%'
AND TABLE_SCHEMA='//my database name here//'

This returns a perfectly formatted query of table names and column names, along with the 'data type' for any column containing 'sort' (or 'Sort'). Now it will be a snap to make my changes to all of the numeric sort fields in the entire database. The query can be modified to return all sorts of useful info, such as all columns in the entire database:


SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='//my database name here//'

or all columns with a specific data type


SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='//my database name here//'
AND DATA_TYPE='varchar'

And then, if you wanted to get really lazy, er, creative... you can loop the results of that query, and 'do stuff' to all of the columns automatically with just a few more lines:


alter table #table_Name# modify #column_name# VARCHAR(55) ;

(this would set the size of your varchar field to 55 characters, for example)

How to find Duplicate Entries in a mySQL Database

Wed, 25 Nov 2009 19:00:00 -0600

Here's a quick easy query that will return all records where a given column's value appears more than once. The trick is the 'group by' phrase, combined with the selection of the 'count(*)' value, which simply counts the records found for each match of 'Name'. This query will return any records where the same value appears more than one time in the given column (in this case, the 'Name' column), along with the 'Counter' value showing how many times the duplicate appears.


SELECT Name,count(*) as Counter
FROM Table_Name
GROUP BY Name
HAVING Counter > 1

Get the recordcount of a ColdFusion update, insert or delete query using CFquery result attribute

Thu, 27 Aug 2009 19:06:00 -0600

When you run a cfquery using SELECT, the number of records returned is easy to find using #queryname.recordcount# - but what about the other types of SQL queries, our good friends Update, Insert and Delete ? Those query types don't return a total of rows in the same way. But there's an easy solution ... once again, we can use 'recordcount', but in this case it is a node of the 'result' variable as defined in our cfquery tag.




UPDATE table_name
SET 
column1 = '#value1#', 
column2 = '#value2#'
WHERE column3 = 1

That query is just a sample, you could have any 'where' statement, values, etc... but notice the "result" attribute of the cfquery tag, which will accept any variable name you want to use. Then, immediately following the query code, we simply get the 'recordcount' node of the result structure, and we have the number of updated records from the query. ( Note: this example is for mySQL - ymmv with other db types)

Get the ID of a newly created record from a ColdFusion insert query using CFquery result attribute

Thu, 27 Aug 2009 15:48:00 -0600

When inserting a new row to a mySQL database table using <cfquery>, it is often necessary to find the ID of the new record so you can refer to it in subsequent queries or other code further down the page. There are a few ways to do this, but I like this one best:




INSERT INTO my_table
(
column_1,
column_2,
etc
)
VALUES
(
'#value1#',
'#value2#',
'etc'
)

Notice the "result" attribute of the cfquery tag, which will accept any variable name you want to use. Then, immediately following the query code, we simple get the 'generated_Key' node of the result value, and voila - we have the ID of the new record, ready for whatever is needed next. (Note: This example is for mySQL - not sure how this plays out in MS SQL or other DB engines... your mileage may vary.)

Duplicate a mySQL table with one line of SQL code

Tue, 19 May 2009 19:52:00 -0600

CREATE TABLE copyname SELECT * FROM originalname Too easy! I'm using this to make a backup of an existing table before running a scheduled data import routine (overwriting the backup_table each time by making a new copy of the original) :




DROP TABLE backup_table


CREATE TABLE backup_table SELECT * FROM data_table

mySQL replace() : find and replace text in existing mySQL data column

Sat, 25 Apr 2009 03:34:00 -0600

This is a really useful function - you can find and replace text in any column of a mySQL database using the " replace() " function.

UPDATE table_name
SET column_name = replace(column_name,'text to find','replacement text');

easy!

mySQL search query : remove case sensitivity

Wed, 17 Dec 2008 12:02:00 -0600

I've had this situation before - a mySQL query using 'like' to search a string value for a given phrase sometimes becomes case sensitive when the query contains other 'Where' phrases. ( As far as I know, 'like' is supposed to be case INsensitive, and this is a known bug in some installations of mysql - I've tried updating mysql drivers and still it remains). But, regardless of the cause, there's an easy fix... [More]