Words about stuff, and things related to stuff

MySQL order by in() list

Here's a simple but powerful MySQL thing. Sometimes when selecting records using a list of values in an IN() statement, you also want to order by that list. (If you've ever tried this, you know that the matches found via IN() are not returned in the same order as the values from you IN() list).

There are a lot of complex ways you could do this, but here's the simplest way I know, ordering the query results by the same list.

view plain print about
1<cfquery datasource="#request.dsn#" name="getstuff">
2 SELECT m.id, m.title
3 FROM mytable m
4 WHERE m.id IN(#request.getids#)
5 GROUP BY m.id
6 ORDER BY m.id IN(#request.getids#)
7 </cfquery>

The trick is the ORDER BY, using the same IN() statement as the IN() from the where clause.

That should do it.

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

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.

view plain print about
1UPDATE tbl_products
2SET flavors = CONCAT(flavors,',199')
3WHERE FIND_IN_SET('105',flavors)
4AND 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)

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:

view plain print about
1SELECT user_city, ID
2FROM site_users
3WHERE user_city = lower(user_city)
4AND NOT user_city = ''
5ORDER 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:

view plain print about
1<cfoutput query="myQueryAbove">
2    <cfquery datasource="#application.dsn#">
3    UPDATE site_users
4    SET user_city = '#capFirstTitle(myQueryAbove.user_city)#'
5    WHERE ID = #myQueryAbove.ID#
6    </cfquery>

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

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:

str_to_date(aa.date_published,'%Y-%m-%d') as articleDate,
aa.title, aa.ID,
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:




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

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:

view plain print about
3WHERE lower(column_name) LIKE '%sort%'
4AND 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:

view plain print about
3WHERE TABLE_SCHEMA='//my database name here//'

or all columns with a specific data type

view plain print about
3WHERE TABLE_SCHEMA='//my database name here//'
4AND 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:

view plain print about
1alter 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

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.

view plain print about
1SELECT Name,count(*) as Counter
2FROM Table_Name
4HAVING Counter >

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

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.

view plain print about
1<cfquery datasource="#request.dsn#" name="saveChanges" result="updateResult">
2UPDATE table_name
4column1 = '#value1#',
5column2 = '#value2#'
6WHERE column3 = 1
9<cfset recordsChanged = updateResult.recordCount>

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

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:

view plain print about
1<cfquery name="insertRecord" datasource="#request.dsn#" result="insertResult">
2INSERT INTO my_table
16<cfset newID = insertResult.generated_Key>

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

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) :

view plain print about
1<cfquery datasource="#request.dsn#" name="dupTable">
2DROP TABLE backup_table
4<cfquery datasource="#request.dsn#" name="dupTable">
5CREATE TABLE backup_table SELECT * FROM data_table

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

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');



mySQL search query : remove case sensitivity

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...


Recent Comments

Importing Events Calendar Data into Mura CMS
Jon said: Hi Michael, Thanks for this post - similarly looking to import data from excel spreadsheet into mur... [More]

New TinyMCE lets you paste as plain text automatically
PRR said: I am using tinyMCE 4.2.3 and configured as suggested in this post but when i try pasting in editor i... [More]

Searching and collecting Mura CMS content by Extended Attribute Value with feed.addParam()
Armando Faes said: After a few hours of head banging - finally thanks to this - Once more THANK YOU! [More]

CF Gallery Creator & jQuery Slider Gallery !New and Improved!
Jan Willis said: Okay, I realized that you are calling my root as the Site - not the wwwroot. So, I moved the JS, CS... [More]

CF Gallery Creator & jQuery Slider Gallery !New and Improved!
Michael Evangelista said: @Jan, it has been a long time (6+ years) so my memory of the details is vague, but I think the image... [More]