Converting date formats between PHP & MySQL

bunner_bob

Pretty simple question - I'm just not finding the answer in my books in a format that is clear to me.

User inputs date in the format MM-DD-YYYY

Form is submitted with above field named "startdate"

Date gets rearranged into format YYYY-MM-DD (this is MySQL's 'date' format, right?). We'll call that "$startdatem".

So question 1 is, what syntax would I use to reformat MM-DD-YYYY into YYYY-MM-DD?

$startdatem = dosomething($startdate) - but what?

Question 2 is, when I get it back out of of MySQL, I need to convert it back to the original format, to echo in the same form field. I know how to get it to display in the form, but again, how to reformat it.

Tried messing with date(), but it's not performing the way I expect.

Thanks in advance for any help!

[deleted]

"So question 1 is, what syntax would I use to reformat MM-DD-YYYY into YYYY-MM-DD? "

you can either use a regexp to match on the three parts and 'back-reference' them in a different order:

$sDate = '10-04-2002';
$sDate = preg_replace("/^{([0-9]{1,2})-([0-9]{1,2})-([0-9]{4})$/",} '\3-\1-\2', $sDate);
echo $sDate;

or explode the date into an array, and print the elements of the array in a different order:

$sDate = '10-04-2002';
$aDate = explode('-', $sDate);
echo $aDate[2].'-'.$aDate[0].'-'.$aDate[1];

"Question 2 is, when I get it back out of of MySQL, I need to convert it back to the original format, "

You can do this in your query, using DATE_FORMAT() http://www.mysql.com/doc/en/Date_and_time_functions.html

SELECT DATE_FORMAT(date_field, '%m-%d-%Y) AS thedate

bunner_bob

That's beautiful. I get the first part - much less complicated than I thought it was going to have to be. Thanks!

Not sure I understand how to work the second part (getting the date back out of MySQL) in with my existing query.

You can do this in your query, using DATE_FORMAT() http://www.mysql.com/doc/en/Date_an..._functions.html

SELECT DATE_FORMAT(date_field, '%m-%d-%Y) AS thedate

As it stands, I have (excerpted from my code):

$db = mysql_connect("localhost", "user", "password");
$result = mysql_query("SELECT * FROM cpevent ORDER BY startdate ASC",$db);
($myrow = mysql_fetch_array($result));
$date = $myrow["startdate"];
etc...

So I want to select all rows from the database and sort by startdate, but I also want to do the DATE_FORMAT bit - how would I integrate these two "selects"?

Thanks - I know this is newbie stuff, perhaps it better belongs in that section.

[deleted]

" how would I integrate these two "selects"? "

you wouldn't, you'd just select the date_format bit, and order by the real date which already exists in the table anyway:

SELECT *, DATE_FORMAT(startdate, 'someformat') AS thedate
FROM cpevent
ORDER BY startdate ASC;

startdate always exists, and 'thedate' is created by the date_format(). Select one thing, order by another.

bunner_bob

Okay - that's cool.

Here's one more curve:

I actually have 11 date fields in each row. This is a db for a nonprofit scheduling events, and events that occur over a range of dates have an "enddate", or if they occur on non-contiguous dates, have date2, date3, date4 etc up to date9.

So I'm thinking I need a chunk of PHP - probably try the "explode" function - that will rip apart the dates and rearrange them after the query has pulled them out. Unless I can use DATE_FORMAT 10 times in my query (after "AS thedate"?)

What do you think the best approach would be?

[deleted]

Methinks you have a weird database design.

Why 11 date fields?
It's common practice to take those dates out of the main table, and create a 'dates' table that holds only the id of the event, a startdate and an enddate.

That way you can add as many start and enddates to the event as you like.

This also solves your date_format 'problem', because you'd only for the date_format on the start and enddate from the dates table.

As for comparing the dates, you'd ofcourse convert the date you get from the form to a format that the database understands, and then simply compare it in the database using < = and >

bunner_bob

I think I understand. So in this second table (dates - or we'll call it "eventdates" just to be safe) I might have three (or 20) different rows all belonging to event id #45, each row having a different startdate and enddate. Of course each row would need a unique ID - or would it? Could there just be three columns - id, startdate, enddate - and the database won't mind?

Then to retrieve I could just run a second query from eventdates WHERE id=45.

To create/input these dates into the form, I could make a button on my main form "additional dates?" which would load a new form that on submit would INSERT into eventdates instead of the regular table.

Sound like what you're thinking about?

[deleted]

You seem to get the point allright 🙂

The database does indeed not care at all about what you put in there, but an auto_increment ID column is usually a good idea because it lets you identify records to much more easily.

"To create/input these dates into the form, I could make a button on my main form "additional dates?" which would load a new form that on submit would INSERT into eventdates instead of the regular table."

I'd always insert straight into eventdates, so all the dates regarding events are always in the eventdates table. If you put the first date into the main table and the rest into the eventdates table, you'd always have to run two queries to find all the dates, and you'd need messy PHP code to merge the results and all that poo.

bunner_bob

Of course I'll still have to post into (and retrieve from) two tables since the dates will be in one table and the event info in another. But I don't expect that will present any problems.

And yes, I should definitely have a unique ID in the eventdates table. I can have a field called "eventid" in eventdates that corresponds to "id" in the events table. Then two queries - one for event info for id 45 (for example) from events table, then set $eventid = $id and run another query from eventdates for for $eventid 45.

Just trying to work out the ergonomics of form entry. As I imagine it:

Content admin fills out form with one start/end date. Two buttons at bottom: "submit event" and "add additional dates".
If submit, just dumps data into the two tables (two separate queries).
If "add additional dates", dumps data into tables and gets another form to add another start/end date, again with two button options.
Etc. for as many dates as required.

Then of course there's reading the data back for editing. The form can't be exactly the same as the original input form because there may be multiple dates, and they should all appear in one place for ease of comprehension. Should be doable though. Of course, the edit form would have "delete this date" options for dates, plus again the "add additional dates" button at the bottom.

Is this how you'd do it?

And by the way, thanks for all the help. I do appreciate your time on this. It's saving me a lot of frustration.

[deleted]

You're banging the nails right on the head here :-)

One tip though, about the 'more dates' part. I had the exact same problem (well, completely different ofcourse, but the same nonetheless). The way I solved the 'more fields' problem was by adding a 'more dates' button next to the dates field. When pressed, the button submits the data, but the script does not actually put the data in the database yet.
Instead, it re-prints the entire form, with additional fields for the dates.
When the user is happy with the dates, he presses 'save' and everything is written to the database.

re-printing the data is easy if you write your script so that the field-names used in the form are the same as the var-names you use when you read data from the database.

That way, the dataset you get from the form is exactly the same shape as the dataset you get from the database, which means you only need one routine/function to print the dataset.

When a form is submitted, you check to see if the 'more fields' button was pressed. If it was, you continue to use the data that came from the form. If it wasn't, you read the data from the database.

If the 'more fields' button was pressed, you can add more fields before you print, simply by adding more values to the array of dates you already got from the form. That makes it look as though there were already a few empty fields submitted.
More values means that the printing routine will automatically print more fields.

This way you save yourself a sh*tload of work in the printing of the forms, and life becomes a much more pleasant experience.

bunner_bob

Some of this I understand, some I probably will understand once I get home and code it (on the road at the moment).

Two questions perplex me still:

At the moment, I have two tables: events, and eventdates. Events contains the fields "eventid" (autoincrementing) and "eventname" (for simplicity I'll limit it to that for now). Eventdates contains "eventdateid" (autoincrementing), "eventid", "startdate" and "enddate". So I can have one event (which will have a unique "eventid") and multiple eventdates that correspond to that event (since they'll all have the same "eventid" as the event has).

Ok so far? Now - if I create a new event, and create some start and end dates for it, and I want to submit it - well, there's no "eventid" until a new row has been created in the events table. So there's no way to know which "eventid" to tag the eventdates with until I've already submitted to the events table AND read back from it. Right? Or is there some way to have MySQL echo back the automatically-generated "eventid" of the row that was just inserted into? And then I can pass that to the eventdates table along with my start and end dates.

If I create multiple sets of start/enddates before I put them into their table, then wouldn't I have multiple form fields that are all named "startdate" and "enddate"? So if I submit that data to the eventdates table, how do I tell what to go where? Can I submit, say, 3 sets of startdate/enddate and each one will automatically go to a new row, with a unique "eventdate" id (but of course the same "eventid"?

I was thinking I'd have to do one date at a time, so the query would add one row at a time to the table. But perhaps there's a way to dump in multiple rows at once.

Thanks again for all the help so far!
(I should probably get a book on db design one of these days...)

[deleted]

" if I create a new event...."

Correct, you cannot get the eventid untuil you actually insert the event.

But this is not a problem because for consistency you'd never want to insert eventdates before you inserted the event that the dates belong to.

Typically the entire operation whould be done inside a 'transaction', so that if any of the date-inserts fail, the event itself is again removed from the database. Either everything is inserted, or nothing is inserted.

" then wouldn't I have multiple form fields that are all named "startdate""

Yes you would, but PHP has a very cool freature to solve that;
You can give the fields an array-name, like "startdates[]". Then when you submit the data, PHP will convert all those fields into one array called 'startdates' and you can simply loop through the contents and insert the dates.

You can even do multi-dimentional arrays, like: dates[][startdate] and dates[][enddate]
This way you have only one array, which you can read like:

echo $POST['dates'][0]['startdate'];
echo $POST['dates'][0]['enddate'];

bunner_bob

Wow – I see how much I don’t know about programming with PHP & MySQL. Unless you’re into doing some serious hand-holding, I think I’d better kluge my way around it until I have more experience with the whole thang.

For now I’ve set it up so I enter the event info, then after it’s entered, I do the date info. The number of users actually entering events will be few enough that they can work with this, until I’ve read a few more chapters in my book/online/etc. I don’t know anything yet about how I’d combine actions into a ‘transaction’, and I don’t know anything about $_POST. You’ve been so helpful I don’t want to ask for more until I’ve exhausted some non-human resources.

[deleted]

Feel free to kludge to your hearts content, but keep in mind that if you write a lot of code now, without paying too much attention to 'proper procedures', you're going to have to re-write the whole thing later.

So experimentation is ofcourse ok, but when something seems to work, start figuring out wether you can improve the code a little, make it a little more 'professional' before you start using it all over.

If you're worried about two people editing the same event, you should consider adding a 'locking' mechanism. Every time a person selects an event to edit, you mark it as 'locked'.

When someone else tries to edit the same record, you can see that it is locked by someone else, and you can give a warning. THen when the person who first locked the record submits his changes, you can un-lock the record again.

This also means that people can only submit changes to a record if they first locked it. That rules out any accedental updates by people submitting pages that were displayed from their browser's cache.

bunner_bob

Wiser now...

I worked out a method of getting the eventid after I create it. If I'm creating a new event (as opposed to editing an existing one), after I post it I use mysql_insert_id to get the last id that was auto-generated, then pass that along to the "add dates" form. Seems to work well.

So it's still two transactions. I am actually allowing users to create events without dates, though they appear listed in a separate table with a warning that "these events don't have dates, so they won't appear on the site". This way someone can set up all the info for an event and then come back later and add dates, times, whatever.

I like what you suggest about locking an event while it's being edited. Would I do my query:

$sql = "SELECT * FROM cpevents WHERE eventid=$eventid";

And then immediately write

$sql = "UPDATE cpevents SET locked='yes' WHERE eventid=$eventid";

? Or is there another way you'd approach it?

[deleted]

Locking means making sure nobody else can get at it.

So you have to find a way of making sure nobody can read the data if it is locked, and that nobody can lock the data while you are busy trying to get a lock.

Remember that it is a multi-user envoriment, queries will be fires at the strangest times, including that millisecond between your select and your update. That way you could get a SELECT telling you that the record is not locked, then the other person locks the record, then you try to lock it, and the lock fails.

You can avoid this by just blindly try to lock the data first, and then see wether it worked:

UPDATE table SET locked=1, lockedby=userid
WHERE id=foo
AND locked=0;

This will try to lock the record for you if it is not locked already.
The clever bit is that you can use mysql_affected_rows() to find out how many rows were updated by this query. If the number is zero, then the query did not update any records, which means that the record was already locked (locked was 1, which is not zero, which means the WHERE clause could not find a match)
If the number is one, then the record was updated and is now locked for you.

All other queries that fetch data from the records to print them for editing must now check that they are selecting from a record that is locked by the same user who is trying to edit them:

SELECT *
FROM table
WHERE id=foo
AND userid = your_id
AND locked=1;

If the record is not locked, or locked by someone else, they query will return nothing.

bunner_bob

Ahh, this is finally making sense to me. I set up locking but I haven't got the userid part in there yet. In fact I'm not even sure how to capture userid. The admin page I've created has the usual host="whatever", user="admin", password="whatever" stuff at the top - so anyone accessing the admin page comes in as "admin". I have a vague idea of how I'd do this - login screen that checks the user against a table of users/passwords, then drops that user into a slot somewhere...

Fortunately this system wil probably be only administered by one or two people in the same physical office, so chances of a collision are rare. But I can see how it might be necessary. One for the next set of lessons I suppose...

[deleted]

There's a plethora (I love that word) of examples of authorization systems out there, just search for 'sessions logon' or something like that.

Good luck!