PHP/MySQL convert timestamp with a data type of text to usuable date format

[katiesgq]

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, JasonPHP Code\[code\]<?php// set the modeif(isset($_GET['p'])) $mode = $_GET['p'];else if(isset($_POST['p'])) $mode = $_POST['p'];else $mode = '';// sanitize inputif(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.<br/>';if(($time_result = valid_time_id($time_id)) == false) $error .= 'Invalid time id.<br/>';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.<br>'; if(empty($last_name)) $error .= 'You must enter a last name.<br>'; if(!valid_email($email)) $error .= 'Invalid email.<br>'; if($email != $retype_email) $error .= 'The two email addresses don\'t match.<br>'; // 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) > 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, ' .'<a href="http://stackoverflow.com/questions/12754866/schedule.php?date='.$student_row['date'].'&time_id='.$student_row['time_id'] .'">click here</a> to cancel the time you are scheduled for first.<br>'; $error =. $schedule_error; } if(!valid_college_id($college_id)) $error .= 'Invalid student id. Student id must contain seven digits including zeros.<br>'; if($college_id != $retype_college_id) $error .= 'The two student ids don\'t match.<br>'; if(empty($phone)) $error .= 'You must enter a phone number.<br>'; $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) >= $time_row['slots']) $error .= 'Sorry, too many people are already scheduled for this time slot.<br>'; if($_SESSION['captcha'] != $verification) $error .= 'Invalid image verification.<br>'; // 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 verificationsrand(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 layoutlayout_top(date("F j, Y", $timestamp).', '.$time_row['time']);// the middle layoutswitch($mode) { default: if($mode == "schedule" && $error == "") { echo display_text("Scheduling Text", $student_id); ?><p><a href="http://stackoverflow.com/questions/12754866/index.php">Click here to go back</a></p><?php } else { ?> <h1 align="center" style="padding-bottom: 0; margin-bottom: 0;"><?=strtoupper(date("F j, Y", $timestamp).' '.$time_row['time'])?></h1> <p align="center" style="padding-top: 0; margin-top: 0;"><strong><a href="http://stackoverflow.com/questions/12754866/index.php?month=<?=date("n", $timestamp)?>&year=<?=date("Y", $timestamp)?>">choose another date</a></strong></p> <?php if($mode == "schedule" && $error != '') { ?> <p class="error"><?=$error?></p> <?php } ?> <form action="<?=$_SERVER['PHP_SELF']?>" method="post"> <input type="hidden" name="p" value="http://stackoverflow.com/questions/12754866/schedule"> <input type="hidden" name="timestamp" value="http://stackoverflow.com/questions/12754866/<?=$timestamp?>"> <input type="hidden" name="time_id" value="http://stackoverflow.com/questions/12754866/<?=$time_id?>"> <fieldset> <legend>Schedule an appointment for this date</legend> <p>Fill out this form to schedule a New Student appointment on this date. Make sure you use a valid email address.</p> <ul> <li> <label for="first_name">First Name</label> <input type="text" name="first_name"<?=($mode == "schedule" ? ' value="'.$first_name.'"' : '')?>> </li> <li> <label for="last_name">Last Name</label> <input type="text" name="last_name"<?=($mode == "schedule" ? ' value="'.$last_name.'"' : '')?>> </li> <li> <label for="email">Email</label> <input type="text" name="email" size="30"<?=($mode == "schedule" ? ' value="'.$email.'"' : '')?>> </li> <li> <label for="retype_email">Retype Email</label> <input type="text" name="retype_email" size="30"<?=($mode == "schedule" ? ' value="'.$retype_email.'"' : '')?>> </li> <li> <label for="college_id">Student ID(For your student ID#, please refer to the e-mail you received regarding your A-B Tech WebAdvisor and Email Accounts.)</label> <input type="text" name="college_id" size="30"<?=($mode == "schedule" ? ' value="'.$college_id.'"' : '')?>> </li> <li> <label for="retype_college_id">Retype Student ID</label> <input type="text" name="retype_college_id" size="30"<?=($mode == "schedule" ? ' value="'.$retype_college_id.'"' : '')?>> </li> <li> <label for="phone">Phone</label> <input type="text" name="phone"<?=($mode == "schedule" ? ' value="'.$phone.'"' : '')?>> </li> <li> <label for="verification">Verification</label> <img src="http://stackoverflow.com/questions/images/verify.php" width="180" height="40" alt="Verification"><br/> <input type="text" name="verification" size="10"> <small>&laquo; type the characters in the image above into this box</small> </li> <li> <input type="submit" value="http://stackoverflow.com/questions/12754866/Submit"> </li> </ul> </fieldset> </form> <?php } break;}// the bottom layoutlayout_bottom();?>\[/code\]