Give User Custom Message Based on Database Fields

Question

This is a registration form for students to sign up for a time to attend an orientation session. I have already made the email address field a UNIQUE field in the database.

What I need to do is if the email address exists, that means the student has already signed up for a day and time to go to orientation. So instead of returning the error message "That email address already exists", I want to return a message that says: "You have already signed up for an orientation day/time. You signed up for at . If you wish to change your day/time, please cancel the first day/time and then come back to sign up for your new time."

So I need to know how to search the database to find the day and time they signed up for and return that in the customized message that is returned when a user tries to sign up more than once.

<?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.<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>';
        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 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" && $error == "") {
            echo display_text("Scheduling Text", $student_id);
            ?><p><a href="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="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="schedule">
            <input type="hidden" name="timestamp" value="<?=$timestamp?>">
            <input type="hidden" name="time_id" value="<?=$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="../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="Submit">
                </li>
            </ul>
            </fieldset>
            </form>
            <?php
        }
        break;
}

// the bottom layout
layout_bottom();
?>

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — tereško
– tereško, Commented Sep 28, 2012 at 1:56

Darian Brown · Accepted Answer · 2012-09-28 01:56:14Z

0

The way I would look at doing this is to first verify their email address is valid, then see if it exists and then create the registration entry if not.

if (!valid_email($email)) {
    // show message
    return FALSE;
}

$query = "SELECT * FROM database WHERE email = '$email';";
$result = mysql_query($query);
if (mysql_num_rows($result) > 0) {
     // email already exists, so tell them to deregister first
     return FALSE;
}

$query = "INSERT registration query....";

Also make sure you escape the email address before using it in your queries.

answered Sep 28, 2012 at 1:56

Darian Brown

1681 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HoppedUpDesigns Over a year ago

Thanks, that helps. I have already made the email field unique so there is no way they can use the same email address twice (you are right, I do need to have a little more validation for the email field). So if the database finds the email address, I need to return the day/time that they have already registered for, then tell them to cancel and try again.

Collectives™ on Stack Overflow

Give User Custom Message Based on Database Fields

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related