http://coldfusion.com logo
#cfml-beginners
Title
# cfml-beginners
j

johnbarrett

03/05/2022, 12:27 AM
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.
Copy code
<cfquery name="Insert">                                                                                                      INSERT INTO users(first_name, last_name, date)
VALUE("#form.first_name#", "#form.last_name#", "#CreateODBCDate(Now())#");
 </cfquery>
d

Daniel Mejia

03/05/2022, 1:13 AM
whats your table design?
does your table have a unique column?
if your table only has a first name and last name columns then that is not enough. Unless thats your intention of never having two people with the same name.
j

johnbarrett

03/05/2022, 2:11 AM
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?
d

deactivateduser

03/05/2022, 2:17 AM
You should add a unique constraint to your tables if you want to require uniqueness
j

johnbarrett

03/05/2022, 2:36 AM
@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?
d

deactivateduser

03/05/2022, 2:38 AM
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)
Trying to insert a non unique value into a table with a unique constraint will throw an error. Some will use that as their “check”. But that is a design choice and you may not get all the info you need to tell the user what is wrong unless you parse the thrown error which I find cumbersome
One thing I noticed in your table is the dates aren't consistent. Are you using a time stamp type for your dates or storing as a varchar?
j

johnbarrett

03/05/2022, 2:55 AM
@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!
👍 1
a

ageax

03/05/2022, 3:51 AM
@johnbarrett - Even ignoring the manual vs timestamp, the date values still look off. What is the datatype of that column:
datetime
or
varchar
?
Because it looks like
varchar
from here...
j

johnbarrett

03/05/2022, 3:56 AM
@ageax it's a varchar, should I change it to a datetime?
a

ageax

03/05/2022, 3:57 AM
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'}
Use "date" if you need to store the date only, like for a DateOfBirth column, you typically only need the date portion. Use "datetime" if you need to store both date and time, like for capturing the exact date and time a record was created.
j

johnbarrett

03/05/2022, 4:00 AM
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!
👍 1
a

ageax

03/05/2022, 4:01 AM
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.
👍 2
j

johnbarrett

03/05/2022, 4:34 AM
@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!
👍 1
a

ageax

03/05/2022, 4:46 AM
Yep. You can using it on any simple/literal value.
CreateODBCDate(Now())
Btw, that will truncate the time, and just return a date "2022-03-05". If you want both date and time, just use
Now()
like someone mentioned on another thread
👍 1
a

Adam Cameron

03/05/2022, 10:48 AM
@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 😉
j

johnbarrett

03/07/2022, 1:25 AM
@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.
2
d

David Buck

03/07/2022, 2:57 PM
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.
💯 1
p

Patrick S

03/07/2022, 3:48 PM
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/
2 Views