mysql database help needed!!

[ahamednishadh]

hello there..

need a small help regarding mysql databse.

i have a table k..

nw this is a table for entering information of a time table like scene..

that is a user can enter a date and then enter the times he is free for that day..

i have the following fields in the table..

ID - userid - foreign key
dates - the date - primary key
slot1 -1st free slot
slot2 -2nd free slot
slot3 -3rd free slot
slot4 -4th free slot
slot5 -5th free slot
slot6 -6th free slot
slot7 -7th free slot
slot8 -8th free slot

all the slots are for the same day... so the user can input different times of the day that he is free...

nw i did this and it worked and now i stumbled upon a problem.. that is i entered sum data for one user and now wen i try to enter data for another user for the same date... i cant coz it says already exists..

nw this is my problem.. how do i make it so that i can have the same user enter multiple dates and also another user enters the same date... BUT the same user cant enter the same date twice... hw can this be done??

thanks a million times in advance!!!!!!

 

[TΞΞNSTAR™]

dates - the date - primary key

Primary key is Unique rite??

dnt hav much idea abt SQL Stuffs.. Bt cn u manipulate Primary keys??

 

[ahamednishadh]

dates - the date - primary key

Primary key is Unique rite??

dnt hav much idea abt SQL Stuffs.. Bt cn u manipulate Primary keys??

ya bro.. dats da prob... i didnt think of this problem at start.. but nw jst stumbled upon it... nw wanna change it accordingly so that it works according to my scenario....

 

[TΞΞNSTAR™]

ya bro.. dats da prob... i didnt think of this problem at start.. but nw jst stumbled upon it... nw wanna change it accordingly so that it works according to my scenario....

Aha.. bt according to my knowledge you cant do changes after you declare a primary key na.. Mmmm gimme some time ill think abt a approach.. Inshallah someone would help you out soon... Even I need to know how to over come this scenario !

 

[ahamednishadh]

Aha.. bt according to my knowledge you cant do changes after you declare a primary key na.. Mmmm gimme some time ill think abt a approach.. Inshallah someone would help you out soon... Even I need to know how to over come this scenario !

well u can change bro... u can edit the database using phpmyadmin... ive done it...

he he thx bro....

 

[TΞΞNSTAR™]

well u can change bro... u can edit the database using phpmyadmin... ive done it...

he he thx bro....

Aha maybe in MySql... bt accourding to the theory u cant na.. :s in Ms Sql... anyway ur welcome bro
\

BUMP

 

[sri_lion]

Do you mean.. for example...

(User A) enters (record A) for 25.02.2010 - Allowed
(User B) enters (record A) for 25.02.2010 - Allowed
(User A) enters (record B) for 25.02.2010 - Not Allowed

???

Dont use date as a primary key, in your scenario its going to be repeated anyway!

 

[homoelectronics]

Using date as the primary key is not a good idea. You may use either separate field as the primarykey. For example column with a serial type (e.g. INT with auto-increment true). Or sometimes you may combine both user-id field and date field as the primary(composite) key.

 

[sri_lion]

Ok I think I know your problem... you've set primary keys for "date" and "user_id" this doesn't work that way because "user A" and "user B" maybe getting only single row per day in the database.. right?

But the next day they are going to get another row... so having those fields as PK won't do it... because it needs to repeat.. you need a separate PK for this say for example "daily_id" or something...

You need to control your input in your script... in the script you make your own primary key by checking "user_id+today's date" when you want to put the record in.. you need to check today's date(string) and user_id combination..

I'll try to give you the pseudo here..

variable my_id = A
variable today = 25022010
variable count = count rows in db where user_id= my_id and todays_date =  today

if (variable count > 1)
{
    allow edit only and pop-up msg "you cannot create a new row, would you like to edit the existing one?"
}
else 
{
    add a new row
}

 

[ahamednishadh]

Do you mean.. for example...

(User A) enters (record A) for 25.02.2010 - Allowed
(User B) enters (record A) for 25.02.2010 - Allowed
(User A) enters (record B) for 25.02.2010 - Not Allowed

???

Dont use date as a primary key, in your scenario its going to be repeated anyway!

ya dats wat i want....

 

[ahamednishadh]

Ok I think I know your problem... you've set primary keys for "date" and "user_id" this doesn't work that way because "user A" and "user B" maybe getting only single row per day in the database.. right?

But the next day they are going to get another row... so having those fields as PK won't do it... because it needs to repeat.. you need a separate PK for this say for example "daily_id" or something...

You need to control your input in your script... in the script you make your own primary key by checking "user_id+today's date" when you want to put the record in.. you need to check today's date(string) and user_id combination..

I'll try to give you the pseudo here..

variable my_id = A
variable today = 25022010
variable count = count rows in db where user_id= my_id and todays_date =  today

if (variable count > 1)
{
    allow edit only and pop-up msg "you cannot create a new row, would you like to edit the existing one?"
}
else 
{
    add a new row
}

well bro.. wat ur saying here is when the date is automatically entered ne??

well my prob is the user can login to the system and then he can enter the date he needs and then enter the times... so he can enter n e day he wants in the future... but he cant enter the same date again... but he can view his old entry and edit it...

so now i need to know first how to make the system where one user can enter as many dates as possible but not the same and then another user can do the same with the same dates...

for eg...

user A can enter his free times for 25th, 26th, 27th, 28th march...
and user B can enter his free times also for the 25th, 26th, 27th, 28th march
BUT
after user A entering the times for 25th and saved it, he cannot enter the same date again to make an entry...

this is basically a system where users insert the times they are free and the admin can chk this and allocate stuff accordingly to them....

 

[sri_lion]

well bro.. wat ur saying here is when the date is automatically entered ne??

well my prob is the user can login to the system and then he can enter the date he needs and then enter the times... so he can enter n e day he wants in the future... but he cant enter the same date again... but he can view his old entry and edit it...

so now i need to know first how to make the system where one user can enter as many dates as possible but not the same and then another user can do the same with the same dates...

for eg...

user A can enter his free times for 25th, 26th, 27th, 28th march...
and user B can enter his free times also for the 25th, 26th, 27th, 28th march
BUT
after user A entering the times for 25th and saved it, he cannot enter the same date again to make an entry...

this is basically a system where users insert the times they are free and the admin can chk this and allocate stuff accordingly to them....

It doesn't matter bro... for this first of all you need a good form validation if the user input the data... make sure the data string passed by the form is always standard creating a date drop down is your best option!

Ok lets look at the picture below think that this is your table.. with data..



Whenever the user trying to create a new record (fill the form and hit submit) your script must look through the database whether the date format together with his ID exist or not, in the example above

user_id = 123 already has a row for the date 25022010

So when he wants to create another one for the same date, in the form he enters Feb 25, 2010 (will be converted to the string 25022010) now you look through the database for a row that's

user_id = 123 and for a string in the date column = 25022010 (which is the date he just entered)

if this is found in a single row, then a row exist, then you only allow UPDATE command in SQL.. not CREATE..

If the row found.. throw a message with Yes and No asking "Record already exist would you like to edit?" if "Yes" you take the user to an editing form but hide the date (or show the date as a label not as a input)

That's all... BTW what language you using for this?

 

[ahamednishadh]

It doesn't matter bro... for this first of all you need a good form validation if the user input the data... make sure the data string passed by the form is always standard creating a date drop down is your best option!

Ok lets look at the picture below think that this is your table.. with data..

View attachment 13860

Whenever the user trying to create a new record (fill the form and hit submit) your script must look through the database whether the date format together with his ID exist or not, in the example above

user_id = 123 already has a row for the date 25022010

So when he wants to create another one for the same date, in the form he enters Feb 25, 2010 (will be converted to the string 25022010) now you look through the database for a row that's

user_id = 123 and for a string in the date column = 25022010 (which is the date he just entered)

if this is found in a single row, then a row exist, then you only allow UPDATE command in SQL.. not CREATE..

If the row found.. throw a message with Yes and No asking "Record already exist would you like to edit?" if "Yes" you take the user to an editing form but hide the date (or show the date as a label not as a input)

That's all... BTW what language you using for this?

hmmm... thx... ill chk on it and c....

im using php and mySQL

 

[xpanuwa]

mata oyage skyp id eaka denna
matath oka gana danna ganna one

 

[sri_lion]

Dude.. I've created an example for you.. its very very basic one, you'll have to modify it further, just to give you an idea (proof of concept) only...

Download the ZIP file, setup it up in your localhost and check out the code.. its in PHP!

http://www.mediafire.com/file/dm1mtwivmiq/TT_example.rar

 

[ahamednishadh]

Dude.. I've created an example for you.. its very very basic one, you'll have to modify it further, just to give you an idea (proof of concept) only...

Download the ZIP file, setup it up in your localhost and check out the code.. its in PHP!

http://www.mediafire.com/file/dm1mtwivmiq/TT_example.rar

eh... elakiri.... thanks a lot..

im busy wit sum other work nw.. ill chk it out tomorrow and let u know bro!!

thank u very much again!!

 

[sri_lion]

eh... elakiri.... thanks a lot..

im busy wit sum other work nw.. ill chk it out tomorrow and let u know bro!!

thank u very much again!!

Ok no probs...

What it does is.. when you submit the form it goes to "check_row.php" and checks for existing record if none found then it submits, if there are records for the date and user_id combination it notifies you and send you back to index to change the date and try again!

Likewise you can send the user to a edit page too... (which I haven't included in the file but I think with some effort you can figure it out )