Search by date

osyrys14 · March 27, 2013

I've been working on a search using PHP and MySQL, I now need to take it to the next level, here's the new form, and what we want to do is search the database and compare 2 different date fields, each in the format of YYYY-MM-DD, along with the userid field what would be the best way to accomplish this? I'm not great with SQL, so any assistance would be greatly appreciated.

<form method="get" action="">
<label>Account ID: </label><input type="text" name="query" /></br>
<label>Begin Date:</label>
<label>Day:</label>
<select name="day">
<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">18</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select>
<label>Month:</label>
<select name="month">
<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>
</SELECT>
<label>Year:</label>
<select name="year">
<option value="2011">2011</option>
<option value="2012">2012</option>
<option value="2013">2013</option>
</SELECT></br>
<label>End Date</label>
<label>Day:</label>
<select name="day2">
<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">18</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select>
<label>Month:</label>
<select name="month2">
<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>
</SELECT>
<label>Year:</label>
<select name="year2">
<option value="2011">2011</option>
<option value="2012">2012</option>
<option value="2013">2013</option>
</SELECT></br>


<input type="submit" name="submit" value="Start Search" />
<input type="reset" value="Reset"
</form>

Barand · March 28, 2013

compare them with what?

osyrys14 · March 28, 2013

To each other, start date/end date. I'm trying to set the variable to search like this

$mydate1=mysql_real_escape_string($_GET['year']"-"$_GET['month']"-"$_GET['day']);

and then in the query do this... (or something like it, I keep getting internal server error...

$query_for_result=mysql_query("SELECT * FROM $db_tb_name WHERE

`$db_tb_atr_name` like '%".$query."%'") AND `dbdate1` > $mydate1 AND `dbdate2` < $mydate2;

But I'm not that great with combining the PHP and SQL variables, so it's blowing up the script.

Edited March 28, 2013 by osyrys14

Barand · March 28, 2013

try

$mydate1 = sprintf('%4d-%02d-%02d', 
    intval($_GET['year']), intval($_GET['month']), intval($_GET['day']));
$mydate2 = sprintf('%4d-%02d-%02d', 
    intval($_GET['year2']), intval($_GET['month2']), intval($_GET['day2']));

$sql = "SELECT *
        FROM $db_tb_name
        WHERE `$db_tb_atr_name` LIKE '%$query%'
        AND `dbdate1` > '$mydate1' AND  `dbdate2` < '$mydate2'";
    
$query_for_result=mysql_query($sql);

osyrys14 · March 28, 2013

I really appreciate your time. This is the code now per your reply...

 
mysql_connect("$db_host","$db_username","$db_password");
mysql_select_db("$db_name");
 
$query=mysql_real_escape_string($_GET['query']);
$mydate1 = sprintf('%4d-%02d-%02d', 
    intval($_GET['year']), intval($_GET['month']), intval($_GET['day']));
$mydate2 = sprintf('%4d-%02d-%02d', 
    intval($_GET['year2']), intval($_GET['month2']), intval($_GET['day2']));

 
$query_for_result=mysql_query("SELECT * FROM $db_tb_name WHERE 
 
`$db_tb_atr_name` LIKE '%$query%'
 AND `dbdate1` > '$mydate1' AND  `dbdate2` < '$mydate2'";
echo "<h2>Search Results</h2><ol>";
while($data_fetch=mysql_fetch_assoc($query_for_result))
{
    echo "<li>";
    echo substr($data_fetch[$db_tb_atr_name], 0,160);
    echo "&nbsp&nbsp";
    echo substr($data_fetch[$db_tb_atr_name2], 0,160);
    echo "&nbsp&nbsp";
    echo substr($data_fetch[$db_tb_atr_name3], 0,160);
    echo "</li><hr/>";
}
echo "</ol>";

And this is the response from the server.

HTTP Error 500 (Internal Server Error): An unexpected condition was encountered while the server was attempting to fulfill the request

osyrys14 · March 28, 2013

Does anyone know what's making this not work? I've looked at it so much I'm getting more confused. Any help is greatly appreciated.

akphidelt2007 · March 29, 2013

Does anyone know what's making this not work? I've looked at it so much I'm getting more confused. Any help is greatly appreciated.

Well one thing, you forgot the parenthesis ending the mysql_query()...

$query_for_result=mysql_query("SELECT * FROM $db_tb_name WHERE

`$db_tb_atr_name` LIKE '%$query%'
AND `dbdate1` > '$mydate1' AND `dbdate2` < '$mydate2'";

osyrys14 · March 29, 2013

OK, Now it's working, but no results.

akphidelt2007 · March 29, 2013

OK, Now it's working, but no results.

What I always do when there is no results is to echo out the query to see exactly what you are generating...

$query = "SELECT * FROM $db_tb_name WHERE
`$db_tb_atr_name` LIKE '%$query%'
AND `dbdate1` > '$mydate1' AND `dbdate2` < '$mydate2'";
 
echo $query;

Post what that says.

osyrys14 · March 29, 2013

Nothing shows.

akphidelt2007 · March 29, 2013

Nothing shows.

Post what you are doing that is preventing you from echoing out a variable.

osyrys14 · March 29, 2013

Here's the whole file, top to bottom...

<!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">
<head>
    <title>Search</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>


<form method="get" action="">
<label>Account ID: </label><input type="text" name="query" /></br>
<label>Begin Date:</label>
<label>Day:</label>
<select name="day">
<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">18</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select>
<label>Month:</label>
<select name="month">
<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>
</SELECT>
<label>Year:</label>
<select name="year">
<option value="2011">2011</option>
<option value="2012">2012</option>
<option value="2013">2013</option>
</SELECT></br>
<label>End Date</label>
<label>Day:</label>
<select name="day2">
<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">18</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select>
<label>Month:</label>
<select name="month2">
<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>
</SELECT>
<label>Year:</label>
<select name="year2">
<option value="2011">2011</option>
<option value="2012">2012</option>
<option value="2013">2013</option>
</SELECT></br>


<input type="submit" name="submit" value="Start Search" />
<input type="reset" value="Reset"
</form>
 
<?php

 
if(isset($_GET['submit'])){
 
$db_host="localhost";
$db_username="myusername";
$db_password="mypasswd";
$db_name="cdrdata";
$db_tb_name="cdr";
$db_tb_atr_space='  ';
$db_tb_atr_name="8";
$db_tb_atr_name2="6";
$db_tb_atr_name3="5";
 

 
mysql_connect("$db_host","$db_username","$db_password");
mysql_select_db("$db_name");
 
$query=mysql_real_escape_string($_GET['query']);
$mydate1 = sprintf('%4d-%02d-%02d', 
    intval($_GET['year']), intval($_GET['month']), intval($_GET['day']));
$mydate2 = sprintf('%4d-%02d-%02d', 
    intval($_GET['year2']), intval($_GET['month2']), intval($_GET['day2']));

 
$mydate1 = sprintf('%4d-%02d-%02d', 
    intval($_GET['year']), intval($_GET['month']), intval($_GET['day']));
$mydate2 = sprintf('%4d-%02d-%02d', 
    intval($_GET['year2']), intval($_GET['month2']), intval($_GET['day2']));

$sql = "SELECT *
        FROM $db_tb_name
        WHERE `$db_tb_atr_name` LIKE '%$query%'
        AND `dbdate1` > '$mydate1' AND  `dbdate2` < '$mydate2'";
echo $query;
$query_for_result=mysql_query($sql); 

echo "<h2>Search Results</h2><ol>";
echo $query;
while($data_fetch=mysql_fetch_assoc($query_for_result))
{
    echo "<li>";
    echo substr($data_fetch[$db_tb_atr_name], 0,160);
    echo "&nbsp&nbsp";
    echo substr($data_fetch[$db_tb_atr_name2], 0,160);
    echo "&nbsp&nbsp";
    echo substr($data_fetch[$db_tb_atr_name3], 0,160);
    echo "</li><hr/>";
}
echo "</ol>";
 
mysql_close();
}
?>

</body>
</html>

akphidelt2007 · March 29, 2013

Change echo $query to echo $sql

osyrys14 · March 29, 2013

Ahhh, that gives me light on it. I appreciate it!!

osyrys14 · March 29, 2013

I now realize I'm doing this whole request wrong. I don't need to compare 2 different date fields, I need that query to show all of the dates between the two put in by the user and compare to the SAME DB column...

akphidelt2007 · March 29, 2013

I now realize I'm doing this whole request wrong. I don't need to compare 2 different date fields, I need that query to show all of the dates between the two put in by the user and compare to the SAME DB column...

Just play around with it. Always debug your queries and your request variables... do a lot of print_r($_POST), print_r($_GET) to figure out what the actual values are. And then if you can't figure it out, come back and ask questions.

Sign In

Search by date

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information