Fake Programmers

« first day (195 days earlier) ← previous day next day → last day (2206 days later) »

Ivo Flipse

12:08

@Sathya does it matter if I have spaces in my table names?

guess not right

Sathya

12:23

@IvoFlipse it shouldn't, but please do avoid them!

Ivo Flipse

how so? for syntactic reasons?

Sathya

yeah

depends on DBMS engines as well

Ivo Flipse

well I removed them :P

Sathya

for instance in Oracle you'll have to wrap them in quotes

Ivo Flipse

now to find out what types I need :P

Sathya

12:25

then 'THIS TABLE' will not be the same as 'this table'

Ivo Flipse

CREATE TABLE `subjects` (
	`subjectid` INT(10) NOT NULL AUTO_INCREMENT,
	`subjectcode` INT(10) NOT NULL DEFAULT '0',
	`firstname` VARCHAR(50) NOT NULL DEFAULT '0',
	`lastname` VARCHAR(50) NULL DEFAULT NULL,
	`address1` VARCHAR(255) NULL DEFAULT NULL,
	`address2` VARCHAR(255) NULL DEFAULT NULL,
	`city` VARCHAR(50) NULL DEFAULT NULL,
	`postalcode` VARCHAR(10) NULL DEFAULT NULL,
	`phone` VARCHAR(15) NULL DEFAULT NULL,
	`email` VARCHAR(50) NULL DEFAULT NULL,
	`genderid` INT(11) NULL DEFAULT NULL,
	`birthday` DATE NULL DEFAULT NULL,

I just messed with the types to something I hoped made sense :P

Sathya

subjectid and subjectcode?

Ivo Flipse

ah the code is something the clinics use

in their other administration stuff

your patient number or whatever

oh right, I've set it to not null, that's wrong :P

Sathya

Ivo Flipse

in my old employers software birthday was required, such BS, people would never bother to fill in something real

Sathya

12:32

1/1/1111

done.

Ivo Flipse

well it had dropdowns :\

very annoying

Sathya

ouch

Ivo Flipse

and you had to set them

so I would tab my way to them, then press down, tab, down, tab, down tab, tab enter :P

Sathya

heh

Ivo Flipse

I wonder how I need to make an insert statement

Sathya

12:35

how do you mean?

Ivo Flipse

is it like INSERT INTO subjects(subjectid, subjectcode, ....) VALUES (id, code, fname, lastname);

Sathya

yes

Ivo Flipse

that sounds easy :P

Sathya

13:03

@IvoFlipse yeah :P

Ivo Flipse

13:22

oh darn, the UI at the moment has only name, not first name + last name

I'm almost tempted to not even support it

but I fear that's going to bite me in the ass :P

Sathya

> name.rsplit(' ')

Ivo Flipse

firstname, lastname = self.name.Value.split(' ')

lol I just did hehe

but then you go: Jan de Boer :P

Sathya

Ivo Flipse

screwed is me

Sathya

there @IvoFlipse fixed :P

Ivo Flipse

13:26

_mysql_exceptions.OperationalError: (1054, "Unknown column 'firstname' in 'field list'")

how strange, field list?

Sathya

weird

Ivo Flipse

hadn't updated the database tables yet

it worked :)

Sathya

aha

Ivo Flipse

I should make it a bit smarter though :P

If I keep pressing save, it will just make new records

Sathya

check for existence and update it, if it exists

Ivo Flipse

13:30

yeah this is a slippery slope, if I only have a unique name, that will mess things up :P

hi @UnsignedCodelabs

Unsigned Code labs

Sathya

@IvoFlipse that's where pk comes in

Ivo Flipse

patient code? or?

ah primary key

Sathya

primary key

Ivo Flipse

I don't think I fully understood that part

Sathya

13:33

let it be a hidden field

always have a pk not based on your existing data

Ivo Flipse

I have set the subject id as PK

just autoincrement

but I don't see how that helps prevent dupes

Sathya

that should be fine, but have a something like s_recordid

@IvoFlipse well if you really want names to be unique, you could put a unique constraint on firstname & lastname...

Ivo Flipse

well not unique, I'm just pondering on making sure I don't overwrite someone with the same name

the primary key is unique, but I don't have any control over that one

Sathya

@IvoFlipse well then you will need a unique constraint then

@IvoFlipse why not?

ah because of auto-increment thing ?

Ivo Flipse

say I add myself now

and I add myself a 100 IDs later, it will check: oh you exist, I'll get you another ID

but it should only do that once

Sathya

13:38

@IvoFlipse well that's where unique constraint should help

Ivo Flipse

perhaps I should poll for the last ID and 'assign' the id myself

Sathya

in Oracle we don't have auto-increment

Ivo Flipse

so I can check the unique combination

Sathya

we'll have to have general the pk ( generally using a sequence)

Ivo Flipse

there's too many people with the same name in belgium :P

their addresses won't be the same though

as soon as the name is not unique, you must fill in a patient code

else I'll just update the first match :P

Ivo Flipse

13:54

> INSERT INTO Combo(ID, Name, CountryCode) VALUES ("4078", "Singapore", "SGP") ON DUPLICATE KEY UPDATE ID=ID+1;

I guess this assumes I know the key right?

and presumably update only updates if there is data?

hi @curchin

1 hour later…

Ivo Flipse

15:14

@TomWijsman you know some SQL right? :P

Tom Wijsman

Yes

Ivo Flipse

I'm getting somewhat stuck on inserting 'duplicate' data

Tom Wijsman

I think you are right.

Ivo Flipse

I found a line of code that made me a nice unique index based on the subjectcode, firstname and last name

Tom Wijsman

Well, I think you want to use REPLACE instead.

Or wait, you don't want to replace it.

Ivo Flipse

15:23

which basically means: you're free to add a patient with the same name, but if you hadn't set a subjectcode yet, you'll have to add a unique one the second time

You Flemish have too many people with similar names :P

Tom Wijsman

Why not let the ID auto_increment?

Then you can just do INSERT INTO Combo(Name, CountryCode) VALUES ("Singapore", "SGP");

Ivo Flipse

because I don't understand how to use an autoincrement ID to check for dupes

the subject code is not meant as an index btw

so how do I check whether this new Tom Wijsman is any different from the previous one? because isn't id automatically incremented?

I mean, if I want to add a new Tom Wijsman, it will just get a new ID, which defeats the purpose :P

or rather, I want that, but only if I know that it's not the same Tom Wijsman as the first one

Tom Wijsman

You can prevent duplicates with

Q: How do I specify unique constraint for multiple columns in MySQL?

I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Thanks in advance. Of course the example is just... an example. So please don't worry about the ...

You just let the ID autoincrement, and add an unique constraint on the column set you don't want to have duplicate in your table.

Ivo Flipse

but on which columns would I put it then?

first name + last name + subjectcode then?

Tom Wijsman

Those that you use to uniquely identify your person.

Yes, I guess so... And subjectid would autoincrement then.

Ivo Flipse

15:32

ah interesting if I leave subjectcode blank, it still says: look dude you already have a Ivo Flipse, you're going to have a code to make this one look different

oh wait, because it's unique, it's required

sigh perhaps I should change the default from NULL to a value

« first day (195 days earlier) ← previous day next day → last day (2206 days later) »

all rooms