Jump to content

Search by date

osyrys14

By osyrys14
in PHP Coding Help

Recommended Posts

osyrys14 Member

osyrys14

Posted
Posted

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>

 

 

Link to comment
https://forums.phpfreaks.com/topic/276241-search-by-date/
Share on other sites
osyrys14 Member

osyrys14

Posted
  • Author
Posted

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.  
Link to comment
https://forums.phpfreaks.com/topic/276241-search-by-date/#findComment-1421535
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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);
Link to comment
https://forums.phpfreaks.com/topic/276241-search-by-date/#findComment-1421538
Share on other sites
osyrys14 Member

osyrys14

Posted
  • Author
Posted

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
Link to comment
https://forums.phpfreaks.com/topic/276241-search-by-date/#findComment-1421546
Share on other sites
akphidelt2007 Member

akphidelt2007

Posted
Posted
  On 3/28/2013 at 11:58 PM, osyrys14 said:

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'";
Link to comment
https://forums.phpfreaks.com/topic/276241-search-by-date/#findComment-1421717
Share on other sites
akphidelt2007 Member

akphidelt2007

Posted
Posted
  On 3/29/2013 at 12:19 AM, osyrys14 said:

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.

Link to comment
https://forums.phpfreaks.com/topic/276241-search-by-date/#findComment-1421722
Share on other sites
osyrys14 Member

osyrys14

Posted
  • Author
Posted

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>
Link to comment
https://forums.phpfreaks.com/topic/276241-search-by-date/#findComment-1421733
Share on other sites
akphidelt2007 Member

akphidelt2007

Posted
Posted
  On 3/29/2013 at 2:16 AM, osyrys14 said:

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.

Link to comment
https://forums.phpfreaks.com/topic/276241-search-by-date/#findComment-1421748
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.