2 Fields in a record unique

[gerard]

I tried to program that 2 fields in a record must be unique or the user get a error message. One of the field is a TEXT the other a DATE. Each day only one time the same text. My problem is that I have not any programmer skills(except CNC Machines and a bit basic from my old c64 or the config.ini from Q3A;) ).

I tried to transform the two fields to a group but nothing is working. Also I´m unable to script it for only one field in Xataface. In phpmysql is it no problem. But that didn´t help me...

Hope somebody can help me.

field 1: placa(TEXT)
field 2: fecha(DATE)

both unique with trigger

message for unique: the record is saved
message for not unique: record is not unique. Please check your datas.(not saved)

Thx for any help.

[shannah]

The easiest way is to simply add a unique key based on these two columns in mysql.
(It will be limited in length to 255 chars so you would only be able to guarantee uniqueness on the first 255 characters of the text field with this strategy).

If that won't do it, you'll need to do some custom validation using php.

[gerard]

I have the problem that i can´t use the DATE as second field.

My Server is standing in Germany but I live now in Venezuela. So I put the line date_default_timezone_set("America/Caracas"); in my index.php. It works well with TIMESTAMP and DATETIME but not with DATE or TIME. TIMESTAMP and DATETIME show me the Venezuelan time and date, but DATE and TIME show me still the German time... Also I can´t use only the first part of TIMESTAMP or DATETIME because I get in phpmyadmin an error(1089) that it is not allowed. So I can check with TIMESTAMP and DATETIME only if it unique in this second. With DATE is it than the problem that it show me from 17:30(5:30 pm) o´clock the date of the next day.

I am now so frustrated of this situation because I can check only that what I don´t want. BTW with TINYTEXT work the unique.

Please helped me. I have also the problem that I am living in the jungle(El Dorado, Venezuela). I am so lucky that a small village with a GPRS connection is exactly in my range. But I have only a 1-2kbyte/sec connection. So it is really hard to use the phpmyadmin. So every test can cost easy 30 min.

[shannah]

If date won't do what you want, then there are still many solutions. If you can't do any PHP coding you will be limited as to your solutions. One example solution would be to create a column that stores a hash that would be unique on the text and date. Then you could calculate this hash in the beforeSave() method. E.g.

1. Add a column named 'hash' to your table . Varchar(32). This will be used to store an md5 hash that is calculated using the date and text fields.
2. Add a unique key on this 'hash' column so that no two rows can contain the same hash.
3. Calculate the hash in the beforeSave() method of the table delegate class:

Code: Select all

function beforeSave(&$record){
    $hash = md5(md5($record->strval('textfield')).md5($record->strval('datefield')));
    $record->setValue('hash', $hash);
}


[gerard]

If date won't do what you want, then there are still many solutions. If you can't do any PHP coding you will be limited as to your solutions. One example solution would be to create a column that stores a hash that would be unique on the text and date. Then you could calculate this hash in the beforeSave() method. E.g.

1. Add a column named 'hash' to your table . Varchar(32). This will be used to store an md5 hash that is calculated using the date and text fields.
2. Add a unique key on this 'hash' column so that no two rows can contain the same hash.
3. Calculate the hash in the beforeSave() method of the table delegate class:

Code: Select all

function beforeSave(&$record){
    $hash = md5(md5($record->strval('textfield')).md5($record->strval('datefield')));
    $record->setValue('hash', $hash);
}


Thx shannah for the quick answer. If I understand you right is this the same result than the code with TINYTEXT and DATE:

CREATE UNIQUE INDEX `index_pdv` (`textfield`(10), `datefield`)

text_field: TINYTEXT(first 10 letters) all works fine

datefield: DATE(wrong date(6h 30min different???) but good format for daily unique)
DATETIME(wrong for daily unique but right date inside. can not chose the first 6 letters because of error 1089)
TIMESTAMP(wrong for daily unique but right date inside. can not chose the first 6 letters because of error 1089)

If I am not wrong I will get with your code also the wrong date(exactly from 5:30pm). So it will me bring me a wrong result from 5:30pm to midnight.

When i will use TIMESTAMP or DATETIME I can´t get the unique from the day because I can not chose only the date(exclude the time).

I don´t understand why I get a different time(date) with DATE and DATETIME.

[shannah]

I spent a fair bit of time a while back dealing with timezone issues for web auction. You need to deal with the timezone in both mysql and php. In fact Xataface will do timezone conversions automatically to correspond with the current system timezone. This may be part of the discrepancy you are seeing. I posted a blog post discussing these issues back when I was working on web auction:
http://www.sjhannah.com/blog/?p=113

-Steve