Search DB Text Field for Month, Day, Year

[tha_ripmasta]

Hello I search the forums, but can't find what i'm looking for.

I want to search by month, day or year.

I have a database already filled with information, my date field in my table is a normal text field, the date is entered like: 08/23/2006 (Month/Day/Year).

I would like to search on the month which would be (08).
How would I accomplish that.

Here is my code:

Form.

<table width="545"  border="0" cellspacing="3" cellpadding="5" bordercolor="#CCCCCC">
				<form method="post" name="formquerydob" action="<?php echo '?id=searchdb&&p=students_search_dob' ?>">
				  <tr>
					<td align="left">
					   <p><span class="headertext">Search by date of birth</span></p>
					   <span class="small-txt-1">Search Area:</span><br />
					   <select name="search_area" style="width: 50%" class="input">
						<option value="dob_month">Month</option>
						<option value="dob_year">Year</option>
						<option value="dob_date">Day</option>
						</select>
					 </td>
				  </tr>
				  <tr>
					<td align="left">
					   <span class="small-txt-1">Search String:</span><br />
					   <input class="input" type="text" name="search_string" style="width: 50%">
					</td>
				  </tr>
				  <tr>
				    <td align="left">
				       <input name="btnSubmitsearch" type="submit" value="Search" class="btn">
				    </td>
				 </tr>
				 </form>

Php Code I used for another simple search:

$ses_organization= $_SESSION['organization_id'];

			$Limit = 20; //Number of results per page

			//$changedate_dob="$student_dob";
			//list($day_dob,$month_dob,$year_dob) = split('[-./]', $changedate_dob); 
			//$timestamp_dob=$year_dob."/".$day_dob."/".$month_dob;


			$SearchString=$_POST["search_string"]; // Get the search tearm
			If($SearchString == "") $SearchString=$_GET["SearchString"]; // Get the search tearm 

			$SearchType = $_POST['search_area'];
			If($SearchType == "") $SearchType=$_GET["SearchType"]; // Get the search type 


			$page=$_GET["page"]; //Get the page number to show
			If($page == "") $page=1; //If no page number is set, the default page is 1 

			//Get the number of results
			$SearchResult=mysql_query("SELECT * FROM students WHERE organization_id='$ses_organization' AND status='1' AND $SearchType='$SearchString' ORDER BY student_last_name") or die(mysql_error());
			$NumberOfResults=mysql_num_rows($SearchResult); 

			//Get the number of pages
			$NumberOfPages=ceil($NumberOfResults/$Limit); 

			$SearchResult=mysql_query("SELECT * FROM students WHERE organization_id='$ses_organization' AND status='1' AND $SearchType='$SearchString' ORDER BY student_last_name LIMIT " . ($page-1)*$Limit . ",$Limit") or die(mysql_error()); 
			//$stotalresults = mysql_num_rows($SearchResult);

How can I make this code work to search for month, day or year

[NogDog]

You could add this to your WHERE clause:

AND SUBSTRING_INDEX(date_field, '/', 1) = 8

[Weedpacket]

Of course, this wouldn't have been an issue if you'd used a date field to store your date. That's what they're for. Then all the database server's date and time functionality (like, extracting the day, month, or year, or the day of the week, or the week of the year, or the day of the year.....) would be available.

[tha_ripmasta]

Weedpacket wrote:

Of course, this wouldn't have been an issue if you'd used a date field to store your date. That's what they're for. Then all the database server's date and time functionality (like, extracting the day, month, or year, or the day of the week, or the week of the year, or the day of the year.....) would be available.

Thanks for replying NogDog and Weedpacket. I trried your suggestion NogDog and that won't workout for me. I will change my table field into a date field like Weedpacket suggested.

Now how would I get the month, day or year using the database server's date and time functionality???

Thanks in advance for any help

[Installer]

MySQL Date and Time Functions

For instance, to select according to the month in a date, datetime, or timestamp column:

WHERE MONTH(date_field) = '11'

[tha_ripmasta]

Thanks, I will try it tonight