What is the best way of stopping duplicates to your MySQL database with ColdFusion? I have searched Google but didn't find anything helpful. Below is my query, I am really lost on this one, and would appreciate any help.

<cfquery name="Insert">                                                                                                      INSERT INTO users(first_name, last_name, date)
VALUE("#form.first_name#", "#form.last_name#", "#CreateODBCDate(Now())#");
 </cfquery>

whats your table design?

Here's a screenshot of the database. The last name would be a good place not to enter the same value. Currently, there a 2 John Barretts (me), so to stop 2 with the same last names would be a good idea. Unless you think that I should enter another field like email?

You should add a unique constraint to your tables if you want to require uniqueness

@deactivateduser this is a great point. I think that I will add an email field;therefore, the would not be duplicate emails. How would I go about checking this?

I mean if you want to “check it” as in you want to know if an email already exists in your table you will have to query the table to see if that email exists in your table and then use logic based on that response. But you still want constraints on your tables if you want to guarantee uniqueness. These constraints do add a bit of slowness to insert queries (nothing you will see until you get to millions/billions of records)

@deactivateduser the first 2 entries were hand written, the rest of the entries is using a time stamp. You give me good thoughts about how to re-design the database to ensure that there will not be be duplicates. Thanks so much for all your help!

@johnbarrett - Even ignoring the manual vs timestamp, the date values still look off. What is the datatype of that column:

datetime

or

varchar

?

@ageax it's a varchar, should I change it to a datetime?

Yes, it should be "date" or "datetime" to prevent the entry of invalid dates. Also _d_on't put quotes around

CreateODBCDate()

because that results in inserting a string into the db table instead of a date object. That would cause the query to fail if the target was a "datetime" column. It only works now because its type "varchar". Well... kinda works, because it inserts a string that "sort of" looks like a date object, but isn't really "{d '2022-03-05'}

I just changed the date to datetime. Also, I should remove the qutoes "" to look like

#CreateODBCDate(Now())#

instead of

"#CreateODBCDate(Now())#"

? Thanks so much for your help!

Though really the query should be using cfqueryparam on all of those values, instead of old style quoting. :) The main benefit is it helps protect your db against sql injection.

@ageax thanks for reminding me about cfqueryparam! I always forget about this. You can use cfqueryparam on all the values? I will look at the documentation and thank you!

Yep. You can using it on any simple/literal value.

@johnbarrett, some observations: • if someone asks what your table schema is, don't show them a picture of your data. Show them the result of

SHOW CREATE TABLE

(or the equiv on your platform of choice). • It is 2022. There is no need to use

createOdbcDate

(or its ilk). • Don't hard code data values - especially ones from user input - in your SQL statements. Pass them as parameters. The SQL statement should just hold SQL. Values should be passed as values. • It doesn't look like you are using a framework, given you are referencing the form scope directly in code that does DB writes. Esp as a newbie, one should be using a framework. • At the very least one ought to be following MVC practices for this sort of code, and it doesn't look to me like you are separating your concerns properly (again: form scope references when writing to a DB). There's some keyword searching and research for you to do there 😉

@Adam Cameron I am not using a framework (I plan to learn ColdBox in the near future), but I am now learning OO practices for ColdFusion. I still have so much to learn, but starting to learn about the MVC pattern.

In my (admittedly limited) experience, most "Users" tables have a unique ID column specifically for the purpose of assigning a unique identifier to each user account. The ID is generated when the account is created, and can then be used throughout the database in any table that stores user-specific data (which makes it useful for SQL JOINs). The ID may be copied to the Session scope on login so that the app can use it to look up data that is specific to the current user. The advantage of this approach is that it doesn't require you to make any assumptions about the uniqueness of the data in the other columns. There may be perfectly legitimate reasons why multiple records could have the same first and last name, birthdate, and even email account. Some of those fields may also be subject to change, which makes them unsuitable for use as global identifiers. For simplicity's sake, you could use ColdFusion's CreateUUID() function (or the equivalent function in MySQL) to generate the unique IDs; then you wouldn't have to verify their uniqueness prior to insertion.

For what it is worth, here is a useful list of things to consider when handling names in general: https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/