PHP search mysql by date

Question

dschuett -1 Junior Poster

14 Years Ago

Ok, so I will do my best at explaining this...

I have set up a database of our company's Parts Ordered forms.

I have created a php form that allows users to inject new POs into the database. Which works successfully.

I am working on the search form to allow users to search for a certain PO base on one of the following criteria: PO Number, Date, or VIN Number.

I am trying to find the best way to allow users to search by date. Since I used the MYSQL function CURDATE(), it is stored as 2010-10-10. What would be the easiest way to allow users to search by this. I know i could use drop down fields in my form but i don't know how to modify my php code for that to work.... here is what i have so far:

Note that if I choose to search by date that i have to enter it in the exact format...xxxx-xx-xx. - I want to make it easy for the user by having drop down fields to select the year, day, and month...any help is appreciated!

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

 <html>
            <head>
                <title>Search the Database</title>
            </head>
            <body>
            <form method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
             Search: <select name="type">
                     <option value='po_num' selected>PO Number</option>
                     <option value='date'>Date</option>
                     <option value='vin_num'>VIN Number</option>
                     </select>
for: <input type="text" name="term" /><br />
            <input type="submit" name="submit" value="submit" />
            </form>
</body>
</html>
    <?php
        $term = $_POST['term'];
        if ($term == '')
        {
        echo 'Please enter a value.';
        exit;
        }
        $search_result = '';
        if (isset($_POST['submit'])) {
        mysql_connect ("localhost", "user","pass")  or die (mysql_error());
        mysql_select_db ("inventory");
        $type = $_POST['type'];
        $term = $_POST['term'];
        $safetype = mysql_real_escape_string($_POST['type']);
        $safeterm = mysql_real_escape_string($_POST['term']);
        $sql = mysql_query("select * from partsorder where $type like '%$term%'");

        while ($row = mysql_fetch_array($sql)){
                //var_dump($row);
            $search_result .= '<br/> <B>PO Number:</B> '.$row['po_num'] . "\n";
            $search_result .= '<br/><B> Date:</B> '.$row['date'] . "\n";
            $search_result .= '<br/><B> VIN Number:</B> '.$row['vin_num'] . "\n";
            $search_result .= '<br/> <B>Description:</B> '.$row['para'] . "\n";
            $search_result .= '<br/> <B>Purchase Agent:</B> '.$row['purch_agt'] . "\n";
            $search_result .= '<br/><br/>' . "\n";
                }
}
echo $search_result;
        ?>

mysql php seo

Edited 11 Years Ago by mike_2000_17 because: Fixed formatting

6 Contributors
8 Replies
3K Views
1 Month Discussion Span
Latest Post 14 Years Ago Latest Post by richieking

ApocDen 0 Junior Poster

14 Years Ago

on line 35 you can order the mysql query by date which may work. try this

$sql = mysql_query("select * from partsorder where $type like '%$term%' ORDER BY date DESC");

hielo 65 Veteran Poster

14 Years Ago

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<html>
<head>
<title>Search the Database</title>
<style type="text/css">
<!--
.inactive{visibility:hidden;display:none;}
-->
</style>
<script type="text/javascript">
<!--
function toggleOptions(sel)
{
	if(1*sel.value==2)
	{
		document.getElementById('termSearch').className='inactive';
		document.getElementById('dateSearch').className='';
	}
	else
	{
		document.getElementById('dateSearch').className='inactive';
		document.getElementById('termSearch').className='';
	}
}
//-->
</script>

</head>
<body>
	<form method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
	Search: <select name="type" onchange="toggleOptions(this)">
			<option value='1' selected>PO Number</option>
			<option value='2'>Date</option>
			<option value='3'>VIN Number</option>
		</select>
		for: 
			<input type="text" id='termSearch' name="term" />
			<span id="dateSearch" class="inactive">
				<!-- complete the rest of your options for each of the selects below -->
				<select name="Month">
					<option value="January">January</option>
					<option value="February">February</option>
				</select>
				<select name="Day">
					<option value="1">1</option>
					<option value="2">2</value>
				</select>
				<select name="Year">
					<option value="2010">2010</option>
					<option value="2009">2009</option>
				</select>
			</span>

			<br />
<input type="submit" name="submit" value="submit" />
</form>
</body>
</html>
<?php
$sql="";
if( isset($_POST) && !empty($_POST) )
{
	$fields=array('0'=>'UNKNOWN'
				,'1'=>'po_number'
				,'2'=>'date'
				,'3'=>'vin_number'
				);
	mysql_connect ("localhost", "user","pass") or die (mysql_error());
	mysql_select_db ("inventory");

	$type=$fields[ intval($_POST['type']) ];

	if('date'==$type)
	{
		$term = date('Y-m-d',strtotime("{$_POST['Month']} {$_POST['Day']} {$_POST['Year']} "));
		if( empty($term) )
		{
			echo 'Your must provide a date.';
		}
		else
		{
			$sql="SELECT * FROM `partsorder` WHERE `{$type}`='".mysql_real_escape_string($term)."'";
		}
	}
	elseif('UNKNOWN'!=$type)
	{
		$term=$_POST['term'];
		if( empty($term) )
		{
			echo 'Please enter a value.';
		}
		else
		{
			$sql="SELECT * FROM `partsorder` WHERE `{$type}` LIKE '%".mysql_real_escape_string($term)."%'";
		}
	}
	if(!empty($sql))
	{
		$result = mysql_query($sql) or die("Unable to execute<br />$sql<br />".mysql_error());
		if(0==mysql_num_rows($result))
		{
			echo 'No records found.';
		}
		else
		{
			while ( $row = mysql_fetch_array($result) )
			{
				//var_dump($row);
				$search_result .= '<br/> <B>PO Number:</B> '.$row['po_num'] . "\n";
				$search_result .= '<br/><B> Date:</B> '.$row['date'] . "\n";
				$search_result .= '<br/><B> VIN Number:</B> '.$row['vin_num'] . "\n";
				$search_result .= '<br/> <B>Description:</B> '.$row['para'] . "\n";
				$search_result .= '<br/> <B>Purchase Agent:</B> '.$row['purch_agt'] . "\n";
				$search_result .= '<br/><br/>' . "\n";
			}
			echo $search_result;
		}
	}
}

?>

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

dschuett -1 Junior Poster · Answer 1 · 2010-08-11T23:49:13+00:00

I realize i could do that, but I need to be able to search by a specific date.

dschuett -1 Junior Poster · Answer 2 · 2010-08-11T23:51:15+00:00

In other words, I need to be able to search all POs that were created on 2008-10-10. But I don't want my users to have to worry about typing in the correct date format.

pintumanas 0 Newbie Poster · Answer 3 · 2010-09-20T21:29:25+00:00

I am adding one more thing here:
If we have some thousand of records of data in our database. After searching the result it will display all records in one pages.Can we add pagination in the above example thats 10 or 15 nos. of records are display per page…like this

fauzey 0 Newbie Poster · Answer 4 · 2010-10-02T21:44:31+00:00

hi hielo,

Appreciate if you can write script for only searhing by date function.. I try to alter you script but no luck... I have been searching a script like this for 3 day ago...

hielo 65 Veteran Poster · Answer 5 · 2010-10-03T03:09:17+00:00

Appreciate if you can write script for only searhing by date function.

All you need to do is to comment out all the options in your select list EXCEPT the Date option. It must still remain with value="2"

also, line 48:

<option value="2">2</value>

should be:

<option value="2">2</option>

Also, IMMEDIATELY before [B]if(!empty($sql))[/B] (line 199), put: echo $sql; do you see the sql command?

richieking 44 Master Poster · Answer 6 · 2010-10-03T18:28:19+00:00

This is quite simple.

//Function to do date.
 function foo($doo='1900') // default
{
   //run for loop;
$year= date("Y");

for($i=$doo,$<=$year;$i++){

// Now html select
$select ="<select name ='foo'>";
$select.="<option value=$i>$i</option></select>";



} 
return $select;
}

This i hope will pull out the date for your select date needed by your customer.

you receive the variable and run it through your sql database and pull your results. simple.

Explore ;)