PHP ?

[eluser]RobertB.[/eluser]
Hello again guys,
This time is a PHP question.

I'm inserting this string in a database field (07:00AM - 05:00PM)this is the way that I would like it to display. I using a form that uses two text fields for the time part and two dropdowns for the AM, PM part. My problem is not inserting this data but updating it.

If I try explode('-', $time) I get

Code:

Array ( [0] => 7:00AM [1] => 5:00PM )

because I think you can only use one delimiter and also I do not have a space in between the time and the AM and PM part.
I would like to get

Code:

Array ( [0] => 7:00 [1] => AM [2] => 5:00 [3] => PM )

I've being trying preg_split() to split it with reg-exp but can't make it work so here I'm again.

Thanks you all.

[eluser]mddd[/eluser]
First question: why are you storing times as text in your database? It would be easier to just use a time field for start and end. Then you can search the database much easier too.

But that aside; you could use preg_match (IF you are sure the times are in the correct format) with something like this:

Code:

$time = '07:00AM - 05:00PM';
$regex = '/([\d:]+)([A-Za-z]+).*([\d:]+)([A-Za-z]+)/';
preg_match($regex, $time, $matches);
// $matches will be array(0=>'07:00AM - 05:00PM', 1=>'07:00', 2=>'AM', 3=>'05:00', 4=>'PM');

Note: I haven't tested the code so there may be a typo in there but this is the idea.

[eluser]RobertB.[/eluser]
Hello mddd,
Thanks, I could not come up with any other logical way of doing it.

If you had a table with monday_schedule, tuesday_schedule, wednesday_schedule, and so on how you thing I should do it? if I store it with time I would need to columns for each day, won't you think so?

Thanks again,
Robert

[eluser]mddd[/eluser]
Robert,

Everybody has their own ways of doing things and that's okay I like to use the data types in myqsl as much as I can. So if I store a time, I would use a datetime field or just a time field.

My guess is you are storing something like opening hours for a company? I would make a table something like

Code:

id         (int)   database id you can use for writing, deleting etc
company_id (int)   the company id
day_id     (int)   the number of the day
opening    (time)  the time of opening
closing    (time)  the time of closing

Now if you need the times for the whole week for company number 1 you would say

Code:

SELECT * FROM opening_hours WHERE company_id=1

Or if you only want the hours for wednesday you could say

Code:

SELECT * FROM opening_hours WHERE company_id=1 AND day_id=3

Or if you want to know which companies are still open at 6PM on a friday

Code:

SELECT company_id FROM opening_hours WHERE day_id=5 AND opening<'18:00:00' AND closing>'18:00:00'

See.. very flexible.

Oh, one more reason why it is good to use these field types: you can easily get things formatted the way you want, using the functions in MySql. No need to write any php code to figure out the parts and so on.
Eg, if you want your opening time to look like '7:00AM', use TIME_FORMAT(opening, '%k %p').
If you want it to look like '07:00:00', use TIME_FORMAT(opening, '%H:%i:%s').
Easy.

[eluser]RobertB.[/eluser]
Hello mddd,

I doing the time as per your recommendation.
I'm using active records but is treating %h %p as another column do you have any idea how can I configure this query?

Code:

this->bd->select('TIME_FORMAT(opening, %h %p) as open');

Thanks again,
Robert

[eluser]theprodigy[/eluser]
try passing in FALSE as the second parameter to your select:

Code:

this->db->select('TIME_FORMAT(opening, %h %p) as open', FALSE);

[eluser]RobertB.[/eluser]
theprodigy, sorry does not work.

ERROR

Code:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%h %p) as opening FROM (dr_weekdays wd) LEFT JOIN dr_opening_hours oh ON wd.id =' at line 1

[eluser]theprodigy[/eluser]
Try:

Code:

this->db->select("TIME_FORMAT(opening, '%h %p') as open", FALSE);

(put the %h %p into single quotes)

[eluser]RobertB.[/eluser]
Thanks a lot, that did it.