leoarce
Reputation: 549
How can I stop certain column data from showing if it's not accurate to the original query?
Can someone help me with the post and query and echos? Records have multiple different dates, and I want to do a search where any of the 6 different dates is between 2 different dates. After i search from form and the post happens, this correctly displays all the different records where one of the 6 dates is in between the 2 dates picked in form. But it shows all the dates of the record. I only want it to show the date(s) that are actually in between the 2 dates picked.
<?php
if (isset($_POST['search'])) {
$fromdate = mysql_real_escape_string(htmlspecialchars($_POST['fromdate']));
$todate = mysql_real_escape_string(htmlspecialchars($_POST['todate']));
echo "<h2>Search Result</h2>";
echo "<p><strong>From:</strong> ".$fromdate." <strong>To:</strong> ".$todate."</p>"; //test
$query = mysql_query("
SELECT *
FROM jobs j
WHERE dropdate1 BETWEEN '$fromdate' AND '$todate'
OR dropdate2 BETWEEN '$fromdate' AND '$todate'
OR dropdate3 BETWEEN '$fromdate' AND '$todate'
OR dropdate4 BETWEEN '$fromdate' AND '$todate'
OR dropdate5 BETWEEN '$fromdate' AND '$todate'
OR dropdate6 BETWEEN '$fromdate' AND '$todate';"
);
while($row = mysql_fetch_array($query)) {
$dropdate1 = $row['dropdate1'];
$dropdate2 = $row['dropdate2'];
$dropdate3 = $row['dropdate3'];
$dropdate4 = $row['dropdate4'];
$dropdate5 = $row['dropdate5'];
$dropdate6 = $row['dropdate6'];
echo "<strong>".$row['id'].":</strong> ".$row['clientname']."<br>";
if ($dropdate1 !== "") {
echo "<strong>Drop 1:</strong> ".$row['dropdate1']."<br>";
}
if ($dropdate2 !== "") {
echo "<strong>Drop 2:</strong> ".$row['dropdate2']."<br>";
}
if ($dropdate3 !== "") {
echo "<strong>Drop 3:</strong> ".$row['dropdate3']."<br>";
}
if ($dropdate4 !== "") {
echo "<strong>Drop 4:</strong> ".$row['dropdate4']."<br>";
}
if ($dropdate5 !== "") {
echo "<strong>Drop 5:</strong> ".$row['dropdate5']."<br>";
}
if ($dropdate6 !== "") {
echo "<strong>Drop 6:</strong> ".$row['dropdate6']."<br>";
}
echo "<br>";
}
}
?>
Here are some sample results.
Search Result
From: 04-01-2016 To: 04-08-2016
1: blah1
Drop 1: 04-03-2015
2: blah2
Drop 1: 03-17-2015
Drop 2: 03-23-2015
Drop 3: 03-30-2015
Drop 4: 04-06-2015
3: blah3
Drop 1: 04-05-2015
Drop 2: 04-05-2015
4: blah4
Drop 1: 03-31-2015
Drop 2: 04-07-2015
5: blah5
Drop 1: 04-05-2015
So the if statements i have there are good for not showing the dates that are empty, but now i want to go a step further and not show the dates that are not between the 2 search dates.
This is what I want the search result to look like:
Search Result
From: 04-01-2016 To: 04-08-2016
1: blah1
Drop 1: 04-03-2015
2: blah2
Drop 4: 04-06-2015
3: blah3
Drop 1: 04-05-2015
Drop 2: 04-05-2015
4: blah4
Drop 2: 04-07-2015
5: blah5
Drop 1: 04-05-2015
Upvotes: 0
Views: 35
Answers (2)
leoarce
Reputation: 549
Another modification. I think this one works properly.
<?php
if (isset($_POST['search'])) {
$fromdate = mysql_real_escape_string(htmlspecialchars($_POST['fromdate']));
$todate = mysql_real_escape_string(htmlspecialchars($_POST['todate']));
list($fmonth, $fday, $fyear) = split('[/.-]', $fromdate);
list($tmonth, $tday, $tyear) = split('[/.-]', $todate);
$f = mysql_real_escape_string("$fmonth-$fday-$fyear");
$t = mysql_real_escape_string("$tmonth-$tday-$tyear");
if ($fromdate == "" || $todate == "") {
echo "<div class='alert alert-danger match-lh30'>From or To cannot be empty! Try again.</div>";
}
else {
echo "<h2>Search Result</h2>";
echo "<p><strong>From:</strong> ".$f." <strong>To:</strong> ".$t."</p>";
$query = mysql_query("SELECT id, clientname, dropdate1, dropdate2, dropdate3, dropdate4, dropdate5, dropdate6 FROM `jobs` WHERE
STR_TO_DATE(`dropdate1`,'%m-%d-%Y') BETWEEN STR_TO_DATE('".$f."', '%m-%d-%Y') AND STR_TO_DATE('".$t."', '%m-%d-%Y')
OR STR_TO_DATE(`dropdate2`,'%m-%d-%Y') BETWEEN STR_TO_DATE('".$f."', '%m-%d-%Y') AND STR_TO_DATE('".$t."', '%m-%d-%Y')
OR STR_TO_DATE(`dropdate3`,'%m-%d-%Y') BETWEEN STR_TO_DATE('".$f."', '%m-%d-%Y') AND STR_TO_DATE('".$t."', '%m-%d-%Y')
OR STR_TO_DATE(`dropdate4`,'%m-%d-%Y') BETWEEN STR_TO_DATE('".$f."', '%m-%d-%Y') AND STR_TO_DATE('".$t."', '%m-%d-%Y')
OR STR_TO_DATE(`dropdate5`,'%m-%d-%Y') BETWEEN STR_TO_DATE('".$f."', '%m-%d-%Y') AND STR_TO_DATE('".$t."', '%m-%d-%Y')
OR STR_TO_DATE(`dropdate6`,'%m-%d-%Y') BETWEEN STR_TO_DATE('".$f."', '%m-%d-%Y') AND STR_TO_DATE('".$t."', '%m-%d-%Y')");
$num_rows = mysql_num_rows($query);
echo "<p><strong>".$num_rows."</strong> records found.</p>";
while($row = mysql_fetch_array($query)) {
$id = $row['id'];
$clientname = $row['clientname'];
$dropdate1 = $row['dropdate1'];
$dropdate2 = $row['dropdate2'];
$dropdate3 = $row['dropdate3'];
$dropdate4 = $row['dropdate4'];
$dropdate5 = $row['dropdate5'];
$dropdate6 = $row['dropdate6'];
if (($dropdate1 !== "" && $dropdate1 >= $f && $dropdate1 <= $t) ||
($dropdate2 !== "" && $dropdate2 >= $f && $dropdate2 <= $t) ||
($dropdate3 !== "" && $dropdate3 >= $f && $dropdate3 <= $t) ||
($dropdate4 !== "" && $dropdate4 >= $f && $dropdate4 <= $t) ||
($dropdate5 !== "" && $dropdate5 >= $f && $dropdate5 <= $t) ||
($dropdate6 !== "" && $dropdate6 >= $f && $dropdate6 <= $t)) {
echo "<div class='row-fluid no-bottom-margin somepaddingtop somepaddingbottom' style='border-bottom: 1px solid #ccc;'>";
echo "<div class='span4' style='min-height: 0;'>";
echo "<strong>".$id.":</strong> ";
echo $clientname;
echo "</div>";
echo "<div class='span8' style='min-height: 0;'>";
if ($dropdate1 !== "" && $dropdate1 >= $f && $dropdate1 <= $t) {
echo "<strong>Drop 1:</strong> ".$dropdate1." ";
}
if ($dropdate2 !== "" && $dropdate2 >= $f && $dropdate2 <= $t) {
echo "<strong>Drop 2:</strong> ".$dropdate2." ";
}
if ($dropdate3 !== "" && $dropdate3 >= $f && $dropdate3 <= $t) {
echo "<strong>Drop 3:</strong> ".$dropdate3." ";
}
if ($dropdate4 !== "" && $dropdate4 >= $f && $dropdate4 <= $t) {
echo "<strong>Drop 4:</strong> ".$dropdate4." ";
}
if ($dropdate5 !== "" && $dropdate5 >= $f && $dropdate5 <= $t) {
echo "<strong>Drop 5:</strong> ".$dropdate5." ";
}
if ($dropdate6 !== "" && $dropdate6 >= $f && $dropdate6 <= $t) {
echo "<strong>Drop 6:</strong> ".$dropdate6." ";
}
echo "</div>";
echo "</div>";
}
}
}
}
?>
Upvotes: 0
lshas
Reputation: 1731
Try this:
<?php
if (isset($_POST['search'])) {
$fromdate = mysql_real_escape_string(htmlspecialchars($_POST['fromdate']));
$todate = mysql_real_escape_string(htmlspecialchars($_POST['todate']));
echo "<h2>Search Result</h2>";
echo "<p><strong>From:</strong> ".$fromdate." <strong>To:</strong> ".$todate."</p>"; //test
$query = mysql_query("SELECT * FROM jobs WHERE dropdate1 BETWEEN '".$fromdate."' AND '".$todate."' OR dropdate2 BETWEEN '".$fromdate."' AND '".$todate."' OR dropdate3 BETWEEN '".$fromdate."' AND '".$todate."' OR dropdate4 BETWEEN '".$fromdate."' AND '".$todate."' OR dropdate5 BETWEEN '".$fromdate."' AND '".$todate."' OR dropdate6 BETWEEN '".$fromdate."' AND '".$todate."'");
$fromDate1 = new DateTime($_POST['fromdate']);
$toDate1 = new DateTime($_POST['todate']);
while($row = mysql_fetch_array($query)) {
$dropdate1 = new DateTime($row['dropdate1']);
$dropdate2 = new DateTime($row['dropdate2']);
$dropdate3 = new DateTime($row['dropdate3']);
$dropdate4 = new DateTime($row['dropdate4']);
$dropdate5 = new DateTime($row['dropdate5']);
$dropdate6 = new DateTime($row['dropdate6']);
echo "<strong>".$row['id'].":</strong> ".$row['clientname']."<br>";
if ($dropdate1 !== "" && $dropdate1 >= $fromDate1 && $dropDate1 <= $toDate1) {
echo "<strong>Drop 1:</strong> ".$row['dropdate1']."<br>";
}
if ($dropdate2 !== "" && $dropdate2 >= $fromDate1 && $dropDate1 <= $toDate1) {
echo "<strong>Drop 2:</strong> ".$row['dropdate2']."<br>";
}
if ($dropdate3 !== "" && $dropdate3 >= $fromDate1 && $dropDate1 <= $toDate1) {
echo "<strong>Drop 3:</strong> ".$row['dropdate3']."<br>";
}
if ($dropdate4 !== "" && $dropdate4 >= $fromDate1 && $dropDate1 <= $toDate1) {
echo "<strong>Drop 4:</strong> ".$row['dropdate4']."<br>";
}
if ($dropdate5 !== "" && $dropdate5 >= $fromDate1 && $dropDate1 <= $toDate1) {
echo "<strong>Drop 5:</strong> ".$row['dropdate5']."<br>";
}
if ($dropdate6 !== "" && $dropdate6 >= $fromDate1 && $dropDate1 <= $toDate1) {
echo "<strong>Drop 6:</strong> ".$row['dropdate6']."<br>";
}
echo "<br>";
}
}
This should work.
Upvotes: 1
Related Questions
- Reference Guide: What does this symbol mean in PHP? (PHP Syntax)
- How can I find all the tables in MySQL with specific column names in them?
- How do I add indexes to MySQL tables?
- How do I change the data type for a column in MySQL?
- Check if Vehicle is available in PHP
- Mysql: Select all data between two dates
- ORA-00907: missing right parenthesis with decode and case in oracle sql
- Retrieve a record with fromdate and todate by passing single date in MySQL
- Mysql query to retrieve record using between for two dates