1

I have datetime column which is in this format YYYY-MM-DD HH:mm:ss.

But in my php form for updating this data I have created 2 separate fields one is for DATEpart and one is for TIMEpart.

There is no problem to receive/load these values into my php form from table using: 

RIGHT(datetime,8) for TIMEpart or for DATEpart LEFT(datetime,10)

Problem is I am not able to update also only that part of data. If I am attempting to update with TIMEpart it will try to update first 8 characters of datetime instead of attempting to update last 8 character. Problem is I do not know how to tell the program to do so, I know that it needs to be defined into my sql query. 

$query3=mysqli_query($db,"update LISTS set datetime=LEFT(datetime,10), 
datetime=RIGHT(datetime,8) where id='$id'");

I am new into SQL and PHP. I have this already working by having 2 separate columns but I would like to simplify sql table if possible.

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brano
  • 57
  • 1
  • 12
  • Why do you have to update only time or date? This is a reason why datetime is usually used. Otherwise, you could use `CURTIME()`, which will give you the current time or `CURDATE()` for current date. Why cannot you use this? If I don't understand your question please try to explain the reason why you are doing it – ZverArt May 21 '17 at 10:54
  • 1
    Datetimes have no format in a table, or are you saying that you store them as strings? You shouldn't. The appropriate type for a datetime is `DATETIME` (or `TIMESTAMP`). To get the date part of a datetime you'd use the `DATE` function and for the time part you'd use the `TIME` function rather then some string functions. And if you want to treat date and time separately, why don't you store them separately? That would be a column of type `DATE` and another of type `TIME`, and reading and updating would be straight-forward. – Thorsten Kettner May 21 '17 at 11:15

1 Answers1

5

I think you want to use addtime():

update lists
    set datetime = addtime(@date, @time)
    where id = @id;

Learn to use proper parameterized queries rather than munging the query string with user input values. Your approach is likely to lead to inexplicable syntax errors and makes the code vulnerable to SQL injection.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • please, show me better approach than @Gordon Linoff – Brano May 21 '17 at 11:23
  • @zevart because I have in my form two separate fields for Date and for Time when event starts, but in my table there are already 2 separate columns, one is date in DATE datatype and one is timestart in TIME datatype. As I said this is attempt to simplify the app. So if I will now merge these two columns into one called datetime with datatype as datetime I also need to amend my querry in my php file. Hope its clearer. If not, feel free to ask. Also, maybe its not possible, as I said I am new. – Brano May 21 '17 at 11:26
  • @Thorsten Kettner well, thats how I see it in my SQL table written so I was thinking that is the order format. This new datetime column is stored in DATETIME datatype. – Brano May 21 '17 at 11:28
  • also @Gordon Linoff, maybe silly question but why do you use for definig variable at@ and not dollar sign$? – Brano May 21 '17 at 11:34
  • 1
    parameterized http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Brano May 21 '17 at 11:58