filling a date field in a database using drop down list

mrw2020

Hi everyone

I'm trying to fill a DATE field in a database table using a drop down list in an html form, this is the code I have so far...


// Set the page title and include the html header
$page_title="Add New Clients";
include ('header.html');

// Include config file...
require_once('_inc/config.inc.php');

// If no first name session variable exists, redirect the user...
if (!isset($_SESSION['fname'])) {

// Start defining the URL.
		$url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);
		// Check for a trailing slash.
		if ((substr($url, -1) == '/') OR (substr($url, -1) == '\\') ) {
			$url = substr ($url, 0, -1); // Chop off the slash.
		}

		// Add the page.
		$url .= '/index.php';
		ob_end_clean(); // Delete the buffer.
		header("Location: $url");
		exit(); // Quit the script.

} else {

require_once ('_inc/ecg_con.php'); // Connect to the database.

echo 'This is the \'Add Clients Page\', you can add new clients here.';

if (isset($_POST['submitted'])) { // Handle the form.

// Set form variables
$fn = escape_data($_POST['fname']);
$ln = escape_data($_POST['lname']);
$ad = escape_data($_POST['ad']);
$ad2 = escape_data($_POST['ad2']);
$town = escape_data($_POST['town']);
$cty = escape_data($_POST['county']);
$pc = escape_data($_POST['pcode']);
$tel = escape_data($_POST['tel']);
$e = escape_data($_POST['email']);
$cmpy = escape_data($_POST['company']);
$enrolled = date('Y-M-d', strtotime($_POST['month'] . "-" . $_POST['day'] . "-" . $_POST['year']));
$course = escape_data($_POST['course']);
$courseweb = escape_data($_POST['coursew']);
$cost = escape_data($_POST['cost']);

$errors = array(); // Initialise the errors array


// Check for a first name.
if (empty($_POST['fname'])) {
	$errors[] = 'You forgot to enter the clients first name.';
} else {
	$fn = escape_data($_POST['fname']);
}

// Check for a last name.
if (empty($_POST['lname'])) {
	$errors[] = 'You forgot to enter the clients last name.';
} else {
	$ln = escape_data($_POST['lname']);
}

// Check for an address.
if (empty($_POST['ad'])) {
	$errors[] = 'Please enter at least the first line of the clients address.';
} else {
	$ad = escape_data($_POST['ad']);
}

// Check for an town.
if (empty($_POST['town'])) {
	$errors[] = 'Please enter the clients town of residence.';
} else {
	$town = escape_data($_POST['town']);
}

// Check for a post code.
if (empty($_POST['pcode'])) {
    $errors[] = 'Please enter the clients post code.';
} else {
    $pc = escape_data($_POST['pcode']);
}

// Check for an email address and that it's in the correct format.
if (preg_match ('/^[[:alnum:]][a-z0-9_\.\-]*@[a-z0-9\.\-]+\.[a-z]{2,4}$/', stripslashes(trim($_POST['email'])))) {
	$e = escape_data($_POST['email']);
} else {
	$errors[] = 'Please enter a valid email address.';
}

// Check for a cost.
if (empty($_POST['cost'])) {
    $errors[] = 'Please enter the cost of the course.';
} else {
    $cost = escape_data($_POST['cost']);
}

// Check for a day.
if (isset ($_POST['day']) && ($_POST['day'] != 'Day')) {
	$enrolled = ($_POST['day']);
} else {
	$errors[] = 'Please enter the day of the course.';

}

// Check for a month.
if (isset ($_POST['month']) && ($_POST['month'] != 'Month')) {
	$enrolled = ($_POST['month']);
 } else {
	$errors[] = 'Please enter the month of the course.';
}

// Check for a month.
if (isset ($_POST['year']) && ($_POST['year'] != 'year')) {
	$enrolled = ($_POST['year']);
 } else {
	$errors[] = 'Please enter the year of the course.';
}

// Check for a course name.
if (empty($_POST['course'])) {
    $errors[] = 'Please enter the name of the course.';
} else {
    $course = escape_data($_POST['course']);
}


if (empty($errors)) { // If everything's OK.

// Make sure the client hasnn't already been registered.
    $query = "SELECT client_id FROM clients WHERE email='$e'";        
    $result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

    if (mysql_num_rows($result) == 0) { // Available.

// Add the client.
		$query = "INSERT INTO clients (course, courseweb, fname, lname, ad, ad2, town, county, pcode, telephone, email, enrolled, company, cost) VALUES ('$course', '$courseweb', '$fn', '$ln', '$ad', '$ad2', '$town', '$cty', '$pc', '$tel', '$e', '$enrolled', '$cmpy', '$cost' )";		
		$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

		if (mysql_affected_rows() == 1) { // If it ran OK.				
// Finish the page.
			echo "<h3>Thank you for using this system.</h3><p><strong>You have added:</strong></p>$fn $ln<br /><strong>with the following address:</strong><br />$ad, $ad2<br /><strong>Town:</strong>$town<br /><strong>County:</strong> $cty<br /><strong>Postcode:</strong> $pc<br /><strong>Telephone:</strong> $tel<br /><strong>Email:</strong> $e<br /><strong>Date course taken:</strong> $enrolled, <strong>Company:</strong> $cmpy, <strong>Cost:</strong> $cost<br /><p><a href='add_client.php'>Add Another client</a> or return to <a href='index.php'>Admin Page</a></p><br><br>";
			include ('footer.html'); // Include the HTML footer.
			exit();

        } else { // If it did not run OK.
            echo '<p id="errors">Could not register this client due to a system error. We apologise for any inconvenience.</p>'; 
        }        			

		} else { // Already registered.
            echo '<h1>Already registered</h1>
            <p id="errors">The client email address is already registered</p> 
            <a href="add_client.php">Add Another client</a> or return to <a href="index.php">Admin Page</a><br><br>';

            include ('footer.html'); 
            exit();
        }		


} else { // Report the errors.    
    echo '<h2>Error!</h2>
    <p id="errors">The following error(s) occurred:<br />';
    foreach ($errors as $msg) { // Print each error.
        echo " - $msg<br />\n";
    }
    echo '</p><p>Please try again.</p>';
    }
    mysql_close(); // Close the database connection.

} // End of the main Submit conditional.
}

The above code does the usual error checking and contains the SELECT statement.

Here is the drop down list...


*Date Course Taken:</strong><br />
      <label>
        <select name="day" id="day">
        <OPTION VALUE="Day">Day</OPTION>
      <OPTION VALUE=01>01</OPTION>
      <OPTION VALUE=02>02</OPTION>
      <OPTION VALUE=03>03</OPTION>
      <OPTION VALUE=04>04</OPTION>
      <OPTION VALUE=05>05</OPTION>
      <OPTION VALUE=06>06</OPTION>
      <OPTION VALUE=07>07</OPTION>
      <OPTION VALUE=08>08</OPTION>
      <OPTION VALUE=09>09</OPTION>
      <OPTION VALUE=10>10</OPTION>
      <OPTION VALUE=11>11</OPTION>
      <OPTION VALUE=12>12</OPTION>
      <OPTION VALUE=13>13</OPTION>
      <OPTION VALUE=14>14</OPTION>
      <OPTION VALUE=15>15</OPTION>
      <OPTION VALUE=16>16</OPTION>
      <OPTION VALUE=17>17</OPTION>
      <OPTION VALUE=18>18</OPTION>
      <OPTION VALUE=19>19</OPTION>
      <OPTION VALUE=20>20</OPTION>
      <OPTION VALUE=21>21</OPTION>
      <OPTION VALUE=22>22</OPTION>
      <OPTION VALUE=23>23</OPTION>
      <OPTION VALUE=24>24</OPTION>
      <OPTION VALUE=25>25</OPTION>
      <OPTION VALUE=26>26</OPTION>
      <OPTION VALUE=27>27</OPTION>
      <OPTION VALUE=28>28</OPTION>
      <OPTION VALUE=29>29</OPTION>
      <OPTION VALUE=30>30</OPTION>
      <OPTION VALUE=31>31</OPTION>
          </select>
      </label>
      <select name="month"> 
      <OPTION VALUE="Month">Month</OPTION>
      <OPTION VALUE="january">January</OPTION>
      <OPTION VALUE="february">February</OPTION>
      <OPTION VALUE="march">March</OPTION>
      <OPTION VALUE="april">April</OPTION> 
      <OPTION VALUE="may">May</OPTION>
      <OPTION VALUE="june">June</OPTION>
      <OPTION VALUE="july">July</OPTION>
      <OPTION VALUE="august">August</OPTION>
      <OPTION VALUE="september">September</OPTION>
      <OPTION VALUE="october">October</OPTION> 
      <OPTION VALUE="november">November</OPTION> 
      <OPTION VALUE="december">December</OPTION>
    </select>
    <select name="year">
      <OPTION>Year</OPTION>
      <OPTION VALUE=2005>2005</OPTION>
      <OPTION VALUE=2006>2006</OPTION>
      <OPTION VALUE=2007>2007</OPTION>
      <OPTION VALUE=2008>2008</OPTION>
      <OPTION VALUE=2009>2009</OPTION>
      <OPTION VALUE=2010>2010</OPTION>
      <OPTION VALUE=2011>2011</OPTION>
      <OPTION VALUE=2012>2012</OPTION>
      <OPTION VALUE=2013>2013</OPTION>
      <OPTION VALUE=2014>2014</OPTION>
      <OPTION VALUE=2015>2015</OPTION>
      <OPTION VALUE=2016>2016</OPTION>
      <OPTION VALUE=2017>2017</OPTION>
      <OPTION VALUE=2018>2018</OPTION>
      <OPTION VALUE=2019>2019</OPTION>
      <OPTION VALUE=2020>2020</OPTION>
     </select></td>
  </tr>
  <tr>  	
    <td colspan="2"><strong>Fields marked with an asterisk* are required.</strong></td>
  </tr>
  <tr>
  	<td colspan="2"><input type="submit" name="submit" value="Add Client" />
	<input type="hidden" name="submitted" value="TRUE" /></td>
  </tr>
</table>

</form>

Unfortunately it doesn't work and doesn't fill the field in the database, can anyone see where I might be going wrong. Any help would be greatly appreciated - thanks:

Derokorian

Forgive me if I'm wrong, but I think in HTML all values need to be quoted, whether string or numeric.

Also note if all 3 are set, you will only end up with year in your $enrolled variable because you overwrite it each time. Instead either check for all 3 at once and create the formatted date string then (which would be easier if your default values were set to the empty string '') or put them each in their own variable and then concatenate those together to make your enrolled var. Here is the first way:

if( !empty($_POST['day']) && !empty($_POST['month'] && !empty($_POST['year']) ) {
   $enrolled = sprintf('%d-%02d-%02d',$_POST['year'],$_POST['month'],$_POST['day']);
} else {
   $errors[] = 'You must select a date for the course.';
}

mrw2020

Thank you very much for your reply, it displays the year and day fine; however it displays the month as '00' - any ideas why that is?

mrw2020

with your help I've solved it and it works a treat, I had the months in the select menu as strings - I've changed it now to numbers

mrw2020

As a follow on from my previous thread I'm trying to get the 'edit client' page to accept this date format: 19/10/2010 - d/m/Y, the code I've put together so far doesn't work, could someone point me in the right direction I'm not that great at sql statements, here is the code...


$query = "UPDATE clients SET course='$course', courseweb='$courseweb',fname='$fn', lname='$ln', ad='$ad', ad2='$ad2', town='$town', county='$cty', pcode='$pc', telephone='$tel', email='$e', cost='$cost', enrolled='$enrolled', DATE_FORMAT(enrolled, '%d/%m/%Y') AS enrolled WHERE client_id=$id";
            $result = @mysql_query ($query); // Run the query.
            if (mysql_affected_rows() == 1) { // If it ran OK.

bradgrafelman

You're going to have to convert the string into the proper YYYY-MM-DD format before you try to store it in the 'enrolled' column.

One way to do that would be using PHP (e.g. by using [man]explode/man or [man]strftime/man + [man]date()), or by doing the conversion in the SQL query itself. To do that, you wouldn't want to store the value directly in the column; instead, you'd set the column value equal to the result of a MySQL function call to STR_TO_DATE().

mrw2020

Unfortunately I think the php functions are a bit beyond my ability, I don't really understand explode() or strftime() + [man]date()). Could you point me in the direction of the MySQL function STR_TO_DATE or is there a tutorial I could look at - I've looked through the MySQL documentation but haven't really been able to make sense of it. Any help on this would be greatly appreciated.

Weedpacket

mrw2020 wrote:
Could you point me in the direction of the MySQL function STR_TO_DATE

http://dev.mysql.com/doc/refman/

And you should be able to figure your way from there.

mrw2020

Weedpacket;10998311 wrote:
.

http://dev.mysql.com/doc/refman/

And you should be able to figure your way from there.

Thank you for the link, however it just takes me to the MySQL docs download page - thanks anyway

bradgrafelman

mrw2020;10998312 wrote:
it just takes me to the MySQL docs download page

I believe that was the desired effect, since the answer lies within that documentation. 😉

Here's a more direct link (found by simply Google'ing "mysql str_to_date"): STR_TO_DATE().

The function's purpose and usage are fairly self-explanatory after reading that section in the manual, but in a nutshell you simply need to provide the date string (e.g. '19/10/2010') and the format string which describes the format of that date string. It returns a date properly formatted as 'YYYY-MM-DD', so you'd simply want to SET the date column equal to the value returned by that function.

mrw2020

Okay, I've been scouring the mysql manual - still can't make head nor tale of STR_TO_DATE. I've also looked at the php equivalent and I've come up with this...


// I set the form variables
$fn = escape_data($_POST['fname']);
$ln = escape_data($_POST['lname']);
$ad = escape_data($_POST['ad']);
$ad2 = escape_data($_POST['ad2']);
$town = escape_data($_POST['town']);
$cty = escape_data($_POST['county']);
$pc = escape_data($_POST['pcode']);
$tel = escape_data($_POST['tel']);
$e = escape_data($_POST['email']);
$cmpy = escape_data($_POST['company']);
$enrolled = date('Y-m-d', strtotime($_POST['enrolled'])); /* here is the $enrolled variable using 'date' and 'strtotime' functions */
$course = escape_data($_POST['course']);
$cost = escape_data($_POST['cost']);

Then I use the following mysql statement to UPDATE the table


if (empty($errors)) { // If everything's OK.

		$query = "UPDATE clients SET course='$course', fname='$fn', lname='$ln', ad='$ad', ad2='$ad2', town='$town', county='$cty', pcode='$pc', telephone='$tel', email='$e', cost='$cost', enrolled='$enrolled' WHERE client_id=$id";
        $result = @mysql_query ($query); // Run the query.
        if (mysql_affected_rows() == 1) { // If it ran OK.

I also display the information from the table in the form to allow for editing...


// Retrieve the clients information.
$query = "SELECT course, fname, lname, ad, ad2, town, county, pcode, telephone, email,  company, cost, DATE_FORMAT(enrolled, '%d/%m/%Y') AS enrolled FROM clients WHERE client_id=$id";		
$result = @mysql_query ($query); // Run the query.

So I call the $enrolled variable in the UPDATE statement but this doesn't work either - can anyone tell me why? I really could do with some help here, any advice would be greatly appreciated