title: PHP/MySQL convert timestamp with a data type of text to usuable date format
body: <p>I have a page that allows users to sign up for an orientation date and time.  I have created error checking to check if the email address exists in the database.  If the email address does exist, I give an error message to the student saying:  "You have already registered.  You are registered for ("timestamp from orientation_student table that matches the email they used to signup with").  My problem is this:  the column that contains the timestamp value has a text data time.  I need to grab the timestamp that matches the email address and return in in a format like: "Tuesday 10/02/2012".  The time part works, I just can't get the date part to work.  My PHP code is below.  The relevant code starts around 50 or so lines into the code, below the comment that says "// code added by Jason McCoy on October 5, 2012"  Thanks a lot, Jason</p>
  
  <p>PHP Code</p>
  
  <pre><code>&lt;?php
  // set the mode
  if(isset($_GET['p']))   $mode = $_GET['p'];
  else if(isset($_POST['p'])) $mode = $_POST['p'];
  else $mode = '';
  
  // sanitize input
  if(isset($_GET['time_id'])) {
      $timestamp = (int)$_GET['timestamp'];
      $time_id = (int)$_GET['time_id'];
  }
  if(isset($_POST['time_id'])) {
      $timestamp = (int)$_POST['timestamp'];
      $time_id = (int)$_POST['time_id'];
  }
  
  // validate input
  $error = '';
  if(date("G", $timestamp) != 0)
      $error .= 'Invalid timestamp.&lt;br/&gt;';
  if(($time_result = valid_time_id($time_id)) == false)
      $error .= 'Invalid time id.&lt;br/&gt;';
  else
      $time_row = mysql_fetch_array($time_result);
  
  switch($mode) {
      default:
          break;
  
      case "schedule":
          // sanitize input
          $first_name = sanitize_input($_POST['first_name']);
          $last_name = sanitize_input($_POST['last_name']);
          $email = sanitize_input($_POST['email']);
          $retype_email = sanitize_input($_POST['retype_email']);
          $college_id = sanitize_input($_POST['college_id']);
          $retype_college_id = sanitize_input($_POST['retype_college_id']);
          $phone = sanitize_input($_POST['phone']);
          $first = (isset($_POST['first']) ? 1 : 0);
          $verification = $_POST['verification'];
  
          // validate input
          $error = '';
          if(empty($first_name))
              $error .= 'You must enter a first name.&lt;br&gt;';
          if(empty($last_name))
              $error .= 'You must enter a last name.&lt;br&gt;';
          if(!valid_email($email))
              $error .= 'Invalid email.&lt;br&gt;';
          if($email != $retype_email)
              $error .= 'The two email addresses don\'t match.&lt;br&gt;';
          // code added by Jason McCoy on October 5, 2012
          // code used to check if the email address already exists in the database
          // if email address exists, return an error message to the user
          // **** DISPLAY THE DATE AND TIME THAT THE STUDENT SIGNED UP FOR USING THIS EMAIL ADDRESS ****
          // **** THE STEP ABOVE HAS NOT BEEN COMPLETED AS OF OCT. 5TH, 2012 ****
           $student_result = db_query("select id, timestamp, time_id from orientation_student where email='".$email."'");
        if(mysql_num_rows($student_result) &gt; 0) {
              $student_row = mysql_fetch_array($student_result);
              $date_result = db_query("select timestamp from orientation_student where email='".$email."'");
              $time_result = db_query("select time from orientation_time where id='".$student_row['time_id']."'");
              $time_row = mysql_fetch_array($time_result);
              $schedule_error .= 'You can only schedule an orientation once, and you are already scheduled for '
                  .$formatted_date. ' at '.$time_row['time'].'. If you want to reschedule your test, '
                  .'&lt;a href="schedule.php?date='.$student_row['date'].'&amp;time_id='.$student_row['time_id']
                  .'"&gt;click here&lt;/a&gt; to cancel the time you are scheduled for first.&lt;br&gt;';
              $error =. $schedule_error;
        }
          if(!valid_college_id($college_id))
              $error .= 'Invalid student id. Student id must contain seven digits including zeros.&lt;br&gt;';
          if($college_id != $retype_college_id)
              $error .= 'The two student ids don\'t match.&lt;br&gt;';
          if(empty($phone))
              $error .= 'You must enter a phone number.&lt;br&gt;';
          $student_result = db_query("select id from ".$GLOBALS['db_pre']."student where canceled='0' and timestamp='".$timestamp."' and time_id='".$time_id."'");
          if(mysql_num_rows($student_result) &gt;= $time_row['slots'])
              $error .= 'Sorry, too many people are already scheduled for this time slot.&lt;br&gt;';
          if($_SESSION['captcha'] != $verification)
              $error .= 'Invalid image verification.&lt;br&gt;';
  
          // if there's no error
          if($error == '') {
              // schedule it
              db_query("insert into ".$GLOBALS['db_pre']."student set first_name='".$first_name
                  ."',last_name='".$last_name
                  ."',email='".$email
                  ."',college_id='".$college_id
                  ."',phone='".$phone
                  ."',timestamp='".$timestamp
                  ."',time_id='".$time_id
                  ."',unschedule_code='".md5(time())
                  ."',inserted_at='".gmdate("Y-m-d H:i:s")
                  ."'");
              $student_id = mysql_insert_id();
  
              /* send email to student
              $subject = "A-B Tech New Student Appointment Confirmation";
              if(current_site() == "orientation") $subject = "A-B Tech New Student Orientation";
              else $subject = "A-B Tech Campus Tour";
              $message = format_text("Scheduling Email", $student_id);
              email($email, $subject, $message);
              */
  
              // get the start and end times for the appointment
              $time_result = db_query("select * from ".$GLOBALS['db_pre']."time where id='".$time_id."'");
              $time_row = mysql_fetch_array($time_result);
              //$timestamp_start = strtotime(date("F j, Y", $timestamp).", ".$time_row['time']);
              //$timestamp_end = strtotime("+1 hour", $timestamp_start);
  
              /*// send email, with calendar attachment, to counselors
              if(current_site() == "orientation") $subject = "A-B Tech New Student Orientation: ";
              else $subject = "A-B Tech Campus Tour: ";
              $subject .= date("F j, Y", $timestamp).", ".$time_row['time']."; ".$first_name." ".$last_name."";
              $message = "A student has scheduled an appointment:\r\n\r\n";
              $message .= "Name: ".$first_name." ".$last_name."\r\n";
              $message .= "Date: ".date("F j, Y", $timestamp).", ".$time_row['time']."\r\n";
              $message .= "Email: ".$email."\r\n";
              $message .= "Phone: ".$phone."\r\n";
  
              // send the email to all the counselors
              $user_result = db_query("select * from user where no_email=0");
              while($user_row = mysql_fetch_array($user_result)) {
                  email($user_row['email'], $subject, $message);
              }*/
          }
          break;
  }
  
  // captcha image verification
  srand(time());
  $_SESSION['captcha'] = substr(md5(rand(1,9999)), rand(1,15), 5);
  $_SESSION['captcha'] = str_replace("O", "1", $_SESSION['captcha']); // to avoid confusion
  $_SESSION['captcha'] = str_replace("o", "2", $_SESSION['captcha']); // ...
  $_SESSION['captcha'] = str_replace("0", "3", $_SESSION['captcha']); // ...
  
  // the top layout
  layout_top(date("F j, Y", $timestamp).', '.$time_row['time']);
  
  // the middle layout
  switch($mode) {
      default:
          if($mode == "schedule" &amp;&amp; $error == "") {
              echo display_text("Scheduling Text", $student_id);
              ?&gt;&lt;p&gt;&lt;a href="index.php"&gt;Click here to go back&lt;/a&gt;&lt;/p&gt;&lt;?php
          } else {
              ?&gt;
              &lt;h1 align="center" style="padding-bottom: 0; margin-bottom: 0;"&gt;&lt;?=strtoupper(date("F j, Y", $timestamp).' '.$time_row['time'])?&gt;&lt;/h1&gt;
              &lt;p align="center" style="padding-top: 0; margin-top: 0;"&gt;&lt;strong&gt;&lt;a href="index.php?month=&lt;?=date("n", $timestamp)?&gt;&amp;year=&lt;?=date("Y", $timestamp)?&gt;"&gt;choose another date&lt;/a&gt;&lt;/strong&gt;&lt;/p&gt;
  
              &lt;?php if($mode == "schedule" &amp;&amp; $error != '') { ?&gt;
              &lt;p class="error"&gt;&lt;?=$error?&gt;&lt;/p&gt;
              &lt;?php } ?&gt;
  
              &lt;form action="&lt;?=$_SERVER['PHP_SELF']?&gt;" method="post"&gt;
              &lt;input type="hidden" name="p" value="schedule"&gt;
              &lt;input type="hidden" name="timestamp" value="&lt;?=$timestamp?&gt;"&gt;
              &lt;input type="hidden" name="time_id" value="&lt;?=$time_id?&gt;"&gt;
              &lt;fieldset&gt;
              &lt;legend&gt;Schedule an appointment for this date&lt;/legend&gt;
              &lt;p&gt;Fill out this form to schedule a New Student appointment on this date. Make sure you use a valid email address.&lt;/p&gt;
              &lt;ul&gt;
                  &lt;li&gt;
                      &lt;label for="first_name"&gt;First Name&lt;/label&gt;
                      &lt;input type="text" name="first_name"&lt;?=($mode == "schedule" ? ' value="'.$first_name.'"' : '')?&gt;&gt;
                  &lt;/li&gt;
                  &lt;li&gt;
                      &lt;label for="last_name"&gt;Last Name&lt;/label&gt;
                      &lt;input type="text" name="last_name"&lt;?=($mode == "schedule" ? ' value="'.$last_name.'"' : '')?&gt;&gt;
                  &lt;/li&gt;
                  &lt;li&gt;
                      &lt;label for="email"&gt;Email&lt;/label&gt;
                      &lt;input type="text" name="email" size="30"&lt;?=($mode == "schedule" ? ' value="'.$email.'"' : '')?&gt;&gt;
                  &lt;/li&gt;
                  &lt;li&gt;
                      &lt;label for="retype_email"&gt;Retype Email&lt;/label&gt;
                      &lt;input type="text" name="retype_email" size="30"&lt;?=($mode == "schedule" ? ' value="'.$retype_email.'"' : '')?&gt;&gt;
                  &lt;/li&gt;
                  &lt;li&gt;
                      &lt;label for="college_id"&gt;Student ID(For your student ID#, please refer to the e-mail you received regarding your A-B Tech WebAdvisor and Email Accounts.)&lt;/label&gt;
                      &lt;input type="text" name="college_id" size="30"&lt;?=($mode == "schedule" ? ' value="'.$college_id.'"' : '')?&gt;&gt;
                  &lt;/li&gt;
                  &lt;li&gt;
                      &lt;label for="retype_college_id"&gt;Retype Student ID&lt;/label&gt;
                      &lt;input type="text" name="retype_college_id" size="30"&lt;?=($mode == "schedule" ? ' value="'.$retype_college_id.'"' : '')?&gt;&gt;
                  &lt;/li&gt;
  
                  &lt;li&gt;
                      &lt;label for="phone"&gt;Phone&lt;/label&gt;
                      &lt;input type="text" name="phone"&lt;?=($mode == "schedule" ? ' value="'.$phone.'"' : '')?&gt;&gt;
                  &lt;/li&gt;
                  &lt;li&gt;
                      &lt;label for="verification"&gt;Verification&lt;/label&gt;
                      &lt;img src="../images/verify.php" width="180" height="40" alt="Verification"&gt;&lt;br/&gt;
                      &lt;input type="text" name="verification" size="10"&gt; &lt;small&gt;&amp;laquo; type the characters in the image above into this box&lt;/small&gt;
                  &lt;/li&gt;
                  &lt;li&gt;
                      &lt;input type="submit" value="Submit"&gt;
                  &lt;/li&gt;
              &lt;/ul&gt;
              &lt;/fieldset&gt;
              &lt;/form&gt;
              &lt;?php
          }
          break;
  }
  
  // the bottom layout
  layout_bottom();
  ?&gt;
  </code></pre>