Hello, in the code below, how would I convert 24 hour time to 12 hour time and put date format as 8/27/13?
Also how in the select statement could I add where wp_events_start_end.end_time is greater than 6pm?

Result: 2013-08-27 End Time: 19:30

<?php
$username = "";
$password = "";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) 
  or die("Unable to connect to MySQL");
echo "<br>";
?>

<?php
//select a database to work with
$selected = mysql_select_db("stormwi1_wrd1",$dbhandle) 
  or die("Could not select stormwi1_wrd1");
?>

<?php
//execute the SQL query and return records
$result = mysql_query("SELECT wp_events_detail.id, 
wp_events_detail.event_name,
wp_events_detail.start_date,
wp_events_start_end.end_time
FROM wp_events_detail
join wp_events_start_end on wp_events_detail.id = wp_events_start_end.event_id
WHERE wp_events_detail.start_date  = curdate() - INTERVAL DAYOFWEEK(curdate())-3 DAY
order by wp_events_detail.start_date");
//fetch tha data from the database
while ($row = mysql_fetch_array($result)) {
    echo 
	"".$row{''}." 
   ".$row{'start_date'}.
   " End Time: ".$row{'end_time'}."<br>";
}
?>


<?php
//close the connection
mysql_close($dbhandle);
?>

    What type of column is 'end_time' ? It would appear that it isn't a TIME column (despite the fact that it's meant to store a.. time...), so I'll assume it's a VARCHAR or similar.

    In that case, you'll have to parse the string manually into it's components (i.e. [man]explode/man on the ':' separator). Then you can check if the 'hour' portion is greater than 12. If so, substract 12 and append "PM"; otherwise, append "AM".

    EDIT: Just re-read your original post and noticed the part about the time comparison. At this point, I would recommend considering the altering of the DB schema to utilize the TIME column type. This would allow you to use functions like TIME_FORMAT() as well as making comparisons.

      The alternative (which might have merit, but not any more than Brad's idea and potentially less), is to use an integer type on the database and store everything in Unix Time, which can be fed to PHP's date and time functions in order to alter formatting as desired...

        @: You're really tempting me to pull up one of my prior rants/soapboxes on why the Unix timestamp should stay in Unix and not anywhere near a database column. Suffice it to say my views on Unix timestamps are that they are great ways to obfuscate a piece of data that could have otherwise been very useful to humans. (Quick, tell me the date and time that correlates to: 1376562273!)

          Thanks Brad, yes end_time is a VARCHAR, I'm pulling info from a third-party vendor software, Event Expresso, so I cant change the db field.

          Would you mind giving me an example of your suggestion to parse the string, I'm very new to php and mysql syntax?

          Thank you.

            delanopg;11032557 wrote:

            Would you mind giving me an example of your suggestion to parse the string, I'm very new to php and mysql syntax?

            Well SQL syntax is irrelevant (at least, I'd certainly not want to have to do this in the SQL query or even in a stored procedure.)

            For the PHP code, again, all you're really doing is using [man]explode/man to get at the 'hour' component itself followed by an [man]if/man statement to determine whether you need to subtract 12 and add "PM" or simply add "AM". Give it a shot and show us what you come up with if it doesn't work.

              dalecosp;11032561 wrote:

              Thu, 15 Aug 2013 05:24:33 -0500

              Excellent; now, were you able to determine that by mere inspection of the data, or did you have to use another tool to deobfuscate that large integer first? 😉

                What does it matter? The software doesn't care, and I don't want anyone looking in the database itself...I don't trust anyone here that much.

                  Thanks but I have no idea where to start, I read the explode link but still it is unclear to me.

                    dalecosp wrote:

                    What does it matter? The software doesn't care, and I don't want anyone looking in the database itself...I don't trust anyone here that much.

                    You should use PostgreSQL, then; it uses (a slight modification of) Julian Days internally for its date and time types (so it supports instants between 4713BC and 297276AD with 1ms resolution), but hides that from its users. 🙂

                      Weedpacket;11032577 wrote:

                      You should use PostgreSQL, then; it uses (a slight modification of) Julian Days internally for its date and time types (so it supports instants between 4713BC and 297276AD with 1ms resolution), but hides that from its users. 🙂

                      Well, porting those thousands upon thousands of lines of code should provide job security, at least.

                      Assuming, of course, "they" find it necessary to do that just for me and you 😉 (I'm guessing based on what I know from accounting that it's not Too Likely(tm) to happen ...

                        delanopg;11032573 wrote:

                        Thanks but I have no idea where to start, I read the explode link but still it is unclear to me.

                        $string_value = "12:15:31";

$exploded = explode(":",$string_value);

$hour = $exploded[0];

if ($hour >= 12) {
   echo $string_value . " PM";
} else {
   echo $string_value . " AM";
}
                          Write a Reply...