Jump to content

Search by date

osyrys14

By osyrys14
in PHP Coding Help

 Share

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 (edited)

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 by osyrys14
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

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

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

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
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
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.